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

2024-01-21 01:32
文章标签 分析 mysql 机制 级别 事务 隔离 database 论证

本文主要是介绍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。

相关文章

Linux下MySQL数据库定时备份脚本与Crontab配置教学

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定
阅读更多...
SpringBoot日志级别与日志分组详解

SpringBoot日志级别与日志分组详解

《SpringBoot日志级别与日志分组详解》文章介绍了日志级别（ALL至OFF）及其作用,说明SpringBoot默认日志级别为INFO,可通过application.properties调整全局或... 目录日志级别1、级别内容2、调整日志级别调整默认日志级别调整指定类的日志级别项目开发过程中，利用日志
阅读更多...
Spring 中的切面与事务结合使用完整示例

Spring 中的切面与事务结合使用完整示例

《Spring中的切面与事务结合使用完整示例》本文给大家介绍Spring中的切面与事务结合使用完整示例,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录 一、前置知识：Spring AOP 与 事务的关系 事务本质上就是一个“切面”二、核心组件三、完
阅读更多...
MySQL中On duplicate key update的实现示例

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP
阅读更多...
MySQL分库分表的实践示例

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分
阅读更多...
Python与MySQL实现数据库实时同步的详细步骤

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述：数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
MySQL 表空却 ibd 文件过大的问题及解决方法

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景：表空却 “吃满” 磁盘的怪事二、问题复现：一步步编程还原异常场景1. 准备测试源表与数据
阅读更多...
基于Redis自动过期的流处理暂停机制

基于Redis自动过期的流处理暂停机制

《基于Redis自动过期的流处理暂停机制》基于Redis自动过期的流处理暂停机制是一种高效、可靠且易于实现的解决方案,防止延时过大的数据影响实时处理自动恢复处理,以避免积压的数据影响实时性,下面就来详... 目录核心思路代码实现1. 初始化Redis连接和键前缀2. 接收数据时检查暂停状态3. 检测到延时过
阅读更多...
Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2