1. What is a lock

The locking mechanism is used to manage concurrent access to shared resources.

The lock and the latch

• Latch is commonly referred to as a lightweight lock because it requires a very short period of time. If the duration is long, the application performance deteriorates. 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 object of lock is a transaction. It is used to lock objects in the database, such as tables, pages, and rows. Generally, locked objects are released only after a transaction commit or rollback. Transaction isolation level (select @@tx_isolation;) .

2, MyISAM storage engine lock

MyISAM storage engine only supports table locks.

MyISAM storage engine’s read and write locks are mutually exclusive, and read and write operations are serial. However, it considers that the write lock has a higher priority than the read lock, so even if the read request first arrives in the lock wait queue, the write lock will be inserted before the read lock request! This is why MyISAM tables are not suitable for applications with a large number of update and query operations, because a large number of update operations make it difficult for query operations to acquire read locks and may block forever. You can adjust the scheduling behavior of MyISAM through a number of Settings.

InnoDB storage engine locks

3.1 Types of locks

InnoDB storage engine implements the following two standard row-level locks:

• A shared Lock (S Lock) allows a transaction to read a row of data.
• An X Lock that allows a transaction to delete or update a row of data.

The X lock is not compatible with any lock, while the S lock is only compatible with the S lock. Both the S and X locks are row locks. Compatibility refers to the compatibility of locks on the same row.

Intent locks are designed primarily to reveal the type of lock that will be requested for the next row in a transaction. If you need to lock X on record R on A page, then you need to lock IX on database A, table, page, and finally record R. If any of these parts cause a wait, the operation needs to wait for the coarse-grained lock to complete.

Two types of intent locks:

• A transaction wants to acquire a shared Lock for rows in a table
• A transaction wants to acquire an exclusive Lock for rows in a table

Intent locks do not block any requests except for full table sweeps.

Lock compatibility

InnoDB grants the requested lock to a transaction if its lock mode is compatible with the current one; Otherwise, if the two are incompatible, the transaction waits for the lock to be released.

3.2 Consistent Unlocked read

Consistent nonlocking read refers to the fact that InnoDB storage engine reads rows in the current execution time database through multi versioning. 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 default Settings, this is the default read mode, that is, read does not occupy and wait on the lock on the table.

Different transaction isolation levels are read in different ways, and non-locking consistent reads are not used at every transaction isolation level. In addition, the definition of snapshot data varies, even though non-locked consistent reads are used.

3.3 Consistent Lock read

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

• The SELECT… FOR UPDATE
• The SELECT… LOCK IN SHARE MODE

3.4 Self-growth and locking

Auto-inc Locking, in fact, adopts a special table Locking mechanism. In order to improve the performance of insertion, the lock is released not after the completion of a transaction, but immediately after the COMPLETION of THE SQL statement of self-increasing value insertion.

3.5 Foreign Keys and Locks

In the InnoDB storage engine, if a foreign key column is not explicitly indexed, the InnoDB storage engine automatically adds an index to it because this avoids table locks.

4. Lock algorithm

4.1 InnoDB storage engine has three algorithms for row locking

• Record Lock: A Lock on a single row Record
• Gap Lock: a Gap Lock that locks a range, but does not contain the record itself. It is a space open on left and closed on right. As,3,5,8 index values are 1, GAP range: (- up, 1), (1, 3), (3, 5), (5, 8], (8 + up). The purpose of GAP Lock is to prevent the illusion of two current reads of the same transaction.
• Next-key Lock: Gap Lock+Record Lock locks a range, and locks the Record itself. For the query of rows, this method is used, the main purpose is to solve the illusion problem.

4.2 Solving Phantom Problem

At the default transaction isolation level, REPEATABLE READ, InnoDB storage engine uses next-key Locking to avoid Phantom Problem.

Phantom Problem means that executing the same SQL statement twice in a row in the same transaction may result in different results, and the second SQL statement may return a row that did not exist before.

The default transaction isolation level of InnoDB storage engine is REPEATABLE READ. In this isolation level, next-key Locking is adopted for Locking. At READ COMMITTED transaction isolation level, only Record Lock is used.

5. Lock problems

5.1 phantom read

