transactions - MySQL 'select for update' behaviour

Question

As per the MySql documentation, MySql supports Multiple granularity locking(MGL).

case-1

Opened terminal-1:

// connected to mysql

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, status from tracking_number limit 5 for update;
+----+--------+
| id | status |
+----+--------+
|  1 |      0 |
|  2 |      0 |
|  3 |      0 |
|  4 |      0 |
|  5 |      0 |
+----+--------+
5 rows in set (0.00 sec)
mysql> 

left it opened and opened terminal-2:

// connected to mysql

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, status from tracking_number limit 5 for update;

<!-- Hangs here. and after some time it says-->
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Though there are plenty of rows to retrieve, T2 waits until t1 completes.

case-2

Left terminal-1 as is.Now in terminal-2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

<!-- case 2.1 -->
mysql> select id, status from tracking_number where id=1;
+----+--------+
| id | status |
+----+--------+
|  1 |      0 |
+----+--------+
1 row in set (0.00 sec)

mysql> select id, status from tracking_number where id=2;
+----+--------+
| id | status |
+----+--------+
|  2 |      0 |
+----+--------+
1 row in set (0.00 sec)

<!-- case 2.2 -->
mysql> select * from tracking_number where id=2 for update;
<!-- Hangs here. and after some time -->
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1. But why in case 1, T2 waits for the same set of rows that T1 has locked?

2. Does it mean the unbounded select query (even with limint parameter. I have tried with different range also) blocks the entire table?

3. Is there any way to let transactions to lock independently without specifying the field of the record(i.e., without using where field=value)?
4. Generally (or as per Java concurrent locking), write lock is exclusive and read is not. In case 2.1, though the records are in write lock mode, how T2 can read the same records? Since this is allowed what is the point in locking it?
5. Case 2.2 is understood.

Opened a terminal and a transaction:

mysql> update tracking_number set status=4 where status=0 limit 5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Left it there and opened another terminal and transaction:

mysql> update tracking_number set status=5 where status=0 limit 5; 

T2 did not succeed until i committed (or rollback) T1.

1. Why is this behavior?

Answer 1

Let me go through your cases and explain how these locks work:

1 case

T1 wants to update some rows in your test table. This transaction puts IX lock on all table and X lock on the first 5 rows.

T2 wants to update some rows in your test table. This transaction puts IX (because IX compatible with IX) lock on all table and tries to first 5 rows but it can't do it because X is not compatible with X

So we are fine.

2.1 case

T1 wants to update some rows in your test table. This transaction put IX lock on all table and X lock on the first 5 rows.

T2 wants to select some rows from your test table. And it does not place any locks (because InnoDB provides non-locking reads)

2.1 case

T1 wants to update some rows in your test table. This transaction put IX lock on all table and X lock on the first 5 rows.

T2 wants to update (select for update)some rows from your test table. Place IS on the whole table and tries to get S lock on the row and fails because X and S are uncompatible.

---

Also always be aware of isolation level: different level cause different mechanism to free/acquire locks

Hope it helps