1
2-----------------------------------------------------------------------
3- Locking issues case 5:
4-   Deleting primary key
5-   using REPEATABLE READ transaction isolation level
6-----------------------------------------------------------------------
7DROP TABLE IF EXISTS t0;
8CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT);
9Inserting 200,000 rows
10UPDATE t0 SET value=100 WHERE id=190000;
11connect  con1,localhost,root,,;
12connect  con2,localhost,root,,;
13connection con1;
14SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
15BEGIN;
16SELECT * FROM t0 WHERE value > 0 FOR UPDATE;
17connection con2;
18SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
19BEGIN;
20DELETE FROM t0 WHERE id=190000;
21COMMIT;
22connection con1;
23ERROR: 1213
24COMMIT;
25connection default;
26disconnect con1;
27disconnect con2;
28DROP TABLE t0;
29