The same query returns inconsistent results multiple times within the same transaction. Transaction A added A record, and transaction B executed A query operation before and after transaction A committed, and found that the latter one had one more record than the previous one. Phantom reads are caused by concurrent transactions adding records, which cannot be solved like unrepeatable reads by locking records because new records cannot be locked at all. Transactions need to be serialized to avoid phantom reads.

5.2 dirty read

Dirty data refers to changes made by transactions to row records in the buffer pool that have not yet been committed. Dirty reads occur only when the isolation level of the transaction is READ UNCOMMITTED. The isolation of the transaction was violated. Dirty READ isolation may seem useless, but there are special cases where you can set the isolation level of a transaction to READ UNCOMMITTED. For example, a slave node in a Replication environment, and queries on that slave do not require a particularly precise return value.

5.3 Cannot be read repeatedly

Data that is read twice in a transaction is not the same, which is called a non-repeatable read. The difference between an unrepeatable read and a dirty read is that a dirty read reads uncommitted data, while an unrepeatable read reads committed data, which violates the requirements of database transaction consistency. The default transaction isolation level of InnoDB storage engine is READ REPEATABLE and next-key Lock algorithm is adopted to avoid unrepeatable reads.

5.4 Missing Updates

The update operation of one transaction can be overwritten by the update operation of another transaction, resulting in data inconsistencies. To avoid missing updates, you need to make the transaction’s operations in this case serialized rather than parallel.

6, a deadlock

Deadlock refers to the phenomenon that two or more transactions are waiting for each other because they compete for lock resources during execution. In addition to the timeout mechanism, the current database is also commonly used to wait for graph (wait graph) deadlock detection.

MyISAM always acquires all locks required by SQL statements at once. This is why no Deadlock Free occurs in MyISAM tables.

The necessary conditions for a deadlock to occur

• Multiple concurrent transactions
• Each transaction holds a lock
• Each transaction needs to hold the lock again
• A locked loop waits between transactions, resulting in a deadlock

Deadlock detection

1.InnoDB initializes a transaction. When a transaction tries to apply for a lock and waits (wait_lock), InnoDB starts deadlock_mark 2. Check for deadlocks and resolve deadlocks by entering the lock_deadlock_check_and_resolve() function. 3. Deadlock detection is limited by a counter. When waiting for wait-for graph detection times out or exceeds the threshold, detection is stopped. 4. One of the logics of deadlock detection is the processing process of wait graph. If a graph is constructed through the lock information and transaction wait chain, if a loop appears in the graph, deadlock is considered to have occurred. 5. Deadlock rollback, one of the internal code processing logic is to compare the number of undo, rollback the number of undo transactions.Copy the code

Deadlock log

update `xxx` set xxx where xxx = xxx RECORD LOCKS space id 123 page no 13726 n bits 248 index idx_xxx of table `xxx` trx  id 123456 lock_mode X locks rec but not gap Record lock, heap no 123 PHYSICAL RECORD: n_fields 13; compact format; info bits 0 update `xxx` set xxx where xxx = xxx RECORD LOCKS space id 123 page no 123456 n bits 128 index PRIMARY of table `xxx` trx id 123456 lock_mode X locks rec but not gap Record lock, heap no 456 PHYSICAL RECORD: n_fields 10; compact format; info bits 0Copy the code

In the deadlock log, you can see which SQL is executed, which page, which index, lock mode, lock attributes.

The properties of the lock

• LOCK_REC_NOT_GAP (lock record)
• LOCK_GAP (GAP before locking record)
• LOCK_ORDINARY (simultaneous lock record + GAP before record. Legendary Next Key lock)
• LOCK_INSERT_INTENTION (actually a special GAP lock)

Deadlock case analysis, the simplest, the most classic deadlock case, add lock order is inconsistent to cause deadlock.

session1:

begin;
select * from user where id = 3 for update;
select * from user where id = 5 for update;
commit;
Copy the code

session2:

begin;
select * from user where id = 5 for update;
select * from user where id = 3 for update;
commit;
Copy the code

The resources

• MySQL Technology Insider — InnoDB Storage Engine (Jiang Chengyao)
• MySQL: Database Development, Optimization and Management and Maintenance (TANG Hanming/Zhai Zhenxing/GUAN Baojun/Wang Hongquan)
• Dev.mysql.com/doc/refman/…
• MySQL locking mechanism
• Yang Qilong’s ramble deadlock