Debian Codegraph @

mysql-test/suite/innodb/r/undetected_deadlock.result

raw
1 #
2 # Bug #29882690 UNDETECTED DEADLOCK WITH GAP AND INSERT INTENTION LOCKS
3 # IS POSSIBLE
4 #
5 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
6 SET @@global.innodb_lock_wait_timeout = 20;
7 CREATE TABLE connections (
8 trx_mysql_thread_id BIGINT PRIMARY KEY,
9 name VARCHAR(200)
10 ) ENGINE=InnoDB;
11 CREATE PROCEDURE show_locks ()
12 BEGIN
13 # this is needed because INFORMATION_SCHEMA.INNODB_TRX is refreshed with debounce 100ms
14 SELECT SLEEP(1);
15 SELECT
16 connections.name, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
17 FROM connections
18 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id)
19 JOIN performance_schema.data_locks ON (trx_id=engine_transaction_id)
20 ORDER BY 1,2,3,4,5,6,7;
21 END //
22 CREATE PROCEDURE register_connection (name VARCHAR(200))
23 BEGIN
24 INSERT INTO connections VALUES (CONNECTION_ID(), name);
25 END //
26 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, val VARCHAR(200));
27 INSERT INTO t1 (id,val) VALUES (1, "a"), (3, "c"), (4, "d");
28 CALL register_connection("con1");
29 BEGIN;
30 CALL register_connection("con2");
31 BEGIN;
32 CALL register_connection("con3");
33 BEGIN;
34 # 1. con1 obtains a GRANTED LOCK_GAP on gap before row id=3
35 SELECT * FROM t1 WHERE id=2 FOR UPDATE;
36 id val
37 CALL show_locks();
38 SLEEP(1)
39 0
40 name object_name index_name lock_type lock_mode lock_status lock_data
41 con1 t1 NULL TABLE IX GRANTED NULL
42 con1 t1 PRIMARY RECORD X,GAP GRANTED 3
43 # 2. con2 obtains a GRANTED LOCK_X on row id=4
44 SELECT * FROM t1 WHERE id=4 FOR UPDATE;
45 id val
46 4 d
47 CALL show_locks();
48 SLEEP(1)
49 0
50 name object_name index_name lock_type lock_mode lock_status lock_data
51 con1 t1 NULL TABLE IX GRANTED NULL
52 con1 t1 PRIMARY RECORD X,GAP GRANTED 3
53 con2 t1 NULL TABLE IX GRANTED NULL
54 con2 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
55 # 3. con2 enqueues a waiting LOCK_INSERT_INTENTION into the gap
56 # before id=3
57 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
58 INSERT INTO t1 (id, val) VALUES (2, "b");
59 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
60 CALL show_locks();
61 SLEEP(1)
62 0
63 name object_name index_name lock_type lock_mode lock_status lock_data
64 con1 t1 NULL TABLE IX GRANTED NULL
65 con1 t1 PRIMARY RECORD X,GAP GRANTED 3
66 con2 t1 NULL TABLE IX GRANTED NULL
67 con2 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 3
68 con2 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
69 # 4. con3 obtains a GRANTED LOCK_GAP on gap before row id=3
70 SELECT * FROM t1 WHERE id=2 FOR UPDATE;
71 id val
72 CALL show_locks();
73 SLEEP(1)
74 0
75 name object_name index_name lock_type lock_mode lock_status lock_data
76 con1 t1 NULL TABLE IX GRANTED NULL
77 con1 t1 PRIMARY RECORD X,GAP GRANTED 3
78 con2 t1 NULL TABLE IX GRANTED NULL
79 con2 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 3
80 con2 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
81 con3 t1 NULL TABLE IX GRANTED NULL
82 con3 t1 PRIMARY RECORD X,GAP GRANTED 3
83 # 5. con3 enqueues a waiting lock on same row as in step 2.
84 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
85 SELECT * FROM t1 WHERE id=4 FOR UPDATE;
86 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
87 CALL show_locks();
88 SLEEP(1)
89 0
90 name object_name index_name lock_type lock_mode lock_status lock_data
91 con1 t1 NULL TABLE IX GRANTED NULL
92 con1 t1 PRIMARY RECORD X,GAP GRANTED 3
93 con2 t1 NULL TABLE IX GRANTED NULL
94 con2 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 3
95 con2 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
96 con3 t1 NULL TABLE IX GRANTED NULL
97 con3 t1 PRIMARY RECORD X,GAP GRANTED 3
98 con3 t1 PRIMARY RECORD X,REC_NOT_GAP WAITING 4
99 # 6. con1 resizes the row with id=3 ("c"->"cccccccccccccccc") causing
100 # locks to be moved
101 UPDATE t1 SET val="cccccccccccccccc" WHERE id=3;
102 # 7. it depends on implementation of con3's LOCK_GAP lands is in front
103 # of con2's waiting II or not
104 # 8. con1 commits
105 COMMIT;
106 ROLLBACK;
107 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
108 ROLLBACK;
109 DROP TABLE t1;
110 DROP PROCEDURE show_locks;
111 DROP PROCEDURE register_connection;
112 DROP TABLE connections;
113 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved ;