14.6.3.1 The InnoDB Transaction Model and Locking

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, you must understand the
notions of transactions
and locking as they relate to
the InnoDB storage engine.

In the InnoDB transaction model, the goal is to
combine the best properties of a multi-versioning database with
traditional two-phase locking. InnoDB does
locking on the row level and runs queries as nonlocking consistent
reads by default, in the style of Oracle. The lock information in
InnoDB is stored so space-efficiently 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.

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
Section 14.6.11.4, “InnoDB Error Handling”.

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

A COMMIT means that the changes
made in the current transaction are made permanent and become
visible to other sessions. A
ROLLBACK
statement, on the other hand, cancels all modifications made by
the current transaction. Both
COMMIT and
ROLLBACK release
all InnoDB locks that were set during the
current transaction.

A user can change the isolation level for a single session or for
all subsequent connections with the 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
Section 13.3.6, “SET TRANSACTION Syntax”.

In row-level locking, InnoDB normally uses
next-key locking. That means that besides index records,
InnoDB can also lock the “gap”
preceding an index record to block insertions by other sessions in
the gap immediately before the index record. A next-key lock
refers to a lock that locks an index record and the gap before it.
A gap lock refers to a lock that locks only the gap before some
index record.

An exclusive (X) lock permits a
transaction 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

Additionally, InnoDB supports
multiple granularity locking which permits
coexistence of record locks and locks on entire tables. To make
locking at multiple granularity levels practical, additional
types of locks called intention locks are
used. Intention locks are table locks in
InnoDB. The idea behind intention locks is
for a transaction to indicate which type of lock (shared or
exclusive) it will require later for a row in that table. There
are two types of intention locks used in
InnoDB (assume that transaction
T has requested a lock of the indicated type
on table t):

Before a transaction can acquire an
S lock on a row in table
t, it must first acquire an
IS or stronger lock on
t.

Before a transaction can acquire an
X lock on a row, it must first
acquire an IX lock on
t.

These rules can be conveniently summarized by means of the
following lock type compatibility 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.

Thus, intention locks do not block anything except full table
requests (for example, LOCK TABLES ...
WRITE). The main purpose of
IX and IS
locks is to show that someone is locking a row, or going to lock
a row in the table.

Deadlock Example

The following example illustrates how an error can occur when a
lock request would cause 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:

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;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

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 transaction

At that point, the lock request for the other client can be
granted and it deletes the row from the table.

Note

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, which is defined by
LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK. 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 the transactions on the
wait-for list. The limit of 1,000,000 locks is defined by
LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK.

14.6.3.1.2 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 of 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
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
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
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
SELECT statements within a
transaction, not necessarily to
DML statements. If you insert
or modify some rows and then commit that transaction, a
DELETE or
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:

Consistent read does not work over DROP
TABLE, because MySQL cannot use a table that has
been dropped and InnoDB destroys the
table.

Consistent read does not work over
ALTER TABLE, because that
statement makes a temporary copy of the original table and
deletes 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.

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:

SELECT ... LOCK IN
SHARE MODE 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.

SELECT ... FOR
UPDATE 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.

All locks set by LOCK IN SHARE MODE and
FOR UPDATE queries are released when the
transaction is committed or rolled back.

Note

