MySQL的鎖詳解

Guanhui轉載: 2020-04-29 11:12:053115瀏覽

鎖，在現實生活中是為我們想要隱藏於外界所使用的工具。在電腦中，是協調多個流程或縣城並發存取某一資源的機制。在資料庫當中，除了傳統的運算資源（CPU、RAM、I/O等等）的爭用之外，資料也是許多使用者共享存取的資源。如何確保資料並發存取的一致性、有效性，是所有資料庫必須解決的問題，鎖的衝突也是影響資料庫並發存取效能的重要因素。從這角度來說，鎖對於資料庫而言就顯得尤為重要。

1、MySQL中的鎖定

#MySQL中有著Lock和Latch的概念，在資料庫中，這兩者都可以被稱為為“鎖”，但是兩者有著截然不同的含義。

MySQL的鎖詳解

Latch一般稱為閂鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的效能會非常差，在InnoDB引擎中，Latch又可以分為mutex（互斥量）和rwlock（讀寫鎖定）。其目的是用來保證並發執行緒操作臨界資源的正確性，並且通常沒有死鎖偵測的機制。

Lock的對像是事務，用來鎖定的是資料庫中的對象，如表、頁、行。且一般lock的物件僅在事務commit或rollback後進行釋放（不同事務隔離等級釋放的時間可能不同）。

關於Latch更詳細的講解可以參考：關於MySQL latch爭用深入分析與判斷，本文主要關注的是Lock鎖定。

鎖定的類型

對資料的操作其實只有兩種，也就是讀寫，而資料庫在實作鎖定時，也會對這兩種操作使用不同的鎖；InnoDB 實作了標準的行級鎖定，也就是共用鎖定（Shared Lock）和互斥鎖定（Exclusive Lock）。

共享鎖定（讀鎖定），允許交易讀取一行資料。
排他鎖（寫鎖），允許交易刪除或更新一行資料。

而它們的名字也暗示著各自的另一個特性，共享鎖之間是相容的，而互斥鎖與其他任意鎖都不相容：

MySQL的鎖詳解

稍微對它們的使用進行思考就能想明白它們為什麼要這麼設計，因為共享鎖定代表了讀取操作、互斥鎖代表了寫入操作，所以我們可以在資料庫中並行讀，但是只能串行寫，只有這樣才能保證不會發生線程競爭，實現線程安全。

鎖定的粒度

Lock鎖定依粒度主要分為表鎖定、頁鎖定和行鎖定。不同的儲存引擎擁有的鎖粒度都不同。

MySQL的鎖詳解

表鎖定

表格層級的鎖定是MySQL各儲存引擎中最大顆粒度的鎖定機制。此鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以取得鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的機率也會最高，致使並發度大打折扣。

使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性儲存引擎。

表格鎖定的語法很簡單：

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

MyISAM在執行查詢前，會自動執行表格的加鎖、解鎖操作，一般情況下不需要使用者手動加、解鎖，但有的時候也需要顯示加鎖。例如：檢索某一個時刻t1，t2表中資料數量。

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

頁鎖定

頁級鎖定是MySQL中比較獨特的一種鎖定級別，在其他資料庫管理軟體中也不是太常見。頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需的資源開銷，以及所能提供的並發處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。

在資料庫實現資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同資料量的資料所需消耗的記憶體數量是越來越多的，實現演算法也會越來越複雜。不過，隨著鎖定資源顆粒度的減小，應用程式的存取請求遇到鎖等待的可能性也會隨之降低，系統整體並發度也隨之提升。

使用頁級鎖定的主要是BerkeleyDB儲存引擎。

行鎖定

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

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

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

总结

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

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

2、InnoDB中的锁

意向锁

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

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

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

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

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

MySQL的鎖詳解

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

行锁的算法

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的区间为：

MySQL的鎖詳解

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

MySQL的鎖詳解

但是不是所有索引都会加上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 ，索引锁定如下：

MySQL的鎖詳解

这时候会话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特性

MySQL的鎖詳解

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

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

MySQL的鎖詳解

可以看到，第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、锁带来的问题

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

MySQL的鎖詳解

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

MySQL的鎖詳解

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.

MySQL的鎖詳解

相關文章