mysql事务总结

 1 简介

MySQL 框架本身不直接支持事务，而由引擎实现。常用引擎中，仅 innodb 支持事务， MyISAM 和 Heap 引擎皆不支持事务，下文对事务的介绍皆以 innodb 为准。

MySQL框架提供多条语句用于操作事务，常用语句如下 

1. START TRANSACTION

    

    

    

   COMMIT

    

    

    

   ROLLBACK

    

    

    

   SET AUTOCOMMIT= {0 | 1}

    

    

    

   
   

   autocommit 属性用于控制是否自动提交，默认情况下是“1”，表示每个更新语句都

   是单独的事务。可通过“SET AUTOCOMMIT”语句将该值设置为 0，表示不自动提交。 “START TRANSACTION”语句用于优雅的启动一个事务，它忽略 autocommit 属性，直到事务结束。

   
   

   2 隐式提交和回滚

    

   2 . 1 简介

    

   有些语句会导致一个事务被隐式提交，包括各种 DDL 语句、一些事务操作语句等，具 体参见 MySQL 手册 

   MySQL 事务有如下一些特点：

   当事务中发生某些异常情况时，innodb 将隐式回滚事务：

   回滚 SQL 语句时，innodb 不会释放为执行该 SQL 获得的锁。

   
   

   3 事 务 隔 离

    

   3 . 1 简 介

   事务之间需要有一定的隔离来避免相互影响，为了区分不同的影响程度，SQL 标准规定了四种隔离级别：

   一个事务能够读到另一个事务尚未提交的中间结果，这会导致“脏读”。

   仅当事务已提交，其结果才可见。该级别存在“nonrepeatable read”（不可重复读）

   的问题——事务过程中，相同 select 语句的结果可能被外界改变。

   解决“nonrepeatable read”：一个事务所读的行内容在该事务后续生命期中保持一 致。存在“phantom reads”（幻读）问题：事务过程中，相同的 where 条件可能读到更 多的行。

   将可能冲突的事务串行化。

   innodb 支持所有四种隔离级别，默认且推荐的级别是“REPEATABLE READ”，并且

   innodb 解决了该级别的“phantom reads”问题。

   innodb 采用 MVCC 机制来实现事务的隔离，MVCC 的基本思想是为不同的事务保存其当 时数据的快照（称为“版本”），它既能保证事务内数据的一致性，又避免了锁的大量使用， 从而提升了性能和并发性。

   innodb 实现了一种非常高效的 MVCC，它并不会为事务访问的数据真正建立快照，而 是通过类似于时间戳的“版本号”机制来确保拿到未被其他事务修改的数据。所谓“版本号”其实是 innodb 维护的一个计数器，每启动一个事务，计数器随着递增，并将该号作为事 务的版本号。

   在默认的“REPEATABLE READ”隔离级别下，当执行各类 SQL 时 innodb 是这么做的：

   innodb 将系统当前的版本号设为新增行的版本号。

   innodb 将系统当前的版本号设为被删除行的删除号，该行并未被立即物理删除。

   内部看来，UPDATE 相当于先 INSERT 再 DELETE。innodb 将新增一行，并把系统当前的 版本号设为新增行的版本号；接着“删除”原来行，将系统当前的版本号设为被删除行的删 除号，但该行并未被立即物理删除。

   innodb 对比当前事务的版本号和找到行的版本号/删除号，当满足如下条件时，行被

   select 出来：

2. 行版本号不大于事务版本号。这确保了该行在事务开始时已存在，或者由当前事务创建、更新；
3. 行删除号不存在，或者删除号大于事务版本号。这确保事务开始前行未被删除；
   •  “START TRANSACTION”或者“SET AUTOCOMMIT=0”只是指出将要开始事务，仅当后 续执行第一个 SQL 时才会真正启动事务，并分配版本号；
   •  事务持续时间要尽量短，否则可能导致大量被删除行滞留在系统中；
   •  TRUNCATE 语句以及 DDL 语句将物理改变行或表，MVCC 失效；
   •  写操作对 MVCC 的作用是在事务提交时才发生的，其记录的版本号和删除号都是事务 提交时的系统版本号；
   •  不应修改隔离级别，除非你确切的知道那么做的结果；
   •  特别注意：MVCC 针对 select，不会影响写操作的 where 判断；
   •  事务总能看到自己的写操作。当事务更新了行后，该事务后续的读写都能够看到这些 更改；
   •  可通过“innodb_max_purge_lag”配置来控制净化线程的行为，具体参见手册(链接)
   •  Innodb 使用隐式锁实现事务结果一致性；
   •  显式锁用于解决先读后写的数据一致性问题；
   •  当先读后写针对的是相同行时，应该用显式独占锁；
   •  显式锁 select 不受 MVCC 影响，它们总是取最新数据（包括本事务的更新）；
   •  尽量使用小事务，大事务可能导致加锁太多、锁定时间太长，影响并发性能；
   • innodb在需要时逐个获取锁，并且在事务结束时一次性释放，因此有死锁的可能，
   • 可通过“SHOW INNODB STATUS”查看事务状态 
   •  一个事务中不应混用多种引擎的表，否则可能导致事务结果处于不一致状态。
   •  应该总是使用“START TRANSACTION”启动事务，避免操作 autocommit。

   在该机制下，一个事务总是能看到自己开始时的数据状态，不会受到其他事务的影响。

   对于被标记为删除的行，innodb 有专门的“净化线程”（purge thread）负责定期进 行物理删除，当行满足如下条件时，认为可以将其物理删除：当前不存在版本号小于该行删 除号的事务。这样可确保不会有事务再引用到该行。

    

    

   4 事 务 和 锁 

    

   4 . 1 简 介

   innodb 使用锁机制来实现事务执行结果的一致性，在事务中执行写操作时，innodb

   会隐式地锁定相关行，避免同时写入。

   同时，innodb 也允许应用自行加锁——当应用需要先读出一些行，再根据这些行进 行写操作时，需要手动加锁来保证数据一致。innodb 提供了两种显式锁：

   【显式共享锁】

   SELECT ... LOCK IN SHARE MODE

   此模式下，当前事务将获得对所有行的 S 锁，其他事务试图更新这些行时会阻塞。 显式共享锁可能导致死锁——当需要在加锁后写这些行时，事务必须取得 X 锁，此时

   如果两个并发事务同时执行，则很可能另一个事务已经拿到了 S 锁，并且也试图拿到 X 锁—

   —于是发生死锁。

    

   【显式独占锁】

   SELECT ... LOCK FOR UPDATE

   显式独占锁用于解决经典的“test and set”问题，同时也能避免显式共享锁可能导 致的死锁问题。执行显式独占锁的事务将获得对所有行的 X 锁，其他事务试图获得 X 或 S 锁 时将会阻塞。

   以仓库系统为例：

   假设仓库表中有一个 wcount 字段，指出当前仓库放了多少货物，当货物超过 100 时， 仓库满了，不能再放。此时，向一个仓库放入货物需要判断仓库是否满，并且需要增加仓库 货物计数。如果有多个事务同时向一个已存了 99 件货物的仓库中放货物时，则很可能出现 仓库溢出的情况。

   这可以使用显式独占锁来解决：

   select id from warehouse where wcount < 100 and xxx lock for update;

   if(failed)exit;

   update ware set whid = xxx where wid = xxx;

   update warehouse set wcount = wcount + 1 where id=xxx;