Mysql数据库事务详解

80年代中国人结婚四大件：手表、自行车、缝纫机、收音机（三转一响）。要把事务娶回家需要四大件，所以事务很刻薄（ACID），四大件清单：原子性(Atom）、一致性（Consistent）、隔离性（Isolate）、持久性（Durable）。ACID就是数据库事务正确执行的四个基本要素的缩写。

1.       原子性：要么不谈，要谈就要结婚！

对于其数据修改，要么全都执行，要么全都不执行。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。最典型的问题就是银行转帐问题。

2.       一致性：恋爱时，什么方式爱我；结婚后还得什么方式爱我；

数据库原来有什么样的约束，事务执行之后还需要存在这样的约束，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B树索引或双向链表）、完整性约束（索引、主键）都必须是一致的。

3.       隔离性：闹完洞房后，是俩人的私事。

事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。当事务可序列化时将获得最高的隔离级别。隔离性是事务机制里相对来说，比较复杂的，下文另说。

4.       持久性：一旦领了结婚证，无法后悔。

修改即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

 

抛开刚才的四大件比方，再谈隔离性。隔离性是指DBMS可以在并发执行的事务间提供不同级别的数据读写安全隔离措施。隔离的级别和并发事务的吞吐量之间存在反比关系。较高级别的隔离可能会带来较高的冲突和较多的事务流产。流产的事务要消耗资源，这些资源必须要重新被访问。所以这需要trade-off，到底是访问速度优先，还是数据绝对正确优先。

两台机器分别向数据库插入数据，在提交事务前再查询一次数据库，此时本机器的读操作可以读到本机器尚未提交的事务的数据吗？这个问题本身就是一种意识错误，事务是一个原子单位，任何隔离级别都改变不了这个真理。

我们先了解几个数据库不得不知的秘密，事务一旦启动，写写肯定是冲突的。那么读写呢？读分成两种，被别人读和读别人的数据。被别人读会产生脏数据的问题。读别人的会产生不可重复读和幻读两种情况。

A.       脏读：读到的数据不是此刻真实的数据。

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。打个比方：

我的支付宝余额3000元，事务A改为2000，但事务A尚未提交。与此同时，事务B正在读取，并且“成功”读取到余额为2000。随后，事务A由于网络或者IO原因执行失败，回滚成3000元，事务B拿到的值继续运算，就是错误的，这算是一次dirtyread。' T, B 

银行转帐，A给B转1000元，B+1000，这个时候，B就能够读取到。这个时候A还没有减掉1000元，后悔了，没有提交，这个时候B把钱提走了，这不扯吗？ 银行在早年代出现类似情形的bug.

B.       不可重复读：读了两次，值不一样。

一个事务对同一行数据重复读取两次，但是却得到了不同的结果。例如，在两次读取的中途，有人对该行数据进行了update操作，并提交，结果就让当然这个事务郁闷了…

还是余额3000元，事务A是一个比较长的事务，一开始读取到3000，结果恰好我的水电自动扣款100成功(事务B执行成功)，事务A在最后读取到的余额成了2900元。这就是不可重复读现象。

C.       幻读：原来没有，现在有了…

事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

事务A正在统计到目前为止的订单数量，一开始读到的是10笔。结果恰好这个时候，家人用此支付宝买了一个家电。等事务A打算提交的时候发现成了11笔。

 

刚才说到了这三种应用事务产生的读写问题，事务产生了对应的4种隔离级别，在Mysql中利用如下：

SELECT @@TX_ISOLATION; 即可看到：说明Mysql的默认是可重复读取。 select@@global.tx_isolation; 查看全局的事务隔离级别。现在我们按照java.sql.Connection定义的四个常量值说开来：

1.       读未提交：TRANSACTION_READ_UNCOMMITTED = 1;

允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行修改后却没有提交的数据，就是担心人家的事务出问题回滚（ROLLBACK）了，而你还拿这个脏数据继续计算。该隔离级别可以通过“排他写锁”实现。绝大部分的数据库没有二到这个地步。

设置：SET GLOBALTRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

表现：可以读取任何数据，但是如果更新到同一数据上，需要等待另一个事务执行完，有超时异常：

注意，即使是GLOBAL设置，也需要打开一个新的会话连接，才能生效，包括当前设置连接。如果是SESSION，当前会话马上生效，但绝不会影响到其它会话的隔离级别，使用SELECT @@TX_ISOLATION;检查一下当前的隔离级别，免得穿越到秦国。任何Mysql设置，首先要清楚它是全局的，还是会话级别的。

2.       读提交：TRANSACTION_READ_COMMITTED   = 2;

允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

设置：SET GLOBALTRANSACTION ISOLATION LEVEL READ COMMITTED;

表现：对同一数据更新需要等待，一个事务如果没有COMMIT，任何其它事务无法读取它的中间值。因为只是加了行共享锁，所以此时，还是可以读到一个事务里正在被update的数据。这里的问题是一个事务你还可以读另外一个事务正在更新的数据。

3.       可重复读取：TRANSACTION_REPEATABLE_READ  = 4;

禁止不可重复读取和脏读取，但是有时可能出现幻影数据，但在innodb中此隔离级别不允许幻象读，应该说这已经是较高级别的安全保证了。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

设置：SET GLOBAL TRANSACTIONISOLATION LEVEL REPEATABLE READ;

表现：除了之上两个要求之后，如果一个事务对一行的读取，即使其它事务的的确确已经修改了此项数据，他也还是会将错就错到底，不会去读这条新值，保证一个事务开始后，读取的任何数据都一份

4.       序列化：TRANSACTION_SERIALIZABLE     = 8;

与“可重复读取”隔离最大的区别是读也会加锁，另外事务无法更新。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。任何数据的插入与更新，都是慢慢来，象单跑道起飞的飞机一样。在序列化隔离中，innodb会对每一个select语句后自动加上lock in share mode.

设置：SET GLOBAL TRANSACTIONISOLATION LEVELSERIALIZABLE;

表现：它依然可以同时起多个事务，但是如果对同一数据进行的任何读写操作，都必须一个等待另一个执行完再说，原则是先到的有锁定权。如果你执行一个update，对方也来一个update，那么出现：

在Java中，修改事务隔离级别，voidsetTransactionIsolation(int level) throws SQLException;

如果出现两边会话设置隔离级别不一致的情况，属性互相独立，以更高隔离级别为准。实际上隔离级别只针对于锁。设定对应的隔离级别，对应的操作都有对应的锁去执行现场清理工作。锁事实上只有两种：

 A. 共享锁(SharedLock) 也叫读锁.
     共享锁表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。
  B.写锁（Write Lock）也叫排它锁
     写锁表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

 

锁粒度

本连接查询

本连接更新

其它连接查询

其它连接insert

其它连接update

LOCK TABLES 表名 READ;

可以

不可以

可以

不可以

不可以

LOCK TABLES 表名 WRITE;

可以

可以

不可以

不可以

不可以

隔离级别的任何实现都离不开锁，DBMS，有一个善良而忠实的看门老者，不停地在给你开门、锁门、防止冲突产生。要mysql锁表的操作是LOCK，而解锁的操作是UNLOCK.

LOCK TABLES 表名 READ LOCAL; 其它连接的insert/update都无法做。（innodb）

  在innodb中两个事务之间对于不同行的操作是可以的，所以它们实现的是行锁。

开始事务时，必须关闭自动提交，SET AUTOCOMMIT=0; 那么在AUTOCOMMIT=0之前的语句会顺利执行并逐条提交。支持事务的表必须是engine =INNODB。当错误时，全盘检查所有ROLLBACK到start位置。ROLLBACK相当于一个标记点，凡是打此标记的地方，都会被自动回滚。如果没有ROLLBACK,那么即使是autocommit=0，即会被正常提交，无法实现数据的回滚。

COMMIT对于单个事务事实上可有可无。因为end$$会强制提交事务。但在多个事务处理时，必须在某个点提交，否则回滚时存在问题。

只是如果AUTOCOMMIT默认为1，即使有SQL语句包含在事务里边，也是每条自动提交。注意这个时间的提交只是当前连接的操作员自己在YY，因为没有COMMIT，无论AUTOCOMMIT的值等于多少，都会不提交到数据库，让别的人看到。但这个时候非常有意思，因为自动提交为1，如果此事务包括的存储过程被再次调用一次，由于又开始一个新事务，会自动提交上一次的那个没有COMMIT的事务。所以就危害性来说，SETAUTOCOMMIT=0危害性要大得多。

即使COMMIT封闭了START TRANSACTION也无法自动把 AUTOMMIT自动置为1，这就是解释陈良允同学为什么那次操作，我无法看到结果，因为它的连接已经执行，却没有自动提交。可以在他的连接上看到，因为对他来说只是一个临时操作。

事务的开始、回滚、提交与否，与AUTOCOMMIT=0的值的改变没有任何关系！所以高度注意：前提是置AUTOCOMMIT=0，这时如果没有封闭一个事务是极其危险的，因为它非常容易引起锁表，这样影响就是全局性的。注：虽然对于每个连接@@autocommit这个这个系统变量是独立的，但是锁表是全局性的。所以正规操作，在一次存储过程中调用，不管事务提交几次，只需在整个存储过程开端设置一次AUTOCOMMIT=0即可，在整个存储过程结束后单独设置一次AUTOCOMMIT=1即可。难怪我以前写的程序会出现：

Error Code : 1205

Lock wait timeout exceeded; tryrestarting transaction

连接A：UPDATE e_course SET agent_nick = '222' WHERE course_id =2;

连接B：UPDATE e_course SET agent_nick = '222' WHERE course_id =3;

即使不是更新的同一行，也会超时。是因为我的存储过程里边连接A对commit没有封闭那个事务，使得事务的set AUTOCOMMIT这个值被置为0，这样，对于这个表的后续操作，则被锁住了。如果此时直接关闭连接A，那么连接B就可以顺利提交。如果在存储过程里写SET AUTOCOMMIT=1; 那么即使没有commit也会解锁。如果保险起见，在存储过程结束应该写上SET AUTOCOMMIT=1; 这样可以避免此连接万一没有释放会造成表锁。注意，这个情况只对此表来说，换了其他的表，可以非常正常地更新。这种阻塞的情况，如果存储过程修改成COMMIT; 或者是 set AUTOCOMMIT=1; 那么被阻塞的语句为立刻执行。使用START TRANSACTION，autocommit仍然被禁用，直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。

mysql的autocommit（自动提交）默认是开启，其对mysql的性能有一定影响，举个例子来说，如果你插入了1000数据，mysql会commit1000次的，如果我们把autocommit关闭掉，通过程序来控制，只要一次commit就可以了。