MySQL 잠금 및 트랜잭션 격리 수준을 알고 계십니까?

관련 무료 학습 권장 사항: mysql 데이터베이스(동영상)

머리말

• MySQL 인덱스 기본 데이터 구조 및 알고리즘
• MySQL 성능 최적화 원칙 - 1부
• MySQL 성능 최적화 - 연습 1
• MySQL 성능 최적화 - 실습 2부

이전에 우리는 MySQL 데이터베이스의 기본 데이터 구조 및 알고리즘과 MySQL 성능 최적화 부분의 일부 내용에 대해 이야기했습니다. MySQL의 잠금 및 트랜잭션 격리 수준에 대해 다시 두 개의 기사로 나누어 이야기하겠습니다. 이 기사는 MySQL의 행 잠금 및 트랜잭션 격리 수준에 중점을 둡니다.

잠금 정의

잠금은 컴퓨터가 여러 프로세스 또는 스레드에서 리소스에 대한 동시 액세스를 조정하는 메커니즘입니다.

데이터베이스에서는 전통적인 컴퓨팅 리소스(예: CPU, RAM, I/O 등)를 두고 경쟁하는 것 외에도 데이터도 사용자가 공유해야 하는 리소스입니다. 동시 데이터 액세스의 일관성과 효율성을 보장하는 방법은 모든 데이터베이스가 해결해야 하는 문제입니다. 잠금 충돌 또한 데이터베이스 동시 액세스 성능에 영향을 미치는 중요한 요소입니다.

잠금 분류

• 는 성능 측면에서 낙관적 잠금(버전 비교에 의해 구현됨)과 비관적 잠금으로 구분됩니다.
• 는 데이터베이스 작업 유형인 읽기 잠금 및 쓰기 잠금으로 구분됩니다. 비관적 잠금에 속함)
  • 읽기 잠금(공유 잠금): 동일한 데이터에 대해 서로 영향을 주지 않고 동시에 여러 읽기 작업을 수행할 수 있습니다.
  • 쓰기 잠금(독점 잠금): 현재 잠금 이전; 쓰기 작업이 완료되면 다른 쓰기 잠금과 읽기 잠금이 차단됩니다.
• 데이터베이스 작업의 세분성은 테이블 잠금과 행 잠금으로 나뉩니다.

잠금에 대한 자세한 내용은 "Understanding of Locks in Java"를 참조하세요.

MySQL 잠금

• 레코드 잠금

• Gap 잠금

• 다음 키 잠금

• 공유 잠금/독점 잠금 및 단독 잠금)

• 의도 공유 및 배타적 잠금

• Insert Intention Locks

• Auto-inc Locks

• Predictive lock, 이런 종류의 잠금은 공간 데이터를 저장하는 공간 인덱스에 주로 사용됩니다.

다음 기사에서 이에 대해 별도로 이야기해 보겠습니다. 이 기사에서는 행 잠금 및 트랜잭션 격리 수준에 중점을 둡니다.

테이블 잠금

각 작업마다 테이블 전체를 잠급니다.

• 오버헤드가 작고 잠금이 빠릅니다.
• 교착 상태가 발생하지 않습니다.
• 잠금 세분성이 크고 잠금 충돌 가능성이 가장 높습니다.
• 동시성이 가장 낮습니다.

기본 작업

예제 테이블은 다음과 같습니다.

# 建表SQLCREATE TABLE mylock (    id INT(11) NOT NULL AUTO_INCREMENT,    NAME VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

# 插入数据INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('1','a'); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('2','b'); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('3','c'); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('4','d');复制代码

• 수동으로 테이블 잠금 추가

lock table 表名称 read(write), 表名称2 read(write);复制代码

• 테이블에 추가된 잠금 보기

show open tables;复制代码

• 테이블 잠금 삭제

unlock tables;复制代码

사례 분석 - 읽기 추가 lock

LOCK TABLE mylock read;复制代码

현재 세션과 다른 세션 모두 테이블을 읽을 수 있습니다.

현재 세션에 잠긴 테이블을 삽입하거나 업데이트하면 오류가 보고되고 다른 세션은 삽입이나 업데이트를 기다립니다.

사례 분석 - 쓰기 잠금 추가

LOCK TABLE mylock WRITE;复制代码

현재 세션에서는 테이블에 대한 추가, 삭제, 수정, 쿼리에는 문제가 없지만, 다른 세션에서 테이블에 대한 모든 작업은 수행됩니다. 차단됩니다.

사례 결론

MyISAM 쿼리 문(SELECT)을 실행하기 전에 관련된 모든 테이블에 읽기 잠금이 자동으로 추가되고, 추가, 삭제, 수정 및 쿼리 작업이 수행되기 전에 쓰기 잠금이 자동으로 추가됩니다. 관련된 테이블.

• MyISAM 테이블에 대한 읽기 작업(읽기 잠금 추가)은 다른 프로세스의 동일한 테이블에 대한 읽기 요청을 차단하지 않지만 동일한 테이블의 쓰기 요청은 차단합니다. 읽기 잠금이 해제된 경우에만 다른 프로세스의 쓰기 작업이 수행됩니다.
• MyISAM 테이블에 쓰기 작업(쓰기 잠금 추가)은 동일한 테이블에 대한 다른 프로세스의 읽기 및 쓰기 작업을 차단합니다. 쓰기 잠금이 해제된 경우에만 다른 프로세스의 읽기 및 쓰기 작업이 수행됩니다.

요약: 읽기 잠금은 쓰기를 차단하지만 읽기는 차단하지만 쓰기 잠금은 읽기와 쓰기를 모두 차단합니다.

행 잠금

각 작업에 대해 한 행의 데이터를 잠급니다.

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

InnoDB 和 MyISAM 的最大不同点：

• 支持事务（TRANSACTION）
• 支持行级锁

行锁支持事务

事务（Transaction）及其 ACID 属性

事务是由一组 SQL 语句组成的逻辑处理单元，事务具有以下四个属性，通常简称为事务的 ACID属性。

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B+树索引或双向链表）也都必须是正确的。
• 隔离性（Lsolation）：数据库系统提供一定的隔离机制，保障事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能保持。

并发事务处理带来的问题

• 更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行值，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖来其他事务所做的更新。

• 脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这个条记录的数据就处于不一致的状态；这时另外一个事务也来读取同一条记录，如果不加控制，第二个事务读取来这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。

总结：事务A读取到来事务B已经修改但尚未提交的数据，还在这个数据基础上做来操作。此时，如果事务B回滚，事务A读取的数据无效，不符合一致性要求。

• 不可重复读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生来改变、或某些记录已经被删除了，这种现象就叫做“不可重复读”。

总结：事务A读取到了事务B已经提交的修改数据，不符合隔离性。

• 幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

总结：事务A读取到了事务B提交的新增数据，不符合隔离性。

事务隔离级别

“脏读”、“不可重复读”、“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

同时，不同应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读” 并不敏感，可能更关系数据并发访问的能力。

查看当前数据库的事务隔离级别

show variables like 'tx_isolation';复制代码

设置事务隔离级别

set tx_isolation='REPEATABLE-READ';复制代码

数据库版本是5.7，隔离级别是Repeatable-Read(可重复读)，不同的数据库版本和隔离级别对语句的执行结果影响很大。所以需要说明版本和隔离级别

行锁与隔离级别案例分析

事务控制语句

• BEGIN 或 START TRANSACTION；显式地开启一个事务；
• COMMIT；也可以使用 COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
• ROLLBACK；有可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
• RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier；把事务回滚到标记点；
• SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

事务处理方法

MYSQL 事务处理主要有两种方法：

1. 用 BEGIN, ROLLBACK, COMMIT来实现
   • BEGIN 开始一个事务
   • ROLLBACK 事务回滚
   • COMMIT 事务确认

2. 直接用 SET 来改变 MySQL 的自动提交模式:
   • SET AUTOCOMMIT=0 禁止自动提交
   • SET AUTOCOMMIT=1`` 开启自动提交

示例表，如下：

