4

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.

To prepare the test, I created a table

CREATE TABLE `test`.`client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

and inserted 1,000,000 rows into the table.


Now I will begin the test.

In MySQL client #1, I deleted all the rows:

mysql #1> delete from client;

While the delete is still executing, in another MySQL client #2, I try to select a row.

mysql #2> select * from client where id=1;
+---------+------+
| id      | name |
+---------+------+
| 1       | joe  |
+---------+------+
1 row in set (0.00 sec)

The result is displayed instantly, the select was not blocked.


Next I will try another test. I insert 1,000,000 rows into the table.

In MySQL client #1, I update all the rows:

mysql #1> update client set name='Bill';

While the update is still executing, in another MySQL client #2, I try to select a row.

mysql #2> select * from client where id=100;
+-----+------+
| id  | name |
+-----+------+
| 100 | joe  |
+-----+------+
1 row in set (0.00 sec)

mysql #2> select * from client where id=1000;
+------+------+
| id   | name |
+------+------+
| 1000 | joe  |
+------+------+
1 row in set (0.00 sec)

The result is displayed instantly, the select was not blocked.

So now my question, how can I demonstrate a SQL select statement getting blocked by another simple SQL delete or or update statement, using InnoDB tables? Or does MySQL never have any blocking issues?

PS I am not trying to simulate two processes deadlocking each other, just one large update or insert blocking a select.

2

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

  • SELECT ... FOR UPDATE, then try SELECT against those rows
  • SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
  • See the MySQL Documentation

Give it a Try !!!

  • I tried to get a blocking read by using BEGIN; SELECT ... FOR UPDATE in Process 1 and a SELECT * for the same table in Process 2. It doesn't seem to block, and instead returns immediately with the data that existed before the transaction began. Is that correct? – you786 Jun 13 '16 at 17:35
  • @you786 Yes, that would be correct because the default transaction isolation level for InnoDB is REPEATABLE READ, no matter how many SELECT * queries run with a live SELECT ...FOR UPDATE. – RolandoMySQLDBA Jun 13 '16 at 19:27
0

Is it possible not to view that row at all by session 2. I mean when session 1 is updating, is possible that session 2 does not access it in select as well?

  • You should not ask follow-up questions in an answer to a question. Create a new question with a link to this one instead. – Lennart Aug 9 '17 at 16:09
  • Not sure what you mean - are you saying that the reason the second statement isn't blocking is that it's ignoring locked data? That would be bad. On the other hand, if you're saying the second statement may not be blocked because it was able to get (and release) it's lock on that one row without interfering with the update, that might be possible. – RDFozz Aug 9 '17 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.