Locks set by different SQL statements in InnoDB

A lock read, an UPDATE or DELETE generally sets the record to be locked on each index record scanned during SQL statement processing. It does not matter whether a condition exists in the WHERE statement to exclude the row. InnoDB does not remember the exact WHERE condition, but only which index range was scanned. The lock is usually a next-key lock and can also prevent the insertion of “gap” before the record. However, gap locking can be explicitly disabled, which will result in no use of the next key lock. See “InnoDB Locking” for more information. The transaction isolation level can also affect which locks are set; See Transaction Isolation Level.

If a secondary index is used in a search and the index record lock to be set is mutually exclusive, InnoDB also retrieves the corresponding clustered index record and sets the lock for it.

If there is no index that fits your statement, and MySQL must scan the entire table to process the statement, every row of the table will be locked, blocking all inserts to the table by other users. It is important to create good indexes so that your queries do not have to scan many rows unnecessarily.

InnoDB sets specific types of locks, as shown below.

  • SELECT … FROM is a consistent read, reading a snapshot of the database with no locking set, unless the transaction isolation level is set to SERIALIZABLE. At the SERIALIZABLE level, the search sets a shared next-key lock on index records encountered. However, for statements that use unique index locks to search unique rows, only index record locks are required.

  • For the SELECT… FOR UPDATE or SELECT… LOCK IN SHARE MODE, which acquires locks for scanned rows and expects them to be released to rows that do not meet the criteria given IN the result set (for example, if they do not meet the criteria given IN the WHERE clause). However, in some cases, rows may not be unlocked immediately because the relationship between the resulting row and its original source is lost during query execution. For example, in a UNION, rows scanned (and locked) from a table might be inserted into a temporary table before evaluating whether they fit the result set. In this case, the relationship between the rows in the temporary table and the rows in the original table is lost, and the subsequent rows are not unlocked until the query execution is complete.

  • SELECT … LOCK IN SHARE MODE Sets a shared next-key LOCK on all index records encountered IN the search. However, for statements that use unique index locks to search unique rows, only index record locks are required.

  • SELECT … FOR UPDATE sets an exclusive next-key lock on each record encountered by the search. However, for statements that use unique index locks to search unique rows, only index record locks are required.

    For indexed records, the search encounters, SELECT… FOR UPDATE prevents other sessions from performing SELECT… LOCK IN SHARE MODE or read certain transaction isolation levels. A consistent read ignores any locks set on records that exist in the read view.

  • UPDATE … WHERE … Set an exclusive next key lock on each record encountered by the search. However, for statements that use unique index locks to search unique rows, only index record locks are required.

  • When UPDATE modifies a clustered index record, the implied lock is taken on the affected second index record. UPDATE performs a repeat check scan before a new secondary index record is inserted, as well as a share lock on the affected secondary index record when a new secondary index record is inserted.

  • DELETE FROM … WHERE … Set an exclusive next key lock on each record encountered by the search. However, for statements that use unique index locks to search unique rows, only index record locks are required.

  • INSERT sets an exclusive lock on the inserted row. This lock is an index record lock, not a next-key lock (that is, no gap lock), and does not prevent other sessions from being inserted into the gap before the inserted row.

    Before the row is inserted, a gap lock called an insert intention gap lock is set. This lock signals the intention of insertion in this way: if multiple transactions are not inserted in the same place in the gap, there is no need to wait for multiple transactions inserted into each other. Suppose there is an index record with values of 4 and 7. Before attempting to obtain an exclusive lock on the inserted row, the individual transactions attempting to insert values 5 and 6 use the insert intent lock to lock the gap between 4 and 7, respectively, but do not block with each other because the rows do not conflict.

    If a duplicate key error occurs, a shared lock is set on the duplicate indexed record. If another session already has a mutex, using a shared lock can cause a deadlock if more than one session tries to insert the same row. This happens if another session deletes the row. Suppose an InnoDB table T1 has the following structure:

    CREATE TABLE t1 (i INT.PRIMARY KEY (i)) ENGINE = InnoDB;
Copy the code

    Now, assume that the three sessions perform the following operations in order:

    The first:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);
Copy the code

    The second:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);
Copy the code

    The third section:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);
Copy the code

    The first:

    ROLLBACK;
Copy the code

    The first operation of session 1 acquires the exclusive lock for that row. Sessions 2 and 3 both result in duplicate key errors and both request a shared lock for that row. When session 1 rolls back, it releases the exclusive lock for that row and queues up the shared lock request for sessions 2 and 3. At this point, sessions 2 and 3 are deadlocked: because the other holds the shared lock, neither can acquire the exclusive lock for that row.

    A similar situation occurs if the table already contains rows with a key value of 1 and all three sessions perform the following operations in order:

    The first:

    START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
Copy the code

    The second:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);
Copy the code

    The third section:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);
Copy the code

    The first:

    COMMIT;
Copy the code

    The first operation of session 1 acquires the exclusive lock for that row. Sessions 2 and 3 both result in duplicate key errors and both request a shared lock for that row. After session 1 commits, it releases the exclusive lock on that row and grants sessions 2 and 3 queued shared lock requests. At this point, sessions 2 and 3 are deadlocked: because the other holds the shared lock, neither can acquire the exclusive lock for that row.

  • INSERT … ON DUPLICATE KEY UPDATE differs from simple in that INSERT places an exclusive lock instead of a shared lock ON the row to be updated in the event of DUPLICATE KEY errors. Exclusive index record locking is used for duplicate primary key values. For duplicate unique key values, an exclusive next-key lock is used.

  • REPLACEINSERT Completes this operation if there are no conflicts on the unique key. Otherwise, the exclusive next key lock is placed on the row to be replaced.

  • INSERT INTO T SELECT … FROM S WHERE … Set exclusive index record lock (without gap lock) T on each inserted row. 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 searches for S as a consistent READ (without locking). Otherwise, InnoDB sets the shared next key lock S on the row in. InnoDB must set the lock in the latter case: each SQL statement must be executed in exactly the same way as the original operation during roll-forward recovery using statement-based binary logs.

    CREATE TABLE … SELECT … On performing SELECT lock with shared next-key or as consistent read, as used for INSERT… The SELECT.

    When a is used in or SELECT, a shared next key lock is set for rows from table. REPLACE INTO t SELECT … FROM s WHERE … “UPDATE t … WHERE col IN (SELECT … FROM s …) “InnoDB“s

  • InnoDB AUTO_INCREMENT sets an exclusive lock at the end of the index associated with the column when initializing a previously specified column on the AUTO_INCREMENT table.

    When used innodb_autoINC_LOCK_mode =0, InnoDB uses the special auto-inc table lock mode, which will acquire and retain the lock to the end of the current SQL statement (not the end of the entire transaction) when accessing the auto-increment counter. When auto-inc holds the table lock, other clients cannot insert the table. Innodb_autoinc_lock_mode =1 innodb_autoinc_lock_mode=1 Table level auto-inc lock is not used with innodb_autoinc_lock_mode=2. For more information, see “AUTO_INCREMENT Handling in InnoDB.”

    InnoDB AUTO_INCREMENT fetches the values of previously initialized columns without setting any locks.

  • If the FOREIGN KEY defines a constraint on the table, any insert, update, or delete operation that needs to check the constraint sets a shared record-level lock on the record it looks at to check the constraint. InnoDB also sets these locks if constraints fail.

  • LOCK TABLES sets table locks, but InnoDB sets these locks at a higher level than MySQL. InnoDB knows about table locks if Innodb_table_LOCKS = 1 (default) and AUTOCOMMIT = 0, and InnoDB knows about row-level locks in the MySQL layer above.

    Otherwise, InnoDB’s automatic deadlock detection will not detect deadlocks involving such table locks. Again, since the higher MySQL layer is unaware of row-level locks in this case, the table lock can be acquired on the table that currently has row-level locks in another session. However, this does not compromise transaction integrity, as described in “deadlock detection.”

  • LOCK TABLES If Innodb_TABLE_LOCKS =1 (the default), two locks are acquired on each table. In addition to table locks on the MySQL layer, it also acquires InnoDB table locks. 4.1.2 MySQL does not acquire InnoDB table locks prior to release; The old behavior can be set to innodb_TABLE_LOCKS =0. If InnoDB does not acquire a table LOCK, LOCK TABLES will complete even if some records of the table are locked by other transactions.

    Innodb_table_locks =0 innodb_table_locks=0 innodb_table_locks=0 The WRITE. For using LOCK TABLES… WRITE implicitly (for example, via triggers) or by locking to read or WRITE locked TABLES, does have the effect of locking TABLES… The READ.

  • When InnoDB commits or aborts a transaction, it releases all locks held by the transaction. Therefore, it doesn’t make much sense to call the autoCOMMIT =1 mode in the InnoDB table on LOCK TABLES because the acquired InnoDB table LOCK will be released immediately.

  • You cannot LOCK other TABLES in the middle of a transaction because LOCK TABLES performs implicit COMMIT and UNLOCK TABLES.

More content welcome to pay attention to my personal public number “Han Elder brother has words”, 100G artificial intelligence learning materials, a large number of back-end learning materials waiting for you to take.