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;