Hierarchical locking in B-tree indexes
View Patent ↗Portions of a B-tree index in a database are locked for concurrency control. In one example, hierarchical lock modes are provided that permit locking a key, a gap between the key and the next key, and a combination of the key and the gap. In another example, key range locking may be applied to the B-tree index using locks on separator keys of index nodes. In another example, key range locking may be applied to the B-tree index using locks on key prefixes.
1. A database system including a processing unit and memory, comprising:
a set of lock modes for use in locking a first key, a gap between the first key and a next key, and the combination of the first key and the gap in a B-tree index, the set of lock modes including:
key shared (SØ) including a shared lock on the first key and no lock on the gap,
gap shared (ØS) including no lock on the first key and a shared lock on the gap,
key exclusive (XØ) including an exclusive lock on the first key and no lock on the gap,
gap exclusive (ØX) including no lock on the first key and an exclusive lock on the gap,
key shared gap exclusive (SX) including a shared lock on the first key and an exclusive lock on the gap, and
key exclusive gap shared (XS) including an exclusive lock on the first key and a shared lock on the gap,
wherein the set of lock modes enables concurrent operations by a first transaction and a second transaction that affect the first key and the gap.
2. The database system of claim 1 wherein the first transaction reads a record identified by the first key while the second transaction inserts a new record with a key falling in the gap identified with the first key.
3. The database system of claim 1 wherein the first transaction modifies a record identified by the first key while the second transaction inserts a new record with a new key falling into the gap identified with the first key.
4. The database system of claim 1 wherein the first transaction deletes a record identified by the first key by turning the first key into a ghost record while the second transaction inserts a new record with a key falling into the gap identified with the first key.
5. The database system of claim 1 wherein the first transaction inserts a first new key with an associated first new record into the gap identified with the first key by turning a ghost record associated with the first new key into a valid record while the second transaction inserts a second new key with an associated second new record falling into the gap identified with the first key.
6. One or more computer readable storage media including computer executable instructions that when executed by a computing device perform operations comprising:
using one or more lock modes of a set of lock modes to lock a first key, a gap between the first key and a next key, and a combination of the first key and the gap in a B-tree index, the set of lock modes including:
key shared (SØ) including a shared lock on the first key and no lock on the gap,
gap shared (ØS) including no lock on the first key and a shared lock on the gap,
key exclusive (XØ) including an exclusive lock on the first key and no lock on the gap,
gap exclusive (ØX) including no lock on the first key and an exclusive lock on the gap,
key shared gap exclusive (SX) including a shared lock on the first key and an exclusive lock on the gap, and
key exclusive gap shared (XS) including an exclusive lock on the first key and a shared lock on the gap,
wherein the set of lock modes enables concurrent operations by a first transaction and a second transaction that affect the first key and the gap.
7. The one or more computer readable storage of claim 6 wherein the first transaction reads a record identified by the first key while the second transaction inserts a new record with a key falling in the gap identified with the first key.
8. The one or more computer readable storage of claim 6 wherein the first transaction modifies a record identified by the first key while the second transaction inserts a new record with a new key falling into the gap identified with the first key.
9. The one or more computer readable storage of claim 6 wherein the first transaction deletes a record identified by the first key by turning the first key into a ghost record while the second transaction inserts a new record with a key falling into the gap identified with the first key.
10. The one or more computer readable storage of claim 6 wherein the first transaction inserts a first new key with an associated first new record into the gap identified with the first key by turning a ghost record associated with the first new key into a valid record while the second transaction inserts a second new key with an associated second new record falling into the gap identified with the first key.
11. A method, comprising:
using one or more lock modes of a set of lock modes to lock a first key, a gap between the first key and a next key, and a combination of the first key and the gap in a B-tree index stored in a computing device comprising a processing unit and memory, the set of lock modes including:
key shared (SØ) including a shared lock on the first key and no lock on the gap,
gap shared (ØS) including no lock on the first key and a shared lock on the gap,
key exclusive (XØ) including an exclusive lock on the first key and no lock on the gap,
gap exclusive (ØX) including no lock on the first key and an exclusive lock on the gap,
key shared gap exclusive (SX) including a shared lock on the first key and an exclusive lock on the gap, and
key exclusive gap shared (XS) including an exclusive lock on the first key and a shared lock on the gap,
wherein the set of lock modes enables concurrent operations by a first transaction and a second transaction that affect the first key and the gap.
12. The method of claim 11 wherein the first transaction reads a record identified by the first key while the second transaction inserts a new record with a key falling in the gap identified with the first key.
13. The method of claim 11 wherein the first transaction modifies a record identified by the first key while the second transaction inserts a new record with a new key falling into the gap identified with the first key.
14. The method of claim 11 wherein the first transaction deletes a record identified by the first key by turning the first key into a ghost record while the second transaction inserts a new record with a key falling into the gap identified with the first key.
15. The method of claim 11 wherein the first transaction inserts a first new key with an associated first new record into the gap identified with the first key by turning a ghost record associated with the first new key into a valid record while the second transaction inserts a second new key with an associated second new record falling into the gap identified with the first key.