1
2-----------------------------------------------------------------------
3- Locking issues case 5:
4-   Deleting primary key
5-   using READ COMMITTED 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 READ COMMITTED;
15BEGIN;
16SELECT * FROM t0 WHERE value > 0 FOR UPDATE;
17connection con2;
18SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
19BEGIN;
20DELETE FROM t0 WHERE id=190000;
21COMMIT;
22connection con1;
23id	value
24ERROR: 0
25COMMIT;
26connection default;
27disconnect con1;
28disconnect con2;
29DROP TABLE t0;
30