--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;