Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30225

RR isolation violation with locking unique search




      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.


        Issue Links



              vlad.lesin Vladislav Lesin
              vlad.lesin Vladislav Lesin
              0 Vote for this issue
              2 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.