Multi-Version Concurrency Control を日本語で読む

Source : Multi-Version Concurrency Control

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.

AWS DMSを使用すると、データベース移行中にデータへの同時アクセスを管理するために、マルチバージョン並行性制御（MVCC）を実装できます。MVCCは、データベースオブジェクトの複数バージョンを保持することで、リーダーとライターがデータに同時にアクセスしても、ブロックや競合を引き起こすことなく、並行処理を可能にする並行性制御手法です

Feature compatibility	AWS SCT / AWS DMS automation level	AWS 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:

Oracleには主に2つのロックタイプが存在します。排他ロック（exclusive locks）と共有ロック（share locks）です。これらは以下のような高レベルのロックセマンティクスを実現します。

• Writers never block readers.
  • ライター（書き込み）はリーダー（読み込み）をブロックしません。
• Readers never block writers.
  • リーダーはライターをブロックしません。
• Oracle never escalates locks from row to page and table level, which reduces potential deadlocks.
  • Oracleはロックのエスカレーション（行ロックからページや表レベルへの拡大）を行わないため、潜在的なデッドロックを減らせます
• In Oracle, users can issue explicit locks on specific tables using the LOCK TABLE statement.
  • また、ユーザーはLOCK TABLE文を使用して特定のテーブルに明示的にロックをかけることができます。

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.

ロックタイプは4つのカテゴリに分類できます：DMLロック、DDLロック、明示的（手動）データロック、およびシステムロックです。以下のセクションでそれぞれのカテゴリについて説明します。

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.

DMLロックは、複数のユーザーが同時にアクセスするデータの整合性を保護します。DML文は、行レベルおよびテーブルレベルの両方で自動的にロックを取得します。

• Row locks or TX — Obtained on a single row of a table by one the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT …​ 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 a COMMIT or ROLLBACK.
  • 行ロック（またはトランザクションロック）は、INSERT、UPDATE、DELETE、MERGE、SELECT … FOR UPDATEといった文によって、テーブルの単一の行に対して取得されます。トランザクションが行ロックを取得すると、同時にテーブルロックも取得され、DDL（データ定義言語）によるテーブルの変更が競合を引き起こすのを防ぎます。このロックは、トランザクションがCOMMITまたはROLLBACKによって終了するまで保持されます
• Table locks or TM — When performing one of the following DML operations: INSERT, UPDATE, DELETE, MERGE, and SELECT …​ 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 a COMMIT or ROLLBACK.
  • テーブルロック（またはTMロック）は、INSERT、UPDATE、DELETE、MERGE、およびSELECT … FOR UPDATEといったDML操作を実行する際に、トランザクションが自動的に取得します。これは、トランザクションがCOMMITまたはROLLBACKを発行する前に、DDL（データ定義言語）によるテーブル変更が行われて競合が発生することを防ぐためです。

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 Share Lock / RS）
  • トランザクションがテーブル上のいくつかの行をロックし、それらを更新する前にロックを取得する場合に発生します
• Row Exclusive lock or RX — Occurs when the transaction holding the lock has updated table rows or used the SELECT …​ FOR UPDATE command.
  • 行排他ロック（Row Exclusive Lock / RX）
  • トランザクションがテーブルの行を更新した場合や、SELECT … FOR UPDATEコマンドを使用した場合に発生します。
• 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 Table Lock / S）
  • 1つのトランザクションがテーブルをロックし、他のトランザクションがテーブルを参照（SELECT … FOR UPDATEを除く）できるようにします。また、テーブルの共有ロックを1つのトランザクションのみが保持している場合に限り、更新も許可されます。複数のトランザクションが同時に共有テーブルロックを取得できます。
• 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.
  • 共有行排他テーブルロック（Share Row Exclusive Table Lock / SRX）
    共有テーブルロックと似ていますが、このロックは一度に1つのトランザクションのみが特定のテーブルに対して取得できます。
• 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.
  • 排他テーブルロック（Exclusive Table Lock / X）
  • 最も制限の厳しいロックタイプで、このロックを保持しているトランザクションのみがテーブルに排他的に書き込みアクセスできます。1つのテーブルに対して、同時に1つのトランザクションのみがXロックを取得できます。

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

Statement	Row locks	Table lock mode	RS	RX	S	SRX	X
SELECT …​ FROM table …​	—	none	Y	Y	Y	Y	Y
INSERT INTO table …​	Yes	SX	Y	Y	N	N	N
UPDATE table …​	Yes	SX	Y	Y	N	N	N
MERGE INTO table …​	Yes	SX	Y	Y	N	N	N
DELETE FROM table …​	Yes	SX	Y	Y	N	N	N
SELECT …​ FROM table FOR UPDATE OF…​	Yes	SX	Y	Y	N	N	N
LOCK TABLE table IN…​	—
ROW SHARE MODE		SS	Y	Y	Y	Y	N
ROW EXCLUSIVE MODE		SX	Y	Y	N	N	N
SHARE MODE		S	Y	N	Y	N	N
SHARE ROW EXCLUSIVE MODE		SSX	Y	N	N	N	N
EXCLUSIVE MODE		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>.

DDLロックの主な目的は、ALTER TABLE EMPLOYEES ADD <COLUMN> などの進行中のDDL操作によってスキーマオブジェクトの定義が変更される際に、その定義を保護することです。

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.

システムロックには、ラッチ、ミューテックス、および内部ロックが含まれます。

補足：
「ラッチ」は主にデータベースの内部処理で一時的な排他制御に使用され、「ミューテックス」はスレッド間での排他制御に使われることが多い同期機構です。「内部ロック」は、データベース管理システム（DBMS）がリソースの整合性を保つために内部的に使用する各種ロック。

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.

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.

