MySQL事务隔离级别锁机制分析论证

2024-01-21 01:32

本文主要是介绍MySQL事务隔离级别锁机制分析论证,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

基本概念

1、事务隔离级别说明
ANSI/ISO SQL标准定义了4中事务隔离级别:未提交读(read uncommitted),提交读(read committed),重复读(repeatable read),串行读(serializable)。
对于不同的事务,采用不同的隔离级别分别有不同的结果。不同的隔离级别有不同的现象。主要有下面3种现在:
a、脏读(dirty read):一个事务可以读取另一个尚未提交事务的修改数据。
b、不可重复读(nonrepeatable read):在同一个事务中,同一个查询在T1时间读取某一行,在T2时间重新读取这一行时候,这一行的数据已经发生修改,可能被更新了(update),也可能被删除了(delete)。
b、幻像读(phantom read):在同一事务中,同一查询多次进行时候,由于其他插入操作(insert)的事务提交,导致每次返回不同的结果集。
不同的隔离级别有不同的现象,并有不同的锁定/并发机制,隔离级别越高,数据库的并发性就越差,4种事务隔离级别分别表现的现象如下表:
隔离级别 脏读 非重复读 幻像读
read uncommitted 允许 允许 允许
read committed   允许 允许
repeatable read     允许
serializable      

2、锁机制说明:
共享锁:由 表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写
排它锁:由 表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁,也可以理解为 表或行锁定。典型是mysql事务中
start transaction;
select * from user where userId = 1 for update ;
执行完这句以后
1)当其他事务想要获取共享锁,比如事务隔离级别为SERIALIZABLE的事务,执行
select * from user;
将会被挂起,因为SERIALIZABLE的select语句需要获取共享锁
  2)当其他事务执行
select * from user where userId = 1 for update;
update user set userAge = 100 where userId = 1; 
也会被挂起,因为for update会获取这一行数据的排它锁,需要等到前一个事务释放该排它锁才可以继续进行
特别注意:如果for update 前的where 并非主键过滤则为表级排它锁,仅有过滤参数为 =ID主键时方为行级排它锁) 

3、锁的范围:
行锁: 对某行记录加上锁
表锁: 对整个表加上锁
这样组合起来就有,行级共享锁,表级共享锁,行级排他锁,表级排他锁

4、悲观锁与悲观锁
悲观锁:显式为数据加锁,常见有如下两种加锁方式 
    1. 显式指定独占锁:select … for update
    2. 在数据库增加表明状态的LOCK字段
乐观锁:通过版本控制实现, 这种方式我们能够更好地提高并发事务的性能。

验证REPEATABLE-READ的实例效果

使用InnoDB,开启两个客户端SESSION1、SESSION2
1、通过SELECT @@GLOBAL.tx_isolation, @@tx_isolation;查看默认的事务隔离级别:

如果我们修改当前session修改其事务隔离级别,可以通过如下方式:


2、REPEATABLE-READ隔离级别验证:
 SESSION1SESSION2
T1mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
T2mysql> use icbc;
Database changed
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 222.00 | card001 |
+--------+----------+
1 row in set (0.11 sec)
mysql> use icbc;
Database changed
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 222.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
T3mysql> update t_eventlog set amount=111 where fromcard='card001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
T4mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 111.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】session1能够读取自己修改的最新数据
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 222.00 | card001 |
+--------+----------+
1 row in set (0.12 sec)
【说明】amount不变,session1中修改数据事务未提交,并未出现脏读
T5 mysql> update t_eventlog set amount=333 where fromcard='card001';
【说明】session2修改直接被挂起,因为session1事务未提交
T6mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_eventlog set amount=333 where fromcard='card001';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
【说明】session1提交事务,session2本应提交修改,但超时需要重启事务,下面加快速度重复上述T2~T6操作
T2mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 111.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 111.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
T3mysql> update t_eventlog set amount=1 where fromcard='card001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
T4mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 1.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】session1能够读取自己修改的最新数据
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 111.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】amount不变,session1中修改数据事务未提交,并未出现脏读
T5 mysql> update t_eventlog set amount=2 where fromcard='card001';
【说明】session2修改直接被挂起,因为session1事务未提交
T6mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)
【说明】提交session1事务
 
T7 Query OK, 1 row affected (4.79 sec)
Rows matched: 1 Changed: 1 Warnings: 0
【说明】session1事务提交,session2执行修改数据
T8mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 1.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】amount不变,session2中修改数据事务未提交,并未出现脏读
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
T9 mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)
T10mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】读取到最新的session2提交事务数据
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
T11mysql> show variables like 'autocommit'\G
*************************** 1. row ***************************
Variable_name: autocommit
        Value: ON
1 row in set, 1 warning (0.00 sec)
【说明】在session1中T8、T10两次的数据不一致,是否违背了重复读呢,其实不然,由于我们设置的事务提交是自动。
mysql> show variables like 'autocommit'\G
*************************** 1. row ***************************
Variable_name: autocommit
        Value: ON
1 row in set, 1 warning (0.00 sec)
A以上已经证明了不会出现脏读,只有在更新事务提交后方可被其他事务读取修改后数据;如果两个事务同时对某一数据行进行更新操作,如A事务先更新,则B事务被挂起,待A更新操作事务提交后,B事务执行更新操作并等待事务提交(更新操作可能出现等待超时),提交后最终数据为B事务提交数据。其实是加上了行共享锁,此时数据行能够被多个事务读取却不能被写。
T12mysql> set @@autocommit = 0
    -> ;
Query OK, 0 rows affected (0.00 sec)
【说明】设置事务提交手动
mysql> set @@autocommit = 0
    -> ;
Query OK, 0 rows affected (0.00 sec)
【说明】设置事务提交手动
T13mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
T14mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
T15 mysql> update t_eventlog set amount=3 where fromcard='card001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
T16mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
 
T17 mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 3.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】提交事务后查询为最新提交的事务数据
T18mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 2.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】数据依然没有变化,目前为手动提交事务,查询也需要手动提交。此时说明在一个事务内重复读,数据是一致的。
 
T19mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT amount,fromcard FROM t_eventlog;
+--------+----------+
| amount | fromcard |
+--------+----------+
| 3.00 | card001 |
+--------+----------+
1 row in set (0.00 sec)
【说明】session1提交查看事务后重新查询,可以看到session2提交的最新的数据
 
B通过手动提交事务验证了,REPEATABLE-READ级别隔离下,在一个事务内,我们多次查询结果是一样的,能够重复读。
以上的验证过程主要是针对共享锁实现。

综上
1、我们在REPEATABLE-READ级别隔离下,如果多个事务同时操作一个数据行,则后执行更新操作的事务将被挂起,直到第一个更新事务提交后,后执行事务将继续执行(可能出现挂起直到超时)其更新动作,待最后执行事务也提交后,数据库将保存最后一个事务的更新结果。此过程中,事务间互不干扰,只有提交事务后的数据方可被其他事务读取(避免了脏读),并且在同一事务下,多次读取的数据将保持一致,实现重复读;
2、mysql有一个autocommit参数,默认是on,他的作用是每一条单独的查询都是一个事务,并且自动开始,自动提交(执行完以后就自动结束了,如果要适用select for update,而不手动调用 start transaction,这个for update的行锁机制等于没用,因为行锁在自动提交后就释放了),所以事务隔离级别和锁机制即使不显式调用start transaction,这种机制在单独的一条查询语句中也是适用的;
3、其实无乱是哪个级别的隔离,甚至最低级别在一个事务(A)更新修改后,其他事务在A事务提交前均被挂起,仅A事务提交后方可进行数据行更新处理(可能超时),其均是由于A事务对其添加了 行共享锁
4、这里虽然没有继续验证,但如果采用SERIALIZABLE级别的隔离,则其查询|更新任意操作后均会对其添加 表共享锁,从而其他事务 仅可以读操作,不能执行任何写操作

这篇关于MySQL事务隔离级别锁机制分析论证的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/627976

相关文章

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型(范围、哈希、列表、键值),主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区(Range Partitioning1、新建分区表:2、分

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后,有时候会需要自动创建分区。比如,一些表数据量非常大,有些数据是热点数据,按照日期分区MululbU

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一:桌面图标进入方法二:运行窗口进入检查版本号对照表php方法三:查找文件路径方法四:检查 S

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》:本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法: 示例:2.TRUNCATE语句✅ 基本语

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一:使用GROUP BY和HAVING子句方法二:仅返回重复值方法三:返回完整记录方法四:

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》:本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满

Spring事务传播机制最佳实践

《Spring事务传播机制最佳实践》Spring的事务传播机制为我们提供了优雅的解决方案,本文将带您深入理解这一机制,掌握不同场景下的最佳实践,感兴趣的朋友一起看看吧... 目录1. 什么是事务传播行为2. Spring支持的七种事务传播行为2.1 REQUIRED(默认)2.2 SUPPORTS2

怎样通过分析GC日志来定位Java进程的内存问题

《怎样通过分析GC日志来定位Java进程的内存问题》:本文主要介绍怎样通过分析GC日志来定位Java进程的内存问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、GC 日志基础配置1. 启用详细 GC 日志2. 不同收集器的日志格式二、关键指标与分析维度1.

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案(推荐)2. 模糊匹配方案参数化查询示例使用场景建议性能优