Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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