Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Currently, lock_t is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: {shared,exclusive,gap-read,gap-write}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (acquired by UPDATE, DELETE, or a locking SELECT) prevents any subsequent INSERT to that range.
INSERT will acquire a gap-write or insert-intention lock (LOCK_INSERT_INTENTION). It conflicts with previously granted gap-read locks. Gap-read locks may be granted on the same lock if a gap-write lock has been granted to an active transaction.
An INSERT requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read locks may be held on the same gap by different transactions.
In lock_rec_has_to_wait(), gap-write locks do not conflict with gap-write locks of other transactions, even though lock_mode_compatible() would not hold.
A transaction can wait for at most one record. MDEV-24671 already simplified lock waits by introducing the function lock_wait(). If we pack 2 record heap numbers per byte (4 bits per heap number) in the record lock bitmap, there is no space for a further LOCK_WAIT flag. We might want to create a special type of bitmap for a waiting lock request, to avoid allocating a large bitmap (if the record heap number is 3000, the size of a straightforward bitmap would be 1500 bytes). We might store the attributes of the single-record lock request in the trx_t object. Maybe we could just preallocate a record lock bitmap object in trx_t, to be used for all waiting requests?
Attachments
Issue Links
- blocks
-
MDEV-21452 Use condition variables and normal mutexes instead of InnoDB os_event and mutex
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- In Review
-
-
-
- Closed
-
-
-
- Closed
-
(4 relates to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 3 bits per record heap number to represent the 6 combinations of lock modes: {not held,shared,exclusive}×{record,gap}.
(An exclusive gap lock would correspond to {{LOCK_INSERT_INTENTION}}.) A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 3 bits per record heap number to represent the 6 combinations of lock modes: \{not held,shared,exclusive\}×\{record,gap\}.
(An exclusive gap lock would correspond to {{LOCK_INSERT_INTENTION}}.) A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
| Link | This issue relates to MDEV-11215 [ MDEV-11215 ] |
| Link | This issue relates to MDEV-17512 [ MDEV-17512 ] |
As noted in MDEV-17512, it could be desirable to have a variation of a locking SELECT that acquires write-gap-locks, so that a later INSERT by the same transaction within the already searched range of keys will be guaranteed to succeed. Currently, FOR UPDATE only guarantees that UPDATE and DELETE will succeed.
In a discussion with monty, arubin and Peter Zaitsev today, the need for having secondary index record locks was questioned. The basic idea is that instead of checking if a lock exists on a secondary index record, we check the lock on the corresponding clustered index record.
We would still seem to need gap locks on secondary index records. A locking read on a secondary index must prevent an INSERT of the same value, for any primary key value:
connection con1; |
BEGIN; |
# returning any amount of rows, possibly including 0 |
SELECT COUNT(*) FROM t WHERE indexed_column BETWEEN 42 AND 64 LOCK IN SHARE MODE; |
connection con2; |
--error ER_LOCK_WAIT_TIMEOUT
|
INSERT INTO t SET pk=12345, indexed_column=50; |
--error ER_LOCK_WAIT_TIMEOUT
|
UPDATE t SET indexed_column=50; |
--error ER_LOCK_WAIT_TIMEOUT
|
DELETE FROM t WHERE indexed_column BETWEEN 30 AND 70; |
connection con1; |
# must return the same result! |
SELECT COUNT(*) FROM t WHERE indexed_column BETWEEN 42 AND 64 LOCK IN SHARE MODE; |
If the SELECT did not acquire gap locks that cover the whole range [42,64], then the INSERT, DELETE or UPDATE would not be blocked.
Note: Even if users do not use SERIALIZABLE isolation level or LOCK IN SHARE MODE or FOR UPDATE, InnoDB will internally acquire shared or exclusive locks in FOREIGN KEY checks or CASCADE or SET operations.
It would seem that we may not really need single-record locks on secondary indexes, but gap locks we do need.
There is a possible reason against relying explicitly on gap locks on secondary indexes. Gap locks are inherently nondeterministic (potentially causing problems with some forms of replication), since they can be attached to a delete-marked purgeable record. That is, purge can widen gaps at any time by removing those "invisible" keys. Because of this, it might be better to prefer single-record locks over gap locks in those cases where we want to lock a single secondary index key.
| NRE Projects | RM_105_CANDIDATE |
| Assignee | Marko Mäkelä [ marko ] | Eugene Kosov [ kevg ] |
| Link | This issue relates to MDEV-18706 [ MDEV-18706 ] |
| Fix Version/s | 10.5 [ 23123 ] |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 3 bits per record heap number to represent the 6 combinations of lock modes: \{not held,shared,exclusive\}×\{record,gap\}.
(An exclusive gap lock would correspond to {{LOCK_INSERT_INTENTION}}.) A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all 9 combinations of record and gap lock modes: {unlocked,shared,exclusive}×{none,gap-read,gap-write}
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (taken by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any {{INSERT}} to that range. A gap-write lock ({{LOCK_INSERT_INTENTION}}) is mutually exclusive with gap-read locks. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read or gap-write locks may be held on the same gap by different transactions, as long as there are not both gap-read and gap-write locks for the same gap. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all 9 combinations of record and gap lock modes: {unlocked,shared,exclusive}×{none,gap-read,gap-write}
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (taken by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any {{INSERT}} to that range. A gap-write lock ({{LOCK_INSERT_INTENTION}}) is mutually exclusive with gap-read locks. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read or gap-write locks may be held on the same gap by different transactions, as long as there are not both gap-read and gap-write locks for the same gap. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all 9 combinations of record and gap lock modes: \{unlocked,shared,exclusive\}×\{none,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (taken by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any {{INSERT}} to that range. A gap-write lock ({{LOCK_INSERT_INTENTION}}) is mutually exclusive with gap-read locks. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read or gap-write locks may be held on the same gap by different transactions, as long as there are not both gap-read and gap-write locks for the same gap. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
| Link |
This issue relates to |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all 9 combinations of record and gap lock modes: \{unlocked,shared,exclusive\}×\{none,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (taken by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any {{INSERT}} to that range. A gap-write lock ({{LOCK_INSERT_INTENTION}}) is mutually exclusive with gap-read locks. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read or gap-write locks may be held on the same gap by different transactions, as long as there are not both gap-read and gap-write locks for the same gap. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (taken by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any {{INSERT}} to that range. A gap-write lock ({{LOCK_INSERT_INTENTION}}) is mutually exclusive with gap-read locks. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read or gap-write locks may be held on the same gap by different transactions, as long as there are not both gap-read and gap-write locks for the same gap. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-21974 [ MDEV-21974 ] |
| Link |
This issue blocks |
| Link | This issue relates to MDEV-17512 [ MDEV-17512 ] |
| Link |
This issue relates to |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (taken by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any {{INSERT}} to that range. A gap-write lock ({{LOCK_INSERT_INTENTION}}) is mutually exclusive with gap-read locks. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read or gap-write locks may be held on the same gap by different transactions, as long as there are not both gap-read and gap-write locks for the same gap. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (acquired by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any subsequent {{INSERT}} to that range. {{INSERT}} will acquire a gap-write or insert-intention lock ({{LOCK_INSERT_INTENTION}}). It conflicts with *previously granted* gap-read locks. Gap-read locks may be granted on the same lock if a gap-write lock has been granted to an active transaction. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read locks may be held on the same gap by different transactions. Currently, gap-write locks conflict with gap-write locks of other transactions. This may be unnecessary. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (acquired by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any subsequent {{INSERT}} to that range. {{INSERT}} will acquire a gap-write or insert-intention lock ({{LOCK_INSERT_INTENTION}}). It conflicts with *previously granted* gap-read locks. Gap-read locks may be granted on the same lock if a gap-write lock has been granted to an active transaction. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read locks may be held on the same gap by different transactions. Currently, gap-write locks conflict with gap-write locks of other transactions. This may be unnecessary. A transaction can wait for at most one record. It might be simplest to not create a separate bitmap for that, but instead store the attributes of the lock request in the {{trx_t}} object. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (acquired by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any subsequent {{INSERT}} to that range. {{INSERT}} will acquire a gap-write or insert-intention lock ({{LOCK_INSERT_INTENTION}}). It conflicts with *previously granted* gap-read locks. Gap-read locks may be granted on the same lock if a gap-write lock has been granted to an active transaction. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read locks may be held on the same gap by different transactions. In {{lock_rec_has_to_wait()}}, gap-write locks do not conflict with gap-write locks of other transactions, even though {{lock_mode_compatible()}} would not hold. A transaction can wait for at most one record. |
| Workflow | MariaDB v3 [ 87694 ] | MariaDB v4 [ 130846 ] |
| Assignee | Eugene Kosov [ kevg ] | Vladislav Lesin [ vlad.lesin ] |
| Description |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}.
There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap-read lock (acquired by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any subsequent {{INSERT}} to that range. {{INSERT}} will acquire a gap-write or insert-intention lock ({{LOCK_INSERT_INTENTION}}). It conflicts with *previously granted* gap-read locks. Gap-read locks may be granted on the same lock if a gap-write lock has been granted to an active transaction. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read locks may be held on the same gap by different transactions. In {{lock_rec_has_to_wait()}}, gap-write locks do not conflict with gap-write locks of other transactions, even though {{lock_mode_compatible()}} would not hold. A transaction can wait for at most one record. |
Currently, {{lock_t}} is a union of table locks and record lock bitmaps. There can be multiple record lock bitmaps in a transaction. A record lock bitmap is specific to an index leaf page and a locking mode. It could be more efficient to use a single bitmap per page, with 4 bits per record heap number to represent all combinations of record and gap lock modes: \{shared,exclusive,gap-read,gap-write\}. There are two types of gap locks (covering the open range of keys from the preceding record to the anchor record). A gap\-read lock (acquired by {{UPDATE}}, {{DELETE}}, or a locking {{SELECT}}) prevents any subsequent {{INSERT}} to that range. {{INSERT}} will acquire a gap-write or insert-intention lock ({{LOCK_INSERT_INTENTION}}). It conflicts with *previously granted* gap-read locks. Gap-read locks may be granted on the same lock if a gap\-write lock has been granted to an active transaction. An {{INSERT}} requires both a gap-write lock and an exclusive lock on the key that is being inserted. Any amount of gap-read locks may be held on the same gap by different transactions. In {{lock_rec_has_to_wait()}}, gap-write locks do not conflict with gap-write locks of other transactions, even though {{lock_mode_compatible()}} would not hold. A transaction can wait for at most one record. |
| Link | This issue relates to MDEV-17512 [ MDEV-17512 ] |
There are two modes of gap locks, sharable within each mode.
That is, multiple locking reads (possibly as part of DELETE or UPDATE) can acquire a read-gap-lock on the same gap. Also, multiple INSERT can acquire a write-gap-lock (called LOCK_INSERT_INTENTION in the code) on the same gap. But at most one type of gap lock may exist on the same gap at any given time.
The following test demonstrates this:
--source include/have_innodb.incdisconnect con1;Both SELECT will acquire a gap lock that precedes the successor of the non-matching record. In this case, the non-matching record would be the page infimum, and the successor would be the record id=1. Any INSERT with a value that is less than this would be prevented.
The example would also work without the INSERT; in that case, the gap lock would be attached to the page supremum, preventing any INSERT into the empty table while either of the two transactions hold the gap lock.