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