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 Failed to load
-
Page Failed to load
-
Page Failed to load
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Summary | Set next-key lock for unique search for RR unconditionally | RR isolation violation with locking unique search |
| 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 no committed, what can cause isolation violation commonly, and duplicate key errors particularly, as for |
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 particularly, as for |
| 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 particularly, as for |
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 |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.7 [ 24805 ] | |
| Affects Version/s | 10.8 [ 26121 ] | |
| Affects Version/s | 10.9 [ 26905 ] | |
| Affects Version/s | 10.10 [ 27530 ] | |
| Affects Version/s | 10.11 [ 27614 ] | |
| Affects Version/s | 10.12 [ 28320 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 10.12 [ 28320 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.12.0 [ 28500 ] | |
| Fix Version/s | 10.3.38 [ 28507 ] | |
| Fix Version/s | 10.4.28 [ 28509 ] | |
| Fix Version/s | 10.5.19 [ 28511 ] | |
| Fix Version/s | 10.6.12 [ 28513 ] | |
| Fix Version/s | 10.7.8 [ 28515 ] | |
| Fix Version/s | 10.8.7 [ 28517 ] | |
| Fix Version/s | 10.9.5 [ 28519 ] | |
| Fix Version/s | 10.10.3 [ 28521 ] | |
| Fix Version/s | 10.11.2 [ 28523 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 10.12 [ 28320 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34691 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34692 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34704 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34708 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34691 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34712 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34808 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34823 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34840 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34902 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34909 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34708 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34925 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34951 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34968 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34975 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34983 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34989 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34996 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35112 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35229 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35260 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35268 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 34989 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35281 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35288 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35302 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35302 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35466 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35491 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35623 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35625 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35803 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35814 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35912 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35914 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35931 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35946 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35969 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36006 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36103 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36135 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36176 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36213 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36229 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 35912 ] |
| Zendesk Related Tickets | 201658 | |
| Zendesk active tickets | 201658 |
| Link |
This issue is blocked by |
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.