Lock:

  • Latch is commonly referred to as a lightweight lock because it requires a very short period of time.
  • The object of lock is a transaction. It is used to lock objects in the database, such as tables, pages, and rows.
Latch:
  • In InnoDB storage engine, Latch can be divided into mutex (mutex) and RWlock (read/write lock).
  • Its purpose is to ensure that concurrent threads operate on critical resources correctly, and there is usually no mechanism for deadlock detection.
The lock:
* InnoDB storage engine supports granular locking, which allows transaction row-level locks and table-level locks to exist simultaneouslyCopy the code
Row-level locks
InnoDB storage engine implements the following two standard row-level locks: * Shared locks (S locks), which allow transactions to read a row of data. * X Lock, which allows transactions to delete or update a row of data.Copy the code
Intent locks

  • To support locking at different granularity, InnoDB storage engine supports an additional locking method called Intention Lock.

  • Intent locking is a process that divides locked objects into multiple tiers. Intent locking means that transactions wish to be locked on a finer granularity. Database, table, page, row.

  • To lock the most fine-grained objects, you first need to lock coarse-grained objects.

Example: lock X on record R on A page, then lock IX on database A, table, page, and finally lock X on record R. If any of these parts cause a wait, the operation needs to wait for the coarse-grained lock to complete.

  • InnoDB storage engine supports intentional locks, which are table level locks. The main purpose is to reveal the type of lock that will be requested for the next row in a transaction.

(1) A transaction wants to acquire a shared Lock on some rows of a table. (2) an intended exclusive Lock on some rows of a table

  • Because InnoDB storage engine supports row-level locks, intentional locks don’t actually block any requests other than full table sweeps.
Lock contention view
  • Starting with InnoDB1.0, tables INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS were added under the INFORMATION_SCHEMA schema. With these three tables, users can more easily monitor current transactions and analyze possible locking problems.
INNODB_TRX table:
  • This table only shows the current running InnoDB transactions and does not directly determine some of the locking conditions. If you need
INNODB_LOCKS table:
  • To view locks, you also need to access the table INNODB_LOCKS
INNODB_LOCK_WAITS table:
  • The INNODB_LOCK_WAITS table visually reflects the waits for the current transaction.
Consistent Nonlocking Read

  • InnoDB storage engine uses multi versioning to read rows in the current execution time database. If the read row is performing a DELETE or UPDATE operation, the read operation does not therefore wait for the row lock to be released. Instead, the InnoDB storage engine reads a snapshot of the row.

  • InnoDB storage engine uses non-locked consistent reads at READ COMMITTED and REPEATABLE READ (the default transaction isolation level of InnoDB storage engine). However, the definition of snapshot data is different.

At the READ COMMITTED transaction isolation level, for snapshot data, non-consistent reads always READ the latest snapshot data of the locked row. In REPEATABLE READ transaction isolation level, for snapshot data, inconsistent reads always READ the version of the row data at the start of the transaction.

  • InnoDB storage engine supports two consistent locking reads for SELECT statements:

  • The SELECT… FOR UPDATE

    • The SELECT… FOR UPDATE adds an X lock to a row read. Other transactions cannot add any locks to a locked row.

  • The SELECT… LOCK IN SHARE MODE.

    • The SELECT… LOCK IN SHARE MODE Adds an S LOCK to a read row. Other transactions can add an S LOCK to the locked row, but if you add an X LOCK, the row will be blocked.

  • For a consistent unlocked read, even if the row read has been selected… FOR UPDATE can also be read, as discussed earlier. The SELECT… FOR UPDATE, the SELECT… LOCK IN SHARE MODE Must be IN a transaction. When the transaction commits, the LOCK is released.

On the lock:
  • In the memory structure of the InnoDB storage engine, there is an auto-increment counter for each table that contains a self-increment value.
  • When an insert is performed on a table that contains a self-growing counter, the counter is initialized and the following statement is executed to get the value of the counter:

SELECT MAX(auto_inc_col) FROM t FOR UPDATE; The insert operation assigns a self-growing column based on the self-growing counter value by 1. This implementation is called auto-inc Locking.

The algorithm of the lock
  • Record Lock (single row record lock)
  • Gap (gap lock)
  • Next-key lock = Gap lock + Record lock