Detailed explanation of MySQL locks

Lock, in real life, is a tool used by us when we want to hide from the outside world. In computers, it is a mechanism for coordinating concurrent access to a resource by multiple processes or counties. In a database, in addition to the competition for traditional computing resources (CPU, RAM, I/O, etc.), data is also a resource shared and accessed by many users. How to ensure the consistency and effectiveness of concurrent access to data is a problem that all databases must solve. Lock conflicts are also an important factor affecting the performance of concurrent access to databases. From this perspective, locks are particularly important for databases.

1. Locks in MySQL

MySQL has the concepts of Lock and Latch. In the database, both of them can be called is "lock", but the two have completely different meanings.

#Latch is generally called a latch (lightweight lock) because the locking time required must be very short. If it lasts for a long time, the application performance will be very poor. In the InnoDB engine, Latch can be divided into mutex (mutex) and rwlock (read-write lock). Its purpose is to ensure the correctness of concurrent threads operating critical resources, and there is usually no deadlock detection mechanism.

Lock's object is a transaction, and it is used to lock objects in the database, such as tables, pages, and rows. And generally, lock objects are only released after transaction commit or rollback (the release time may be different for different transaction isolation levels).

For a more detailed explanation of Latch, please refer to: Regarding in-depth analysis and judgment of MySQL latch contention, this article mainly focuses on Lock locks.

Types of locks

There are actually only two operations on data, namely reading and writing. When the database implements locks, it will also do this. The two operations use different locks; InnoDB implements standard row-level locks, namely shared locks (Shared Lock) and exclusive locks (Exclusive Lock).

• Shared lock (read lock) allows a transaction to read one row of data.

• Exclusive lock (write lock), allows a transaction to delete or update a row of data.

And their names also imply another characteristic of each. Shared locks are compatible with each other, while mutex locks are incompatible with any other locks:

If you think about their use a little bit, you can understand why they are designed this way. Because shared locks represent read operations and mutex locks represent write operations, we can parallelize them in the database. Read, but can only write serially. Only in this way can we ensure that thread competition will not occur and thread safety can be achieved.

Lock granularity

Lock locks are mainly divided into table locks, page locks and row locks according to the granularity. Different storage engines have different lock granularities.

Table lock

Table-level locking is the most granular locking mechanism among MySQL storage engines. The biggest feature of this locking mechanism is that the implementation logic is very simple and brings minimal negative impact on the system. So acquiring and releasing locks is very fast. Since table-level locks lock the entire table at one time, the deadlock problem that plagues us can be avoided.

Of course, the biggest negative impact of large locking granularity is that the probability of contention for locking resources will be the highest, which will greatly reduce concurrency.

Table-level locking is mainly used by some non-transactional storage engines such as MyISAM, MEMORY, and CSV.

The syntax of table lock is very simple:

# 获取表锁
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
 
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
 
# 释放表锁
UNLOCK TABLES

MyISAM will automatically perform table locking and unlocking operations before executing the query. Generally, the user does not need to manually add or unlock the table, but there are It also needs to be displayed when locked. For example: retrieve the number of data in the table at a certain time t1 and t2.

LOCK TABLE t1 read, t2 read;
select count(t1.id1) as 'sum' from t1;
select count(t2.id1) as 'sum' from t2;
UNLOCK TABLES;

Page lock

Page-level locking is a unique locking level in MySQL and is not too common in other database management software. The characteristic of page-level locking is that the locking granularity is between row-level locking and table-level locking, so the resource overhead required to obtain the lock and the concurrent processing capability it can provide are also between the above two. In addition, page-level locking and row-level locking will cause deadlock.

In the process of implementing resource locking in the database, as the granularity of the locking resources decreases, more and more memory is consumed to lock the same amount of data, and the implementation algorithm will also become more and more complex. The more complex it is. However, as the granularity of locked resources decreases, the possibility of application access requests encountering lock waits will also decrease, and the overall concurrency of the system will also increase.

The main use of page-level locking is the BerkeleyDB storage engine.

Row lock

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

总结

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

2、InnoDB中的锁

意向锁

上节提到InnoDB 支持多种粒度的锁，也就是行锁和表锁。为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock）。

那什么是意向锁呢？我们在这里可以举一个例子：如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

与上一节中提到的两种锁的种类相似的是，意向锁也分为两种：

• 意向共享锁（IS）：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁。

• 意向互斥锁（IX）：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

随着意向锁的加入，锁类型之间的兼容矩阵也变得愈加复杂：

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

行锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁。

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。例如有一个索引有10，11，13和20这4个值，那么该索引可能被Next-Key Locking的区间为：

除了Next-Key Locking，还有Previous-Key Locking技术。同样上述的值，使用Previous-Key Locking技术，那么可锁定的区间为：

但是不是所有索引都会加上Next-key Lock的，在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock。

接下来，我们来通过一个例子解释一下。

CREATE TABLE z (
    a INT,
    b INT,
    PRIMARY KEY(a),    // a是主键索引
    KEY(b)    // b是普通索引
);
INSERT INTO z select 1, 1;
INSERT INTO z select 3, 1;
INSERT INTO z select 5, 3;
INSERT INTO z select 7, 6;
INSERT INTO z select 10, 8;

这时候在会话A中执行 SELECT * FROM z WHERE b = 3 FOR UPDATE ，索引锁定如下：

这时候会话B执行的语句落在锁定范围内的都会进行waiting

SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4, 2;
INSERT INTO z SELECT 6, 5;

用户可以通过以下两种方式来显示的关闭Gap Lock：

• 将事务的隔离级别设为 READ COMMITED。

• 将参数innodb_locks_unsafe_for_binlog设置为1。

从上面的例子可以看出来，Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，设计它的目的是用来解决Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB就是使用它来解决幻读问题。

幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL可能会返回之前不存在的行，也就是第一次执行和第二次执行期间有其他事务往里插入了新的行。

一致性非锁定读

一致性非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过多版本控制（MVCC）的方式来读取当前执行时间数据库中行的数据。如果读取的这行正在执行DELETE或UPDATE操作，这时读取操作不会向XS锁一样去等待锁释放，而是会去读一个快照数据。MVCC相关的知识我已经在另外一篇文章中阐述了，这里就不做过多原理的分析了。地址：谈谈MySQL InnoDB存储引擎事务的ACID特性

在事务隔离级别RC和RR下，InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义却不同，在RC级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在RR级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

下面我们通过一个例子来看看大家是否对MVCC理解了。

可以看到，第1步和第2步是非常容易理解的，而在第3步事务B插入一条新的数据后，在第4步事务A还是查不到，也就是利用了MVCC的特性来实现。当事务B提交后，第5步的查询在RC和RR隔离级别下的输出是不同的，这个的原因在另一篇博客中也说到了，是因为他们创建ReadView的时机不同。

但是很诡异的是在第6步的时候，事务A更新了一条它看不见的记录，然后查询就能够查询出来了。这里很多人容易迷惑，不可见不代表记录不存在，它只是利用了可见性判断忽略了而已。更新成功之后，事务A顺其自然的记录了这条记录的Undo log，在随后的查询中，因为它能够看见自己的改动这一个可见性的判断，自然就能够查询出来了。这里很多名词需要去深入读一下此文：谈谈MySQL InnoDB存储引擎事务的ACID特性

一致性锁定读

前面说到，在默认隔离级别RR下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作。

• SELECT … FOR UPDATE （X锁）

• SELECT … LOCK IN SHARE MODE （S锁）

3、锁带来的问题

通过锁定机制可以实现事务隔离性要求，使得事务可以并发的工作。锁提高了并发，但是却会带来潜在的问题。不过好在有事务隔离性的要求，不同的隔离级别解决的锁的问题也不同，这里只进行简单的介绍，不进行举例分析了。

InnoDB存储引擎在RR级别就已经解决了所有问题，但是它和Serializable的区别在哪里呢？区别就在于RR级别还存在一个丢失更新问题，而SERIALIZABLE无论对于查询还是更新都会进行锁定操作。

As shown in the figure, the user's original amount is 100. If the judgment of transfer and deposit in the program is to query first and then update, there will be a problem of lost updates, that is, the later updates will overwrite the previous updates. If you want to avoid this problem, you can only base the amount on the latest value in the table every time you update it. If you must query first and then update, you can determine the amount in the update conditions (optimistic locking), or you can use SERIALIZABLE with the highest isolation level.

4. Deadlock

Deadlock means that two or more transactions compete for lock resources during the execution process. This creates a phenomenon of waiting for each other. Here is a deadlock problem encountered in a previous project and an in-depth analysis: analysis of the MySQL deadlock problem caused by an online problem. I will not go into details here.

Recommended tutorial: "Mysql Tutorial"

The above is the detailed content of Detailed explanation of MySQL locks. For more information, please follow other related articles on the PHP Chinese website!