… wait timeout exceeded; try restarting transaction root@localhost:test 14:38:03>insert into t values(23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:44:37>insert into t values(31); ERROR 1205 (HY000): Lock wait timeout exceeded… t values(33); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Here I tried to insert a value out of [21…

… START TRANSACTION; transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START TRANSACTION… Lock wait timeout exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(29); ERROR 1205 (HY000): Lock wait timeout …

… the gap, improving the concurrence. You would avoid all “Lock wait timeout exceeded” messages in my example. So only row with value…> commit; session2> commit; session1> select * from t; +——+ | i | +——+ | 21 | | 26 | | 30 | +——+ No locks on the gap, so no wait timeout and more concurrence.

Peter, The first example is READ COMMITTED. I‘ve changed the isolation level to get that example of a phantom read. With REPEATABLE READ the rows and gaps gets locked and then you get the lock wait timeout.

…, What isolation mode do you use for your first example ? I just tried it with PS 5.5 and default (REPEATABLE…) mysql> INSERT INTO t VALUES(26); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> show variables like “%isol%”;