Beginners Guide to Transactions and Locking in MySQL

What is a MySQL Transaction

A transaction is a way for you to execute one or more SQL statements as a single unit of work so that either all or none of the statements succeed. This appears to happen in isolation of work being done by any other transaction. If all the statements succeed, you commit the transaction to record their effect permanently in the database. If an error occurs during the transaction, you roll back to cancel it. Any statements executed up to that point within the transaction are undone, leaving the database in the state it was in prior to the point at which the transaction began.

Note: In MySQL, transactions are supported only for those tables that use a transactional storage engine (like InnoDB). These statements have no noticeable effect on tables managed by a non-transactional storage engine.

The figure above is an example of an attempted transfer of $1,000 from your savings account to your checking account. You would not be happy if the money was successfully withdrawn from your savings account but was never deposited into your checking account.

To protect against this kind of error, the program that handles your transfer request would first begin a transaction and then issue the SQL statements needed to move the money from your savings to your checking account. It ends the transaction only if everything succeeds. If a problem occurs, the program would instruct the server to undo all the changes made since the transaction began.

ACID

ACID stands for:

Atomic – All statements execute successfully or are canceled as a unit.
Consistent – A database that is in a consistent state when a transaction begins is left in a consistent state by the transaction.
Isolated – One transaction does not affect another.
Durable – All changes made by transactions that complete successfully are recorded properly in the database. Changes are not lost.

Transactional processing provides stronger guarantees about the outcome of the database operations, but also requires more overhead in CPU cycles, memory, and disk space. Transactional properties are essential for some applications but not for others, and you can choose which ones make the most sense for your applications.

Financial operations typically need transactions, and the guarantees of data integrity outweigh the cost of additional overhead. On the other hand, for an application that logs webpage accesses to a database table, a loss of a few records if the server host crashes might be tolerable.

Transaction SQL Control Statements

START TRANSACTION (or BEGIN): Explicitly begins a new transaction
SAVEPOINT: Assigns a location in the process of a transaction for future reference
COMMIT: Makes the changes from the current transaction permanent
ROLLBACK: Cancels the changes from the current transaction
ROLLBACK TO SAVEPOINT: Cancels the changes executed after the savepoint
RELEASE SAVEPOINT: Removes the savepoint identifier
SET AUTOCOMMIT: Disables or enables the default autocommit mode for the current connection

SQL Control Statements Flow: Example

The flow shown in the slide assumes that the AUTOCOMMIT mode is enabled. The transaction is automatically committed if the statement executes successfully, permanently saving any changes caused by executing the statement. If the statement does not execute successfully, the transaction is automatically rolled back, undoing any changes resulting from executing the statement.

AUTOCOMMIT Mode

AUTOCOMMIT mode determines how and when new transactions are started. When AUTOCOMMIT mode is enabled, each SQL statement implicitly starts a new transaction. When each statement completes, the transaction is committed. The result of this is that when AUTOCOMMITmode is enabled, changes to transactional tables are effective immediately.

You can override this behavior by explicitly starting a new transaction with START TRANSACTION. Subsequent statements are not persisted to the tables until you end the transaction with an explicit COMMIT.

Set AUTOCOMMIT mode to 0 in an option file, or:

SET GLOBAL AUTOCOMMIT=0;
SET SESSION AUTOCOMMIT=0;
SET @@AUTOCOMMIT :=0;

Check the AUTOCOMMIT setting with SELECT:

SELECT @@AUTOCOMMIT;

When you enable AUTOCOMMIT you do not disable transactions. For example, when you execute a single statement that affects multiple rows and one modification fails, the outcome differs when you use a transactional storage engine such as InnoDB, and a nontransactional engine such as MyISAM. With a MyISAM table, the statement terminates as soon as the error is encountered, leaving the rows that have already been inserted in the table. With an InnoDB table, all rows that have already been inserted are withdrawn from the table and the operation, therefore, has no net effect.

By default, AUTOCOMMIT is enabled globally. Disable AUTOCOMMIT globally within an option file, or disable it for a specific session by setting the autocommit variable.

Implicit Commit

The COMMIT statement always explicitly commits the current transaction. Other transaction control statements (such as those listed below) also have the effect of implicitly committing the current transaction.

- SQL statements that implicitly commit:

START TRANSACTION
SET AUTOCOMMIT = 1

