快速掌握MySQL锁、事务隔离级别

一、锁

1、定义

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外， 数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性 是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个 重要因素。

2、分类

• 性能区分: 乐观锁、悲观锁;
• 数据库操作: 读锁、写锁 （都是悲观锁）;
  • 共享锁(读锁)： 同一条数据，多个select可以同时进行而不会互相影响 ；
  • 排它锁(写锁)：一个操作没有commit或rollback前，他会阻断其他的读锁和写锁；
• 数据操作: 表锁、行锁；

二、表锁

1、定义

​ 每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲 突的概率最高，并发度最低；

2、演示

2.1、创建表、新增数据

‐‐建表SQL
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
  `salary` int DEFAULT NULL COMMENT '工资金额',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户账户';
-- 插入数据
INSERT INTO `user` VALUES ('1', 'super', '4900');
INSERT INTO `user` VALUES ('2', 'admin', '6000');
INSERT INTO `user` VALUES ('3', 'manage', '7000');

图示:

2.2 表加锁、查看锁、释放锁

• 加锁：

  • lock table 表名 read;
    
    备注：

    1. 当前session和其他的session 都可以select这张表的数据;
    2. 当前session在执行INSERT、UPDATE就会报错。其他session执行INSERT、UPDATE就会等待，如果等待超时时间内锁释放了，sql执行成功如果没有就会超时失败；

  • lock table 表名 write；
    

    备注：

    1. 当前session执行SELECT、INSERT、UPDATE、DELETE都是没有问题的，其他session执行就会阻塞等待；

• 查看

  • show open tables；
    

• 释放

  • unlock tables；
    

2.3、小总结

​ MyISAM在执行SELECT前,会自动给涉及的所有表加读锁,在执行增删改 操作前,会自动给涉及的表加写锁。

• 对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但是会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
• 对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有 当写锁释放后,才会执行其它进程的读写操作

重要： 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

三、行锁

1、定义

​ 每一个操作锁住一行数据开销大，加锁慢；就会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。 这就是为什么高并发项目表的存储引擎一般选用INNODB;

2、事务的特性

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

• 原子性( Atomicity )：SQL语句要么全部执行，要么全都不执行。没有一半一半的事。
• 一致性( Consistent )：在事务开始和完成时，数据都必须保持一致状态，意味着所有相关数据规则都必须应用于事务的修改，以保持数据的完整性。事务结束时所有的内部数据结构也都必须是正确的。
• 隔离性( Isolation )：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
• 持久性 ( Durable )：事务完成后，对数据的变更应该是永久性的，即使系统出现故障，也要保持事务完成后的变更数据。

3、事务的隔离级别

3.1 、并发操作的问题

• 脏读( Dirty Reads )：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读
• 不可重复读( Non-Repeatable Reads )：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。
• 幻读( Phantom Reads )：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。

3.2、隔离级别

隔离级别	脏读	不可重复度	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

备注：隔离级别越严格，并发越小。

3.3、图文演示

##设置隔离级别:
mysql> set session transaction isolation level read uncommitted;
## 查询隔离级别:
mysql> select @@transaction_isolation;

4、可重复读( repeatable-read )

4.1、设置隔离级别

4.2、查询、更新

4.2.1、执行顺序

1. 左边窗口开启事务；
2. 执行 select * from user;
3. 右边窗口开启事务，执行步骤2；
4. 右边窗口执行update操作，执行步骤2；
5. 右边窗口提交事务commit；
6. 左边窗口步骤2，两次查询的结果是一样的，没有出现不可重复读的问题；
   

​ 7. 左边窗口接着执行update user set salary = salary - 100 where id = 1， salary没有变成5000-100=4900；
8. super的salary值用的是步骤7中的4900来算的，所以是4800，数据的一致性倒是没有被破坏。可重复读的 隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作 不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本 号，是当前读（当前版本）。

5、读已提交( read-committed)

5.1、设置隔离级别

5.2、查询、更新

1. 两个窗口分别开始session；
2. 都想执行select * from user;
3. 右窗口先执行UPDATE更新语句后，执行步骤2的操作；
4. 左窗口执行步骤2的操作。这时候右边窗口的没有执行commit操作，所以左边窗口无法查询到右边窗口已经更新的数据，到这我们就解决了脏读的问题
   

5.2.1 、提交事务后

​ 5. 上图右边窗口提交update事务后，左边窗口在执行与上一步相同的查询，结果 与上一步不一致，即产生了不 可重复读的问题 ；

6、读未提交(read-uncommitted)

6.1、设置隔离级别

6.2、查询、更新

1. 左边窗口开启事务，执行select查询；
2. 右边窗口开启事务，执行select查询；
3. 右边窗口执行update操作，执行select查询；
4. 右边窗口，左边窗口执行select查询，左边窗口可以查询到右边窗口未提交事务的update操作；
   
5. 右边窗口执行rollback操作，此时左边窗口再次执行查询操作就会出现脏数据
   
6. 左边窗口在执行 update user set salary = salary - 100 where id = 1; 按照显示是4600-100=4500。但是结果真是这样吗？ 你要是这样理解就真的太天真 了，在应用程序中，我们会用4700-100=4600，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别；
   

7、串行化

7.1、设置隔离级别![在这里插入图片描述](https://img-blog.csdnimg.cn/20201230171008291.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDgxMDUy,size_16,color_FFFFFF,t_70

7.2、查询、更新

1. 左边窗口开启事务，执行select操作；
2. 右边窗口开启事务，执行insert操作；( 表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此 不会出现幻读的情况，这种隔离级别并发性极低，实际上在开发中基本不会用到)；
   

下面篇我们介绍一下mysql的mvcc，间隙锁