[MDEV-30225] RR isolation violation with locking unique search Created: 2022-12-14  Updated: 2023-11-08  Resolved: 2022-12-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.11.2, 11.0.0, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Major
Reporter: Vladislav Lesin Assignee: Vladislav Lesin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-30010 Slave (additional info): Commit faile... Closed

 Description   

Currently next-key lock is requested only if a record is 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 non-delete-marked record is reached. 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 is "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, it's 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 violation commonly, and duplicate key errors on slaves particularly, as for MDEV-30010.



 Comments   
Comment by Marko Mäkelä [ 2022-12-16 ]

The proposed code change looks reasonable to me:

diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index ff0f9d47892..395739e4b4e 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -5114,8 +5114,10 @@ row_search_mvcc(
 			goto no_gap_lock;
 		}
 
+		/* Set next-key lock both for delete- and non-delete-marked
+		records for unique search, because non-delete-marked record can
+		be marked as deleted while transaction suspends. */
 		if (!set_also_gap_locks
-		    || (unique_search && !rec_get_deleted_flag(rec, comp))
 		    || dict_index_is_spatial(index)) {
 
 			goto no_gap_lock;

That is, we would no longer relax the gap locking for unique indexes at the transaction isolation levels REPEATABLE READ or SERIALIZABLE.

Generated at Thu Feb 08 10:14:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.