Apart from these transaction control statements, other types of statements may have the effect of implicitly committing (and thereby terminating) the current transaction. These statements behave as if you issued a COMMIT prior to executing the actual statement. In addition, these statements are themselves non-transactional, which means that they cannot be rolled back if they succeed.

- Non-transactional statements that cause a commit: 1. Data definition statements (ALTER, CREATE, DROP) 2. Administrative statements (GRANT, REVOKE, SET PASSWORD) 3. Locking statements (LOCK TABLES, UNLOCK TABLES)

- Example of statements that cause an implicit commit:

TRUNCATE TABLE
LOAD DATA INFILE

In general, the data definition statements, data access and user management (administrative) statements, and locking statements have this effect.

NOTE: There are a number of exceptions, and not all of these statements cause an implicit commit in all versions of the server. However, it is advisable to treat all non-DML statements as if they cause an implicit commit.

Transactional Storage Engines

To ensure that a transactional storage engine is compiled into your MySQL server and that it is available at run time, use the SHOW ENGINES statement.

mysql> SHOW ENGINES\G
********************* 2. row *********************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking,
and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
********************* 1. row *********************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
 ...

The value in the Support column is YES or NO to indicate that the engine is or is not available. If the value is DISABLED, the engine is present but turned off. The value DEFAULT indicates the storage engine that the server uses by default. The engine designated as DEFAULT should be considered available. The Transactions, XA, and Savepoints columns indicate whether the storage engine supports those features.

Transaction Isolation Problems

Multiple pending transactions may exist simultaneously within the server (at most one transaction per session). When multiple clients are accessing data from the same table concurrently, the following consistency issues can occur:

Dirty read: Suppose that transaction T1 modifies a row. If transaction T2 reads the row and sees the modification even though T1 has not committed it, that is a dirty read. One reason this is a problem is that if T1 rolls back, the change is undone but T2 does not know that.
Non-repeatable read: The same read operation yields different results when it is repeated at a later time within the same transaction.
Phantom read: Suppose that transactions T1 and T2 begin, and T1 reads some rows. If T2 inserts a new row and T1 sees that row when it repeats the same read operation, a phantom read has occurred (the new row being the phantom row).

Isolation Levels

If one client’s transaction changes data, should transactions for other clients see those changes or should they be isolated from them? The transaction isolation level determines the ways in which simultaneous transactions interact when accessing the same data.

Use storage engines to implement isolation levels. Isolation level choices vary among database servers, so the levels as implemented by InnoDB might not correspond exactly to levels as implemented in other database systems.

InnoDB implements four isolation levels that control the extent to which changes made by transactions are noticeable to other simultaneously occurring transactions:

READ UNCOMMITTED: Allows dirty reads, non-repeatable reads, and phantom reads to occur
READ COMMITTED: Allows non-repeatable reads and phantoms to occur. Uncommitted changes remain invisible.
REPEATABLE READ: Gets the same result both times, regardless of committed or uncommitted changes made by other transactions. In other words, it gets a consistent result from different transactions on the same data.
SERIALIZABLE: Similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes

Isolation Level	Dirty Read	Non-Repeatable read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Not possible	Possible	Possible
Repeatable Read	Not possible	Not possible	Possible*
Serializable	Not possible	Not possible	Not possible

* Not possible for InnoDB, which uses snapshots for Repeatable Read.

Setting the Isolation Level

Set the [isolation_level] value in the option file or on the command line to READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For the SET TRANSACTION ISOLATION LEVEL statement, set the [isolation_level] value to READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, or SERIALIZABLE.

[mysqld]
transaction-isolation = [isolation_level]

Syntax for SET TRANSACTION ISOLATION LEVEL statement.

SET GLOBAL TRANSACTION ISOLATION LEVEL [isolation_level];
SET SESSION TRANSACTION ISOLATION LEVEL [isolation_level];
SET TRANSACTION ISOLATION LEVEL [isolation_level];

The transaction level can be set either at the global or at the session level. Without an explicit specification, the transaction isolation level is set at the session level. For example, the following statement sets the isolation level to READ COMITTED for the current mysql session:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This is equivalent to:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

To set the default level for all subsequent mysql connections, use the GLOBAL keyword instead of SESSION:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

NOTE: Setting the default transaction isolation level globally applies to all new client connections established from that point on. Existing connections are unaffected

Global Isolation Level

A client can always modify the transaction isolation level for its own session, but changing the default transaction isolation level globally requires the SUPER privilege. To find out what the current isolation level is, use the tx_isolation server variable. For example:

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

