1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
Bug #28176805 PERFORMANCE_SCHEMA.DATA_LOCKS.LOCK_MODE
DOES NOT RECOGNIZE LOCK_REC_NOT_GAP
CREATE TABLE t1 (id INT PRIMARY KEY) Engine=InnoDB;
INSERT INTO t1 VALUES (0),(10),(20),(30);
CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT, val INT) Engine=InnoDB;
BEGIN;
SELECT * FROM t1 WHERE id=10 FOR SHARE;
id
10
INSERT INTO t1 VALUES (5);
SELECT * FROM t1 WHERE id=25 FOR SHARE;
id
SELECT * FROM t1 WHERE id=22 FOR UPDATE;
id
SELECT * FROM t1 where id>=10 and id<=20 FOR SHARE;
id
10
20
SELECT * FROM t1 WHERE id>20 FOR UPDATE;
id
30
BEGIN;
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C2_will_wait';
INSERT INTO t1 VALUES (21);
SET DEBUG_SYNC = 'now WAIT_FOR C2_will_wait';
BEGIN;
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
Variable_name Value
innodb_autoinc_lock_mode 0
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C3_will_wait';
INSERT INTO t2 (val) SELECT id FROM t1 WHERE id>=0 AND id< 10;;
SET DEBUG_SYNC = 'now WAIT_FOR C3_will_wait';
BEGIN;
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C4_will_wait';
INSERT INTO t1 VALUES (35);
SET DEBUG_SYNC = 'now WAIT_FOR C4_will_wait';
SELECT
object_name,
index_name,
lock_type,
lock_mode,
lock_status,
lock_data
FROM performance_schema.data_locks
ORDER BY 1,2,3,4,5,6;
object_name index_name lock_type lock_mode lock_status lock_data
t1 NULL TABLE IS GRANTED NULL
t1 NULL TABLE IS GRANTED NULL
t1 NULL TABLE IX GRANTED NULL
t1 NULL TABLE IX GRANTED NULL
t1 NULL TABLE IX GRANTED NULL
t1 PRIMARY RECORD S GRANTED 20
t1 PRIMARY RECORD S WAITING 5
t1 PRIMARY RECORD S,GAP GRANTED 30
t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 0
t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 10
t1 PRIMARY RECORD X GRANTED 30
t1 PRIMARY RECORD X GRANTED supremum pseudo-record
t1 PRIMARY RECORD X,GAP GRANTED 30
t1 PRIMARY RECORD X,GAP,INSERT_INTENTION WAITING 30
t1 PRIMARY RECORD X,INSERT_INTENTION WAITING supremum pseudo-record
t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 5
t2 NULL TABLE AUTO_INC GRANTED NULL
t2 NULL TABLE IX GRANTED NULL
ROLLBACK;
ROLLBACK;
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
DROP TABLE t2;
|
