Background

TiDB supports foreign key constraint check by default. When writing a child table, it checks whether the corresponding foreign key value exists in the parent table, and locks the row in the parent table to avoid the foreign key value being deleted by other operations violating the foreign key constraint. However, the current implementation uses exclusive locks for foreign key checks, which can lead to performance bottlenecks in high-concurrency scenarios. To address this, we have introduced shared locks for foreign key checks in pessimistic transactions. This allows multiple transactions to read-lock the same foreign key simultaneously, improving concurrency and performance.

Tracking list(master)

• kvproto add shared lock type
• tikv support shared locks in mvcc storage format
• tikv update lock waiting logic for shared locks
• tikv support shared locks in write commands
• tikv prevent xlock starving
• tikv support deadlock detection
• tikv add shared lock unit tests
• client go support shared locks
• tidb use shared locks in foreign key checks
• tidb coprocessor resolve shared locks
• tidb add integration tests for shared locks
• tidb support shared lock in unistore
• compatibility with other features(1pc, async commit, fair locking features)

Development Task

kvproto

• Add shared lock type.
  • 8.5-shared-lock
    • Add Op::SharedLock for acquiring shared lock in pessimistic transacitons. pingcap/kvproto#1369
    • distinguish between shared prewrite locks and shared pessimistic locks pingcap/kvproto#1377
    • Support shared locks for LockInfo pingcap/kvproto#1378
  • master
    • Add Op::SharedLock and Op::SharedPessimisticLock for acquiring shared lock in pessimistic transacitons. pingcap/kvproto#1374
  • release-8.5
    • Add Op::SharedLock and Op::SharedPessimisticLock for acquiring shared lock in pessimistic transacitons. (#1374) pingcap/kvproto#1417

tikv

• Update storage format to support shared locks, including Lock and Write records, and also enable the MVCC layer to support reading and writing shared locks.
  • 8.5-shared-lock
    • txn: implement Lock encode/decode #19091
    • txn: embed share locks into lock info #19127
  • master
    • txn: implement Lock encode/decode #19237
  • release-8.5
    • txn: implement Lock encode/decode (#19237) #19389
• Modify the lock-waiting logic for shared locks.
  • 8.5-shared-lock
    • txn: support shared lock for lock wait table #19094
  • master
    • txn: support shared lock for lock wait table #19271
  • release-8.5
    • txn: support shared lock for lock wait table (#19271) #19405
• Modify the handling logic of write commands to support shared locks, including acquire_pessimistic_lock, prewrite, commit, resolve_lock, etc.
  • 8.5-shared-lock
    • txn: support shared lock for pessimistic / prewrite / commit schedulers #19110
    • txn: implement rollback, cleanup and lock resolving for shared locks #19149
    • txn: implement rollback shared locks for flashback #19193
    • txn: add test for rollback & scan shared lock #19200
  • master
    • txn: add shared lock handling for all schedulers #19304
    • txn: support shared lock for pessimistic / prewrite / commit schedulers #19282
  • release-8.5
    • txn: support shared lock for pessimistic / prewrite / commit schedulers (#19282) #19423
• Deadlock & Starving
  • master
    • txn_types: add shrink-only flag to shared locks #19280
    • txn: avoid xlock starvation by setting slock shrink-only #19313
  • release-8.5
    • txn_types: add shrink-only flag to shared locks (#19280) #19424
• Scan lock for shared locks.
  • master
    • storage: support hard limit when scan_lock seeing shared locks #19338

tidb & client-go

• 8.5-shared-lock
  • Update the LockKeys interface to support shared locks.
    • *: add basic support for shared lock client-go#1784
    • txnkv: fix the issue that lockKeys doesn't release the internal rlock client-go#1820
  • Modify the foreign key check logic to use shared locks.
    • *: add basic support for shared lock pingcap/tidb#64432
  • Coprocessor resolve shared locks
    • copr: support resolving shared locks pingcap/tidb#64928
  • Disable some features not compatible yet
    • txn: change default settings for shared lock hotfix pingcap/tidb#64931
  • tidb side integration tests
    • txn: add tests for shared lock & disable fair lock pingcap/tidb#65004
• master
  • txn: support shared lock client-go#1833
  • *: implement shared lock for foreign key pingcap/tidb#65752
• release-8.5
  • txn: support shared lock (#1833) client-go#1897