mysql> SELECT @@global.tx_isolation,
        @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| READ-UNCOMMITTED      | REPEATABLE-READ        |
+-----------------------+------------------------+

When this variable is used unprefixed, the session transaction isolation level is returned. Use the global and session prefixes to explicitly obtain the global or session isolation level, respectively.

You can also use the server variable to set the transaction isolation level. The same isolation levels are valid as in the SET TRANSACTION ISOLATION LEVEL syntax, except that it must be a string representation (rather than plain keywords) and you must separate the words that define an isolation level by a single hyphen (dash or minus sign) rather than whitespace.

Locking Concepts

Locking is a mechanism that prevents problems from occurring with simultaneous data access by multiple clients. It places a lock on data on behalf of one client to restrict access by other clients to the data until the lock has been released. The lock allows access to data by the client that holds the lock but places limitations on what operations can be done by other clients that are contending for access. The effect of the locking mechanism is to serialize access to data so that when multiple clients want to perform conflicting operations, each must wait its turn. Not all types of concurrent access produce conflicts, so the type of locking that is necessary to allow a client to access data depends on whether the client wants to read or write:

If a client wants to read data, other clients that want to read the same data do not produce conflict, and they all can read at the same time. However, another client that wants to write (modify) data must wait until the read has finished.
If a client wants to write data, all other clients must wait until the write has finished, regardless of whether those clients want to read or write.

A reader must block writers but must not block other readers. A writer must block both readers and writers. Read locks and write locks allow these restrictions to be enforced. Locking makes clients wait for access until it is safe for them to proceed. In this way, locks prevent data corruption by disallowing concurrent conflicting changes and reading of data that is in the process of being changed.

Explicit Row Locks

InnoDB supports two locking modifiers that may be added to the end of SELECT statements:

1. LOCK IN SHARE MODE clause: A shared lock, which means that no other transactions can take exclusive locks but other transactions can also use shared locks. Because normal reads do not lock anything, they are not affected by the locks.

SELECT * FROM Country WHERE Code='AUS'
LOCK IN SHARE MODE\G

2. FOR UPDATE clause: Locks each selected row with an exclusive lock, preventing others from acquiring any lock on the rows but allowing reading of the rows.

SELECT counter_field INTO @@counter_field
FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field =
@@counter_field + 1;

In the REPEATABLE READ isolation level, LOCK IN SHARE MODE can be added to SELECT operations to force other transactions to wait for the transaction if they want to modify the selected rows. This is similar to operating at the SERIALIZABLE isolation level, for which InnoDB implicitly adds LOCK IN SHARE MODE to SELECT statements that have no explicit locking modifier. If it selects rows that have been modified in an uncommitted transaction, it locks the SELECT until that transaction commits.

Deadlocks

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. A deadlock can occur when:

Transactions acquire locks on multiple tables, but in the opposite order
Statements such as UPDATE or SELECT … FOR UPDATE lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue
There are multiple transactions, and one is waiting for the other(s) to complete to form a cycle. For example, T1 is waiting for T2, T2 is waiting for T3, and T3 is waiting for T1.

Rollback When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

If a SELECT statement calls a stored function in a transaction, and a statement within the function fails, that statement rolls back. Also, if you perform a ROLLBACK after that, the entire transaction rolls back.

Transaction Example: Deadlock

The first DELETE statement hangs while waiting for a lock. During the execution of the UPDATE statement, a deadlock is detected in Session 2 due to the conflicts between the two sessions. The UPDATE is aborted, allowing the DELETE from Session 1 to complete.

Implicit Locks

The MySQL server locks the table (or row) based on the commands issued and the storage engines being used:

Operation	InnoDB	MyISAM
SELECT	No lock*	Table-level shared lock
UPDATE/DELETE	Row-level exclusive lock	Table-level exclusive lock
ALTER TABLE	Table-level shared lock	Table-level shared lock

* No lock unless SERIALIZABLE level, LOCK IN SHARE MODE, or FOR UPDATE is used

InnoDB tables use row-level locking so that multiple sessions and applications can read from and write to the same table simultaneously, without making each other wait and without producing inconsistent results. For this storage engine, avoid using the LOCK TABLES statement; it does not offer any extra protection but instead reduces concurrency.

The automatic row-level locking makes these tables suitable for your busiest databases with your most important data, while also simplifying application logic because you do not need to lock and unlock tables. Consequently, the InnoDB storage engine is the default in MySQL 5.6.