MySQL事务及隔离级别详解
2024-10-13 13:46:02
MySQL事务及隔离级别详解
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.MySQL的基本架构
MySQL的基本架构可以分为三块，即连接池，核心功能层，存储引擎层。
1>.连接池 （线程处理工具）
主要功能是与用户请求建立连接。第一层向用户响应请求的数据，这个层次不是MySQL服务独有的，其实大多数C/S架构的工作软件基本上都是这种工作模式，只不过在处理连接池的内存，进程的释放等管理关系上mysql可能有着自己独特的方式。
我们知道MySQL是单进程多线程的模型，MySQL的线程有很多种类型，比方说：负责和用户建立连接的叫做连接线程，把缓存中的数据同步到磁盘中去的叫做刷新线程，甚至于不同的存储引擎还维持着多个后台线程等等。那么一个MySQL服务器和客户端同时能建立多少个连接呢？mysql管理连接是通过线程池来实现的，其实很多基于线程机制的连接管理器都基于线程池（thread pool）来管理的。一个线程池所拥有的线程数是有限的，所以我们MySQL在启动的时候会事先创建一个线程池，比如在创建了100个线程，那么来一个客户端连接就给它分配一个线程，当连接的总数到达150（超过提前创建的100个线程数量了）时，MySQL会先响应前100个线程，而将后面的50个请求放在一个队列中，然他们在这个队列中进行排队，一旦前面的100个线程中的任意一个用户退出了，就会空出来一个线程，这个空出来的线程不会被销毁，而是把这个线程给它清理一下，还原到最初的样子，再去响应在队列中排队的50个线程的任何一个。所以用户的连接线程在线程池的管理模式中是不会被销毁的，一般会实现线程重用，从而避免了线程的重建和释放。
MySQL的客户端和服务器进行连接是基于msyql协议的，MySQL支持文本协议和二进制的协议。只不过二进制的协议高效一些。不管是基于文本还是二进制协议mysql的发送数据默认为明文（也就是我们用一个解码器就可以将数据直接读取出来，尽管你用了二进制编码或是其他文本格式编码），可以使用ssl加密数据。
2>.核心功能层
主要功能是查询解析，分析，优化，缓存，内置函数（BIF），触发器，视图等跨存储引擎的功能，你也可以说MySQL的主要功能都在这一层提供。第二次我们也可以叫它为MySQL的核心服务层。
3>.存储引擎层
主要功能是对数据的存入和提取。我们知道MySQL是插件式存储引擎，而存储引擎又是表级别的概念，我们创建任何一张表的时候我们都可以指定它的存储引擎，在同一个库中的多个表可以使用不同的存储引擎，所以我们说存储引擎对MySQL来讲是一个非常灵活的机制。
二.MySQL锁
MySQL的并发访问控制是基于锁来实现。
1>.执行操作时施加的锁的模式有以下两种：
读锁：
特点是用户在读的时候施加的锁，为防止别人修改，但是用户可以读，即可以多个用户可以同时对一张表进行读取操作但不能修改数据该表的内容，因此读锁还被称为共享锁。在做数据备份时要建议手动施加读锁。
写锁：
特点是当对一个表做修改操作时，其他用户不能对这张表进行读同时也不能写哟。因此写锁也被称为独占锁或排它锁。
2>.施加的锁的粒度（简称锁粒度）有以下两种：
表锁（table lock）：
锁定了整张表。 表锁是关系型数据库中最基本的锁策略，也是开销最小的锁策略（因为请求锁，施加锁，释放锁，管理锁都是急需要占用资源的）。
行锁（row lock）：
只锁定了表中需要的行，不一定是一行哟。
表锁有个缺点，就是当用户对一张表进行修改操作时会锁定整张表，如果表中的数据有1万行，而当前用户只是需要修改其中的几行内容，这会导致其他用户想要查询前用户不需要修改的行是被拒绝，需要等待该用户操作完成结束方能进行查询操作。因此我们总结为：“ 粒度越小，开销越大，但并发性越好， 粒度越大，开销越小，但并非性越差。”在实际生产环境中，数据库的锁策略至关重要。
3>. 根据锁的实现位置分为以下两种：
MySQL锁：可以手动使用，可以使用显示锁
存储引擎锁：自动进行的（隐式锁），
a>.显示锁：
lock tables：施加锁
LOCK TABLES：解锁
tbl_name lock_type
[, tbl_name lock_type] ...
READ | WRITE 锁的类型
unlock tables：解锁
b>.InnoDB存储引擎也支持另外一种显示锁（锁定挑选出的部分行,行级锁）
select .... lock in share mode
select .... for update
mysql> select database();
+-------------+
| database() |
+-------------+
| yinzhengjie |
+-------------+
row in set (0.00 sec) mysql>
mysql> show tables;
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| Classes |
| score |
| students |
| tearchers |
+-----------------------+
rows in set (0.00 sec) mysql>
mysql>
mysql>
mysql> desc Classes;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ClassID | tinyint() | NO | | NULL | |
| Class | varchar() | NO | | NULL | |
| ClassMember | tinyint() | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec) mysql>
mysql> lock tables Classes read; #将Classes 设置读锁
Query OK, rows affected (0.00 sec) mysql> select * from Classes; #发现即使配置给Classes表配置了读锁，当是我们可以进行读取操作。
+---------+--------------------------+-------------+
| ClassID | Class | ClassMember |
+---------+--------------------------+-------------+
| | 木叶忍者村第七班 | |
| | 木叶忍者村第十班 | |
| | 木叶忍者村第八班 | |
| | 木叶忍者村第三班 | |
+---------+--------------------------+-------------+
rows in set (0.01 sec) mysql>
mysql> insert into Classes value (,'木叶忍者村第一班',); #事假读锁之后发现无法向Classes表中插入数据
ERROR (HY000): Table 'Classes' was locked with a READ lock and can't be updated
mysql>
mysql> unlock tables; #现在我们进行一下解锁
Query OK, rows affected (0.00 sec) mysql> insert into Classes value (,'木叶忍者村第一班',); #解锁成功后，我们就可以可以插入数据啦。
Query OK, row affected (0.03 sec) mysql>
读锁案例展示
mysql> select database();
+-------------+
| database() |
+-------------+
| yinzhengjie |
+-------------+
row in set (0.00 sec) mysql> .
mysql> lock tables Classes write; #对Classes 添加写锁
Query OK, rows affected (0.00 sec) mysql>
mysql> select * from Classes; #我们打开另外的一个中断，进程查询操作，发现一只没有数据返回，被阻塞了，需要等待Classes 这个表解除写锁（unlock tables;）才会返回数据哟
写锁案例展示
mysql> show table status like 'Classes'\G
*************************** . row ***************************
Name: Classes
Engine: InnoDB #注意，设置行所的话该表的存储引擎必须是InnoDB哟！如果你的存储引擎是MyISAM的话，生产环境中不建议你用alter命令修改存储属性，因为生产环境中的表特别大，修改的时间可能会等待很差时间的！
Version:
Row_format: Compact
Rows:
Avg_row_length:
Data_length:
Max_data_length:
Index_length:
Data_free:
Auto_increment: NULL
Create_time: -- ::
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
row in set (0.01 sec) mysql> select * from Classes where ClassMember = lock in share mode; #设置读锁
+---------+--------------------------+-------------+
| ClassID | Class | ClassMember |
+---------+--------------------------+-------------+
| | 木叶忍者村第十班 | |
| | 木叶忍者村第八班 | |
| | 木叶忍者村第三班 | |
+---------+--------------------------+-------------+
rows in set (0.00 sec) mysql> select * from Classes where ClassMember = for update; #设置写锁
+---------+--------------------------+-------------+
| ClassID | Class | ClassMember |
+---------+--------------------------+-------------+
| | 木叶忍者村第十班 | |
| | 木叶忍者村第八班 | |
| | 木叶忍者村第三班 | |
+---------+--------------------------+-------------+
rows in set (0.01 sec) mysql>
mysql> select * from Classes ; #我们发现可以查询到Classes ，那是因为Classes 这张表数据两太小，而设计行所则是当select语句执行完毕的时候默认就会释放掉行锁哟，因此对于比较大的文件你会看到效果。
+---------+--------------------------+-------------+
| ClassID | Class | ClassMember |
+---------+--------------------------+-------------+
| | 木叶忍者村第七班 | |
| | 木叶忍者村第十班 | |
| | 木叶忍者村第八班 | |
| | 木叶忍者村第三班 | |
| | 木叶忍者村第一班 | |
+---------+--------------------------+-------------+
rows in set (0.00 sec) mysql>
设置行所案例展示
三.MySQL事务（Transaction）
事务就是一组原子性的查询语句，也就是说事物跟数据库创建或是表的创建是无关的哟，它是将多个查询当作一个独立的工作单元，典型的案例就是银行转账操作，当任何一条条件不满足是事物就会回退之前的操作。注意InnoDB是支持事物的而MyISAM是不支持事物的，所以不要妄图在MyISAM的存储引擎表上使用事物哟！
1>.ACID测试
事物是支持ACID测试的，也就是说能够满足ACID测试就表示其支持事务，或兼容事务。其中ACID满足以下四点：
A：Atomicity，原子性，都执行或者都不执行
C：Consistency，一致性，从一个一致性状态转到另外一个一致性状态
I：Isolaction，隔离性。一个事务的所有修改操作在提交前对其他事务时不可见的
D: Durability, 持久性，一旦事务得到提交，其所做的修改会永久有效
2>.隔离级别
a>. READ UNCOMMITTED（读未提交）
可以读取到别人没有提交的事物，又名“脏读”，因此这种方式数据安全性最差，但好处就是并发性最强。不能重复读（两次读取同一张表的内容可能不一致，也就是说，当不同的用户读取同一张表时，当某DBA对这张进行的删除表中的数据时，其他用户也能读取到删除的行即使别人还没有提交事物呢，而DBA回滚操作是，其他用户任然也可以看到回滚的内容，因此两次读取的内容是不一致的。），甚至会产生幻读（我们读取到的数据和真实数据不一致），因此这种模式用的相对较少。
b>.READ COMMITTED（读提交）
读提交指的是一个事物开始的时候只能看见已经提交事物的修改，其他未修改的事物这里通通都看不到，我们也可以说提交之后才能读，市面上常见的大多数关系型数据库隔离级别都是读提交，不过MySQL并不是。该模式存在幻读现象。对事物要求不特别严格的场景下，可以使用读提交。
c>.REPEATABLE READ (可重读)
可重读解决了脏读的问题，在最近的一个事物提交之前， 所有读到的事物都是一样的。因此，在同一事物中多次读同一数据的内容是一样的。但是仍然没有解决幻读的问题。mysql默认就是用的这种模式。该模式存在幻读现象。
d>.SERIALIZABLE（可串行化）
事物和事物之间严格进行隔离，只要涉及到同一个数据彼此之间是不能做任何交叠的，必须要等到其他人提交了或是把数据修改完成之后我们才能读取，通过强制事务的串行执行避免了幻读，但是性能极低。它的隔离界别极高，只有确保数据非常明确的情况下才能用到SERIALIZABLE。
e>,跟事物相关的常用命令
start transaction #启动事务
commit #事务提交
rollback #事务回顾
savepoint #控制回滚的位置
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
f>.自动提交事物
如果没有显式启动事务，每个语句都会当作一个默认的事务，其执行完成会被自动提交。如果我们关闭了自动提交，那么我们就必须手动启动事物，然后手动进行提交。如果没有提交就会导致数据丢失哟！
g>.MVCC：多版本并发控制
每个事务启动时，InnoDB会为每个启动的事务提供一个当下时刻的快照。为实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间(里面存储的系统版本号[system version number])
MVCC只在两个隔离级别下有效,即：read committed（读提交）和repeatable read（可重读）
mysql> show table status where Name='students'\G
*************************** . row ***************************
Name: students
Engine: InnoDB
Version:
Row_format: Compact
Rows:
Avg_row_length:
Data_length:
Max_data_length:
Index_length:
Data_free:
Auto_increment:
Create_time: -- ::
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
row in set (0.00 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 山中井野 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql> start transaction;
Query OK, rows affected (0.00 sec) mysql> delete from students where StudentID=;
Query OK, row affected (0.00 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql> rollback; #在没有提交事物之前，是可以回滚你的所有操作哟。
Query OK, rows affected (0.00 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 山中井野 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql>
事物回滚案例展示
mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 山中井野 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql> start transaction;
Query OK, rows affected (0.00 sec) mysql> delete from students where StudentID=;
Query OK, row affected (0.00 sec) mysql> commit; #注意，这里是提交了之后，这个事物就结束了。回滚也无法回滚啦！
Query OK, rows affected (0.16 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql>
mysql> rollback; #你会很明显的发现提交事物之后回滚也不生效啦,因为你没有启动新的事物啊。
Query OK, rows affected (0.00 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql>
提交事物案例展示
mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 佐助 | | boy | | |
| | 春野樱 | | girl | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql> start transaction; #启动一个事物
Query OK, rows affected (0.00 sec) mysql> delete from students where StudentID=;
Query OK, row affected (0.00 sec) mysql> savepoint a; #当我们删除了一行数据之后，我们对其操作进行一个记录。
Query OK, rows affected (0.00 sec) mysql> delete from students where StudentID=;
Query OK, row affected (0.00 sec) mysql>
mysql> savepoint b;
Query OK, rows affected (0.00 sec) mysql> delete from students where StudentID=;
Query OK, row affected (0.00 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql> rollback to b; #表示回滚到b的节点中去，也就是说b以后的操作都会恢复回来。
Query OK, rows affected (0.00 sec) mysql> select * from students;
+-----------+--------------+-----+--------+---------+-----------+
| StudentID | Name | Age | Gender | ClassID | TeacherID |
+-----------+--------------+-----+--------+---------+-----------+
| | 旋涡鸣人 | | boy | | |
| | 奈良鹿丸 | | boy | | |
| | 日向雏田 | | girl | | |
| | 犬冢牙 | | boy | | |
| | 油女志乃 | | boy | | |
| | 秋道丁次 | | boy | | |
| | 日向宁次 | | boy | | |
| | 李洛克 | | boy | | |
| | 天天 | | boy | | |
+-----------+--------------+-----+--------+---------+-----------+
rows in set (0.00 sec) mysql>
指定回滚位置案例展示
mysql> show global variables like 'autocommit'; #我们可以进行查看，发现它是开启的状态。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec) mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| |
+----------------------+
row in set (0.00 sec) mysql> set session autocommit = ; #我们关闭自动提交的功能。
Query OK, rows affected (0.00 sec) mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| |
+----------------------+
row in set (0.00 sec) mysql> show global variables like 'autocommit'; #再次查看，发现它是被关闭了。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
row in set (0.00 sec) mysql>
关闭自动提交事物案例展示
mysql> show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec) mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
row in set (0.00 sec) mysql>
查看MySQL的事物隔离级别
最新文章
- Java中2+2==5解读
- Fitbit Flex 智能手环佩戴心得 主要说说过敏
- ls -F一种非常有用的ls格式
- 最大联通子数组之和（dfs，记忆化搜索，状态压缩）
- Junit3
- FastLoad错误 — RDBMS error 2634
- Python 3 加密简介
- SQLite本地事务处理
- iozone文件系统测试 与EXCEL 制图
- Linux的目录结构及其作用
- MCS-51系统中断优先级的软扩展
- 名叫Florida的女孩问题
- 关于glibc中的res_init()函数
- DAY24、面向对象
- Redis 常用操作命令，非常详细！
- SpringBoot集成Swagger接口管理工具
- [20171120]11G关闭直接路径读.txt
- 【Python】脚本运行报错：IndentationError: unindent does not match any outer indentation level
- BZOJ2301：莫比乌斯反演+二维容斥解决GCD范围计数
- Android 开发工具类 02_DensityUtils