Details
Major 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.
Attachments
Issue Links
- is blocked by
-
MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration
-
- Closed
-
- relates to
-
-
- Closed
-
- mentioned in
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
The proposed code change looks reasonable to me:
diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.ccindex 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)) {That is, we would no longer relax the gap locking for unique indexes at the transaction isolation levels REPEATABLE READ or SERIALIZABLE.