Locking of rows for update using SELECT FOR
UPDATE only applies when autocommit is disabled
(either by beginning transaction with
START
TRANSACTION or by setting
autocommit to 0. If
autocommit is enabled, the rows matching the specification are
not locked.

Usage 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
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 read or write to
the applicable row in the PARENT table waits
until you are finished, that is, 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:

A SELECT ... FOR
UPDATE reads the latest available data, setting
exclusive locks on each row it reads. Thus, it sets the same
locks a searched SQL UPDATE would
set on the rows.

The preceding description is merely an example of how
SELECT ... FOR
UPDATE works. In MySQL, the specific task of
generating a unique identifier actually can be accomplished
using only a single access to the table:

The SELECT statement merely
retrieves the identifier information (specific to the current
connection). It does not access any table.

14.6.3.1.4 InnoDB Record, Gap, and Next-Key Locks

InnoDB has several types of record-level
locks including record locks, gap locks, and next-key locks. For
information about shared locks, exclusive locks, and intention
locks, see Section 14.6.3.1.1, “InnoDB Lock Modes”.

Record lock: This is a lock on an index record.

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.

Next-key locking 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 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 ( or )
denote exclusion of the interval endpoint and
[ or ] denote inclusion of
the endpoint:

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.

Gap Locks

The next-key locking example in the previous section shows that
a gap might span a single index value, multiple index values, or
even be empty.

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.

A type of gap lock called an insert intention gap lock is set by
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 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.

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 they only stop other
transactions from inserting to the gap. Thus, a gap X-lock has
the same effect as a gap S-lock.

Disabling Gap Locking

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.

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

14.6.3.1.5 Avoiding the Phantom Problem Using Next-Key Locking

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

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.

14.6.3.1.6 Locks Set by Different SQL Statements in InnoDB

A locking read, an UPDATE, or a
DELETE generally set record locks
on every index record that is scanned in the processing of the
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
Section 14.6.3.1.4, “InnoDB Record, Gap, and Next-Key Locks”. The transaction
isolation level also can affect which locks are set; see
Section 13.3.6, “SET TRANSACTION Syntax”.

If a secondary index is used in a search and 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 unnecessarily scan many rows.

For SELECT ... FOR
UPDATE or
SELECT ... LOCK IN SHARE
MODE, 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
UNION, scanned (and locked) rows
from a table might be inserted into a temporary table before
evaluation 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.

InnoDB sets specific types of locks as
follows.

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

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

REPLACE is done like an
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: In roll-forward recovery from a backup, every SQL
statement must be executed in exactly the same way it was
done originally.

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.

While initializing a previously specified
AUTO_INCREMENT column on a table,
InnoDB sets an exclusive lock on the end
of the index associated with the
AUTO_INCREMENT column. In accessing the
auto-increment counter, InnoDB uses a
specific AUTO-INC table lock mode where
the lock lasts only to the end of the current SQL statement,
not to the end of the entire transaction. Other sessions
cannot insert into the table while the
AUTO-INC table lock is held; see
Section 14.6.3.1, “The InnoDB Transaction Model and Locking”.

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.

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.

14.6.3.1.7 Implicit Transaction Commit and Rollback

By default, MySQL starts the session for each new connection
with autocommit mode 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
Section 14.6.11.4, “InnoDB Error Handling”.

If a session that has autocommit disabled ends without
explicitly committing the final transaction, MySQL rolls back
that transaction.

14.6.3.1.8 Deadlock Detection and Rollback

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

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.

As of MySQL 5.1.24, if a SELECT
calls a stored function in a transaction, and a statement within
the function fails, that statement rolls back. Furthermore, if
ROLLBACK is
executed after that, the entire transaction rolls back. Before
5.1.24, the failed statement did not roll back when it failed
(even though it might ultimately get rolled back by a
ROLLBACK later
that rolls back the entire transaction).

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:

Use SHOW ENGINE
INNODB STATUS to determine the cause of the latest
deadlock. That can help you to tune your application to
avoid deadlocks.

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
mysql session open for a long time with
an uncommitted transaction.

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. Then your queries
need to scan fewer index records and consequently set fewer
locks. Use EXPLAIN
SELECT 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
SELECT to return data from an
old snapshot, do not add the clause FOR
UPDATE or LOCK IN SHARE MODE 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
LOCK TABLES with
transactional tables, such as InnoDB
tables, is to begin a transaction with SET
autocommit = 0 (not
START
TRANSACTION) followed by LOCK
TABLES, and to not call
UNLOCK
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:

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.