网站建设软件开发,php网站开发门槛高吗,企业网站软件开发,如何做qq钓鱼网站MySQL 事务 文章比较长#xff0c;建议分段阅读 后续如果有改动会在 Junebao.top 之前对事务的了解仅限于知道要么全部执行#xff0c;要么全部不执行#xff0c;能背出 ACID 和隔离级别#xff0c;知其然但不知其所以然#xff0c;现在觉得非常有必要系统学一下#xff…MySQL 事务 文章比较长建议分段阅读 后续如果有改动会在 Junebao.top 之前对事务的了解仅限于知道要么全部执行要么全部不执行能背出 ACID 和隔离级别知其然但不知其所以然现在觉得非常有必要系统学一下关于事务关于 LBCC关于 MVCC关于死锁 …… 并发的问题
所谓 事务 是用户定义的一个 数据库操作序列 这些操作要么全做要么全不做是一个不可分割的工作单位在关系型数据库中一个事务可以是一条 SQL 语句一组 SQL 语句或者是整个程序事务的开始和结束由用户显示控制如果用户没有显式定义事务则由 DBMS 按默认规定自动划分事务如在 MySQL 中默认 autocommit 为 ON 则开启事务自动提交每条没有显式定义事务的 SQL 语句都会被当作一个单独的事务并自动提交
mysql show session variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | ON |
----------------------
1 row in set, 1 warning (0.00 sec)事务有四个特性即 ACID
AAtomicity: 原子性 事务是数据库的基本工作单位事务中包含的诸操作要么都做要么都不做。CConsistency: 一致性事务的执行结果必须使数据库从一个一致性状态转换到另一个一致性状态所谓一致性状态是指数据库中因该只包含成功事务执行的结果如果一个事务在执行过程中被迫中断但这个未完成的事务对数据库的部分修改已经写入物理数据库这时数据库就处于一种 “不正确” 的状态或者称处于 “不一致” 的状态。I Isolation: 隔离性一个事务的执行不能被其他事务所干扰即一个事务内部操作所使用的数据对其他并发的事务应该是隔离的所有并发执行的事物之间不能相互干扰。DDurability持久性一个事务一旦提交他对数据的操作就应该是持久的接下来的其他操作或故障不应该对他有任何影响。
只有保证了事务的 ACID 特性对数据库的操作才能是安全的因此不管是 LDCC 还是 MVCC其核心目的都是保证事务的 ACID 特性。可能破坏事务 ACID 特性的因素包括
多个事务并行运行时不同事物的操作交叉执行。事务执行过程中被强制停止。
上面两个因素分别对应事务处理的两类技术并发控制和数据库恢复技术事务被强制终止一般由事物内部故障系统故障等造成发生类似故障时一般采用日志文件恢复等方法使数据库恢复到上一个一致性状态这里着重研究由并发导致的 ACID 被破坏的情况和解决方案。
并发导致的数据不一致包括丢失修改脏读不可重复读幻读。
丢失修改 如上图两个并发的事务 T1, T2 同时读表中的某条记录 total 得到 16 并且对其进行修改最终造成后提交的事务 T2 的修改结果覆盖了先提交的事务 T1 的修改结果这种现象叫做丢失修改。
针对丢失修改还有一种情况就是如果 T2 在修改 total 之后发生异常进行了回滚就会导致 total 值重置为 16这种现象被称为 回滚覆盖而第一种情况被称为提交覆盖
不可重复读和幻读
事务 T1 读取数据后T2 对该数据进行了更新操作 导致 T1 无法再次读到前一次读取的结果这种现象叫做不可重复读导致不可重复读的原因包括下面三种情况
T1 读取某一数据后T2 对其进行了修改导致 T1 再次读该数据时得到与之前不一致的值。T1 读取某些数据后T2 删除了其中的某些数据当 T1 再次读这些数据时发现之前的一些数据神秘消失了。T1 读取某些数据后T2 向其中插入了一些数据当 T1 再次读这些数据时发现莫名其妙多了一些数据。
后两种情况也被常常称为 幻读 幻读与第一种情况的不同在于
幻读往往在读取某一范围的数据时产生。幻读是因为其他事务执行了插入或删除语句导致的但第一种情况一般是执行更新语句导致的。 为了方便不可重复读一般只指第一种情况幻读指后两种情况。 脏读
事务 T1 修改了某条记录T2 读取到了 T1 未提交的这条记录但 T1 由于某些原因被回滚了这就导致了 T2 读取到的数据与数据库中的数据不一致即脏数据。
对比
一致性问题产生过程一致性问题产生原因脏读不可重读幻读丢失修改
导致上述四种不一致的原因就是破坏了事务的隔离性进而导致一致性被破坏而保证数据隔离性的方法就是使用正确的方式调度并发操作 但有的时候为了性能我们有允许牺牲一部分隔离性比如对有些数据量很大少量脏数据对结果影响很小或影响可以接收时我们可以降低一致性要求以减少系统开销这就是数据库的隔离级别。
隔离级别脏读不可重复读幻读读未提交 Read uncommitted可能可能可能读已提交 Read committed可能可能可重复读 Repeatable read可能串行化 Serializable
并发控制技术
隔离级别只是定义了在不同的级别下应该保证哪些一致性具体实现这些隔离级别的方法有很多如传统的基于锁的并发控制LBCC还有一些无锁并发控制方案如时间戳timestamp), 乐观控制法scheduler多版本并发控制MVCC等我们主要探索基于锁的并发控制LBCC和多版本并发控制MVCC
基于锁的并发控制LBCC
所谓封锁就是事务在某个数据对象进行操作之前先申请锁对该对象加锁后该事务就拥有了一定的对该对象的控制在该事务释放该锁前其他事务不能操作此数据对象。
从锁的模式来看锁可以分为共享锁和排它锁共享锁又称为读锁S 锁排它锁又称为写锁X锁。
X 锁若事务 T 对数据对象 A 加上了 X 锁则只允许 T 读取和修改 A 其他任何事务不得再对 A加任何类型的锁直到 T 释放锁。S 锁若事务 T 对数据对象 A 加上了 S 锁则 T 和其他事务都可以可以读 A同时其他事务可以继续申请 A 的 S 锁但是直到所有事务都释放 A 的 S 锁为止所有事务并不包括自己A 是不允许修改的。这就意味着如果只有一个事务对 A 添加了 S 锁那他自己是可以修改数据的。
封锁协议
一级封锁协议 事务 T 在修改数据 R之前必须对其加 X 锁直到事务提交或回滚才释放锁一级封锁协议可以防止丢失修改一级封锁协议只在写数据时加锁读数据时并不需要获取锁所以它无法解决脏读幻读不可重复读。**二级封锁协议**要求在一级封锁协议的基础上读数据前必须加 S 锁读完即可立刻释放 S 锁。因为对数据 A 加 X 锁后不允许其他事务再申请其他锁所以事务要想获得 S 锁读就必须等持有 X 锁的事务写完提交或回滚这样就可以避免脏读但由于二级封锁协议允许读完后立刻释放 S 锁无法保证下一次读时数据不被修改所以所以它不能保证可重复读包括幻读。**三级封锁协议**要求在一级封锁协议的基础上读数据前必须加 S 锁直到事务结束才释放。该协议可以解决不可重复读包括幻读的问题。
死锁活锁
死锁和活锁是使用 LBCC 解决一致性问题时必须考虑的问题
活锁如果 T1 封锁了 RT2 请求 R 的锁这时 T2 应该等待然后 T3 也请求 R 并等待这时 T1 释放了 R 的锁但该锁被 T3 获得在这过程中 T4 也请求 R T3 释放锁后锁又被 T4 获得…… 这导致 T2 一直无法获得锁这种某个事务陷入饥饿的状态现象叫做活锁避免活锁的简单策略是先来先服务。死锁如果 T1 封锁了 R1, T2 封锁了 R2, 然后 T1 又来申请 R2, T2 又来申请 R1, 这会造成 T1 T2 相互等待永远无法结束的局面形成死锁。
预防死锁
死锁出现的原因是两个事务都已经封锁了一些数据对象然后都去请求已经被对方锁定的数据对象预防死锁就是要想办法破坏死锁产生的条件通常使用一次封锁法和顺序封锁法解决
一次封锁法一个事务必须一次性将所有要使用的数据全部加锁否则就不能继续执行。但由于数据库中的数据并不是一尘不变的我们往往很难准确知道要用哪写数据为了实现一次封锁就不得不扩大封锁范围将可能的数据全部加锁这会造成系统并发度降低影响性能。顺序封锁法预先对数据对象规定一个封锁顺序所有事物按这个顺序实施封锁但这样同样存在问题 数据库中的数据对象很多要维护这么多对象的封锁顺序并不容易。谁应该先被封锁谁应该后被封锁关于这个顺序很难得到一个最优解。
解除死锁
上面个两种预防死锁的办法在现实中可行性很低所以DBS通常采用诊断并解除死锁的办法解决死锁问题。
诊断死锁
超时法如果一个事务的等待时间超过特定期限就认为其发生了死锁。等待图法将事务和其等待的数据对象以有向图的形式组织检测图中是否有回路发现回路即为死锁。
解除死锁
发现死锁后我们往往会选择一个处理死锁代价最小的事务将其撤销以释放他持有的所有锁当然对撤销事务的数据修改操作必须加以修复。
可串行化调度和两段锁协议
可串行化调度
如果多个事务并发执行的结果和按某一次序串行执行这些事务的结果是一样的就说这种调度是可串行化调度他是并发事务正确调度的准则。
两段锁协议2PL
两段锁协议用来保证调度是可串行化的两段锁协议是指所有事务必须分两个阶段对数据项进行加锁和解锁操作。在对一个数据项进行读写操作前必须申请并获得该数据项的封锁。拓展阶段释放完一个封锁后事务不再去申请或获取任何其他锁。收缩阶段
封锁粒度
加锁时你可以选择对一个属性值关系索引项整个索引甚至整个数据库加锁加锁对象的大小叫做锁的粒度一般来说粒度越大并发度越小系统开销也越小封锁粒度越小并发度越高系统开销也就越大。一个 DBS 应该尽可能兼顾并发度和系统开销这样显然不能只支持某一粒度的封锁这种提供多种封锁粒度供不同事务选择的封锁方法叫做多粒度封锁。
多粒度封锁可以由多粒度树描述如下 多粒度树的根节点是整个数据库表示最大的数据粒度叶子节点表示最小的封锁粒度多粒度封锁协议 允许粒度树的每一个节点可以被单独加锁某一个节点加锁意味着该节点的所有子节点也被加同样的锁对该节点来说这个锁属于显式加锁对于其子节点来说属于隐式加锁他们的效果是一样的。
显式和隐式加锁看起来是理所当然的但这会导致一个问题在对某一数据项加锁时我们必须保证当前要加的这把锁与其显示假的锁不冲突同时还要保证与其隐式假的锁不冲突为此在加锁前我们必须要
检查数据项有无显示加锁保证不与其冲突。检查数据项的所有父节点保证不与其隐式加锁冲突。检查数据项的所有子节点保证加锁后由于本次加锁获得隐式锁的数据项不与其原来的锁冲突。
这样一来每次加锁我们不得不遍历整个粒度树这种效率是非常低下的为此我们引入了一种新锁意向锁
意向锁用于提升加锁效率无法手动创建它的含义是如果对一个节点加意向锁则说明该节点的下层节点正在被加锁对任意节点加锁时必须先对它的上层节点加意向锁。有三种常用的意向锁他们分别是意向共享锁IS 锁意向排它锁IX锁共享意向排它锁SIX锁
IS 锁表示其子节点准备加 S 锁IX 锁表示其子节点准备加 X 锁SIX 锁如果对一个数据对象加 SIX 锁表示对他加 S 锁在家 IX 锁例如对某表加 SIX 锁表示该事务要通过 S 锁读整个表同时还要更新个别元组IX锁。
根据上面的描述我们可以得出以下的锁强度偏序关系图和数据锁的相容矩阵 锁强度表示一个锁对其他锁的排斥程度 XSIXIXSIS-XFFFFFTSIXFTTFTTIXFTTFFTSFFFTTTISFTTTTT-TTTTTT
规律对于意向锁来说使用强度更高的锁来替换强度低的锁是安全的。
意向锁如何提高加锁效率
如果某一时刻数据库中数据对象的锁持有情况如下图粒度树所示对表 T1 加了 S 锁其父节点对应加了 IS 锁T1 的子节点也隐式获得了 S 锁当然所有节点也隐式获得了 IS 锁所有隐式锁都未画出 如果现在我们希望更新记录 R1根据封锁协议就必须对 R1 添加 X 锁对其父节点加 IX 锁这时只需要检查 T1 和 DB 的锁是否与之不相容T1 持有 S 锁与 X 锁不相容调度器会阻止加锁。
相反如果我们只是想对 R1 加 S 锁进行读操作就需要先对父节点加 IS 锁T1 持有 S 锁与 IS 锁相容再检查 R1 持有的锁是否与 S 锁相容相容允许加锁。
加入意向锁后我们不需要再去遍历所有子节点便可以判断能否未数据项加锁可以提高系统并发度减少加锁解锁开销。
多版本并发控制MVCC
通过 LBCC 我们可以解决所有的并发不一致问题那为什么还会有其他并发控制方案呢归根结底还是基于性能的考虑 LBCC 只是实现了允许并发读但对于并发读写写写操作只能串行执行在读写都很频繁的场景下并发性能将大大降低因此人们才提出各种无锁并发控制方案MVCC 就属于其中一种。
MVCC 的大概思路是每一个事务都有一个唯一的ID当某一个事务要修改某行数据时先将这一行原来的数据做一个快照保存下来当有其他并发事务也要操作这个事务时可以操作之前的版本这样最新的版本只被写事务维持不会干扰到读事务以此实现隔离MVCC 并没有一个统一的标准不同 DBS 的实现也不尽相同下面以 MySQL InnoDB 引擎为例说一下 MVCC 的一个具体流程。
InnoDB 中的 MVCC
既然是 MVCC 那最重要的就是旧版本的数据要存在在 MySQL InnoDB 中这些数据会以 回滚段 rollback segment的形式保存在表空间中更具体来说他们会被保存在 undo log 中。
其次InnoDB 会在所有表中加两个隐藏列 DB_TRX_ID 和 DB_ROLL_PTR前者占 6 字节表示插入或更新该行的最后一个事务的事务标识符后者占 7 个字节称为回滚指针指向回滚段的 undo log 记录。 InnoDB 插入的隐藏列还有一个 DB_ROW_ID会随着新行的插入会单调递增如果使用了默认自增ID的聚簇索引索引中就会包含这个列。 每当我们插入或更新一行数据删除被认为是更新的一种InnoDB 会为这个事务分配一个唯一单调递增的事务ID这个 ID会记录在这一行的 DB_TRX_ID 中表示这一行数据的最新版本。
如下图设有一张表 t, 包含两个字段 id 和 name, 它的初始状态如下 表示最后插入或修改这条记录的事务 ID 是 100现在如果有一个新的事务要修改这条记录设其 ID 为 200则现在表的状态就会变成下面这样 灰色行表示历史版本被记录在 undo log 中从最新版本的回滚指针可以找到这条记录的历史版本这条链表被叫做版本链
除此之外当一个事务第一次执行读操作时会为该事务生成一个一致性视图 read-view, 这个数据结构包含此刻所有活跃着的未提交的写事务的事务ID列表和此时DBS分配出去的最大事务号有了 read-view 就可以判断出哪写事务是已经提交了的哪写事务是未提交的具体判断逻辑如下
从活跃事务列表中找到最小的事务ID记为 min_t_id, 读取到某一版本的事务ID记为 row_t_id, 系统分配的最大事务ID 记为 max_t_id
如果 row_t_id min_t_id, 说明这一版本的事务一定被提交了这一版本可见如果 row_t_id max_t_id, 说明这一版本的事务还没有开始一般不存在这种情况不可见如果 row_t_id min_t_id row_t_id max_t_id, 还需要分两种情况讨论 如果 row_t_id 在活跃事务列表中则说明该版本是由未提交的事务创建的不可见但对于自身事务是可见的。如果 row_t_id 不在活跃事务列表中说明创建该版本的事务已提交该版本是可见的。
有了版本链和 read-view一个事务就可以根据 read-view 顺着版本链依照上面的规则一直往下直到找到一个可见的版本
以上面的例子为例如果事务的并发时序图如下 当事务二更新操作执行后版本链变为 事务三第一次执行查询语句时生成的 read-view 为
| active list | max |
---------------------------
| 200, 300 | 300 |
---------------------------read view根据 read-view 得到 max_t_id 300, min_t_id 200, 从最新版本开始遍历
row_t_id 300, 满足条件3同时 row_t_id 在 active list 中说明这一版本未提交不可见根据回滚指针检查下一个版本。row_t_id 200同样满足条件三在 active list 中不可见看下一个版本。row_t_id 100, 满足条件一可见则这个读事务会使用这一版本的信息。
如下图如果在第五时刻事务 2 提交第六时刻事务三再次读取 版本链并没有发生改变但如果在 RC 的隔离界别下事务三的第二条查询语句会重新生成 read-view, 这时活跃事务只有 200最大事务为 300按上面的规则row_t_id 300 时满足条件三但不在活跃事务列表中所以这条记录是可见的这就会读出 wangwu, 导致不可重复读。
但如果使用 InnoDB 默认的 RR 隔离界别read-view 只会在事务执行第一条查询语句时生成后续所有查询语句使用同一个 read-view, 由此避免不可重复读。
一些其他问题
InnoDB 中的锁
在上面我们介绍了 LBCC这里再简单介绍一下 InnoDB 中几个具体的锁他们以锁粒度划分。
记录锁
Record Locks也叫行锁加在索引记录上的锁。例如SELECT c1 FROM t WHERE c1 10 For UPDATE, 防止任何其他事务插入、更新或删除 t.c1 值为10的行, 行锁加在索引上而不是记录上因为innodb一定会有一个聚簇索引因此最终的行锁都会落到聚簇索引上。
间隙锁
gap Locks, 是对索引记录之间间隙加的锁或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE这样可以防止其他事务插入 c1 值在 10 - 20 之间的数据。
这里的间隙可以是两个索引值之间的间隙也可以是从无穷到单个索引值之间的间隙
使用唯一索引检索唯一行时不会使用间隙锁但是如果检索条件仅包含多列唯一索引的一些列时仍然会使用间隙锁锁定例如一个简单的 SELECT 语句
SELECT * FROM child WHERE id 100;如果 id 使用唯一索引则该语句只会加记录锁。
但如果 id 未建立索引或建立了非唯一索引那么从负无穷到100的间隙将会被间隙锁锁定。
还有一个需要值得注意的问题在一个间隙上不同的事务可以持有相互冲突的锁这是因为如果某条记录被从索引中清除那我们必须合并由不同事务保存在记录上的间隙锁。所以说间隙锁的唯一目的是为了防止记录被插入间隙 一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定。共享和专用间隙锁之间没有区别。 它们彼此不冲突并且执行相同的功能。
间隙锁定可以显式禁用。 如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_unsafe_for_binlog系统变量现已弃用则会发生这种情况。 在这种情况下将禁用间隙锁定来进行搜索和索引扫描并且间隙锁定仅用于外键约束检查和重复键检查。
使用 READ COMMITTED 隔离级别或启用innodb_locks_unsafe_for_binlog还有其他效果。MySQL 在计算完 WHERE 条件后将释放不匹配行的记录锁。 对于 UPDATE 语句InnoDB 进行“半一致”读取以便将最新的提交版本返回给 MySQL以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。
间隙锁总结
进行范围查询或使用非唯一索引作为检索条件时会使用间隙锁。间隙锁只用于阻止别的事务插入间隙他不阻止别的间隙锁锁定相同内容在一个间隙上不同的事务可以持有相互冲突的锁。间隙锁只在 RR 隔离界别下起作用可以手动关闭。
临键锁
临键锁是间隙锁和记录锁的结合临键锁使得一个会话如果在某个索引记录上建立了共享或排它锁其他会话不能在该索引记录前面的间隙插入数据。
假设某个表的索引包含值10111320则其临键锁可能包含以下间隔 其实只需要记住临键锁锁定的是一个左开右闭的区间即可 临键锁总结
临键锁是行锁和间隙锁的组合。临键锁锁定的是一个左开右闭的区间。InnoDB RR 隔离级别下临键锁用来解决幻读。
插入意向锁
insert intention lock 是在插入新的记录之前通过 INSERT 操作设置的一种间隙锁该锁以这样一种方式发出插入意图的信号即如果多个事务要插入的数据在同一间隙内但不是相同的位置那这些事务就不需要相互等待。比如假设有值为4和7的索引记录。尝试分别插入值5和6的单独事务在获得插入行上的独占锁之前每个事务都使用插入意图锁锁定4和7之间的间隙但不会互相阻塞因为行不冲突。
自增锁
AUTO-INC锁是一种特殊的表级锁如果一个表中有 AUTO_INCREMENT列则要插入该表的事务在插入之前会先获取该锁该锁是表级锁但不是事务级锁插入语句执行完后就会立刻释放不会等待事务提交才释放。自增锁也具有不同的模式可以使用 innodb_autoinc_lock_mode 选项0 1 2控制自增锁递增算法以谋求效率和安全性的要求. 在默认状态下该选项设置为 1在该模式下
“批量插入”使用特殊的AUTO-INC表级锁并将其保持到语句结束。 这适用于所有INSERT … SELECTREPLACE … SELECT和LOAD DATA语句。 一次只能执行一条持有AUTO-INC锁的语句。 如果批量插入操作的源表与目标表不同则在对源表中选择的第一行进行共享锁之后将对目标表执行AUTO-INC锁。 如果批量插入操作的源和目标是同一表则在对所有选定行进行共享锁之后将获取AUTO-INC锁。“简单插入”预先知道要插入的行数通过在互斥量轻型锁的控制下获得所需数量的自动增量值来避免表级AUTO-INC锁定 仅在分配过程的整个过程中才保留直到语句完成为止。 除非另一个事务持有AUTO-INC锁否则不使用表级AUTO-INC锁。 如果另一个事务持有AUTO-INC锁则“简单插入”将等待AUTO-INC锁就好像它是“批量插入”一样。混合模式插入”如果用户为多行“简单插入”中的某些行 (但不是所有行) 的AUTO_INCREMENT列提供显式值InnoDB分配的自动增量值会多于要插入的行数。但是自动分配的所有值都是由最近执行的上一条语句生成的自动增量值连续生成的因此“多余的”号码就会丢失。
其他模式可以参考 官方文档
关于幻读
网上对幻读的定义各种各样有人把幻读囊括在不可重复中比如我们的教材有人说对某一范围的数据执行删除或插入会导致幻读有人说只有插入导致的才叫幻读实际上在 SQL 92 标准里明确定义了什么是幻读 P3 (Phantom): SQL-transaction T1 reads the set of rows N that satisfy some search condition. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the search condition used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same search condition, it obtains a different collection of rows.当一个事务 T1 读到满足某些条件的行集合后事务 T2 向表中插入了满足这些条件的一行或多行数据如果 T1 使用相同的条件重复读取它将得到不同的结果这叫幻读而对于删除的情况92 标准也明确说了这属于不可重复读。
P2 (Non-repeatable read): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
P2(“不可重复读取”)SQL-Transaction T1读取一行。然后SQL-Transaction T2修改或删除该行并执行提交。如果T1随后尝试重新读取该行它可能会收到修改后的值或发现该行已被删除。在 InnoDB 的文档中也可以看见 Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. 所以对于 MVCC 解决不可重复读中删除情况的问题和修改其实是一样的。
还有一个问题是 InnoDB 的 RR 隔离级别有没有解决幻读的问题这在文档里面也清晰的写了解决了解决方式就是上面的临键锁。
关于丢失修改
上面说到并发导致的问题时提到了两类丢失修改的问题提交覆盖和回滚覆盖但似乎在平时说到并发问题时大家只提脏读幻读不可重复读在 SQL 92 标准里也没有发现对所谓丢失修改问题的描述事实上这两种情况都是在读的结果上进行了修改对于回滚覆盖InnoDB 的任何隔离级别下他都不会发生因为回滚到的是针对数据库的上一个已提交的版本而不是针对该事务的而对于提交覆盖我们完全可以在应用程序层面使用诸如 CAS 等技术手段避免这类问题网上也有使用悲观锁或乐观锁避免的方案可以参考事务的隔离级别以及Mysql事务的使用
关于 InnoDB 中的死锁
在 LBCC 那一节也简单说了一下死锁和活锁这里再详细了解一下 MySQL InnoDB 的死锁问题。先看一个官方文档给出的死锁的例子
CREATE TABLE t (i INT) ENGINE InnoDB;
INSERT INTO t (i) VALUES(1);事务一先对数据加 S 锁执行读操作(未提交)
START TRANSACTION;
SELECT * FROM t WHERE i 1 LOCK IN SHARE MODE;然后再开启一个事务二尝试删除该记录
START TRANSACTION;
DELETE FROM t WHERE i 1;由于删除会对数据加 X 锁这与事务一加的 S 锁并不相容所以事务二会等待事务一释放 S 锁像下面这样 而等待一段时间后会提示获取锁超时并终止事务
DELETE FROM t WHERE i 11205 - Lock wait timeout exceeded; try restarting transaction时间: 51.473s这样并不存在死锁的问题但如果在事务二等待的期间事务一执行了对这条记录的删除操作
DELETE FROM t WHERE i 1;就会发生死锁
DELETE FROM t WHERE i 11213 - Deadlock found when trying to get lock; try restarting transaction时间: 12.216s原因是事务一如果想要删除就必须要一个 X 锁但 X 锁已经被事务二请求了不能授予事务一且由于事务二事先请求 X 锁事务一持有的 S 锁也不能升级为 X 锁这就会导致死锁出现这个问题时InnoDB 会回滚一个小事务事务的大小由插入、更新或删除的行数决定并抛出上面的错误来解除死锁。
在高并发系统上当多个线程等待相同的锁时死锁检测会导致速度变慢。 所以有时当发生死锁时禁用死锁检测而依靠innodb_lock_wait_timeout 设置进行事务回滚可能会更有效。 可以使用 innodb_deadlock_detect 配置选项禁用死锁检测。 InnoDB使用自动行级锁定。即使在仅插入或删除单行的事务中也可能会遇到死锁。这是因为这些操作并不是真正的“原子”操作它们会自动对插入或删除的行的可能是多个索引记录设置锁定。 您可以使用以下技术来处理死锁并减少发生死锁的可能性 使用 SHOW ENGINE INNODB STATUS命令以确定最近死锁的原因。这可以帮助您调整应用程序以避免死锁。 如果频繁出现死锁警告引请通过启用innodb_print_all_deadlocks 配置选项来收集更广泛的调试信息 。有关每个死锁的信息而不仅仅是最新的死锁都记录在MySQL error log 中。完成调试后请禁用此选项。 如果事务由于死锁而失败在任何时候请重试一遍死锁并不可怕。 请保持插入或更新事务足够小避免锁被一个事务长时间占用以此减少冲突概率。 进行一系列相关更改后立即提交事务以减少冲突的发生。特别是不要长时间未提交事务而使交互式 mysql会话保持打开状态。 如果您使用锁定读取SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE请尝试使用较低的隔离级别例如 READ COMMITTED。 在事务中修改处于多个表或同一表中的不同行集时每次都要以一致的顺序去执行这些操作。这样事务会形成定义明确的队列而不会导致死锁。例如将数据库操作组织到应用程序内的函数中而不是在不同位置编码多个类似的INSERTUPDATE和DELETE语句序列。 对表中的数据建立合适索引这样您的查询将会使用更少的索引记录同样也会使用更少的锁。使用EXPLAIN SELECT以确定MySQL认为哪些索引最适合您的查询。 如果可以请尽量少的使用锁以允许 SELECT从一个旧的快照返回数据不要添加条款FOR UPDATE或LOCK IN SHARE MODE给它。在READ COMMITTED这里使用隔离级别是件好事因为同一事务中的每个一致性读取均从其自己的新快照读取。 如果没有其他办法可以使用表级锁序列化事务。对事务表例如InnoDB表使用LOCK TABLES的正确方法是SET autocommit 0not START TRANSACTION后跟来开始事务直到明确提交事务后才对LOCK TABLES调用 UNLOCK TABLES。例如如果您需要写表 t1和从表中读取数据 t2则可以执行以下操作 SET autocommit0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;表级锁可防止对表的并发更新从而避免死锁但代价是对繁忙系统的响应速度较慢。 序列化事务的另一种方法是创建一个仅包含一行的辅助“信号量”表。 在访问其他表之前让每个事务更新该行。 这样所有事务都以串行方式发生。 注意在这种情况下InnoDB即时死锁检测算法也适用因为序列化锁是行级锁。 对于MySQL表级锁必须使用超时方法来解决死锁。 参考
MySQL 官方文档 innodb-multi-versioning
美团技术文章 - Innodb中的事务隔离级别和锁的关系
解决死锁之路 - 学习事务与隔离级别
