mirror of
https://github.com/MariaDB/server.git
synced 2025-04-21 06:35:31 +02:00
3ddc00dc3b
Before the fix next-key lock was requested only if a record was delete-marked for locking unique search in RR isolation level. There can be several delete-marked records for the same unique key, that's why InnoDB scans the records until eighter non-delete-marked record is reached or all delete-marked records with the same unique key are scanned. For range scan next-key locks are used for RR to protect scanned range from inserting new records by other transactions. And this is the reason of why next-key locks are used for delete-marked records for unique searches. If a record is not delete-marked, the requested lock type was "not-gap". When a record is not delete-marked during lock request by trx 1, and some other transaction holds conflicting lock, trx 1 creates waiting not-gap lock on the record and suspends. During trx 1 suspending the record can be delete-marked. And when the lock is granted on conflicting transaction commit or rollback, its type is still "not-gap". So we have "not-gap" lock on delete-marked record for RR. And this let some other transaction to insert some record with the same unique key when trx 1 is not committed, what can cause isolation level violation. The fix is to set next-key locks for both delete-marked and non-delete-marked records for unique search in RR.
35 lines
1 KiB
Text
35 lines
1 KiB
Text
connect pause_purge,localhost,root;
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
connection default;
|
|
CREATE TABLE t (pk int PRIMARY KEY, sk INT UNIQUE) ENGINE=InnoDB;
|
|
INSERT INTO t VALUES (10, 100);
|
|
connect con1,localhost,root;
|
|
BEGIN;
|
|
SELECT * FROM t WHERE sk = 100 FOR UPDATE;
|
|
pk sk
|
|
10 100
|
|
connect con2,localhost,root;
|
|
SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL insert_wait_started";
|
|
INSERT INTO t VALUES (5, 100) # trx 1;
|
|
connect con3,localhost,root;
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
SET DEBUG_SYNC="now WAIT_FOR insert_wait_started";
|
|
SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL delete_started_waiting";
|
|
DELETE FROM t WHERE sk = 100 # trx 2;
|
|
connection con1;
|
|
SET DEBUG_SYNC="now WAIT_FOR delete_started_waiting";
|
|
DELETE FROM t WHERE sk=100;
|
|
COMMIT;
|
|
disconnect con1;
|
|
connection con2;
|
|
disconnect con2;
|
|
connection con3;
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
disconnect con3;
|
|
connection default;
|
|
SELECT * FROM t;
|
|
pk sk
|
|
5 100
|
|
disconnect pause_purge;
|
|
SET DEBUG_SYNC="RESET";
|
|
DROP TABLE t;
|