Database Locks

tags : Database, Database Transactions, Database Indexing, PostgreSQL, Distributed Systems, Concurrency, Concurrency Consistency Models

This is about two or more sessions try to access the same data at the same time. So we put locks in the transactions. So locks are things that we put in transactions.

• In databases, “locks” have a very specific meaning.
• What engineers would call a “lock”, is referred to as a “latch” in DBMS internals terminology.
• A “lock” then is a higher level abstraction that functions as a logical lock rather than a fine-grained physical one.
• Locks are generally used to control access to database objects like rows, pages, tables, etc, to ensure data integrity and transactional consistency.
• “Latches” are low-level synchronization primitives used to protect access to internal DB structures.

HN user^

Meta §

Preventing Conflicts §

• Locks alone are not sufficient to prevent conflicts
• A concurrency control strategy must define how locks are being acquired and released
• As this also has an impact on transaction interleaving. (See Non-realtime interleaved execution model)

Types of Concurrency control §

Different DBs have differnt names and implementations, this is just generalization. See https://www.vldb.org/pvldb/vol8/p209-yu.pdf

Exclusive Locks §

Read lock §

• Prevents a resource from being written while allowing other concurrent reads.

Write lock §

Blocks both Readers and Writers concurring for the same lock

Pessimistic locking (Avoid Conflicts) §

Sidenote: RWMutex and Two Phase Locking (2PL) are similar, one is more used application development and the latter one more in database transaction safety etc.

• RWMutex, Two Phase Locking (2PL) etc.
• Atomics don’t scale either
• Such shared locking help, but they do not scale. In order to scale RWMutex,
  • Time spent by readers while holding the mutex should be significant
  • Otherwise, overhead of synchronizing between multiple readers becomes dominant, and the system won’t scale well. Because the synchronization itself is a write operation on the mutex’s internal data.
  • See Amdahl’s law - Wikipedia, How to Quantify Scalability

Optimistic locking mechanism (Allow conflicts, better scalability, Time based) §

• Allow conflicts to occur, but you need to detect them. Reader starts reading an object w/o any synchronization (optimistically hoping for success), and when it finishes it verifies that the object was not changed under its feet.

Logical clocks §

• See Clocks

State distribution §

• State distribution
• Also see Data Replication

MVCC §

• See MVCC
• NOTE: There are some variants of MVCC which use Pessimistic locking. (Eg. MV2PL)

Other locks §

Predicate locks §

Range locks §

Lock levels §

• Serialized Database Access
  • Making the database run queries one by one
  • Terrible concurrency
  • Highest level of consistency
• Table Lock
  • Lock the table for your transaction with slightly better concurrency, but concurrent table writes are still slowed.
• Row Lock
  • Locks the row you are working on even better than table locks, but if multiple transactions need this row, they will need to wait.
  • Row-level read and write locks are recorded directly in the locked rows and can be inspected using the pgrowlocks extension.
• Range locks
  • Between the last two levels of locks(Table and Row); they lock the range of values captured by the transaction and don’t allow inserts or updates within the range captured by the transaction.

Postgres Lock Modes §

What are lock modes? §

• When MVCC does not suffice, application devs can use these lock modes. They’re defined by which other lock modes they conflict with, which in-turn are used by different commands.
• Most PostgreSQL commands automatically acquire locks of appropriate modes to carry out things. Otherwise can use LOCK. Eg. TRUNCATE obtains an ACCESS EXCLUSIVE lock to carry out its actions.

Table Locks (8) §

NOTE: Some locks have “row” in the name but they still mean table level locks

• Once acquired, a lock is normally held until the end of the transaction.
• If a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to.

Row Locks (4) (Write only locks) §

• These too like table level locks are used automatically by postgres when required by different commands
• The same transaction can hold conflicting locks
  • on the same row
  • on the same row, even in different subtransactions
• 2 different transactions can never hold conflicting locks on the same row.
• Row-level locks do not affect data querying; they block only writers and lockers
• Release is similar to table lock release

TODO Advisory locks §

• Locks that applications can use
• Eg. emulate pessimistic locking strategies typical of so-called “flat file” data management systems.
• While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat
• Automatically cleaned up by the server at the end of the session
• Acquire
  • Session level
    • Held until explicitly released or the session ends
    • TODO Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released.
  • Transaction level