| 1 |
# |
| 2 |
# Bug#32068538 TRX->LOCK.N_REC_LOCKS INCONSISTENT WITH REAL NUM OF TRX REC LOCKS |
| 3 |
# |
| 4 |
# In this scenario, a lock on a deleted record is inherited to a gap during |
| 5 |
# purge for transaction which already has this gap locked before, which could |
| 6 |
# lead to wrong count of locks reported. |
| 7 |
--source include/have_debug_sync.inc |
| 8 |
|
| 9 |
CREATE TABLE t1 (id INT PRIMARY KEY); |
| 10 |
INSERT INTO t1 (id) VALUES (1),(2), (4),(5); |
| 11 |
# We want the qury plan to be deterministic, so that just one record is locked |
| 12 |
# with each point select (as opposed to full scan for example). |
| 13 |
ANALYZE TABLE t1; |
| 14 |
|
| 15 |
--connect (c1, localhost, root,,) |
| 16 |
BEGIN; |
| 17 |
DELETE FROM t1 WHERE id=2; |
| 18 |
|
| 19 |
--connect (c2, localhost, root,,) |
| 20 |
BEGIN; |
| 21 |
# This doesn't have to wait, but creates an S,GAP lock on gap before 4 |
| 22 |
SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id=3 FOR SHARE; |
| 23 |
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL c2_will_wait WAIT_FOR c2_can_go'; |
| 24 |
# This one has to wait for the DELETE of id=2 to COMMIT or ROLLBACK. |
| 25 |
# It will create a WAITING S,REC_NOT_GAP on record id=2. |
| 26 |
--send |
| 27 |
SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id=2 FOR SHARE; |
| 28 |
|
| 29 |
--connection c1 |
| 30 |
SET DEBUG_SYNC = 'now WAIT_FOR c2_will_wait'; |
| 31 |
SELECT trx_id |
| 32 |
FROM information_schema.innodb_trx |
| 33 |
WHERE trx_query = |
| 34 |
'SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id=2 FOR SHARE' |
| 35 |
INTO @c2_trx_id; |
| 36 |
# This will first release the X,REC_NOT_GAP lock from id=2 and grant it to c2 |
| 37 |
COMMIT; |
| 38 |
|
| 39 |
|
| 40 |
# Purge will remove the no longer needed row with id=2 and move all |
| 41 |
# locks from it to the gap before 4. |
| 42 |
# As c2 already has S,GAP lock before 4 we will double count it. |
| 43 |
|
| 44 |
# We don't use wait_innodb_all_purged.inc here, because even with nudging purge |
| 45 |
# by innodb_purge_stop_now + innodb_purge_run_now there's no guarantee that |
| 46 |
# purge threads will update purge_sys->done.trx before we get here, and |
| 47 |
# srv_export_innodb_status() reports the initial purge_sys->done.trx==0 as |
| 48 |
# INNODB_PURGE_TRX_ID_AGE=0, which looks like there's nothing to wait for. |
| 49 |
# We use COUNT(*)=1 as opposed to checking for LOCK_DATA=2, because LOCK_DATA |
| 50 |
# can be NULL if the page is missing from buffer pool. Also, we don't check for |
| 51 |
# lock_status='WAITING', because a lock becomes granted on COMMIT, which is |
| 52 |
# before it is moved to the gap by purge. We don't check ENGINE_TRANSACTION_ID, |
| 53 |
# because at this point the only transaction holding locks on t1 must be c2's. |
| 54 |
let $wait_condition = SELECT COUNT(*)=1 |
| 55 |
FROM performance_schema.data_locks |
| 56 |
WHERE object_schema='test' |
| 57 |
AND object_name='t1' |
| 58 |
AND lock_type='RECORD'; |
| 59 |
--source include/wait_condition.inc |
| 60 |
|
| 61 |
# Even though the lock is already (re)moved from id=2 when we get here, there's |
| 62 |
# still a possibility that reported trx_rows_locked is 2, because innodb_trx is |
| 63 |
# cached for 0.1 second. Therefore we wait here for expected result to appear. |
| 64 |
# We expect number of record locks to be one less than before, and to match |
| 65 |
# actual number of lock_type='RECORD', which is 1. |
| 66 |
# The reason we also wait for data_locks to drop to 1, is to make sure the |
| 67 |
# test scenario is really testing what we wanted, and that the two tables match. |
| 68 |
let $wait_condition = SELECT trx_rows_locked=1 |
| 69 |
FROM information_schema.innodb_trx |
| 70 |
WHERE trx_id=@c2_trx_id; |
| 71 |
--source include/wait_condition.inc |
| 72 |
|
| 73 |
# Clean up the test |
| 74 |
--connection default |
| 75 |
SET DEBUG_SYNC = 'now SIGNAL c2_can_go'; |
| 76 |
--disconnect c2 |
| 77 |
--disconnect c1 |
| 78 |
|
| 79 |
DROP TABLE t1; |