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
73
74
75
76
77
78
79
80
|
--source include/have_debug_sync.inc
--echo # Bug #28176910
--echo # PERFORMANCE_SCHEMA.DATA_LOCKS.OBJECT_INSTANCE_BEGIN
--echo # CHANGES DEPENDING ON ORDER
# This scenario is meant to create a situation in which a single
# transaction has two locks for the same record, which differ only
# in their mode (X vs X,REC_NOT_GAP) which historicaly caused a bug
# in that both of them had the same performance.data_locks.engine_lock_id.
# Having two rows with the same engline_lock_id caused visible problems:
# using SELECT...FROM data_locks ORDER BY ... caused one of the two rows
# to be reported twice and the other row not even once.
# There were also problems with data_lock_waits which should contain
# exactly one edge for each pair of conflicting locks, however having
# two rows with the same id in data_locks could lead to strange results
# when joining data_locks with data_lock_waits.
# Finally, the information_schema.innodb_trx.trx_requested_lock_id could
# be joined with performance_schema.data_locks.engine_lock_id again
# leading to strange results.
# This test checks that:
# - engline_lock_id values are unique
# - that ORDER BY does not lead to duplicates
# - that JOIN with data_lock_waits works correctly
# - that JOIN with innodb_trx works correctly
CREATE TABLE t1(
id INT PRIMARY KEY
) Engine=InnoDB;
INSERT INTO t1 VALUES (1);
# Create a situation in which a transaction holds more than one lock on same record.
BEGIN;
SELECT * FROM t1 WHERE id=1 FOR SHARE;
SELECT * FROM t1 WHERE id=1 FOR UPDATE;
SELECT COUNT(DISTINCT engine_lock_id) FROM performance_schema.data_locks;
SELECT
lock_type,
lock_mode,
lock_status,
lock_data
FROM performance_schema.data_locks
ORDER BY 1,2,3,4;
--connect (C1, localhost, root,,)
BEGIN;
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C1_will_wait';
--send SELECT * FROM t1 FOR UPDATE;
--connection default
SET DEBUG_SYNC = 'now WAIT_FOR C1_will_wait';
SELECT
r.object_name,r.lock_type,r.lock_mode,r.lock_status,r.lock_data ,
b.object_name,b.lock_type,b.lock_mode,b.lock_status,b.lock_data
FROM performance_schema.data_lock_waits
JOIN performance_schema.data_locks r
ON(r.engine_lock_id = requesting_engine_lock_id)
JOIN performance_schema.data_locks b
ON(b.engine_lock_id = blocking_engine_lock_id)
ORDER BY b.lock_mode;
SELECT r.object_name,r.lock_type,r.lock_mode,r.lock_status,r.lock_data
FROM information_schema.innodb_trx
JOIN performance_schema.data_locks r
ON(r.engine_lock_id = trx_requested_lock_id);
ROLLBACK;
--connection C1
--reap
ROLLBACK;
--connection default
--disconnect C1
DROP TABLE t1;
|
