Debian Codegraph @

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

raw
1 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
2 SET @@global.innodb_lock_wait_timeout = 20;
3 CREATE TABLE connections (
4 trx_mysql_thread_id BIGINT PRIMARY KEY,
5 name VARCHAR(200)
6 ) ENGINE=InnoDB;
7 CREATE PROCEDURE show_locks ()
8 BEGIN
9 # this is needed because INFORMATION_SCHEMA.INNODB_TRX is refreshed with debounce 100ms
10 SELECT SLEEP(1);
11 SELECT
12 connections.name, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
13 FROM connections
14 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id)
15 JOIN performance_schema.data_locks ON (trx_id=engine_transaction_id)
16 ORDER BY 1,2,3,4,5,6,7;
17 END //
18 CREATE PROCEDURE register_connection (name VARCHAR(200))
19 BEGIN
20 INSERT INTO connections VALUES (CONNECTION_ID(), name);
21 END //
22 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, val CHAR(200));
23 INSERT INTO t1 (id,val) VALUES (1, "a"), (3, "c"), (1998,"x"), (2000, "z");
24 CALL register_connection("con1");
25 BEGIN;
26 SELECT * FROM t1 WHERE id<=3 FOR SHARE;
27 id val
28 1 a
29 3 c
30 CALL register_connection("con2");
31 BEGIN;
32 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL con2_will_wait';
33 INSERT INTO t1 VALUES (2,"b");
34 SET DEBUG_SYNC='now WAIT_FOR con2_will_wait';
35 CALL register_connection("con3");
36 BEGIN;
37 SELECT * FROM t1 WHERE id=2000 FOR UPDATE;
38 id val
39 2000 z
40 CALL register_connection("con4");
41 BEGIN;
42 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL con4_will_wait';
43 SELECT * FROM t1 WHERE 1999<=id FOR SHARE;
44 SET DEBUG_SYNC='now WAIT_FOR con4_will_wait';
45 CALL show_locks();
46 SLEEP(1)
47 0
48 name object_name index_name lock_type lock_mode lock_status lock_data
49 con1 t1 NULL TABLE IS GRANTED NULL
50 con1 t1 PRIMARY RECORD S GRANTED 1
51 con1 t1 PRIMARY RECORD S GRANTED 3
52 con2 t1 NULL TABLE IX GRANTED NULL
53 con2 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 3
54 con3 t1 NULL TABLE IX GRANTED NULL
55 con3 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 2000
56 con4 t1 NULL TABLE IS GRANTED NULL
57 con4 t1 PRIMARY RECORD S WAITING 2000
58 INSERT INTO t1 (id,val) VALUES (10,"x");
59 INSERT INTO t1 (id,val) VALUES (11,"x");
60 INSERT INTO t1 (id,val) VALUES (12,"x");
61 INSERT INTO t1 (id,val) VALUES (13,"x");
62 INSERT INTO t1 (id,val) VALUES (14,"x");
63 INSERT INTO t1 (id,val) VALUES (15,"x");
64 INSERT INTO t1 (id,val) VALUES (16,"x");
65 INSERT INTO t1 (id,val) VALUES (17,"x");
66 INSERT INTO t1 (id,val) VALUES (18,"x");
67 INSERT INTO t1 (id,val) VALUES (19,"x");
68 INSERT INTO t1 (id,val) VALUES (20,"x");
69 INSERT INTO t1 (id,val) VALUES (21,"x");
70 INSERT INTO t1 (id,val) VALUES (22,"x");
71 INSERT INTO t1 (id,val) VALUES (23,"x");
72 INSERT INTO t1 (id,val) VALUES (24,"x");
73 INSERT INTO t1 (id,val) VALUES (25,"x");
74 INSERT INTO t1 (id,val) VALUES (26,"x");
75 INSERT INTO t1 (id,val) VALUES (27,"x");
76 INSERT INTO t1 (id,val) VALUES (28,"x");
77 INSERT INTO t1 (id,val) VALUES (29,"x");
78 INSERT INTO t1 (id,val) VALUES (30,"x");
79 INSERT INTO t1 (id,val) VALUES (31,"x");
80 INSERT INTO t1 (id,val) VALUES (32,"x");
81 INSERT INTO t1 (id,val) VALUES (33,"x");
82 INSERT INTO t1 (id,val) VALUES (34,"x");
83 INSERT INTO t1 (id,val) VALUES (35,"x");
84 INSERT INTO t1 (id,val) VALUES (36,"x");
85 INSERT INTO t1 (id,val) VALUES (37,"x");
86 INSERT INTO t1 (id,val) VALUES (38,"x");
87 INSERT INTO t1 (id,val) VALUES (39,"x");
88 INSERT INTO t1 (id,val) VALUES (40,"x");
89 INSERT INTO t1 (id,val) VALUES (41,"x");
90 INSERT INTO t1 (id,val) VALUES (42,"x");
91 INSERT INTO t1 (id,val) VALUES (43,"x");
92 INSERT INTO t1 (id,val) VALUES (44,"x");
93 INSERT INTO t1 (id,val) VALUES (45,"x");
94 INSERT INTO t1 (id,val) VALUES (46,"x");
95 INSERT INTO t1 (id,val) VALUES (47,"x");
96 INSERT INTO t1 (id,val) VALUES (48,"x");
97 INSERT INTO t1 (id,val) VALUES (49,"x");
98 INSERT INTO t1 (id,val) VALUES (50,"x");
99 INSERT INTO t1 (id,val) VALUES (51,"x");
100 INSERT INTO t1 (id,val) VALUES (52,"x");
101 INSERT INTO t1 (id,val) VALUES (53,"x");
102 INSERT INTO t1 (id,val) VALUES (54,"x");
103 INSERT INTO t1 (id,val) VALUES (55,"x");
104 INSERT INTO t1 (id,val) VALUES (56,"x");
105 INSERT INTO t1 (id,val) VALUES (57,"x");
106 INSERT INTO t1 (id,val) VALUES (58,"x");
107 INSERT INTO t1 (id,val) VALUES (59,"x");
108 INSERT INTO t1 (id,val) VALUES (60,"x");
109 INSERT INTO t1 (id,val) VALUES (61,"x");
110 INSERT INTO t1 (id,val) VALUES (62,"x");
111 INSERT INTO t1 (id,val) VALUES (63,"x");
112 INSERT INTO t1 (id,val) VALUES (64,"x");
113 INSERT INTO t1 (id,val) VALUES (65,"x");
114 INSERT INTO t1 (id,val) VALUES (66,"x");
115 INSERT INTO t1 (id,val) VALUES (67,"x");
116 INSERT INTO t1 (id,val) VALUES (68,"x");
117 INSERT INTO t1 (id,val) VALUES (69,"x");
118 INSERT INTO t1 (id,val) VALUES (70,"x");
119 INSERT INTO t1 (id,val) VALUES (71,"x");
120 INSERT INTO t1 (id,val) VALUES (72,"x");
121 INSERT INTO t1 (id,val) VALUES (73,"x");
122 INSERT INTO t1 (id,val) VALUES (74,"x");
123 INSERT INTO t1 (id,val) VALUES (75,"x");
124 INSERT INTO t1 (id,val) VALUES (76,"x");
125 INSERT INTO t1 (id,val) VALUES (77,"x");
126 INSERT INTO t1 (id,val) VALUES (78,"x");
127 INSERT INTO t1 (id,val) VALUES (79,"x");
128 INSERT INTO t1 (id,val) VALUES (80,"x");
129 INSERT INTO t1 (id,val) VALUES (81,"x");
130 INSERT INTO t1 (id,val) VALUES (82,"x");
131 INSERT INTO t1 (id,val) VALUES (83,"x");
132 INSERT INTO t1 (id,val) VALUES (84,"x");
133 INSERT INTO t1 (id,val) VALUES (85,"x");
134 INSERT INTO t1 (id,val) VALUES (86,"x");
135 INSERT INTO t1 (id,val) VALUES (87,"x");
136 INSERT INTO t1 (id,val) VALUES (88,"x");
137 INSERT INTO t1 (id,val) VALUES (89,"x");
138 INSERT INTO t1 (id,val) VALUES (90,"x");
139 INSERT INTO t1 (id,val) VALUES (91,"x");
140 INSERT INTO t1 (id,val) VALUES (92,"x");
141 INSERT INTO t1 (id,val) VALUES (93,"x");
142 INSERT INTO t1 (id,val) VALUES (94,"x");
143 INSERT INTO t1 (id,val) VALUES (95,"x");
144 INSERT INTO t1 (id,val) VALUES (96,"x");
145 INSERT INTO t1 (id,val) VALUES (97,"x");
146 INSERT INTO t1 (id,val) VALUES (98,"x");
147 INSERT INTO t1 (id,val) VALUES (99,"x");
148 CALL show_locks();
149 SLEEP(1)
150 0
151 name object_name index_name lock_type lock_mode lock_status lock_data
152 con1 t1 NULL TABLE IS GRANTED NULL
153 con1 t1 PRIMARY RECORD S GRANTED 1
154 con1 t1 PRIMARY RECORD S GRANTED 3
155 con2 t1 NULL TABLE IX GRANTED NULL
156 con2 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 3
157 con3 t1 NULL TABLE IX GRANTED NULL
158 con3 t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 2000
159 con4 t1 NULL TABLE IS GRANTED NULL
160 con4 t1 PRIMARY RECORD S WAITING 2000
161 COMMIT;
162 id val
163 2000 z
164 SELECT * FROM t1 WHERE id<=3 FOR SHARE;
165 id val
166 1 a
167 3 c
168 CALL show_locks();
169 SLEEP(1)
170 0
171 name object_name index_name lock_type lock_mode lock_status lock_data
172 con1 t1 NULL TABLE IS GRANTED NULL
173 con1 t1 PRIMARY RECORD S GRANTED 1
174 con1 t1 PRIMARY RECORD S GRANTED 3
175 con2 t1 NULL TABLE IX GRANTED NULL
176 con2 t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 3
177 con4 t1 NULL TABLE IS GRANTED NULL
178 con4 t1 PRIMARY RECORD S GRANTED 1
179 con4 t1 PRIMARY RECORD S GRANTED 2000
180 con4 t1 PRIMARY RECORD S GRANTED 3
181 con4 t1 PRIMARY RECORD S GRANTED supremum pseudo-record
182 COMMIT;
183 COMMIT;
184 COMMIT;
185 DROP TABLE t1;
186 DROP PROCEDURE show_locks;
187 DROP PROCEDURE register_connection;
188 DROP TABLE connections;
189 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved ;