【MySQL】16.MySQL事务管理新（2）

文章目录8. 事务隔离级别8.1 如何理解隔离性18.2 隔离级别8.2.1 脏读8.2.2 不可重复读8.2.3 幻读8.3 查看与设置隔离性8.3.1 查看8.3.2 设置8.3.2.1 设置当前会话隔离性另起一个会话看不多只影响当前会话8.3.2.2 设置全局隔离性另起一个会话会被影响。8.4 读未提交【Read Uncommitted】8.5 读提交【Read Committed】8.6 可重复读【Repeatable Read】8.6.1 update操作8.6.2 insert操作8.7 串行化【serializable】8.8 一致性(Consistency)8.9 推荐阅读8. 事务隔离级别8.1 如何理解隔离性1MySQL服务可能会同时被多个客户端进程(线程)访问访问的方式以事务方式进行一个事务可能由多条SQL构成也就意味着任何一个事务都有执行前执行中执行后的阶段。而所谓的原子性其实就是让用户层要么看到执行前要么看到执行后。执行中出现问题可以随时回滚。所以单个事务对用户表现出来的特性就是原子性。但毕竟所有事务都要有个执行过程那么在多个事务各自执行多个SQL的时候就还是有可能会出现互相影响的情况。比如多个事务同时访问同一张表甚至同一行数据。就如同你妈妈给你说你要么别学要学就学到最好。至于你怎么学中间有什么困难你妈妈不关心。那么你的学习对你妈妈来讲就是原子的。那么你学习过程中很容易受别人干扰此时就需要将你的学习隔离开保证你的学习环境是健康的。数据库中为了保证事务执行过程中尽量不受干扰就有了一个重要特征隔离性数据库中允许事务受不同程度的干扰就有了一种重要特征隔离级别8.2 隔离级别读未提交【Read Uncommitted】 在该隔离级别所有的事务都可以看到其他事务没有提交的执行结果。实际生产中不可能使用这种隔离级别的但是相当于没有任何隔离性也会有很多并发问题如脏读幻读不可重复读等我们上面为了做实验方便用的就是这个隔离性。读提交【Read Committed】 该隔离级别是大多数数据库的默认的隔离级别不是MySQL默认的。它满足了隔离的简单定义一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读即一个事务执行时如果多次select 可能得到不同的结果。可重复读【Repeatable Read】 这是MySQL默认的隔离级别它确保同一个事务在执行中多次读取操作数据时会看到同样的数据行。但是会有幻读问题。串行化【Serializable】这是事务的最高隔离级别它通过强制事务排序使之不可能相互冲突从而解决了幻读的问题。它在每个读的数据行上面加上共享锁。但是可能会导致超时和锁竞争这种隔离级别太极端实际生产基本不使用隔离级别如何实现隔离基本都是通过锁实现的不同的隔离级别锁的使用是不同的。常见有表锁行锁读锁写锁间隙锁(GAP)Next-Key锁(GAP行锁)等。不过我们目前现有这个认识就行先关注上层使用。假设有两个账户张三100元和李四10000元。现在有两个事务T1 和 T2同时进行T1 (事务1):张三给李四转 50 元。T2 (事务2):查询张三和李四的总金额。8.2.1 脏读定义读取到了另一个事务尚未提交的数据。T1开始执行UPDATE account SET balance balance - 50 WHERE name 张三;。此时张三的余额变为50元。T2在此时查询张三的余额读到了50元。关键点T1发现操作有误执行ROLLBACK;撤销了转账。张三的余额又变回了100元。T2继续后续操作它基于一个“不干净”的、最终被撤销的数据50元进行了计算导致结果错误。8.2.2 不可重复读定义在一个事务内多次读取同一行数据结果不一致。T2开始第一次查询张三的余额结果是100元。在T2还没结束的时候T1执行了转账UPDATE ... WHERE name 张三;将张三的余额改为50元并COMMIT;。T2再次查询张三的余额结果变成了50元。8.2.3 幻读定义在一个事务内多次查询一个范围的数据结果集的行数不一致。T2开始第一次查询SELECT * FROM account WHERE balance 90;结果是张三(100) 和 李四(10000)共2行。在T2还没结束的时候T1执行了INSERT INTO account VALUES (3, 王五, 95);并COMMIT;。T2再次执行同样的查询SELECT * FROM account WHERE balance 90;结果变成了张三(100)、李四(10000)、王五(95)共3行。8.3 查看与设置隔离性8.3.1 查看-- 查看 mysql SELECT global.transaction_isolation; -- 查看全局隔级别 -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; -- 查看会话(当前)全局隔级别 --------------------------------- | session.transaction_isolation | --------------------------------- | READ-UNCOMMITTED | --------------------------------- 1 row in set (0.00 sec) mysql SELECT transaction_isolation; -- 查看当前会话的隔离级别和SELECT session.transaction_isolation;作用差不多 ------------------------- | transaction_isolation | ------------------------- | READ-UNCOMMITTED | ------------------------- 1 row in set (0.00 sec)8.3.2 设置-- 设置 -- 设置当前会话 or 全局隔离级别语法 SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}8.3.2.1 设置当前会话隔离性另起一个会话看不多只影响当前会话左边终端mysql SELECT global.transaction_isolation; -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; --------------------------------- | session.transaction_isolation | --------------------------------- | READ-UNCOMMITTED | --------------------------------- 1 row in set (0.00 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | READ-UNCOMMITTED | ------------------------- 1 row in set (0.00 sec) mysql set session transaction isolation level serializable; -- 串行化 Query OK, 0 rows affected (0.00 sec) mysql SELECT global.transaction_isolation; -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; --------------------------------- | session.transaction_isolation | --------------------------------- | SERIALIZABLE | --------------------------------- 1 row in set (0.00 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | SERIALIZABLE | ------------------------- 1 row in set (0.00 sec) mysql右边终端mysql SELECT global.transaction_isolation; -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; --------------------------------- | session.transaction_isolation | --------------------------------- | READ-UNCOMMITTED | --------------------------------- 1 row in set (0.00 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | READ-UNCOMMITTED | ------------------------- 1 row in set (0.00 sec) mysql8.3.2.2 设置全局隔离性另起一个会话会被影响。左边终端mysql SELECT global.transaction_isolation; -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; --------------------------------- | session.transaction_isolation | --------------------------------- | SERIALIZABLE | --------------------------------- 1 row in set (0.00 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | SERIALIZABLE | ------------------------- 1 row in set (0.00 sec) mysql set global transaction isolation level READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) mysql SELECT global.transaction_isolation; -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; --------------------------------- | session.transaction_isolation | --------------------------------- | SERIALIZABLE | --------------------------------- 1 row in set (0.01 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | SERIALIZABLE | ------------------------- 1 row in set (0.00 sec) mysql -- 注意如果没有现象关闭mysql客户端重新连接。右边终端mysql SELECT global.transaction_isolation; -------------------------------- | global.transaction_isolation | -------------------------------- | READ-UNCOMMITTED | -------------------------------- 1 row in set (0.00 sec) mysql SELECT session.transaction_isolation; --------------------------------- | session.transaction_isolation | --------------------------------- | READ-UNCOMMITTED | --------------------------------- 1 row in set (0.00 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | READ-UNCOMMITTED | ------------------------- 1 row in set (0.00 sec) mysql8.4 读未提交【Read Uncommitted】几乎没有加锁虽然效率高但是问题太多严重不建议采用左边终端-- 设置隔离级别为 读未提交 mysql set global transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) -- 重启客户端 mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | READ-UNCOMMITTED | ------------------------- 1 row in set (0.00 sec) mysql select * from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql begin; Query OK, 0 rows affected (0.00 sec) mysql update account set blance123.0 where id1; -- 更新指定行 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql -- 没有commit哦右边终端mysql begin; Query OK, 0 rows affected (0.00 sec) mysql select *from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 123.00 | -- 读到终端A更新但是未commit的数据[insertdelete同样] | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql -- 一个事务在执行中读到另一个执行中事务的更新(或其他操作)但是未commit的数据这种现象叫做脏读(dirty read)8.5 读提交【Read Committed】需要注意更改读提交后两个终端都要重新打开一下避免使用旧的隔离性。左边终端-- 终端A mysql set global transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) -- 重启客户端 mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | READ-COMMITTED | ------------------------- 1 row in set (0.00 sec) mysql begin; -- 手动开启事务同步的开始终端B事务 Query OK, 0 rows affected (0.00 sec) mysql update account set blance123.0 where id1; -- 更新张三数据 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql commit; -- commit提交 Query OK, 0 rows affected (0.01 sec)右边终端mysql select *from account; -- 左边终端更新前 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 321.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql select *from account; -- 左边终端更新后commit前 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 321.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql select *from account; -- commit后读到了 -- 但是此时还在当前事务中并未commit那么就造成了同一个事务内同样的读取在不同的时间段(依旧还在事务操作中)读取到了不同的值这种现象叫做不可重复读(non reapeatable read)这个是问题吗 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 123.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql8.6 可重复读【Repeatable Read】8.6.1 update操作左边终端mysql set global transaction isolation level repeatable read; -- 设置全局隔离级别RR Query OK, 0 rows affected (0.01 sec) -- 关闭终端重启 mysql select tx_isolation; ----------------- | tx_isolation | ----------------- | REPEATABLE-READ | -- 隔离级别RR ----------------- 1 row in set, 1 warning (0.00 sec) mysql begin; -- 开启事务同步的终端B也开始事务 Query OK, 0 rows affected (0.00 sec) mysql update account set blance4321.0 where id1; -- 更新数据 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql commit; -- 提交事务右边终端mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | REPEATABLE-READ | ------------------------- 1 row in set (0.00 sec) mysql select *from account; -- 初始数据 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 123.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql begin; -- 开启事务 Query OK, 0 rows affected (0.00 sec) mysql select *from account; -- 终端A commit前 查看 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 123.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql select *from account; -- 终端A commit后 查看 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 123.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql commit; -- 结束事务 Query OK, 0 rows affected (0.00 sec) mysql select *from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | -- 看到更新 | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql8.6.2 insert操作与update操作类似左边终端mysql begin; Query OK, 0 rows affected (0.00 sec) mysql insert into account (id,name,blance) values(3, 王五, 5432.0); Query OK, 1 row affected (0.00 sec) mysql commit; Query OK, 0 rows affected (0.00 sec) mysql右边终端mysql select *from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql begin; Query OK, 0 rows affected (0.00 sec) mysql select *from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql select *from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) -- 多次查看发现终端A在对应事务中insert的数据在终端B的事务周期中也没有什么影响也符合可重复的特点。但是一般的数据库在可重复读情况的时候无法屏蔽其他事务insert的数据(为什么因为隔离性实现是对数据加锁完成的而insert待插入的数据因为并不存在那么一般加锁无法屏蔽这类问题),会造成虽然大部分内容是可重复读的但是insert的数据在可重复读情况被读取出来导致多次查找时会多查找出来新的记录就如同产生了幻觉。这种现象叫做幻读(phantom read)。很明显MySQL在RR级别的时候是解决了幻读问题的(解决的方式是用Next-Key锁(GAP行锁)解决的。这块比较难有兴趣同学了解一下)。 mysql commit; Query OK, 0 rows affected (0.00 sec) mysql select *from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | | 3 | 王五 | 5432.00 | ---------------------- 3 rows in set (0.00 sec) mysql8.7 串行化【serializable】串行化是MySQL中最高、最严格的隔离级别。它的核心目标是完全消除并发事务之间的干扰确保任何时刻只有一个事务在执行从而实现最高的数据一致性。对所有操作全部加锁进行串行化不会有问题但是只要串行化效率很低几乎完全不会被采用。在串行化级别下MySQL 会通过锁机制来强制实现这种“排队”效果。具体来说读操作加锁 (Shared Lock / S Lock)当一个事务执行SELECT查询时它会为它读取的每一行数据或整个表取决于查询范围加上一个共享锁。这个共享锁会阻止其他事务对这些数据进行修改即加排他锁但允许其他事务也进行读取加共享锁。写操作加锁 (Exclusive Lock / X Lock)当一个事务执行UPDATE,DELETE,INSERT等修改操作时它会为涉及的数据加上一个排他锁。这个排他锁会阻止其他事务对这些数据进行任何操作无论是读还是写。左边终端终端Amysql set global transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | SERIALIZABLE | ------------------------- 1 row in set (0.00 sec) mysql begin; Query OK, 0 rows affected (0.00 sec) mysql select * from account; ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | | 3 | 王五 | 5432.00 | ---------------------- 3 rows in set (0.00 sec) mysql update account set blance1.00 where id1; -- 终端A中有更新或者其他操作会阻塞。直到终端B事务提交。 Query OK, 1 row affected (41.21 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql commit; Query OK, 0 rows affected (0.01 sec) mysql右边终端终端Bmysql SELECT transaction_isolation; ------------------------- | transaction_isolation | ------------------------- | SERIALIZABLE | ------------------------- 1 row in set (0.00 sec) mysql begin; Query OK, 0 rows affected (0.00 sec) mysql select *from account; -- 初始数据 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | | 3 | 王五 | 5432.00 | ---------------------- 3 rows in set (0.00 sec) mysql select *from account; -- 左边updata后 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | | 3 | 王五 | 5432.00 | ---------------------- 3 rows in set (0.00 sec) mysql commit; -- 提交之后终端A中的update才会提交。 Query OK, 0 rows affected (0.00 sec) mysql select *from account; -- 右边commit后 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 4321.00 | | 2 | 李四 | 10000.00 | | 3 | 王五 | 5432.00 | ---------------------- 3 rows in set (0.01 sec) mysql select *from account; -- 左边commit后 ---------------------- | id | name | blance | ---------------------- | 1 | 张三 | 1.00 | | 2 | 李四 | 10000.00 | | 3 | 王五 | 5432.00 | ---------------------- 3 rows in set (0.00 sec) mysql阻塞可以看到左边终端卡住右边commit后左边commit后总结其中隔离级别越严格安全性越高但数据库的并发性能也就越低往往需要在两者之间找一个平衡点。不可重复读的重点是修改和删除同样的条件, 你读取过的数据再次读取出来发现值不一样了幻读的重点在于新增同样的条件, 第1次和第2次读出来的记录数不一样说明mysql默认的隔离级别是可重复读一般情况下不要修改上面的例子可以看出事务也有长短事务这样的概念。事务间互相影响指的是事务在并行执行的时候即都没有commit的时候影响会比较大。8.8 一致性(Consistency)事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态。当数据库只包含事务成功提交的结果时数据库处于一致性状态。如果系统运行发生中断某个事务尚未完成而被迫中断而改未完成的事务对数据库所做的修改已被写入数据库此时数据库就处于一种不正确不一致的状态。因此一致性是通过原子性来保证的。其实一致性和用户的业务逻辑强相关一般MySQL提供技术支持但是一致性还是要用户业务逻辑做支撑也就是一致性是由用户决定的。而技术上通过AID保证C。 一致性 (C)是一个结果而原子性 (A)、隔离性 (I) 和持久性 (D) 是实现这个结果的手段和保障。8.9 推荐阅读https://www.jianshu.com/p/398d788e1083 https://tech.meituan.com/2014/08/20/innodb-lock.html https://www.cnblogs.com/aspirant/p/9177978.html