mirror of
https://github.com/MariaDB/server.git
synced 2025-04-20 14:15:30 +02:00
1bfd3cc457
PROBLEM:
A deadlock was possible when a transaction tried to "upgrade" an already
held Record Lock to Next Key Lock.
SOLUTION:
This patch is based on observations that:
(1) a Next Key Lock is equivalent to Record Lock combined with Gap Lock
(2) a GAP Lock never has to wait for any other lock
In case we request a Next Key Lock, we check if we already own a Record
Lock of equal or stronger mode, and if so, then we change the requested
lock type to GAP Lock, which we either already have, or can be granted
immediately, as GAP locks don't conflict with any other lock types.
(We don't consider Insert Intention Locks a Gap Lock in above statements).
The reason of why we don't upgrage Record Lock to Next Key Lock is the
following.
Imagine a transaction which does something like this:
for each row {
request lock in LOCK_X|LOCK_REC_NOT_GAP mode
request lock in LOCK_S mode
}
If we upgraded lock from Record Lock to Next Key lock, there would be
created only two lock_t structs for each page, one for
LOCK_X|LOCK_REC_NOT_GAP mode and one for LOCK_S mode, and then used
their bitmaps to mark all records from the same page.
The situation would look like this:
request lock in LOCK_X|LOCK_REC_NOT_GAP mode on row 1:
// -> creates new lock_t for LOCK_X|LOCK_REC_NOT_GAP mode and sets bit for
// 1
request lock in LOCK_S mode on row 1:
// -> notices that we already have LOCK_X|LOCK_REC_NOT_GAP on the row 1,
// so it upgrades it to X
request lock in LOCK_X|LOCK_REC_NOT_GAP mode on row 2:
// -> creates a new lock_t for LOCK_X|LOCK_REC_NOT_GAP mode (because we
// don't have any after we've upgraded!) and sets bit for 2
request lock in LOCK_S mode on row 2:
// -> notices that we already have LOCK_X|LOCK_REC_NOT_GAP on the row 2,
// so it upgrades it to X
...etc...etc..
Each iteration of the loop creates a new lock_t struct, and in the end we
have a lot (one for each record!) of LOCK_X locks, each with single bit
set in the bitmap. Soon we run out of space for lock_t structs.
If we create LOCK_GAP instead of lock upgrading, the above scenario works
like the following:
// -> creates new lock_t for LOCK_X|LOCK_REC_NOT_GAP mode and sets bit for
// 1
request lock in LOCK_S mode on row 1:
// -> notices that we already have LOCK_X|LOCK_REC_NOT_GAP on the row 1,
// so it creates LOCK_S|LOCK_GAP only and sets bit for 1
request lock in LOCK_X|LOCK_REC_NOT_GAP mode on row 2:
// -> reuses the lock_t for LOCK_X|LOCK_REC_NOT_GAP by setting bit for 2
request lock in LOCK_S mode on row 2:
// -> notices that we already have LOCK_X|LOCK_REC_NOT_GAP on the row 2,
// so it reuses LOCK_S|LOCK_GAP setting bit for 2
In the end we have just two locks per page, one for each mode:
LOCK_X|LOCK_REC_NOT_GAP and LOCK_S|LOCK_GAP.
Another benefit of this solution is that it avoids not-entirely
const-correct, (and otherwise looking risky) "upgrading".
The fix was ported from
mysql/mysql-server@bfba840dfa
mysql/mysql-server@75cefdb1f7
Reviewed by: Marko Mäkelä
78 lines
1.7 KiB
Text
78 lines
1.7 KiB
Text
#
|
|
# Bug #23755664 DEADLOCK WITH 3 CONCURRENT DELETES BY UNIQUE KEY
|
|
#
|
|
connection default;
|
|
CREATE TABLE `t`(
|
|
`id` INT,
|
|
`a` INT DEFAULT NULL,
|
|
PRIMARY KEY(`id`),
|
|
UNIQUE KEY `u`(`a`)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t (`id`,`a`) VALUES
|
|
(1,1),
|
|
(2,9999),
|
|
(3,10000);
|
|
connect deleter,localhost,root,,;
|
|
connect holder,localhost,root,,;
|
|
connect waiter,localhost,root,,;
|
|
connection deleter;
|
|
SET DEBUG_SYNC =
|
|
'lock_sec_rec_read_check_and_lock_has_locked
|
|
SIGNAL deleter_has_locked
|
|
WAIT_FOR waiter_has_locked';
|
|
DELETE FROM t WHERE a = 9999;
|
|
connection holder;
|
|
SET DEBUG_SYNC=
|
|
'now WAIT_FOR deleter_has_locked';
|
|
SET DEBUG_SYNC=
|
|
'lock_sec_rec_read_check_and_lock_has_locked SIGNAL holder_has_locked';
|
|
DELETE FROM t WHERE a = 9999;
|
|
connection waiter;
|
|
SET DEBUG_SYNC=
|
|
'now WAIT_FOR holder_has_locked';
|
|
SET DEBUG_SYNC=
|
|
'lock_sec_rec_read_check_and_lock_has_locked SIGNAL waiter_has_locked';
|
|
DELETE FROM t WHERE a = 9999;
|
|
connection deleter;
|
|
connection holder;
|
|
connection waiter;
|
|
connection default;
|
|
disconnect deleter;
|
|
disconnect holder;
|
|
disconnect waiter;
|
|
DROP TABLE `t`;
|
|
SET DEBUG_SYNC='reset';
|
|
CREATE TABLE `t`(
|
|
`id` INT NOT NULL PRIMARY KEY
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t (`id`) VALUES (1), (2);
|
|
connect holder,localhost,root,,;
|
|
connect waiter,localhost,root,,;
|
|
connection holder;
|
|
BEGIN;
|
|
SELECT id FROM t WHERE id=1 FOR UPDATE;
|
|
id
|
|
1
|
|
SELECT id FROM t WHERE id=2 FOR UPDATE;
|
|
id
|
|
2
|
|
connection waiter;
|
|
SET DEBUG_SYNC=
|
|
'lock_wait_suspend_thread_enter SIGNAL waiter_will_wait';
|
|
SELECT id FROM t WHERE id = 1 FOR UPDATE;
|
|
connection holder;
|
|
SET DEBUG_SYNC=
|
|
'now WAIT_FOR waiter_will_wait';
|
|
SELECT * FROM t FOR UPDATE;
|
|
id
|
|
1
|
|
2
|
|
COMMIT;
|
|
connection waiter;
|
|
id
|
|
1
|
|
connection default;
|
|
disconnect holder;
|
|
disconnect waiter;
|
|
DROP TABLE `t`;
|
|
SET DEBUG_SYNC='reset';
|