How can I make a select statement get blocked?

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.

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

• You can have SELECTs get deadlocked by UPDATEs and DELETEs

• Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

• You can have UPDATEs and DELETEs blocked by SELECTs

• How are DB locks tied to connections and sessions?

• Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

• You can just lock the table before the SELECT and unlock it afterwards

• LOCK TABLES table READ;

• SELECT ... ;

• UNLOCK TABLES;

• You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

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

StackExchange Database Administrators Q#41865, answer score: 4