Multi-Version Concurrency Control
With Amazon 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.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
N/A |
N/A |
N/A |
Oracle usage
Two primary lock types exist in Oracle: exclusive locks and share locks, which implement the following high-level locking semantics:
-
Writers never block readers.
-
Readers never block writers.
-
Oracle never escalates locks from row to page and table level, which reduces potential deadlocks.
-
In Oracle, users can issue explicit locks on specific tables using the
LOCK TABLE
statement.
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.
-
Row locks or TX — Obtained on a single row of a table by one the following statements:
INSERT
,UPDATE
,DELETE
,MERGE
, andSELECT … FOR UPDATE
. If a transaction obtains a row lock, a table lock is also acquired to prevent DDL modifications to the table that might cause conflicts. The lock exists until the transaction ends with aCOMMIT
orROLLBACK
. -
Table locks or TM — When performing one of the following DML operations:
INSERT
,UPDATE
,DELETE
,MERGE
, andSELECT … FOR UPDATE
, a transaction automatically acquires a table lock to prevent DDL modifications to the table that might cause conflicts if the transaction did not issue aCOMMIT
orROLLBACK
.
All table lock types:
-
Row share lock or RS — Occurs when the transaction holding the lock on the table has locked some rows in the table before updating them.
-
Row Exclusive lock or RX — Occurs when the transaction holding the lock has updated table rows or used the
SELECT … FOR UPDATE
command. -
Share table lock or S — One transaction locks the table and allows other transactions to query the table (exclude
SELECT … FOR UPDATE
), it also allows updates only if a single transaction holds the share table lock. Multiple transactions may hold a share table lock concurrently. -
Share row exclusive table lock or SRX — Similar to S lock but with this lock, only a single transaction at a time can acquire this lock on a given table.
-
Exclusive table lock or X — Most restrictive lock type, it allows the transaction that holds the lock an exclusive write access to the table. Only one transaction can obtain an X lock for a table.
The following table provides additional information regarding row and table locks.
Statement | Row locks | Table lock mode | RS | RX | S | SRX | X |
---|---|---|---|---|---|---|---|
|
— |
none |
Y |
Y |
Y |
Y |
Y |
|
Yes |
SX |
Y |
Y |
N |
N |
N |
|
Yes |
SX |
Y |
Y |
N |
N |
N |
|
Yes |
SX |
Y |
Y |
N |
N |
N |
|
Yes |
SX |
Y |
Y |
N |
N |
N |
|
Yes |
SX |
Y |
Y |
N |
N |
N |
|
— |
||||||
|
SS |
Y |
Y |
Y |
Y |
N |
|
|
SX |
Y |
Y |
N |
N |
N |
|
|
S |
Y |
N |
Y |
N |
N |
|
|
SSX |
Y |
N |
N |
N |
N |
|
|
X |
N |
N |
N |
N |
N |
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 or 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:
-
Transaction level
-
SET TRANSACTION ISOLATION LEVEL
-
LOCK TABLE
-
SELECT … FOR UPDATE
-
-
Session level
-
ALTER SESSION SET ISOLATION 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
MySQL usage
When using InnoDB, MySQL 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 MySQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not 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 provides strong transaction isolation for each database session and minimizes lock-contention in multi-user environments.
-
Similar to Oracle, MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data. Reads never block writes and writes never blocks reads.
-
Similar to Oracle, MySQL does not escalate locks to table-level such as when an entire table is locked for writes when a certain threshold of row locks is exceeded.
InnoDB uses three additional fields for each row:
-
DB_TRX_ID
— Indicates the transaction identifier for the last transaction that inserted or updated the row. -
DB_ROLL_PTR
— Points to an undo log record written to the rollback segment. -
DB_ROW_ID
— Contains a row ID that increases monotonically as new rows are inserted.
Implicit and explicit transactions (Auto-commit behavior)
Unlike Oracle, MySQL 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).
-
Use the
START TRANSACTION
(orBEGIN TRANSACTION
) statements and thenCOMMIT
orROLLBACK
. -
Set
AUTOCOMMIT
toOFF
at the session level.
With explicit transactions:
-
Users can explicitly issue a lock similar to the
LOCK TABLE
statement in Oracle. -
SELECT… FOR UPDATE
is supported.
Unlike Oracle there are only two types of table-level locks when using the LOCK TABLE
command: read lock and write lock.
Read lock or shared S lock
-
The session that holds the lock can only read the table.
-
Multiple sessions can acquire a
READ
lock for the table at the same time. -
Other sessions can read the table without explicitly acquiring a
READ
lock. -
For InnoDB tables,
READ LOCAL
is the same asREAD
.
Write lock or exclusive X lock
-
The session that holds the lock can read and write the table.
-
Only the session that holds the lock can access the table. No other session can access it until the lock is released.
-
Lock requests for the table by other sessions block while the
WRITE
lock is held. -
The
LOW_PRIORITY
modifier is deprecated and has no effect.
For row-level locking:
-
Intention shared IS lock — Indicates that a transaction intends to set a shared lock.
-
Intention exclusive IX lock — Indicates that a transaction intends to set an exclusive lock.
X | IX | S | IS | |
---|---|---|---|---|
X |
Not permitted |
Not permitted |
Not permitted |
Not permitted |
IX |
Not permitted |
Permitted |
Not permitted |
Permitted |
S |
Not permitted |
Not permitted |
Permitted |
Permitted |
IS |
Not permitted |
Permitted |
Permitted |
Permitted |
Records lock
A record lock is a lock on an index record. For example, the SELECT id FROM emps WHERE id = 50 FOR UPDATE
query prevents any other transaction from inserting, updating, or deleting rows where the value of emps.id
is 50.
Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses it for record locking.
Gaps lock
A gap lock is a lock on a gap between index records, before the first index record, or after the last index record. For example, SELECT id FROM emps WHERE id BETWEEN 50 and 80 FOR UPDATE
prevents other transactions from inserting a value of 60 into the emps.id
column whether or not there was already any value in the column because the gaps between all existing values in the range are locked.
Transaction-level locking
-
SET TRANSACTION ISOLATION LEVEL
-
LOCK TABLE
-
SELECT … FOR UPDATE
Syntax
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE
MySQL deadlocks
Deadlocks occur when two or more transactions acquired locks on each other’s process resources such as table or row. MySQL can detect deadlocks automatically and resolve the event by aborting one of the transactions and allowing the other transaction to complete.
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. MySQL obtains exclusive row-level locks on rows referenced by the SELECT FOR UPDATE
statement. Make sure that this statement runs 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
Summary
Description | Oracle | MySQL |
---|---|---|
Dictionary tables to obtain information about locks |
v$lock; v$locked_object; v$session_blockers; |
SHOW OPEN TABLES WHERE in_use = 1; |
Lock a table |
BEGIN; LOCK TABLE employees IN SHARE ROW EXCLUSIVE MODE; |
LOCK TABLE employees READ |
Explicit locking |
SELECT * FROM employees WHERE employee_id=102 FOR UPDATE; |
SELECT * FROM employees WHERE employee_id=102 FOR UPDATE; |
Explicit locking, options |
SELECT ... FOR UPDATE |
SELECT ... FOR UPDATE |
For more information, see InnoDB Multi-Versioning