Menu:
innodb-deadlocks:: Deadlocks in InnoDB
To implement a large-scale, busy, or highly reliable database application, to port substantial code from a different database system, or to tune MySQL performance, it is important to understand 'InnoDB' locking and the 'InnoDB' transaction model.
This section discusses several topics related to 'InnoDB' locking and the 'InnoDB' transaction model with which you should be familiar.
*note innodb-locking:: describes lock types used by 'InnoDB'.
*note innodb-transaction-model:: describes transaction isolation levels and the locking strategies used by each. It also discusses the use of 'autocommit', consistent non-locking reads, and locking reads.
*note innodb-locks-set:: discusses specific types of locks set in 'InnoDB' for various statements.
*note innodb-next-key-locking:: describes how 'InnoDB' uses next-key locking to avoid phantom rows.
*note innodb-deadlocks:: provides a deadlock example, discusses deadlock detection, and provides tips for minimizing and handling deadlocks in 'InnoDB'.
File: manual.info.tmp, Node: innodb-locking, Next: innodb-transaction-model, Prev: innodb-locking-transaction-model, Up: innodb-locking-transaction-model
This section describes lock types used by 'InnoDB'.
*note innodb-shared-exclusive-locks::
*note innodb-intention-locks::
*note innodb-record-locks::
*note innodb-gap-locks::
*note innodb-next-key-locks::
*note innodb-insert-intention-locks::
*note innodb-auto-inc-locks::
*note innodb-predicate-locks::
Shared and Exclusive Locks
'InnoDB' implements standard row-level locking where there are two types of locks, shared ('S') locks and exclusive ('X') locks.
A shared ('S') lock permits the transaction that holds the lock to read a row.
An exclusive ('X') lock permits the transaction that holds the lock to update or delete a row.
If transaction 'T1' holds a shared ('S') lock on row 'r', then requests from some distinct transaction 'T2' for a lock on row 'r' are handled as follows:
A request by 'T2' for an 'S' lock can be granted immediately. As a result, both 'T1' and 'T2' hold an 'S' lock on 'r'.
A request by 'T2' for an 'X' lock cannot be granted immediately.
If a transaction 'T1' holds an exclusive ('X') lock on row 'r', a request from some distinct transaction 'T2' for a lock of either type on 'r' cannot be granted immediately. Instead, transaction 'T2' has to wait for transaction 'T1' to release its lock on row 'r'.
Intention Locks
'InnoDB' supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as *note 'LOCK TABLES ... WRITE': lock-tables. takes an exclusive lock (an 'X' lock) on the specified table. To make locking at multiple granularity levels practical, 'InnoDB' uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:
An intention shared lock ('IS') indicates that a transaction intends to set a shared lock on individual rows in a table.
An intention exclusive lock ('IX') indicates that a transaction intends to set an exclusive lock on individual rows in a table.
For example, note 'SELECT ... LOCK IN SHARE MODE': select. sets an 'IS' lock, and note 'SELECT ... FOR UPDATE': select. sets an 'IX' lock.
The intention locking protocol is as follows:
Before a transaction can acquire a shared lock on a row in a table, it must first acquire an 'IS' lock or stronger on the table.
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an 'IX' lock on the table.
Table-level lock type compatibility is summarized in the following matrix.
'X' 'IX' 'S' 'IS'
'X'
Conflict Conflict Conflict Conflict
'IX'
Conflict Compatible Conflict Compatible
'S'
Conflict Conflict Compatible Compatible
'IS'
Conflict Compatible Compatible Compatible
A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.
Intention locks do not block anything except full table requests (for example, *note 'LOCK TABLES ... WRITE': lock-tables.). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
Transaction data for an intention lock appears similar to the following in note 'SHOW ENGINE INNODB STATUS': show-engine. and note InnoDB monitor: innodb-standard-monitor. output:
TABLE LOCK table `test`.`t` trx id 10080 lock mode IXRecord Locks
A record lock is a lock on an index record. For example, 'SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;' prevents any other transaction from inserting, updating, or deleting rows where the value of 't.c1' is '10'.
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. See *note innodb-index-types::.
Transaction data for a record lock appears similar to the following in note 'SHOW ENGINE INNODB STATUS': show-engine. and note InnoDB monitor: innodb-standard-monitor. output:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;Gap Locks
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, 'SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;' prevents other transactions from inserting a value of '15' into column 't.c1', whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.
A gap might span a single index value, multiple index values, or even be empty.
Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the 'id' column has a unique index, the following statement uses only an index-record lock for the row having 'id' value 100 and it does not matter whether other sessions insert rows in the preceding gap:
SELECT * FROM child WHERE id = 100;If 'id' is not indexed or has a nonunique index, the statement does lock the preceding gap.
It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.
Gap locks in 'InnoDB' are 'purely inhibitive', which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.
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 (which is now deprecated). In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
There are also other effects of using the 'READ COMMITTED' isolation level or enabling 'innodb_locks_unsafe_for_binlog'. Record locks for nonmatching rows are released after MySQL has evaluated the 'WHERE' condition. For 'UPDATE' statements, 'InnoDB' does a 'semi-consistent' read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the 'WHERE' condition of the *note 'UPDATE': update.
Next-Key Locks
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
'InnoDB' performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the 'gap' before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record 'R' in an index, another session cannot insert a new index record in the gap immediately before 'R' in the index order.
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)For the last interval, the next-key lock locks the gap above the largest value in the index and the 'supremum' pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
By default, 'InnoDB' operates in 'REPEATABLE READ' transaction isolation level. In this case, 'InnoDB' uses next-key locks for searches and index scans, which prevents phantom rows (see *note innodb-next-key-locking::).
Transaction data for a next-key lock appears similar to the following in note 'SHOW ENGINE INNODB STATUS': show-engine. and note InnoDB monitor: innodb-standard-monitor. output:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;Insert Intention Locks
An insert intention lock is a type of gap lock set by *note 'INSERT': insert. operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.
Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);Transaction data for an insert intention lock appears similar to the following in note 'SHOW ENGINE INNODB STATUS': show-engine. and note InnoDB monitor: innodb-standard-monitor. output:
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec *insert intention* waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...AUTO-INC Locks
An 'AUTO-INC' lock is a special table-level lock taken by transactions inserting into tables with 'AUTO_INCREMENT' columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
The 'innodb_autoinc_lock_mode' variable controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.
For more information, see *note innodb-auto-increment-handling::.
Predicate Locks for Spatial Indexes
'InnoDB' supports 'SPATIAL' indexing of columns containing spatial data (see *note optimizing-spatial-analysis::).
To handle locking for operations involving 'SPATIAL' indexes, next-key locking does not work well to support 'REPEATABLE READ' or 'SERIALIZABLE' transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the 'next' key.
To enable support of isolation levels for tables with 'SPATIAL' indexes, 'InnoDB' uses predicate locks. A 'SPATIAL' index contains minimum bounding rectangle (MBR) values, so 'InnoDB' enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.
File: manual.info.tmp, Node: innodb-transaction-model, Next: innodb-locks-set, Prev: innodb-locking, Up: innodb-locking-transaction-model
Menu:
innodb-locking-reads:: Locking Reads
The 'InnoDB' transaction model aims combine the best properties of a multi-versioning database with traditional two-phase locking. 'InnoDB' performs locking at the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle. The lock information in 'InnoDB' is stored space-efficiently so that lock escalation is not needed. Typically, several users are permitted to lock every row in 'InnoDB' tables, or any random subset of the rows, without causing 'InnoDB' memory exhaustion.
File: manual.info.tmp, Node: innodb-transaction-isolation-levels, Next: innodb-autocommit-commit-rollback, Prev: innodb-transaction-model, Up: innodb-transaction-model
14.7.2.1 Transaction Isolation Levels .....................................
Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
'InnoDB' offers all four transaction isolation levels described by the SQL:1992 standard: 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', and 'SERIALIZABLE'. The default isolation level for 'InnoDB' is 'REPEATABLE READ'.
A user can change the isolation level for a single session or for all subsequent connections with the note 'SET TRANSACTION': set-transaction. statement. To set the server's default isolation level for all connections, use the '--transaction-isolation' option on the command line or in an option file. For detailed information about isolation levels and level-setting syntax, see note set-transaction::.
'InnoDB' supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default 'REPEATABLE READ' level, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with 'READ COMMITTED' or even 'READ UNCOMMITTED', in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking. 'SERIALIZABLE' enforces even stricter rules than 'REPEATABLE READ', and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.
The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.
'REPEATABLE READ'
This is the default isolation level for 'InnoDB'. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) note 'SELECT': select. statements within the same transaction, these note 'SELECT': select. statements are consistent also with respect to each other. See *note innodb-consistent-read::.
For locking reads (note 'SELECT': select. with 'FOR UPDATE' or 'LOCK IN SHARE MODE'), note 'UPDATE': update, and *note 'DELETE': 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 locks to block insertions
by other sessions into the gaps covered by the range. For
information about gap locks and next-key locks, see *note
innodb-locking::.It is not recommended to mix locking statements (note 'UPDATE': update, note 'INSERT': insert, note 'DELETE': delete, or 'SELECT ... FOR ...') with non-locking note 'SELECT': select. statements in a single 'REPEATABLE READ' transaction, because typically in such cases you want 'SERIALIZABLE' instead. This is because a non-locking *note 'SELECT': select. statement presents the state of the database from a read-view which consists of transactions committed before the read-view was created and before the current transaction's own writes, while the locking statements see and modify the most recent state of the database to use locking. In general, these two different table states are inconsistent with each other and difficult to parse.
'READ COMMITTED'
Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see *note innodb-consistent-read::.
For locking reads (note 'SELECT': select. with 'FOR UPDATE' or 'LOCK IN SHARE MODE'), note 'UPDATE': update. statements, and *note 'DELETE': 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. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.
Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, see *note innodb-next-key-locking::.
Only row-based binary logging is supported with the 'READ COMMITTED' isolation level. If you use 'READ COMMITTED' with 'binlog_format=MIXED', the server automatically uses row-based logging.
Using 'READ COMMITTED' has additional effects:
* For *note 'UPDATE': update. or *note 'DELETE': delete.
statements, 'InnoDB' holds locks only for rows that it updates
or deletes. Record locks for nonmatching rows are released
after MySQL has evaluated the 'WHERE' condition. This greatly
reduces the probability of deadlocks, but they can still
happen.
* For *note 'UPDATE': update. statements, if a row is already
locked, 'InnoDB' performs a 'semi-consistent' read, returning
the latest committed version to MySQL so that MySQL can
determine whether the row matches the 'WHERE' condition of the
*note 'UPDATE': update. If the row matches (must be updated),
MySQL reads the row again and this time 'InnoDB' either locks
it or waits for a lock on it.Consider the following example, beginning with this table:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;In this case, the table has no indexes, so searches and index scans use the hidden clustered index for record locking (see *note innodb-index-types::) rather than indexed columns.
Suppose that one session performs an *note 'UPDATE': update. using these statements:
# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;Suppose also that a second session performs an *note 'UPDATE': update. by executing this statement following those of the first session:
# Session B
UPDATE t SET b = 4 WHERE b = 2;As note 'InnoDB': innodb-storage-engine. executes each note 'UPDATE': update, it first acquires an exclusive lock for each row that it reads, and then determines whether to modify it. If note 'InnoDB': innodb-storage-engine. does not modify the row, it releases the lock. Otherwise, note 'InnoDB': innodb-storage-engine. retains the lock until the end of the transaction. This affects transaction processing as follows.
When using the default 'REPEATABLE READ' isolation level, the first *note 'UPDATE': update. acquires an x-lock on each row that it reads and does not release any of them:
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lockThe second note 'UPDATE': update. blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first note 'UPDATE': update. commits or rolls back:
x-lock(1,2); block and wait for first UPDATE to commit or roll backIf 'READ COMMITTED' is used instead, the first *note 'UPDATE': update. acquires an x-lock on each row that it reads and releases those for rows that it does not modify:
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)For the second 'UPDATE', 'InnoDB' does a 'semi-consistent' read, returning the latest committed version of each row that it reads to MySQL so that MySQL can determine whether the row matches the 'WHERE' condition of the *note 'UPDATE': update.:
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lockHowever, if the 'WHERE' condition includes an indexed column, and 'InnoDB' uses the index, only the indexed column is considered when taking and retaining record locks. In the following example, the first note 'UPDATE': update. takes and retains an x-lock on each row where b = 2. The second note 'UPDATE': update. blocks when it tries to acquire x-locks on the same records, as it also uses the index defined on column b.
CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;
# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;The effects of using the 'READ COMMITTED' isolation level are the same as enabling the deprecated 'innodb_locks_unsafe_for_binlog' variable, with these exceptions:
* Enabling 'innodb_locks_unsafe_for_binlog' is a global setting
and affects all sessions, whereas the isolation level can be
set globally for all sessions, or individually per session.
* 'innodb_locks_unsafe_for_binlog' can be set only at server
startup, whereas the isolation level can be set at startup or
changed at runtime.'READ COMMITTED' therefore offers finer and more flexible control than 'innodb_locks_unsafe_for_binlog'.
'READ UNCOMMITTED'
*note 'SELECT': select. statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like 'READ COMMITTED'.
'SERIALIZABLE'
This level is like 'REPEATABLE READ', but 'InnoDB' implicitly converts all plain note 'SELECT': select. statements to note 'SELECT ... LOCK IN SHARE MODE': select. if 'autocommit' is disabled. If 'autocommit' is enabled, the note 'SELECT': select. is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain note 'SELECT': select. to block if other transactions have modified the selected rows, disable 'autocommit'.)
File: manual.info.tmp, Node: innodb-autocommit-commit-rollback, Next: innodb-consistent-read, Prev: innodb-transaction-isolation-levels, Up: innodb-transaction-model
14.7.2.2 autocommit, Commit, and Rollback .........................................
In 'InnoDB', all user activity occurs inside a transaction. If 'autocommit' mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with 'autocommit' enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error. See *note innodb-error-handling::.
A session that has 'autocommit' enabled can perform a multiple-statement transaction by starting it with an explicit note 'START TRANSACTION': commit. or note 'BEGIN': commit. statement and ending it with a note 'COMMIT': commit. or note 'ROLLBACK': commit. statement. See *note commit::.
If 'autocommit' mode is disabled within a session with 'SET autocommit = 0', the session always has a transaction open. A note 'COMMIT': commit. or note 'ROLLBACK': commit. statement ends the current transaction and a new one starts.
If a session that has 'autocommit' disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
Some statements implicitly end a transaction, as if you had done a note 'COMMIT': commit. before executing the statement. For details, see note implicit-commit::.
A note 'COMMIT': commit. means that the changes made in the current transaction are made permanent and become visible to other sessions. A note 'ROLLBACK': commit. statement, on the other hand, cancels all modifications made by the current transaction. Both note 'COMMIT': commit. and note 'ROLLBACK': commit. release all 'InnoDB' locks that were set during the current transaction.
Grouping DML Operations with Transactions
By default, connection to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. This mode of operation might be unfamiliar if you have experience with other database systems, where it is standard practice to issue a sequence of DML statements and commit them or roll them back all together.
To use multiple-statement transactions, switch autocommit off with the SQL statement 'SET autocommit = 0' and end each transaction with note 'COMMIT': commit. or note 'ROLLBACK': commit. as appropriate. To leave autocommit on, begin each transaction with note 'START TRANSACTION': commit. and end it with note 'COMMIT': commit. or *note 'ROLLBACK': commit. The following example shows two transactions. The first is committed; the second is rolled back.
$> mysql test
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a | b |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>Transactions in Client-Side Languages
In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as note 'COMMIT': commit. to the MySQL server as strings just like any other SQL statements such as note 'SELECT': select. or *note 'INSERT': insert. Some APIs also offer separate special transaction commit and rollback functions or methods.
File: manual.info.tmp, Node: innodb-consistent-read, Next: innodb-locking-reads, Prev: innodb-autocommit-commit-rollback, Up: innodb-transaction-model
14.7.2.3 Consistent Nonlocking Reads ....................................
A consistent read means that 'InnoDB' uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a *note 'SELECT': select. sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.
If the transaction isolation level is 'REPEATABLE READ' (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.
With 'READ COMMITTED' isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
Consistent read is the default mode in which 'InnoDB' processes *note 'SELECT': select. statements in 'READ COMMITTED' and 'REPEATABLE READ' isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.
Suppose that you are running in the default 'REPEATABLE READ' isolation level. When you issue a consistent read (that is, an ordinary *note 'SELECT': select. statement), 'InnoDB' gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.
Note:
The snapshot of the database state applies to note 'SELECT': select. statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a note 'DELETE': delete. or *note 'UPDATE': update. statement issued from another concurrent 'REPEATABLE READ' transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.You can advance your timepoint by committing your transaction and then doing another note 'SELECT': select. or note 'START TRANSACTION WITH CONSISTENT SNAPSHOT': commit.
This is called multi-versioned concurrency control.
In the following example, session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.
Session A Session B
SET autocommit=0; SET autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------If you want to see the 'freshest' state of the database, use either the 'READ COMMITTED' isolation level or a locking read:
SELECT * FROM t LOCK IN SHARE MODE;With 'READ COMMITTED' isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. With 'LOCK IN SHARE MODE', a locking read occurs instead: A 'SELECT' blocks until the transaction containing the freshest rows ends (see *note innodb-locking-reads::).
Consistent read does not work over certain DDL statements:
Consistent read does not work over *note 'DROP TABLE': drop-table, because MySQL cannot use a table that has been dropped and 'InnoDB' destroys the table.
Consistent read does not work over *note 'ALTER TABLE': alter-table. operations that make a temporary copy of the original table and delete the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction's snapshot was taken. In this case, the transaction returns an error: 'ER_TABLE_DEF_CHANGED' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_table_def_changed), 'Table definition has changed, please retry transaction'.
The type of read varies for selects in clauses like note 'INSERT INTO ... SELECT': insert, note 'UPDATE ... (SELECT)': update, and *note 'CREATE TABLE ... SELECT': create-table. that do not specify 'FOR UPDATE' or 'LOCK IN SHARE MODE':
By default, 'InnoDB' uses stronger locks in those statements and the *note 'SELECT': select. part acts like 'READ COMMITTED', where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
To perform a nonlocking read in such cases, enable the 'innodb_locks_unsafe_for_binlog' option and set the isolation level of the transaction to 'READ UNCOMMITTED', 'READ COMMITTED', or 'REPEATABLE READ' to avoid setting locks on rows read from the selected table.
File: manual.info.tmp, Node: innodb-locking-reads, Prev: innodb-consistent-read, Up: innodb-transaction-model
14.7.2.4 Locking Reads ......................
If you query data and then insert or update related data within the same transaction, the regular 'SELECT' statement does not give enough protection. Other transactions can update or delete the same rows you just queried. 'InnoDB' supports two types of locking reads that offer extra safety:
*note 'SELECT ... LOCK IN SHARE MODE': select.
Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
*note 'SELECT ... FOR UPDATE': select.
For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an 'UPDATE' statement for those rows. Other transactions are blocked from updating those rows, from doing 'SELECT ... LOCK IN SHARE MODE', or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)
These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of these 'pointer' values.
All locks set by 'LOCK IN SHARE MODE' and 'FOR UPDATE' queries are released when the transaction is committed or rolled back.
Note:
Locking reads are only possible when autocommit is disabled (either by beginning transaction with *note 'START TRANSACTION': commit. or by setting 'autocommit' to 0.
A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery. For example, the following statement does not lock rows in table 't2'.
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;To lock rows in table 't2', add a locking read clause to the subquery:
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;Locking Read Examples
Suppose that you want to insert a new row into a table 'child', and make sure that the child row has a parent row in table 'parent'. Your application code can ensure referential integrity throughout this sequence of operations.
First, use a consistent read to query the table 'PARENT' and verify that the parent row exists. Can you safely insert the child row to table 'CHILD'? No, because some other session could delete the parent row in the moment between your 'SELECT' and your 'INSERT', without you being aware of it.
To avoid this potential issue, perform the *note 'SELECT': select. using 'LOCK IN SHARE MODE':
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;After the 'LOCK IN SHARE MODE' query returns the parent ''Jones'', you can safely add the child record to the 'CHILD' table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the 'PARENT' table waits until you are finished, that is, until the data in all tables is in a consistent state.
For another example, consider an integer counter field in a table 'CHILD_CODES', used to assign a unique identifier to each child added to table 'CHILD'. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the 'CHILD' table.
Here, 'LOCK IN SHARE MODE' is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.
To implement reading and incrementing the counter, first perform a locking read of the counter using 'FOR UPDATE', and then increment the counter. For example:
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;A note 'SELECT ... FOR UPDATE': select. reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL note 'UPDATE': update. would set on the rows.
The preceding description is merely an example of how *note 'SELECT ... FOR UPDATE': select. works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();The *note 'SELECT': select. statement merely retrieves the identifier information (specific to the current connection). It does not access any table.
File: manual.info.tmp, Node: innodb-locks-set, Next: innodb-next-key-locking, Prev: innodb-transaction-model, Up: innodb-locking-transaction-model
A locking read, an note 'UPDATE': update, or a note 'DELETE': delete. generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are 'WHERE' conditions in the statement that would exclude the row. 'InnoDB' does not remember the exact 'WHERE' condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the 'gap' immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used. For more information, see note innodb-locking::. The transaction isolation level can also affect which locks are set; see note innodb-transaction-isolation-levels::.
If a secondary index is used in a search and the index record locks to be set are exclusive, 'InnoDB' also retrieves the corresponding clustered index records and sets locks on them.
If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not scan more rows than necessary.
'InnoDB' sets specific types of locks as follows.
*note 'SELECT ... FROM': select. is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to 'SERIALIZABLE'. For 'SERIALIZABLE' level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
For note 'SELECT ... FOR UPDATE': select. or note 'SELECT ... LOCK IN SHARE MODE': select, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the 'WHERE' clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a *note 'UNION': union, scanned (and locked) rows from a table might be inserted into a temporary table before evaluating whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.
*note 'SELECT ... LOCK IN SHARE MODE': select. sets shared next-key locks on all index records the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
*note 'SELECT ... FOR UPDATE': select. sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
For index records the search encounters, note 'SELECT ... FOR UPDATE': select. blocks other sessions from doing note 'SELECT ... LOCK IN SHARE MODE': select. or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.
*note 'UPDATE ... WHERE ...': update. sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
When note 'UPDATE': update. modifies a clustered index record, implicit locks are taken on affected secondary index records. The note 'UPDATE': update. operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.
*note 'DELETE FROM ... WHERE ...': delete. sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
*note 'INSERT': insert. sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an 'InnoDB' table 't1' has the following structure:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;Now suppose that three sessions perform the following operations in order:
Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);Session 1:
ROLLBACK;The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.
A similar situation occurs if the table already contains a row with key value 1 and three sessions perform the following operations in order:
Session 1:
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);Session 1:
COMMIT;The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 commits, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.
note 'INSERT ... ON DUPLICATE KEY UPDATE': insert-on-duplicate. differs from a simple note 'INSERT': insert. in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.
note 'REPLACE': replace. is done like an note 'INSERT': insert. if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced.
'INSERT INTO T SELECT ... FROM S WHERE ...' sets an exclusive index record lock (without a gap lock) on each row inserted into 'T'. If the transaction isolation level is 'READ COMMITTED', or 'innodb_locks_unsafe_for_binlog' is enabled and the transaction isolation level is not 'SERIALIZABLE', 'InnoDB' does the search on 'S' as a consistent read (no locks). Otherwise, 'InnoDB' sets shared next-key locks on rows from 'S'. 'InnoDB' has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.
note 'CREATE TABLE ... SELECT ...': create-table. performs the note 'SELECT': select. with shared next-key locks or as a consistent read, as for *note 'INSERT ... SELECT': insert-select.
When a 'SELECT' is used in the constructs 'REPLACE INTO t SELECT ... FROM s WHERE ...' or 'UPDATE t ... WHERE col IN (SELECT ... FROM s ...)', 'InnoDB' sets shared next-key locks on rows from table 's'.
'InnoDB' sets an exclusive lock on the end of the index associated with the 'AUTO_INCREMENT' column while initializing a previously specified 'AUTO_INCREMENT' column on a table.
With 'innodb_autoinc_lock_mode=0', 'InnoDB' uses a special 'AUTO-INC' table lock mode where the lock is obtained and held to the end of the current SQL statement (not to the end of the entire transaction) while accessing the auto-increment counter. Other clients cannot insert into the table while the 'AUTO-INC' table lock is held. The same behavior occurs for 'bulk inserts' with 'innodb_autoinc_lock_mode=1'. Table-level 'AUTO-INC' locks are not used with 'innodb_autoinc_lock_mode=2'. For more information, See *note innodb-auto-increment-handling::.
'InnoDB' fetches the value of a previously initialized 'AUTO_INCREMENT' column without setting any locks.
If a 'FOREIGN KEY' constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. 'InnoDB' also sets these locks in the case where the constraint fails.
*note 'LOCK TABLES': lock-tables. sets table locks, but it is the higher MySQL layer above the 'InnoDB' layer that sets these locks. 'InnoDB' is aware of table locks if 'innodb_table_locks = 1' (the default) and 'autocommit = 0', and the MySQL layer above 'InnoDB' knows about row-level locks.
Otherwise, 'InnoDB''s automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because in this case the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another session currently has row-level locks. However, this does not endanger transaction integrity, as discussed in *note innodb-deadlock-detection::.
note 'LOCK TABLES': lock-tables. acquires two locks on each table if 'innodb_table_locks=1' (the default). In addition to a table lock on the MySQL layer, it also acquires an 'InnoDB' table lock. To avoid acquiring 'InnoDB' table locks, set 'innodb_table_locks=0'. If no 'InnoDB' table lock is acquired, note 'LOCK TABLES': lock-tables. completes even if some records of the tables are being locked by other transactions.
In MySQL 5.7, 'innodb_table_locks=0' has no effect for tables locked explicitly with note 'LOCK TABLES ... WRITE': lock-tables. It does have an effect for tables locked for read or write by note 'LOCK TABLES ... WRITE': lock-tables. implicitly (for example, through triggers) or by *note 'LOCK TABLES ... READ': lock-tables.
All 'InnoDB' locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke *note 'LOCK TABLES': lock-tables. on 'InnoDB' tables in 'autocommit=1' mode because the acquired 'InnoDB' table locks would be released immediately.
You cannot lock additional tables in the middle of a transaction because note 'LOCK TABLES': lock-tables. performs an implicit note 'COMMIT': commit. and *note 'UNLOCK TABLES': lock-tables.
File: manual.info.tmp, Node: innodb-next-key-locking, Next: innodb-deadlocks, Prev: innodb-locks-set, Up: innodb-locking-transaction-model
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a *note 'SELECT': select. is executed twice, but returns a row the second time that was not returned the first time, the row is a 'phantom' row.
Suppose that there is an index on the 'id' column of the 'child' table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;The query scans the index starting from the first record where 'id' is bigger than 100. Let the table contain rows having 'id' values of 90 and 102. If the locks set on the index records in the scanned range do not lock out inserts made in the gaps (in this case, the gap between 90 and 102), another session can insert a new row into the table with an 'id' of 101. If you were to execute the same *note 'SELECT': select. within the same transaction, you would see a new row with an 'id' of 101 (a 'phantom') in the result set returned by the query. If we regard a set of rows as a data item, the new phantom child would violate the isolation principle of transactions that a transaction should be able to run so that the data it has read does not change during the transaction.
To prevent phantoms, 'InnoDB' uses an algorithm called next-key locking that combines index-row locking with gap locking. 'InnoDB' performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the 'gap' before the index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record 'R' in an index, another session cannot insert a new index record in the gap immediately before 'R' in the index order.
When 'InnoDB' scans an index, it can also lock the gap after the last record in the index. Just that happens in the preceding example: To prevent any insert into the table where 'id' would be bigger than 100, the locks set by 'InnoDB' include a lock on the gap following 'id' value 102.
You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to 'lock' the nonexistence of something in your table.
Gap locking can be disabled as discussed in *note innodb-locking::. This may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled.
File: manual.info.tmp, Node: innodb-deadlocks, Prev: innodb-next-key-locking, Up: innodb-locking-transaction-model
Menu:
innodb-deadlocks-handling:: How to Minimize and Handle Deadlocks
A deadlock is a situation in which multiple transactions are unable to proceed because each transaction holds a lock that is needed by another one. Because all transactions involved are waiting for the same resource to become available, none of them ever releases the lock it holds.
A deadlock can occur when transactions lock rows in multiple tables (through statements such as note 'UPDATE': update. or note 'SELECT ... FOR UPDATE': select.), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue. For a deadlock example, see *note innodb-deadlock-example::.
To reduce the possibility of deadlocks, use transactions rather than note 'LOCK TABLES': lock-tables. statements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such as note 'SELECT ... FOR UPDATE': select.) in each transaction; create indexes on the columns used in note 'SELECT ... FOR UPDATE': select. and note 'UPDATE ... WHERE': update. statements. The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations. For more information about avoiding and recovering from deadlock conditions, see *note innodb-deadlocks-handling::.
When deadlock detection is enabled (the default) and a deadlock does occur, 'InnoDB' detects the condition and rolls back one of the transactions (the victim). If deadlock detection is disabled using the 'innodb_deadlock_detect' variable, 'InnoDB' relies on the 'innodb_lock_wait_timeout' setting to roll back transactions in case of a deadlock. Thus, even if your application logic is correct, you must still handle the case where a transaction must be retried. To view the last deadlock in an 'InnoDB' user transaction, use note 'SHOW ENGINE INNODB STATUS': show-engine. If frequent deadlocks highlight a problem with transaction structure or application error handling, enable 'innodb_print_all_deadlocks' to print information about all deadlocks to the note 'mysqld': mysqld. error log. For more information about how deadlocks are automatically detected and handled, see *note innodb-deadlock-detection::.
File: manual.info.tmp, Node: innodb-deadlock-example, Next: innodb-deadlock-detection, Prev: innodb-deadlocks, Up: innodb-deadlocks
14.7.5.1 An InnoDB Deadlock Example ...................................
The following example illustrates how an error can occur when a lock request causes a deadlock. The example involves two clients, A and B.
First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an 'S' lock on the row by selecting it in share mode:
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+Next, client B begins a transaction and attempts to delete the row from the table:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;The delete operation requires an 'X' lock. The lock cannot be granted because it is incompatible with the 'S' lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.
Finally, client A also attempts to delete the row from the table:
mysql> DELETE FROM t WHERE i = 1;Deadlock occurs here because client A needs an 'X' lock to delete the row. However, that lock request cannot be granted because client B already has a request for an 'X' lock and is waiting for client A to release its 'S' lock. Nor can the 'S' lock held by A be upgraded to an 'X' lock because of the prior request by B for an 'X' lock. As a result, 'InnoDB' generates an error for one of the clients and releases its locks. The client returns this error:
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transactionAt that point, the lock request for the other client can be granted and it deletes the row from the table.
File: manual.info.tmp, Node: innodb-deadlock-detection, Next: innodb-deadlocks-handling, Prev: innodb-deadlock-example, Up: innodb-deadlocks
14.7.5.2 Deadlock Detection ...........................
When deadlock detection is enabled (the default), 'InnoDB' automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. 'InnoDB' tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.
'InnoDB' is aware of table locks if 'innodb_table_locks = 1' (the default) and 'autocommit = 0', and the MySQL layer above it knows about row-level locks. Otherwise, 'InnoDB' cannot detect deadlocks where a table lock set by a MySQL *note 'LOCK TABLES': lock-tables. statement or a lock set by a storage engine other than 'InnoDB' is involved. Resolve these situations by setting the value of the 'innodb_lock_wait_timeout' system variable.
If the 'LATEST DETECTED DEADLOCK' section of 'InnoDB' Monitor output includes a message stating, ''TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION',' this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list.
For techniques to organize database operations to avoid deadlocks, see *note innodb-deadlocks::.
Disabling Deadlock Detection
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the 'innodb_lock_wait_timeout' setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the 'innodb_deadlock_detect' variable.
File: manual.info.tmp, Node: innodb-deadlocks-handling, Prev: innodb-deadlock-detection, Up: innodb-deadlocks
14.7.5.3 How to Minimize and Handle Deadlocks .............................................
This section builds on the conceptual information about deadlocks in *note innodb-deadlock-detection::. It explains how to organize database operations to minimize deadlocks and the subsequent error handling required in applications.
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. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.
'InnoDB' uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really 'atomic'; they automatically set locks on the (possibly several) index records of the row inserted or deleted.
You can cope with deadlocks and reduce the likelihood of their occurrence with the following techniques:
At any time, issue *note 'SHOW ENGINE INNODB STATUS': show-engine. to determine the cause of the most recent deadlock. That can help you to tune your application to avoid deadlocks.
If frequent deadlock warnings cause concern, collect more extensive debugging information by enabling the 'innodb_print_all_deadlocks' variable. Information about each deadlock, not just the latest one, is recorded in the MySQL error log. Disable this option when you are finished debugging.
Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
Keep transactions small and short in duration to make them less prone to collision.
Commit transactions immediately after making a set of related changes to make them less prone to collision. In particular, do not leave an interactive *note 'mysql': mysql. session open for a long time with an uncommitted transaction.
If you use locking reads (note 'SELECT ... FOR UPDATE': select. or note 'SELECT ... LOCK IN SHARE MODE': select.), try using a lower isolation level such as 'READ COMMITTED'.
When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock. For example, organize database operations into functions within your application, or call stored routines, rather than coding multiple similar sequences of 'INSERT', 'UPDATE', and 'DELETE' statements in different places.
Add well-chosen indexes to your tables so that your queries scan fewer index records and set fewer locks. Use *note 'EXPLAIN SELECT': explain. to determine which indexes the MySQL server regards as the most appropriate for your queries.
Use less locking. If you can afford to permit a *note 'SELECT': select. to return data from an old snapshot, do not add a 'FOR UPDATE' or 'LOCK IN SHARE MODE' clause to it. Using the 'READ COMMITTED' isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot.
If nothing else helps, serialize your transactions with table-level locks. The correct way to use note 'LOCK TABLES': lock-tables. with transactional tables, such as 'InnoDB' tables, is to begin a transaction with 'SET autocommit = 0' (not note 'START TRANSACTION': commit.) followed by note 'LOCK TABLES': lock-tables, and to not call note 'UNLOCK TABLES': lock-tables. until you commit the transaction explicitly. For example, if you need to write to table 't1' and read from table 't2', you can do this:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... DO SOMETHING WITH TABLES T1 AND T2 HERE ...
COMMIT;
UNLOCK TABLES;Table-level locks prevent concurrent updates to the table, avoiding deadlocks at the expense of less responsiveness for a busy system.
Another way to serialize transactions is to create an auxiliary 'semaphore' table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the 'InnoDB' instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock. With MySQL table-level locks, the timeout method must be used to resolve deadlocks.
File: manual.info.tmp, Node: innodb-configuration, Next: innodb-compression, Prev: innodb-locking-transaction-model, Up: innodb-storage-engine