6

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.

Improve this question

1 Answer 1

Reset to default
4

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 !!!

Improve this answer
2
  • 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? Commented Jun 13, 2016 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. Commented Jun 13, 2016 at 19:27

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.