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 differentcommands
. - Most PostgreSQL commands automatically acquire locks of appropriate modes to carry out things. Otherwise can use
LOCK
. Eg.TRUNCATE
obtains anACCESS 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
- Session level