Transactional locking mechanism and Multi-version concurrency control(MVCC)

1. Overview

 

1.1 Data Concurrency and Consistency

• Data concurrency: Ensures that users can access data at the same time
• Data consistency: Ensures that each user sees a consistent view of the data, including visible changes made by the user's own transactions and committed transactions of other users

2. Multiversion Read Consistency (MVCC)

2.1 Oracle

Multi-versioning is the ability to simultaneously materialize multiple versions of data. multi-version read consistency, which means that database queries have the following characteristics

Features	Description
Read-consistent queries	The data returned by a query is committed and consistent with respect to a single point in time. No dirty read
Nonblocking queries	Readers and writers of data do not block one another

To illustrate the problem with dirty reads, suppose one transaction updates a column value without committing. A second transaction reads the updated and dirty (uncommitted) value. The first session rolls back the transaction so that the column has its old value, but the second transaction proceeds using the updated value, corrupting the database. Dirty reads compromise data integrity, violate foreign keys, and ignore unique constraints.

2.2 PostgreSQL

Unlike most other database systems that use locks for concurrency control, Postgres maintains data consistency by using a multiversion model. This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session.

The main difference between multiversion and lock models is that in MVCC locks acquired for querying (reading) data don't conflict with locks acquired for writing data and so reading never blocks writing and writing never blocks reading.

3. Transactional Locking Mechanism

3.1 Use of Locks

In a single-user database, locks are not necessary because only one user is modifying information. However, when multiple users are accessing and modifying data, the database must provide a way to prevent concurrent modification of the same data. Locks achieve the following important database requirements:

Feature	Description
Consistency	The data a session is viewing or changing must not be changed by other sessions until the user is finished.
Integrity	The data and structures must reflect all changes made to them in the correct sequence.

3.2 Locking Behavior

The database maintains several different types of locks, depending on the operation that acquired the lock. In general, the database uses two types of locks: 

3.2.1 Exclusive locks

• Only one exclusive lock can be obtained on a resource such as a row or a table
• This mode prevents the associated resource from being shared. A transaction obtains an exclusive lock when it modifies data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

3.2.2 Share locks

• Many share locks can be obtained on a single resource.
• This mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer who needs an exclusive lock. Several transactions can acquire share locks on the same resource.

Locks affect the interaction of readers and writers. A reader is a query of a resource, whereas a writer is a statement modifying a resource. The following rules summarize the locking behavior of the Database for readers and writers:

• A row is locked only when modified by a writer.

When a statement updates one row, the transaction acquires a lock for this row only. By locking table data at the row level, the database minimizes contention for the same data. Under normal circumstances, the database does not escalate a row lock to the block or table level.

• A writer of a row blocks a concurrent writer of the same row.

If one transaction is modifying a row, then a row lock prevents a different transaction from modifying the same row simultaneously.

• A reader never blocks a writer.

Because a reader of a row does not lock it, a writer can modify this row. The only exception is a SELECT ... FOR UPDATE statement, which is a special type of SELECT statement that does lock the row that it is reading.

• A writer never blocks a reader.

When a row is being changed by a writer, the database uses undo data to provide readers with a consistent view of the row.

3.3 Transaction Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshot	No	No	No
Serializable	No	No	No

3. References

https://www.postgresql.org/docs/7.1/mvcc.html

https://en.wikipedia.org/wiki/Multiversion_concurrency_control

https://www.researchgate.net/publication/276202834_Data-based_collaboration_on_a_grand_scale#pf5

https://docs.oracle.com/cd/E25054_01/server.1111/e25789/consist.htm#targetText=Locking%20Mechanisms,transactions%20accessing%20the%20same%20resource.

https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-2017#targetText=Transactions%20specify%20an%20isolation%20level,modifications%20made%20by%20other%20transactions.