• Vlad Lesin's avatar
    MDEV-30225 RR isolation violation with locking unique search · 3ddc00dc
    Vlad Lesin authored
    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.
    3ddc00dc
monitor.result 26.1 KB