CREATE TABLE `user` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR (255) DEFAULT NULL,    `balance` INT (11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('zhangsan','450');INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('lisi', '16000');INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('wangwu','2400');复制代码

行锁演示

一个 session 开启事务更新不提交，另一个 seesion 更新同一条记录会阻塞，更新不同记录u会阻塞。

读未提交

（1）打开一个客户端A，并设置当前事务模式为 read uncommitted (读未提交)，查询表 user 的初始化值

set tx_isolation='read-uncommitted';复制代码

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表 user

（3）这时，虽然客户端B的事务还没提交，但是在客户端A就可以查询到B已经更新的数据

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那么客户端A查询到的数据其实就是脏数据。

（5）在客户端A执行更新语句 update user set balance = balance - 50 where id = 1; zhangsan 的 balance没有变成350，居然是400，是不是很奇怪，数据不一致啊。如果你这么想就太天真了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别。

读已提交

（1）打开一个客户端A，并设置当前事务模式为 read committed (读已提交)，查询表 user 的所有记录

set tx_isolation='read-committed';复制代码

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表 user

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题。

（4）客户端B的事务提交

（5）客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题。

可重复读

（1）打开一个客户端A，并设置当前的事务模式为 repeatable read ，查询表 user 的所有记录。

set tx_isolation='REPEATABLE-READ';复制代码

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表 user 并提交。

（3）在客户端A查询表 user 的所有记录，与步骤（1）查询结果一直，没有出现不可重复读的问题。

（4）在客户端A，接着执行 update user set balance = balance - 50 where id = 1 , balance 没有变成 400 - 50 = 350， zhangsan 的 balance 的值用的是步骤(2) 中的 350 来计算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了 MVCC（multi-version concurrency control）机制，select 操作不会更新版本号，是快照读（历史版本）；insert、update、delete 会更新版本号，是当前读（当前版本）。

我们下篇来讲 MVCC。

（5）重新打开客户端B，插入一条新数据后提交。

（6）在客户端A查询表user 的所有记录，没有查出新增数据，所以没有出现幻读。

（7）验证幻读 在客户端A执行 update user set balance = 8888 where id = 4; ，能更新成功，再次查询到客户端B新增的数据。

串行化

（1）打开一个客户端A，并设置当前事务模式为 serializable ，查询表 user 的初始值

set tx_isolation='serializable';复制代码

（2）打开一个客户端B，并设置当前事务模式为 serializable ，插入一条记录报错，表被锁了插入失败，MySQL 中事务隔离级别为 serializable 时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

案例结论

InnoDB 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一下，但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定的。当系统并发量最高的时候，InnoDB 的整体性能和 MyISAM 相比就会有比较明显的优势。

但是，InnoDB 的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让 InnoDB 的整体性能表现不仅不能比 MyISAM 高，甚至可能会更差。

行锁分析

通过检查 innodb_row_lock 状态变量来分析系统上的行锁的竞争情况：

show status like 'innodb_row_lock%';复制代码

对各个状态量的说明如下：

• Innodb_row_lock_current_waits ：当前正在等待锁定的数量
• Innodb_row_lock_time ：从系统启动到现在锁定总时间长度
• Innodb_row_lock_time_avg ：每次等待所花平均时间
• Innodb_row_lock_time_max ：从系统启动到现在等待最长的一次所花时间
• Innodb_row_lock_waits ：系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

• Innodb_row_lock_time_avg （等待平均时长）
• Innodb_row_lock_waits （等待总次数）
• Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统 中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

死锁

set tx_isolation='REPEATABLE-READ';复制代码

Session_1执行：select * from user where id=1 for update;
Session_2执行：select * from user where id=2 for update;
Session_1执行：select * from user where id=2 for update;
Session_2执行：select * from user where id=1 for update;复制代码

查看近期死锁日志信息：

show engine innodb status\G;复制代码

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况 mysql没法自动检测死锁

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁；
2. 合理设计索引，尽量缩小锁的范围；
3. 尽可能减少检索条件范围，避免间隙锁；
4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行；
5. 尽可能低级别事务隔离。

问答

1. MySQL 默认级别是 repeatable-read，有什么办法可以解决幻读妈？

间隙锁（Gap Lock）在某些情况下可以解决幻读问题，它是 Innodb 在 可重复读 提交下为解决幻读问题时引入的锁机制。要避免幻读可以用间隙锁在Session_1 下面执行 update user set name = 'hjh' where id > 10 and id  ，则其他 Session 没法在这个范围锁包含的间隙里插入或修改任何数据。

如：user 表有3条数据， id > 2 and id  会把第三条记录锁住，其他会话对则无法对第三条记录做操作。

2. 无索引锁会升级为表锁，锁主要是加在索引上，如果对非索引字段更新，行锁可能会变变锁。

客户端A执行： update user set balance = 800 where name = 'zhangsan'; 

客户端B对该表任一行执行修改、删除操作都会阻塞

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

3. 또한 local in share mode(共享锁) 和 for update(排它锁) ，例如： select * from test_innodb_lock where a = 2 for update;를 사용하여 다른 세션이 이 데이터 행만 읽을 수 있도록 특정 행을 잠글 수 있으며, 해당 행을 잠근 세션이 제출될 때까지 수정이 차단됩니다.

위 내용은 MySQL 잠금 및 트랜잭션 격리 수준을 알고 계십니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!