Movatterモバイル変換

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set toSERIALIZABLE. ForSERIALIZABLE 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.

SELECT ... FOR UPDATE andSELECT ... FOR SHARE statements that use a unique index acquire locks for scanned rows, and release the locks for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in theWHERE 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 aUNION, 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.

Forlocking reads (SELECT withFOR UPDATE orFOR SHARE),UPDATE, andDELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.

For index records the search encounters,SELECT ... FOR UPDATE blocks other sessions from doingSELECT ... FOR SHARE 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.

WhenUPDATE modifies a clustered index record, implicit locks are taken on affected secondary index records. TheUPDATE 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 anInnoDB tablet1 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 simpleINSERT 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 anINSERT 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 intoT. If the transaction isolation level isREAD COMMITTED,InnoDB does the search onS as a consistent read (no locks). Otherwise,InnoDB sets shared next-key locks on rows fromS.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 theSELECT with shared next-key locks or as a consistent read, as forINSERT ... SELECT.

When aSELECT is used in the constructsREPLACE INTO t SELECT ... FROM s WHERE ... orUPDATE t ... WHERE col IN (SELECT ... FROM s ...),InnoDB sets shared next-key locks on rows from tables.

InnoDB sets an exclusive lock on the end of the index associated with theAUTO_INCREMENT column while initializing a previously specifiedAUTO_INCREMENT column on a table.

Withinnodb_autoinc_lock_mode=0,InnoDB uses a specialAUTO-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 theAUTO-INC table lock is held. The same behavior occurs for“bulk inserts” withinnodb_autoinc_lock_mode=1. Table-levelAUTO-INC locks are not used withinnodb_autoinc_lock_mode=2. For more information, SeeSection 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

InnoDB fetches the value of a previously initializedAUTO_INCREMENT column without setting any locks.

If aFOREIGN 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 theInnoDB layer that sets these locks.InnoDB is aware of table locks ifinnodb_table_locks = 1 (the default) andautocommit = 0, and the MySQL layer aboveInnoDB 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 inSection 17.7.5.2, “Deadlock Detection”.

LOCK TABLES acquires two locks on each table ifinnodb_table_locks=1 (the default). In addition to a table lock on the MySQL layer, it also acquires anInnoDB table lock. To avoid acquiringInnoDB table locks, setinnodb_table_locks=0. If noInnoDB table lock is acquired,LOCK TABLES completes even if some records of the tables are being locked by other transactions.

In MySQL 9.3,innodb_table_locks=0 has no effect for tables locked explicitly withLOCK TABLES ... WRITE. It does have an effect for tables locked for read or write byLOCK TABLES ... WRITE implicitly (for example, through triggers) or byLOCK TABLES ... READ.

AllInnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invokeLOCK TABLES onInnoDB tables inautocommit=1 mode because the acquiredInnoDB table locks would be released immediately.

You cannot lock additional tables in the middle of a transaction becauseLOCK TABLES performs an implicitCOMMIT andUNLOCK TABLES.