MySQL lock mechanism Summary (II), mysql lock mechanism Summary

MySQL lock mechanism Summary (II), mysql lock mechanism Summary

Preface:

Mysql is a database system that supports plug-in storage engines. The lock mechanism discussed in this article mainly includes the SERVER layer locks and the storage engine locks. The storage engine refers to innodb, other storage parameters are not discussed for the moment.

1. Basic concepts related to locks in Databases

1) optimistic lock and pessimistic lock

Optimistic locks and pessimistic locks are both concurrent control policies. Pessimistic locks assume that multiple transactions access the same resource at the same time, and adopt the "Lock first and then access" strategy, which may lead to the risk of deadlock. Compared with the pessimistic lock, optimistic locks are generated instead of a timestamp or version number, assuming that multiple transactions do not affect each other during the running process. When writing and reading records, they are not locked, in the transaction commit phase, check whether the version number of the record has been modified (if modified, it indicates that there are other transaction reads and writes) and determine whether to roll back the transaction. Currently, in the database field, almost all DBMS adopt the pessimistic lock mechanism.

2) MVCC (Multi-Version Concurrency Control)

MVCC is also a concurrency control method. MVCC improves the pessimistic lock control mechanism and achieves the effect of "read locks and read/write conflicts" through the historical versions of redundant data, improves concurrency. MVCC mainly applies to read commit and Repeatable read isolation levels.

3) Two-phase lock protocol

The so-called two-segment lock protocol means that the lock is divided into two phases: Lock and unlock, to ensure that the lock and unlock phases are not staggered. For the database system, the transaction is in the lock phase at the beginning; when the transaction is committed or rolled back, the transaction enters the unlock phase. Only database systems that meet the two lock protocols can schedule concurrent transactions in a serialize manner.

4) Intention lock

The intention lock mechanism stipulates that if a node is locked, the intention lock must be applied to the previous node. For example, before locking a record, add an intention lock to the table where the record is located. Intention locks mainly include IS and IX. Their compatibility with S and x is not described here. The intention lock is mainly used to improve the efficiency of table lock and row lock conflict detection.

5) Table lock, record (ROW) Lock

Table locks and record locks are the most basic locks in the lock system. Used to lock tables and records respectively. For a table, there can be four lock categories, namely S, X, IS, and IX. For a table, IS and IX indicate that records need to be read and written; record locks mainly include X locks and S locks. For the implementation of row locks, refer to the previous article to learn the source code of INNODB row locks.

6) metadata lock)

A dictionary lock is a lock that protects metadata. It is mainly used to prevent conflicts between DDL and DML. For more information about MDL, see mysql metadata lock.

7) deadlock

A deadlock refers to a State in which two or more transactions occupy the resources that the other party expects to obtain, form a cyclic wait, and cannot continue to execute each other.

2. Example

The above lists so many types of locks. The following uses a simple example to illustrate how various locks work, and how they apply and release locks in sequence. Here we assume that the isolation level is RC and ID is the primary key.

begin：update t3 set c1=1 where id=1;commit;

Process	Execution statement	Execution content	Dictionary lock	Row/table locks
1	Begin		Release MDL Release_transactional_locks	Release table locks and row locks
2	Update t3 set c1 = 1 where id = 1;	Upper dictionary lock	GLOBAL: STATMENT MDL_INTENTION_EXCLUSIVE
3			TABLE: TRANSACTION MDL_SHARED_WRITE
4		Upstream lock		LOCK_TABLE: IX (Table: t3)
5				LOCK_REC: X (Id = 1)
6		Execute update
7		Release MDL	GLOBAL: STATMENT
8	Commit;	COMMIT Dictionary lock	COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE
9		Submit execution
10		Release engine lock		Lock_release
11		Release MDL	COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE
12			Release_transactional_locks TABLE: TRANSACTION

As you can see, the first line begin indicates that a new transaction is started, and the previous transaction of the session is committed implicitly, and the previous lock needs to be released. Rows 2nd to 7 are the locks in the two-phase locks. The dictionary locks, table intention locks, and row locks are successively applied. After the lock is completed, the real update phase starts. From here, we can also see that MySQL write operations comply with the pessimistic lock policy. Rows 4th and 5th show how the intention Lock works. Before record the row lock with id = 1, the intention lock is first applied to table t3. Row 3: after the STATEMENT is executed, the STATEMENT-level dictionary lock can be released to prevent long hold locks from blocking DDL operations on the table. 8-12 is the commit phase. The lock release process enters the two locks. The table locks and row locks at the engine layer are released successively, and then the MDL locks at the TRANSACTION level are released.

3. Lock analysis of common statements

Assume that the isolation level is RC and the id is the primary key.

Typical statements	SQL layer (MDL lock)		Storage Engine Innodb
	Range/Object	Hold time	Table lock	Row lock
SELECT Operation SELECT * FROM T	TABLE: MDL_SHARED_READ	MDL_TRANSACTION	None	None
Show create table T	TABLE: MDL_SHARED_HIGH_PRIO	MDL_TRANSACTION
LOCK TABLE T READ	TABLE: MDL_SHARED_READ	MDL_TRANSACTION	None	None
LOCK TABLE T WRITE	GLOBAL: MDL_INTENTION_EXCLUSIVE	MDL_STATEMENT	None	None
	SCHEMA: MDL_INTENTION_EXCLUSIVE TABLE: MDL_SHARED_NO_READ_WRITE	TRANSACTION
Flush table t with read lock	TABLE: MDL_SHARED_NO_WRITE	TRANSACTION	None	None
Flush table with read lock	GLOBAL: MDL_SHARED	MDL_EXPLICIT	None	None
	COMMIT: MDL_SHARED	MDL_EXPLICIT
DML operations SELECT * from t for update; Update T set c1 =? Where id =?	GLOBAL: MDL_INTENTION_EXCLUSIVE	MDL_STATEMENT	IX	X
	TABLE: MDL_SHARED_WRITE	TRANSACTION
DDL operations Alter table t add column c1 int; Truncate table t;	GLOBAL: MDL_INTENTION_EXCLUSIVE	MDL_STATEMENT	None	X
	SCHEMA: MDL_INTENTION_EXCLUSIVE TABLE: MDL_EXCLUSIVE	TRANSACTION
	COMMIT: MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT
Set global read_only = 1;	GLOBAL: MDL_SHARED COMMIT: MDL_SHARED	MDL_EXPLICIT	None	None