MySQLのInnoDBストレージエンジンでは、テーブル内のデータへの同時アクセスを制御するためにさまざまなロックモードが提供されています。データの一貫性は、マルチバージョン並行制御（MVCC）メカニズムによって維持されます。ほとんどのMySQLコマンドは、参照しているテーブルがコマンド実行中に不整合な方法で削除や変更されないように、適切なロックモードを自動的に取得します。

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.

MVCC（マルチバージョン並行制御）メカニズムは、同じ行に対して複数のトランザクションが同時に更新を行うことで生じるデータの不整合を防ぎます。MVCCは各データベースセッションに対して強力なトランザクション分離を提供し、マルチユーザー環境でのロック競合を最小限に抑えます。

• 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.
  • Oracleと同様に、MVCCによってクエリ（読み取り）用に取得されるロックは、データの書き込み用に取得されるロックと競合しません。読み取りは書き込みをブロックせず、書き込みも読み取りをブロックしません。
• 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.
  • Oracleと同様に、MySQL（特にInnoDB）は、一定数の行ロックが閾値を超えた場合に、テーブル全体を書き込み用にロックするようなロックエスカレーションを行いません

InnoDB uses three additional fields for each row:

InnoDBは各行に3つの追加フィールドを使用します。

• 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.
  • 新しい行が挿入されるたびに単調増加する行IDを含みます。

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).

Oracleとは異なり、MySQLはデフォルトでトランザクションに自動コミット（auto-commit）を使用します。ただし、Oracleのデフォルト動作（自動コミットなし）に似た明示的なトランザクションをサポートするためのオプションが2つあります。

• Use the START TRANSACTION (or BEGIN TRANSACTION) statements and then COMMIT or ROLLBACK.
  • START TRANSACTION（または BEGIN TRANSACTION）文を使用し、その後 COMMIT または ROLLBACK で終了させます。
• Set AUTOCOMMIT to OFF at the session level.
  • セッションレベルで AUTOCOMMIT を OFF に設定します。

With explicit transactions:
明示的なトランザクションの場合:

• Users can explicitly issue a lock similar to the LOCK TABLE statement in Oracle.
  • Oracle の LOCK TABLE 文と同様に、ユーザーは明示的にロックを発行できます。
• SELECT… FOR UPDATE is supported.
  • SELECT… FOR UPDATE がサポートされています。

Unlike Oracle there are only two types of table-level locks when using the LOCK TABLE command: read lock and write lock.

Oracleとは異なり、MySQLではLOCK TABLEコマンドを使用した場合のテーブルレベルロックは「読み取りロック（read lock）」と「書き込みロック（write lock）」の2種類のみです

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.
  • 複数のセッションが同時にテーブルのREADロックを取得できます。
• Other sessions can read the table without explicitly acquiring a READ lock.
  • 他のセッションは、明示的にREADロックを取得しなくても、テーブルを読み取ることができます。
• For InnoDB tables, READ LOCAL is the same as READ.
  • InnoDBテーブルの場合、READ LOCALはREADと同じです。

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.
  • WRITE ロックが保持されている間、他のセッションからの当該テーブルに対するロック要求はブロックされます。
• The LOW_PRIORITY modifier is deprecated and has no effect.
  • LOW_PRIORITY 修飾子は非推奨であり、効果はありません。

For row-level locking:

行レベルのロックについては以下のとおりです。

• Intention shared IS lock — Indicates that a transaction intends to set a shared lock.
  • 意図共有ロック（Intention Shared / IS） — トランザクションが共有ロックを取得しようとしていることを示します。
• Intention exclusive IX lock — Indicates that a transaction intends to set an exclusive lock.
  • 意図排他ロック（Intention Exclusive / IX） — トランザクションが排他ロックを取得しようとしていることを示します。

	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.

レコードロックは、インデックスレコードに対するロックです。たとえば SELECT id FROM emps WHERE id = 50 FOR UPDATE というクエリは、emps.id の値が 50 である行に対する他のトランザクションからの INSERT、UPDATE、DELETE を防ぎます。

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.

テーブルにインデックスが定義されていない場合でも、レコードロックは常にインデックスレコードをロックします。このような場合、InnoDB は隠しクラスタ化インデックスを作成し、それをレコードロックに使用します。

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.

ギャップロックは、インデックスレコード間の隙間、最初のインデックスレコードの前、または最後のインデックスレコードの後に対するロックです。たとえば SELECT id FROM emps WHERE id BETWEEN 50 and 80 FOR UPDATE は、範囲内にすでに値があるかどうかに関わらず、他のトランザクションが emps.id 列に 60 を挿入することを防ぎます。範囲内の既存値間にあるすべての隙間がロックされるためです。

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.

デッドロックは、2 つ以上のトランザクションが互いのプロセスリソース（テーブルや行など）にロックを取得し合った場合に発生します。MySQL はデッドロックを自動的に検出し、片方のトランザクションを中断してもう一方を完了させることでこのイベントを解決できます。

Examples

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

LOCK TABLE コマンドを使用してテーブルに明示的なロックを取得します。

-- 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.

SELECT… FOR UPDATE コマンドによる明示的ロックです。MySQL は SELECT FOR UPDATE 文で参照される行に対し、排他的な行レベルロックを取得します。この文は必ずトランザクション内で実行してください。

-- 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, LOCK TABLES and UNLOCK TABLES Statements, and SET TRANSACTION Statement in the MySQL documentation.

詳細は MySQL ドキュメントの InnoDB Multi-Versioning、LOCK TABLES and UNLOCK TABLES Statements、SET TRANSACTION Statement を参照してください。