MySQL 잠금에 대한 자세한 설명

자물쇠는 실생활에서 우리가 외부 세계로부터 숨고 싶을 때 사용하는 도구입니다. 컴퓨터에서는 여러 프로세스나 카운티가 리소스에 대한 동시 액세스를 조정하는 메커니즘입니다. 데이터베이스에서 데이터는 기존 컴퓨팅 리소스(CPU, RAM, I/O 등)에 대한 경쟁 외에도 많은 사용자가 공유하고 액세스하는 리소스이기도 합니다. 데이터에 대한 동시 접근의 일관성과 효율성을 어떻게 보장하는가는 모든 데이터베이스가 해결해야 할 문제이다. 잠금 충돌 역시 데이터베이스에 대한 동시 접근 성능에 영향을 미치는 중요한 요소이다. 이러한 관점에서 잠금은 데이터베이스에 특히 중요합니다.

1. MySQL의 잠금

MySQL에는 잠금과 래치라는 개념이 있습니다. 데이터베이스에서는 둘 다 "잠금"이라고 부를 수 있지만 완전히 다른 의미를 갖습니다.

잠금 시간이 매우 짧아야 하므로 일반적으로 래치를 래치(경량 잠금 장치)라고 합니다. 오랫동안 지속되면 애플리케이션의 성능이 매우 저하됩니다. InnoDB 엔진에서는 Latch를 mutex(뮤텍스)와 rwlock(읽기-쓰기 잠금)으로 나눌 수 있습니다. 그 목적은 중요한 리소스를 작동하는 동시 스레드의 정확성을 보장하는 것이며 일반적으로 교착 상태 감지 메커니즘이 없습니다.

Lock의 개체는 트랜잭션이며 테이블, 페이지, 행과 같은 데이터베이스의 개체를 잠그는 데 사용됩니다. 그리고 일반적으로 잠금 개체는 트랜잭션 커밋 또는 롤백 후에만 해제됩니다(해제 시간은 트랜잭션 격리 수준에 따라 다를 수 있음).

Latch에 대한 자세한 설명은 MySQL 래치 경합에 대한 심층 분석 및 판단을 참조하세요. 이 기사는 주로 잠금 잠금에 중점을 둡니다.

잠금 유형

데이터에는 읽기와 쓰기라는 두 가지 작업만 있습니다. 데이터베이스가 잠금을 구현할 때 InnoDB는 표준 행 수준 잠금, 즉 공유에도 서로 다른 잠금을 사용합니다. 잠금(공유 잠금) 및 배타적 잠금(배타적 잠금).

• 공유 잠금(읽기 잠금)을 사용하면 트랜잭션이 데이터 행을 읽을 수 있습니다.

• 배타적 잠금(쓰기 잠금)을 사용하면 트랜잭션에서 데이터 행을 삭제하거나 업데이트할 수 있습니다.

그리고 그 이름은 각각의 또 다른 특징을 암시하기도 합니다. 공유 잠금은 서로 호환되지만 뮤텍스 잠금은 다른 잠금과 호환되지 않습니다.

조금만 생각해 보면 그 이유를 알 수 있습니다. 공유 잠금은 읽기 작업을 나타내고 뮤텍스 잠금은 쓰기 작업을 나타내기 때문에 데이터베이스에서 병렬로 읽을 수 있지만 이러한 방식으로만 스레드 경쟁이 발생하지 않도록 할 수 있습니다. 스레드 안전성을 달성합니다.

잠금 세분성

잠금 잠금은 세분성에 따라 주로 테이블 잠금, 페이지 잠금, 행 잠금으로 구분됩니다. 스토리지 엔진마다 잠금 단위가 다릅니다.

테이블 잠금

테이블 수준 잠금은 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 'sum' from t1;
select count(t2.id1) as 'sum' from t2;
UNLOCK TABLES;

페이지 잠금

페이지 수준 잠금은 MySQL의 고유한 잠금 수준이며 다른 데이터베이스 관리 소프트웨어에서는 흔하지 않습니다. 페이지 수준 잠금의 특징은 잠금 세분성이 행 수준 잠금과 테이블 수준 잠금 사이에 있으므로 잠금을 획득하는 데 필요한 리소스 오버헤드와 제공할 수 있는 동시 처리 기능도 위 둘 사이에 있다는 것입니다. 또한 페이지 수준 잠금과 행 수준 잠금은 교착 상태를 유발합니다.

데이터베이스에서 리소스 잠금을 구현하는 과정에서 잠긴 리소스의 세분성이 감소함에 따라 동일한 양의 데이터를 잠그는 데 점점 더 많은 메모리가 소비되고 구현 알고리즘이 점점 더 복잡해집니다. 그러나 잠긴 리소스의 세분성이 감소함에 따라 애플리케이션 액세스 요청에서 잠금 대기가 발생할 가능성도 감소하고 시스템의 전체 동시성도 증가합니다.

페이지 수준 잠금의 주요 용도는 BerkeleyDB 스토리지 엔진입니다.

라인 잠금

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

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

使用行级锁定的主要是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无论对于查询还是更新都会进行锁定操作。

그림과 같이 사용자의 원래 금액은 100입니다. 프로그램에서 이체 및 입금의 판단이 먼저 쿼리한 후 업데이트하는 것이라면 업데이트가 손실되는 문제가 있습니다. 즉, 이후 업데이트가 덮어쓰게 됩니다. 이전 업데이트. 이 문제를 피하려면 테이블이 업데이트될 때마다 테이블의 최신 값만을 기준으로 금액을 계산하면 됩니다. 먼저 쿼리한 다음 업데이트해야 하는 경우 업데이트 조건(낙관적 잠금)에서 양을 판단하거나 가장 높은 격리 수준으로 SERIALIZABLE을 사용할 수 있습니다.

4. 교착상태

교착상태는 실행 과정에서 두 개 이상의 트랜잭션이 잠금 자원을 두고 경쟁하면서 발생하는 상호 대기 현상을 말합니다. 이는 이전 프로젝트에 직접적으로 발생하는 교착 상태 문제입니다. 분석: 온라인 문제로 인해 발생하는 MySQL 교착 상태 문제에 대한 분석은 여기서 반복하지 않습니다.

추천 튜토리얼: "Mysql Tutorial"

위 내용은 MySQL 잠금에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!