RDBMS concurrency control
- mvcc in rdbms
- transaction isolation levels in postgres
- select for update construct
- advisory locks
- predicate locking
- concurrency control mechanisms in databases
- vacuum process in postgresql
1. MVCC – Multi-Version Concurrency Control
This is the default mechanism in various RDBMS, including Postgres. When performing UPDATE/DELETE operations, instead of overwrite, a new version of the record is created. Old data is marked as ‘DEAD’, and will be removed by the VACUUM operation.
VACUUM can be called either manually or automatically (AUTOVACUUM). Removal of ‘DEAD’ data occurs when all active transactions stop seeing old versions.
MVCC resolves multi-threaded access problems at the basic level:
- A record is not blocked by readers; instead, a new version of the data is created
- At the moment of the write operation, readers see a snapshot of the previous data.
At the same time, this happens without read locks, but changes can block each other. In this case, the behavior will depend on the transaction isolation level.
2. Transactions
Some tasks of coordinating data access can be effectively solved using transactions and configuring the transaction isolation level:
- Read Committed
- Repeatable Read
- Serializable
3. select for update
The ‘select for update’ construct is taking a lock on specific records at the DB level. The lock will be taken immediately after executing this construct, and released at the moment of COMMIT and ROLLBACK of the transaction.
A feature of the construct is that it is not a predicate lock, i.e. it is impossible to lock a record that does not yet exist.
4. Advisory locks
The most flexible option – the DB client must manually take a lock on a specific int number. The identifier can be any number, not necessarily an existing record id, although this is one of the options.
It looks like this:
SELECT pg_advisory_xact_lock(42); -- Lock for the duration of the current transaction
There is also an option to take a lock for the duration of the session.
In other words – we took a lock with id=42. If another thread tries to take a lock with id=43 – this will happen immediately, but if with id=42 – it will require the initial transaction to complete.