Multi-Version Concurrency Control

With AWS DMS, you can implement Multi-Version Concurrency Control (MVCC) to manage concurrent access to data during database migrations. MVCC is a concurrency control method that maintains multiple versions of database objects, allowing readers and writers to access the data simultaneously without blocking or causing conflicts.

Oracle usage

Two primary lock types exist in Oracle: exclusive locks and share locks, which implement the following high-level locking semantics:

Lock types can be divided into four categories: DML locks, DDL locks, Explicit (Manual) data locking, and System locks. The following sections describe each category.

DML locks

DML locks preserve the integrity of data accessed concurrently by multiple users. DML statements acquire locks automatically both on row and table levels.

The following table provides additional information regarding row and table locks.

DDL locks

The main purpose of a DDL lock is to protect the definition of a schema object while it is modified by an ongoing DDL operation such as ALTER TABLE EMPLOYEES ADD <COLUMN>.

Explicit (Manual) data locking

Users have the ability to explicitly create locks to achieve transaction-level read consistency for when an application requires transactional exclusive access to a resource without waiting for other transactions to complete. Explicit data locking can be performed at the transaction level or the session level:

System locks

System locks include latches, mutexes, and internal locks.

Examples

Explicitly lock data using the LOCK TABLE command.

-- Session 1
LOCK TABLE EMPLOYEES IN EXCLUSIVE MODE;
-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;
-- Session 2 waits for session 1 to COMMIT or ROLLBACK

Explicitly lock data using the SELECT… FOR UPDATE command. Oracle obtains exclusive row-level locks on all the rows identified by the SELECT FOR UPDATE statement.

-- Session 1
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;
-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;
-- Session 2 waits for session 1 to COMMIT or ROLLBACK

For more information, see Automatic Locks in DDL Operations, Automatic Locks in DML Operations, and Automatic and Manual Locking Mechanisms During SQL Operations in the Oracle documentation.

PostgreSQL usage

PostgreSQL provides various lock modes to control concurrent access to data in tables. Data consistency is maintained using a Multi-Version Concurrency Control (MVCC) mechanism. Most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables aren’t dropped or modified in incompatible ways while the command runs.

The MVCC mechanism prevents viewing inconsistent data produced by concurrent transactions performing updates on the same rows. MVCC in PostgreSQL provides strong transaction isolation for each database session and minimizes lock-contention in multiuser environments.

Implicit and explicit transactions (Auto-commit behavior)

Unlike Oracle, PostgreSQL uses auto-commit for transactions by default. However, there are two options to support explicit transactions, which are similar to the default behavior in Oracle (non-auto-commit).

\set AUTOCOMMIT off

With explicit transactions:

Similar to Oracle, PostgreSQL automatically acquires the necessary locks to control concurrent access to data. PostgreSQL implements the following types of locks.

Table-level locks

Row-level locks

Page-level locks

Shared or exclusive locks used to control read or write access to table pages in the shared buffer pool. They are released immediately after a row is fetched or updated.

Deadlocks

Occur when two or more transactions are waiting for one another to release each lock.

Transaction-level locking

PostgreSQL doesn’t support session isolation levels, although it can be controlled by transactions.

PostgreSQL LOCK TABLE synopsis

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

If ONLY and * are specified, the command stops with an error.

There is no UNLOCK TABLE command. Locks are always released at the end of a transaction (COMMIT / ROLLBACK).

You can use the LOCK TABLE command inside a transaction and it should appear after the START TRANSACTION statement.

Examples

Obtain an explicit lock on a table using the LOCK TABLE command.

-- Session 1
START TRANSACTION;
LOCK TABLE EMPLOYEES IN EXCLUSIVE MODE;

-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;

-- Session 2 waits for session 1 to COMMIT or ROLLBACK

Explicit lock by the SELECT… FOR UPDATE command. PostgreSQL obtains exclusive row-level locks on rows referenced by the SELECT FOR UPDATE statement. Must be ran inside a transaction.

-- Session 1
START TRANSACTION;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;

-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;

-- Session 2 waits for session 1 to COMMIT or ROLLBACK

PostgreSQL deadlocks

Deadlocks occur when two or more transactions acquired locks on each other’s process resources (table or row). PostgreSQL can detect Deadlocks automatically and resolve the event by aborting one of the transactions, allowing the other transaction to complete.

Simulating a deadlock:

Session 1 - step1:
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
Session 2 - step2:
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
Session 2 step3:
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
Session 1 - step4:
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

Session 1 is waiting for Session 2 and Session 2 is waiting for Session 1 = deadlock.

Real-time monitoring of locks using catalog tables:

Monitor locks using the following SQL query.

SELECT
block.pid AS block_pid,
block_stm.usename AS blocker_user,
block.mode AS block_mode,
block.locktype AS block_locktype,
block.relation::regclass AS block_table,
block_stm.query AS block_query,
block.GRANTED AS block_granted,
waiting.locktype AS waiting_locktype,
waiting_stm.usename AS waiting_user,
waiting.relation::regclass AS waiting_table,
waiting_stm.query AS waiting_query,
waiting.mode AS waiting_mode,
waiting.pid AS waiting_pid
from pg_catalog.pg_locks AS waiting JOIN
pg_catalog.pg_stat_activity AS waiting_stm
ON (waiting_stm.pid = waiting.pid)
join pg_catalog.pg_locks AS block
ON ((waiting."database" = block."database"
AND waiting.relation = block.relation)
OR waiting.transactionid = block.transactionid)
join pg_catalog.pg_stat_activity AS block_stm
ON (block_stm.pid = block.pid)
where NOT waiting.GRANTED
and waiting.pid <> block.pid;

Generate an explicit lock using the SELECT… FOR UPDATE statement.

-- Session 1
START TRANSACTION;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;

-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;

-- Session 2 waits for session 1 to COMMIT or ROLLBACK

Run the SQL query from step #1 monitoring locks while distinguishing between the “blocking” and “waiting” session.

-[ RECORD 1 ]-
block_pid        | 31743
blocker_user     | aurora_admin
block_mode       | ExclusiveLock
block_locktype   | transactionid
block_table      |
block_query      | SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;
block_granted    | t
waiting_locktype | transactionid
waiting_user     | aurora_admin
waiting_table    |
waiting_query    | UPDATE EMPLOYEES
                 | SET SALARY=SALARY+1000
                 | WHERE EMPLOYEE_ID=114;
waiting_mode     | ShareLock
waiting_pid      | 31996

Summary

v$lock;
v$locked_object;
v$session_blockers;

pg_locks
pg_stat_activity

BEGIN;
LOCK TABLE employees IN SHARE
ROW EXCLUSIVE MODE;

LOCK TABLE employees IN SHARE
ROW EXCLUSIVE MODE;

SELECT * FROM employees
WHERE employee_id=102 FOR UPDATE;

BEGIN;
SELECT * FROM employees WHERE
employee_id=102 FOR UPDATE;

SELECT…FOR UPDATE

SELECT … FOR…
KEY SHARE
SHARE
NO KEY UPDATE
UPDATE

For more information, see LOCK and Explicit Locking in the PostgreSQL documentation.