| 1 |
--echo # |
| 2 |
--echo # Bug #23755664 DEADLOCK WITH 3 CONCURRENT DELETES BY UNIQUE KEY |
| 3 |
--echo # |
| 4 |
|
| 5 |
--source include/have_innodb.inc |
| 6 |
--source include/have_debug.inc |
| 7 |
--source include/have_debug_sync.inc |
| 8 |
--source include/count_sessions.inc |
| 9 |
|
| 10 |
--connection default |
| 11 |
# There are various scenarious in which a transaction already holds "half" |
| 12 |
# of a record lock (for example, a lock on the record but not on the gap) |
| 13 |
# and wishes to "upgrade it" to a full lock (i.e. on both gap and record). |
| 14 |
# This is often a cause for a deadlock, if there is another transaction |
| 15 |
# which is already waiting for the lock being blocked by us: |
| 16 |
# 1. our granted lock for one half |
| 17 |
# 2. her waiting lock for the same half |
| 18 |
# 3. our waiting lock for the whole |
| 19 |
|
| 20 |
# |
| 21 |
# SCENARIO 1 |
| 22 |
# |
| 23 |
# In this scenario, three different threads try to delete the same row, |
| 24 |
# identified by a secondary index key. |
| 25 |
# This kind of operation (besides LOCK_IX on a table) requires |
| 26 |
# an LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X lock on a secondary index |
| 27 |
# 1. `deleter` is the first to get the required lock |
| 28 |
# 2. `holder` enqueues a waiting lock |
| 29 |
# 3. `waiter` enqueues right after `holder` |
| 30 |
# 4. `deleter` commits, releasing the lock, and granting it to `holder` |
| 31 |
# 5. `holder` now observes that the row was deleted, so it needs to |
| 32 |
# "seal the gap", by obtaining a LOCK_X|LOCK_REC, but.. |
| 33 |
# 6. this causes a deadlock between `holder` and `waiter` |
| 34 |
# |
| 35 |
# This scenario does not fail if MDEV-10962 is not fixed because of MDEV-30225 |
| 36 |
# fix, as the 'holder' does not "seal the gap" after 'deleter' was committed, |
| 37 |
# because it was initially sealed, as row_search_mvcc() requests next-key lock |
| 38 |
# after MDEV-30225 fix in the case when it requested not-gap lock before the |
| 39 |
# fix. |
| 40 |
# |
| 41 |
# But let the scenario be in the tests, because it can fail if MDEV-30225 |
| 42 |
# related code is changed |
| 43 |
|
| 44 |
CREATE TABLE `t`( |
| 45 |
`id` INT, |
| 46 |
`a` INT DEFAULT NULL, |
| 47 |
PRIMARY KEY(`id`), |
| 48 |
UNIQUE KEY `u`(`a`) |
| 49 |
) ENGINE=InnoDB; |
| 50 |
|
| 51 |
INSERT INTO t (`id`,`a`) VALUES |
| 52 |
(1,1), |
| 53 |
(2,9999), |
| 54 |
(3,10000); |
| 55 |
|
| 56 |
--connect(deleter,localhost,root,,) |
| 57 |
--connect(holder,localhost,root,,) |
| 58 |
--connect(waiter,localhost,root,,) |
| 59 |
|
| 60 |
|
| 61 |
--connection deleter |
| 62 |
SET DEBUG_SYNC = |
| 63 |
'lock_sec_rec_read_check_and_lock_has_locked |
| 64 |
SIGNAL deleter_has_locked |
| 65 |
WAIT_FOR waiter_has_locked'; |
| 66 |
--send DELETE FROM t WHERE a = 9999 |
| 67 |
|
| 68 |
--connection holder |
| 69 |
SET DEBUG_SYNC= |
| 70 |
'now WAIT_FOR deleter_has_locked'; |
| 71 |
SET DEBUG_SYNC= |
| 72 |
'lock_sec_rec_read_check_and_lock_has_locked SIGNAL holder_has_locked'; |
| 73 |
--send DELETE FROM t WHERE a = 9999 |
| 74 |
|
| 75 |
--connection waiter |
| 76 |
SET DEBUG_SYNC= |
| 77 |
'now WAIT_FOR holder_has_locked'; |
| 78 |
SET DEBUG_SYNC= |
| 79 |
'lock_sec_rec_read_check_and_lock_has_locked SIGNAL waiter_has_locked'; |
| 80 |
--send DELETE FROM t WHERE a = 9999 |
| 81 |
|
| 82 |
--connection deleter |
| 83 |
--reap |
| 84 |
|
| 85 |
--connection holder |
| 86 |
--reap |
| 87 |
|
| 88 |
--connection waiter |
| 89 |
--reap |
| 90 |
|
| 91 |
--connection default |
| 92 |
|
| 93 |
--disconnect deleter |
| 94 |
--disconnect holder |
| 95 |
--disconnect waiter |
| 96 |
|
| 97 |
DROP TABLE `t`; |
| 98 |
SET DEBUG_SYNC='reset'; |
| 99 |
|
| 100 |
# SCENARIO 2 |
| 101 |
# |
| 102 |
# Here, we form a situation in which con1 has LOCK_REC_NOT_GAP on rows 1 and 2 |
| 103 |
# con2 waits for lock on row 1, and then con1 wants to upgrade the lock on row 1, |
| 104 |
# which might cause a deadlock, unless con1 properly notices that even though the |
| 105 |
# lock on row 1 can not be upgraded, a separate LOCK_GAP can be obtaied easily. |
| 106 |
|
| 107 |
CREATE TABLE `t`( |
| 108 |
`id` INT NOT NULL PRIMARY KEY |
| 109 |
) ENGINE=InnoDB; |
| 110 |
|
| 111 |
INSERT INTO t (`id`) VALUES (1), (2); |
| 112 |
|
| 113 |
--connect(holder,localhost,root,,) |
| 114 |
--connect(waiter,localhost,root,,) |
| 115 |
|
| 116 |
--connection holder |
| 117 |
BEGIN; |
| 118 |
SELECT id FROM t WHERE id=1 FOR UPDATE; |
| 119 |
SELECT id FROM t WHERE id=2 FOR UPDATE; |
| 120 |
|
| 121 |
--connection waiter |
| 122 |
SET DEBUG_SYNC= |
| 123 |
'lock_wait_before_suspend SIGNAL waiter_will_wait'; |
| 124 |
--send SELECT id FROM t WHERE id = 1 FOR UPDATE |
| 125 |
|
| 126 |
--connection holder |
| 127 |
SET DEBUG_SYNC= |
| 128 |
'now WAIT_FOR waiter_will_wait'; |
| 129 |
SELECT * FROM t FOR UPDATE; |
| 130 |
COMMIT; |
| 131 |
|
| 132 |
--connection waiter |
| 133 |
--reap |
| 134 |
|
| 135 |
--connection default |
| 136 |
|
| 137 |
--disconnect holder |
| 138 |
--disconnect waiter |
| 139 |
|
| 140 |
DROP TABLE `t`; |
| 141 |
SET DEBUG_SYNC='reset'; |
| 142 |
|
| 143 |
--source include/wait_until_count_sessions.inc |