MySQL InnoDB Transaction Model

InnoDB transaction examples (for INSERT and UPDATE/DELETE operations) will be given and READ_COMMITTED and REPEATABLE_READ isolation levels will be compared.

It is tested with Mysql 5.5.
Some of the information are taken from http://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-model.html.

1. BACKGROUND

Excerpt from MySQL documentation

1. Mysql InnoDB does locking on row level.

* Record lock: This is a lock on an index record. 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 this index for record locking

* Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

* Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

2. Mysql supports following transaction isolation levels

i. REPEATABLE READ

Features:
* Dirty Read => NO: It does not read uncommitted results.
* Repeatable read => YES: In a transaction, multiple select statements return same row with same content

* Phantom read => YES: In a transaction, multiple select statements return same result set with exact same number
* For UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

ii. READ COMMITTED
* Dirty Read: NO : It does not read uncommitted results.
* Repeatable read => NO : Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. This means that multiple select statements may return different content for the same row.
* Phantom Read => YES
* For UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.

iii. READ UNCOMMITTED
iv. SERIALIZABLE

3. In InnoDB, all user activity occurs inside a transaction. If auto-commit mode is enabled, each SQL statement forms a single transaction on its own.

4. By default, MySQL starts the session for each new connection with auto-commit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error.

5. If auto-commit mode is disabled within a session with SET auto-commit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

2. SETUP

We will investigate two scenarios.

* Two transactions making UPDATE on same table
* One transaction making INSERT after another transaction making UPDATE on same table

We will try each scenario for each of above tables

mytable
-id (Primary Key)
-name (Secondary Index)
-value

mytable_no_index
-id (Primary Key)
-name
-value

3. SCENARIO 1 - Two transactions making UPDATE

CASE 1 - Table without secondary index - mytable_no_index

i. REPEATABLE_READ

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable_no_index SET value=22 WHERE name='a'	...
Get lock and run query	UPDATE mytable_no_index SET value=22 WHERE name='b'
...	Blocked waiting for lock
...	Blocked waiting for lock
Commit Transaction	Get lock and run query
	Commit Transaction

Explanation: Since transaction iso lation level is repeatable_read, InnoDB locks the indexes until it finds the desired row. Since there is no secondary index for name column, query scans all rows. Even though WHERE clause eliminates other rows, all scanned indexes/rows are not released. As a result, second update query waits for the lock, although the first transaction updates a different row.

ii. READ_COMMITED

SCENARIO 1 Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable_no_index SET value=22 WHERE name='a'	...
Get lock and run query	UPDATE mytable_no_index SET value=22 WHERE name='b'
...	Get lock and run query
...	...
Commit Transaction	...
	Commit Transaction

Explanation: Since transaction isolation level is read_commited, InnoDB locks only the index. WHERE clause eliminates other rows releasing their lock,releasing in contrast to repeatable_read isolation level. Because of this, second update query runs without waiting any lock.

CASE 2 - Table with secondary index - mytable

i. REPEATABLE_READ

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a'	...
Get lock and run query	UPDATE mytable SET value=22 WHERE name='b'
...	Get lock and run query
...	...
Commit Transaction	...
	Commit Transaction

Explanation: Since transaction isolation level is repeatable_read, InnoDB locks the indexes until it finds the desired row. However, since there is a secondary index on name column, it will scan indexes for value a and will not scan whole table. As a result, second update query will not wait for lock and run.


Lets expand it more:

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a' and value=1	...
Get lock and run query	UPDATE mytable SET value=22 WHERE name='a' and value=2
...	Blocked waiting for lock
...	Blocked waiting for lock
Commit Transaction	Get lock and run query
	Commit Transaction

Explanation: Since both update statements scan same index records for a, first transaction lock all rows for a and second transaction waits for lock.

ii. READ_COMMITED

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a'	...
Get lock and run query	UPDATE mytable SET value=22 WHERE name='b'
...	Get lock and run query
...	...
Commit Transaction	...
	Commit Transaction

Explanation: Since transaction isolation level is read_commited, InnoDB locks only the index. Its outcome is identical to updating a table with secondary index.

4. SCENARIO 2 - One transaction making INSERT after another transaction making UPDATE

CASE 1 - Table without secondary index - mytable_no_index

i. REPEATABLE_READ

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable_no_index SET value=22 WHERE name='a'	...
Get lock and run query	INSERT INTO mytable_no_index(name, value) VALUES(‘abc’, 1)
...	Blocked waiting for lock
...	Blocked waiting for lock
Commit Transaction	Get lock and run query
	Commit Transaction

Explanation: Since transaction isolation level is repeatable_read, InnoDB locks the indexes until it finds the desired row. Since there is no secondary index for name column, query scans all rows. In addition to that, it puts gap locks for the indexes, thus disabling any insert by other transactions. This is done to block phantom reads.

ii. READ_COMMITED

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable_no_index SET value=22 WHERE name='a'	...
Get lock and run query	INSERT INTO mytable_no_index(name, value) VALUES(‘abc’, 1)
...	Get lock and run query
...	...
Commit Transaction	...
	Commit Transaction

Explanation: Since transaction isolation level is read_commited, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.

CASE 2 - Table with secondary index - mytable

i. REPEATABLE_READ

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a'	...
Get lock and run query	INSERT INTO mytable(name, value) VALUES(‘abc’, 1)
...	Blocked waiting for lock
...	Blocked waiting for lock
Commit Transaction	Get lock and run query
	Commit Transaction

Explanation: Since transaction isolation level is repeatable_read, InnoDB locks the indexes until it finds the desired row. It locks the index records for a. In addition to that, InnoDB puts gap locks before selected index, thus disabling any insert by other transactions. Since insert query will use index records for a, it will wait for lock.

Lets expand more.

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a'	...
Get lock and run query	INSERT INTO mytable(name, value) VALUES(‘bcd’, 1)
...	Get lock and run query
...	...
Commit Transaction	...
	Commit Transaction

Since bcd is after index record a, it will not wait for lock

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a'	...
Get lock and run query	INSERT INTO mytable(name, value) VALUES(‘012’, 1)
...	Blocked waiting for lock
...	Blocked waiting for lock
Commit Transaction	Get lock and run query
	Commit Transaction

Since 012 is in the gap before index record a, it will wait for lock

ii. READ_COMMITED

Transaction 1	Transaction 2
Start Transaction	Start Transaction
UPDATE mytable SET value=22 WHERE name='a'	...
Get lock and run query	INSERT INTO mytable(name, value) VALUES(‘abc’, 1)
...	Get lock and run query
...	...
Commit Transaction	...
	Commit Transaction

Explanation: Since transaction isolation level is read_commited, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.

5. DISABLING GAP LOCKS

Excerpt from MySQL documentation

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ_COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

If innodb_locks_unsafe_for_binlog variable is set to false, insert operations will not be blocked.

6. Where to Go From Here

http://dev.mysql.com/doc/refman/5.5/en/innodb-concepts.html
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html