MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
A locking read, an
UPDATE
, or a
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
Section 14.7.1, “InnoDB Locking”. The transaction isolation level
can also affect which locks are set; see
Section 14.7.2.1, “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.
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. However, only an index record lock is required for
statements that lock rows using a unique index to search for a
unique row.
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 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.
SELECT ... LOCK IN
SHARE MODE
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.
SELECT ... FOR
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.
For index records the search encounters,
SELECT ... FOR
UPDATE
blocks other sessions from doing
SELECT ... LOCK IN
SHARE MODE
or from reading in certain transaction
isolation levels. Consistent reads 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. However, only an index record lock is
required for statements that lock rows using a unique index to
search for a unique row.
When UPDATE
modifies a
clustered index record, implicit locks are taken on affected
secondary index records. The
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.
DELETE FROM ... WHERE
...
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.
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.
INSERT
... ON DUPLICATE KEY UPDATE
differs from a simple
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.
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:
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.
CREATE TABLE ...
SELECT ...
performs the
SELECT
with shared next-key
locks or as a consistent read, as for
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
Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.
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 14.7.5.2, “Deadlock Detection”.
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,
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
LOCK TABLES ...
WRITE
. It does have an effect for tables locked for
read or write by
LOCK TABLES ...
WRITE
implicitly (for example, through triggers) or
by LOCK TABLES
... READ
.
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
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 LOCK TABLES
performs an implicit COMMIT
and
UNLOCK
TABLES
.