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 13.6.8.4, “InnoDB
Record, Gap, and Next-Key Locks”. The transaction
isolation level also can affect which locks are set; see
Section 12.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.
Differences between shared and exclusive locks are described in
Section 13.6.8.1, “InnoDB
Lock Modes”.
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.
SELECT ... FROM ...
LOCK IN SHARE MODE
sets shared next-key locks on
all index records the search encounters.
For index records the search encounters,
SELECT ... FROM ...
FOR UPDATE
blocks other sessions from doing
SELECT ... FROM ...
LOCK IN SHARE MODE
or from reading in certain
transaction isolation levels. Consistent reads will ignore
any locks set on the records that exist in the read view.
UPDATE ... WHERE
...
sets an exclusive next-key lock on every
record the search encounters.
DELETE FROM ...
WHERE ...
sets an exclusive next-key lock on every
record the search 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 that must be updated.
INSERT INTO T SELECT ... FROM S WHERE ...
sets an exclusive index record 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.
CREATE TABLE
... SELECT ...
performs the
SELECT
with shared next-key
locks or as a consistent read, as for
INSERT ...
SELECT
.
For REPLACE INTO T SELECT ... FROM S WHERE
...
, InnoDB
sets shared
next-key locks on rows from 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 13.6.8, “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.
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
Section 13.6.8.8, “Deadlock Detection and Rollback”. See also
Section 13.6.14, “Restrictions on InnoDB
Tables”.
User Comments
Add your own comment.