1--source include/have_debug_sync.inc
2
3--echo # Bug #28176910
4--echo # PERFORMANCE_SCHEMA.DATA_LOCKS.OBJECT_INSTANCE_BEGIN
5--echo # CHANGES DEPENDING ON ORDER
6
7# This scenario is meant to create a situation in which a single
8# transaction has two locks for the same record, which differ only
9# in their mode (X vs X,REC_NOT_GAP) which historicaly caused a bug
10# in that both of them had the same performance.data_locks.engine_lock_id.
11# Having two rows with the same engline_lock_id caused visible problems:
12# using SELECT...FROM data_locks ORDER BY ... caused one of the two rows
13# to be reported twice and the other row not even once.
14# There were also problems with data_lock_waits which should contain
15# exactly one edge for each pair of conflicting locks, however having
16# two rows with the same id in data_locks could lead to strange results
17# when joining data_locks with data_lock_waits.
18# Finally, the information_schema.innodb_trx.trx_requested_lock_id could
19# be joined with performance_schema.data_locks.engine_lock_id again
20# leading to strange results.
21# This test checks that:
22# - engline_lock_id values are unique
23# - that ORDER BY does not lead to duplicates
24# - that JOIN with data_lock_waits works correctly
25# - that JOIN with innodb_trx works correctly
26
27CREATE TABLE t1(
28  id INT PRIMARY KEY
29) Engine=InnoDB;
30
31INSERT INTO t1 VALUES (1);
32# Create a situation in which a transaction holds more than one lock on same record.
33BEGIN;
34SELECT * FROM t1 WHERE id=1 FOR SHARE;
35SELECT * FROM t1 WHERE id=1 FOR UPDATE;
36
37SELECT COUNT(DISTINCT engine_lock_id) FROM performance_schema.data_locks;
38
39SELECT
40    lock_type,
41    lock_mode,
42    lock_status,
43    lock_data
44FROM performance_schema.data_locks
45ORDER BY 1,2,3,4;
46
47--connect (C1, localhost, root,,)
48
49    BEGIN;
50    SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C1_will_wait';
51    --send SELECT * FROM t1 FOR UPDATE;
52
53--connection default
54    SET DEBUG_SYNC = 'now WAIT_FOR C1_will_wait';
55    SELECT
56        r.object_name,r.lock_type,r.lock_mode,r.lock_status,r.lock_data ,
57        b.object_name,b.lock_type,b.lock_mode,b.lock_status,b.lock_data
58    FROM performance_schema.data_lock_waits
59    JOIN performance_schema.data_locks r
60    ON(r.engine_lock_id = requesting_engine_lock_id)
61    JOIN performance_schema.data_locks b
62    ON(b.engine_lock_id = blocking_engine_lock_id)
63    ORDER BY b.lock_mode;
64
65    SELECT r.object_name,r.lock_type,r.lock_mode,r.lock_status,r.lock_data
66    FROM information_schema.innodb_trx
67    JOIN performance_schema.data_locks r
68    ON(r.engine_lock_id = trx_requested_lock_id);
69
70
71    ROLLBACK;
72
73--connection C1
74    --reap
75    ROLLBACK;
76
77--connection default
78--disconnect C1
79
80DROP TABLE t1;