Mysql默认事务隔离级别及行锁

向右看齐 2022-11-13 04:25 212阅读 0赞

## 隔离级别回顾 ##

前文提到数据库事物的隔离级别，回顾下：

1、READ-UNCOMMITTED -> 读未提交：没有任何保险措施，脏读、不可重复读，幻读，都有可能发生  
2、READ\_COMMITTED -> 读提交：只能读取已提交的数据，不会发生脏读  
3、REPEATABLE\_READ -> 可重复读：不会发生脏读和不可重复读，**可能存在幻读，mysql不存在，Innodb存储引擎通过多版本并发控制解决了幻读的问题。**  
4、SERIALIZABLE -> 串行化：如果串行化执行，一个个排队执行，就不会存在数据混乱不一致的问题，但是代价就是效率低。

mysql innoDB默认隔离级别 -> REPEATABLE\_READ:可重复读  
即当前事务进行中不会读取到其他事务未提交的数据，也不会读取到新提交的数据

## 验证 ##

下面先验证下，验证步骤整理：  
1.开启两个mysql链接A和B(不是两个执行窗口)，链接A和B分别开启一个事务，方式为执行:BEGIN;  
2.链接A中根据ID更新一条数据不执行COMMIT提交操作，链接B中查询该数据，验证是否存在脏读  
3.链接A中根据ID更新一条数据执行COMMIT提交操作，链接B不执行COMMIT时查询该数据，验证是否存在不可重复读  
4.新开链接C中插入一条数据，新开链接D查询该数据，验证是否存在幻读

测试表:  
CREATE TABLE `t_test_batch` (  
`t_id` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT ‘’,  
`code` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘代码’,  
`name` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘名称’,  
PRIMARY KEY (`t_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin COMMENT=‘测试事务隔离级别’;

造一条数据，字段name值为444：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70]

开始进行验证

1.  打开一个新链接A并开启事务，执行对示例数据的修改

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 1]

1.  打开一个新链接B并开启事务，执行对示例数据的查询，并没有读到链接A中事务对数据的修改

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 2]

1.  链接A中执行COMMIT提交事务，再次执行链接B中的查询，返回结果没有变化  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 3]
2.  链接B中执行COMMIT提交事务，再次执行链接B中的查询，返回结果变化，读取到了链接A事务的修改

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 4]  
5.链接D开启事务查询code='AaA3’数据，没有数据。此时链接C中插入code='AaA3’的数据，链接D再次查询，仍然查不到，链接D提交当前事务，再次查询可以查到该数据。此处截图忽略。。。

## 结论 ##

结论：mysql默认的事务隔离级别可以确保事务内不会发生脏读、不可重复读和幻读。事务A修改或新增了数据，事务B只有提交后才能读取到事务A的修改或新增。

## 发散一下 ##

这里就有个疑问了，如果事务A和B同时修改同一条数据，会发生什么呢？继续验证，开启两个事务，同时执行下面的update操作。

链接A  
BEGIN;

UPDATE t\_test\_batch t SET t.`name`=‘0.0.0’ WHERE t.t\_id=‘0009c52726154074a650444a15f910ad’;

链接B  
BEGIN;

UPDATE t\_test\_batch t SET t.`name`=‘11111’ WHERE t.t\_id=‘0009c52726154074a650444a15f910ad’;

结果发现，A先执行没有问题，没有提交的情况下，B执行后卡住了，一直在转圈，貌似锁住了。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 5]

查看当前是否存在锁(information\_schema.INNODB\_LOCKS)，发现了两条记录，显示0009c52726154074a650444a15f910ad这条记录有两个X锁，并且锁的是行。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 6]

## S锁和X锁 ##

Mysql X锁：排他锁-写锁，mysql修改数据会对数据加锁确保数据一致，事务修改数据会自动加锁。事务A对一条数据修改加锁，则事务B、事务C要修改该数据时，在A没有提交事务时，只能尝试对该数据加锁，然后排队陷入等待中。排他锁，排斥不允许其他事务插手。

Mysql S锁：说了排他锁，就有必要提一下共享锁即S锁-读锁。为了确保事务读取数据时，该数据不会被其他事务修改，当前事务可以给数据加上S锁，多个事务可以同时加锁，一层层的S锁。这么做的目的是：我现在只想安静的读取数据，想修改数据的乖乖等我用完再说！  
同时S锁还有个作用，如果有人正在修改这个数据，当前读取该数据时如果尝试加共享锁也会陷入等待中。不加共享锁随便读，加了共享锁可能会等等等！

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 7]  
增删改时会自动加X锁  
查询默认不加锁，可以手动加S锁和X锁  
方式为  
加S锁: select \* from table\_xxx lock in share mode;  
加X锁: select \* from table\_xxx for update;

## 乐观锁与悲观锁 ##

上面表述了mysql的两种锁，这两种锁均为悲观锁，意为不管你信不信，反正我就认为一定会和别人起冲突，我先上锁我先用。

还有一种乐观锁，即不直接加锁，乐观的认为当前没人和我抢占，只有在提交修改时才会通过版本号判断是否存在冲突。当发现当前数据数据库中版本号与当前使用的版本号不一致时，认为数据已被修改，放弃。

悲观锁数据库已实现，乐观锁需要我们自己实现。

## 遗留问题 ##

上述的锁，锁类型lock\_mode可以看到都是record，即行锁。

还有一种锁为表锁。百度上搜到说是insert会锁表，update锁行，或者什么走主键索引锁行，不然锁表，实际测试并不是这样。。

各种尝试暂时没有发现如何出现information\_schema.INNODB\_LOCKS表中lock\_type为非record，想出现表锁的情况，暂时没有成功过~

不过可以手动执行锁表：LOCK TABLES … WRITE/READ  
如 LOCK TABLES t\_test\_batch READ，期间所有事务执行修改新增都会陷入等待。  
使用UNLOCK TABLES解锁，不晓得这个和行锁表锁的概念有什么区别，此处待进一步了解。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70]: /images/20221022/a427c53f2580439ea98a7de8d6fa5ef2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 1]: /images/20221022/42aa3216f042416e991bec7dc522b3d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 2]: /images/20221022/8fd56b4dc38e46beabb810c3055d7cb8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 3]: /images/20221022/44ffb61b714f4099961b6a51ff8369fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 4]: /images/20221022/72dac39769c34eaf8afe3648af92fe66.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 5]: /images/20221022/2fce7076244d4588858db09d2924c983.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 6]: /images/20221022/9587de02952c4dce8c27f95b5f5337e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTgwMjE_size_16_color_FFFFFF_t_70 7]: /images/20221022/054a2ae09611438ab912eb1ac98dd6d2.png