MySQL基础知识点整理——事务一、简介1、什么是事务？事务是一组原子SQL查询，或一个独立的工作单元。如果数据库引擎能够成功地将查询集的所有语句应用到数据库，则执行查询集。如果由于崩溃或其他原因无法执行这些语句中的任何一个，则不会执行任何语句。也就是说，事务中的语句要么全部执行成功，要么全部执行失败。在整个过程中，无论事务是否成功完成，始终确保数据完整性。2.如何使用交易银行应用程序是解释交易必要性的经典示例。假设一家银行的数据库有两个表：一个支票表和一个储蓄表。现在要从用户的活期账户转200元到他的储蓄账户，那么至少需要三个步骤：1.查看活期账户的余额是否高于200元。2.从您的支票账户余额中减去200。3.将200添加到您的储蓄账户余额中。以上三个步骤的操作必须打包在一个事务中。如果任何步骤失败，则必须回滚所有步骤。您可以使用STARTTRANSACTION语句启动事务，然后使用COMMIT提交事务以持久保存修改的数据，或者使用ROLLBACK撤消所有修改。示例交易SQL如下：/*STARTTRANSACTION*/STARTTRANSACTION;/*检查支票账户余额是否高于$200*/SELECTbalanceFROMcheckingWHEREcustomer_id=10233276;/*从支票账户余额中减去$200*/UPDATEcheckingSETbalance=balance-200.00WHEREcustomer_id=10233276;/*储蓄账户余额增加200元*/UPDATEsavingsSETbalance=balance+200.00WHEREcustomer_id=10233276;/*提交交易*/COMMIT;ACID的特性ACID意味着原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个运行良好的事务处理系统必须具备这些标准特征。1.原子性（atomicity）一个事务必须被看作是一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行一部分操作，这就是事务的原子性。2.一致性（consistency）数据库总是从一种一致状态过渡到另一种一致状态。在前面的例子中，一致性确保即使系统在执行第三条和第四条语句之间崩溃，也不会在支票账户中损失200，因为交易最终没有提交，所以交易中所做的更改不会迷路了。将被保存到数据库中。3.隔离（isolation）通常来说，一个事务所做的修改在最后提交之前对其他事务是不可见的。前面的例子，当第三条语句执行完，第四条语句还没有开始的时候，另一个账户汇总程序此时开始运行，它看到的活期账户余额还没有减去200元。4.持久性一旦事务被提交，它的修改将被永久保存到数据库中。这时，即使系统崩溃，修改的数据也不会丢失。三、事务隔离级别1.READUNCOMMITTED（读未提交）在READUNCOMMITTED级别，一个事务中所做的修改即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据，这也称为脏读（DirtyRead）。这个级别会导致很多问题。在性能上，READUNCOMMITTED不会比其他级别好多少，但是它缺少其他级别的很多好处。除非有非常必要的原因，一般在实际应用中很少使用。.2.READCOMMITTED（提交读）大多数数据库系统默认的隔离级别是READCOMMITTED（但MysQL不是）。READCOMMITTED满足前面提到的隔离的简单定义：事务开始时，只能“看到”已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前所做的任何修改对其他事务都是不可见的。这个级别有时被称为不可重复读（nonrepeatableread），因为两次执行同??一个查询可能会得到不同的结果。3.REPEATABLEREAD（可重复读）REPEATABLEREAD解决了脏读问题。该级别保证在同一个事务中多次读取同一条记录的结果是一致的。但理论上，可重复读隔离级别仍然无法解决另一个幻读（PhantomRead）问题。所谓幻读是指当一个事务读取了一个范围内的记录时，另一个事务在该范围内插入了一条新记录，当前一个事务再次读取该范围内的记录时，就会产生PhantomRow。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，MultiversionConcurrencyControl）解决幻读问题。可重复读是MySQL默认的事务隔离级别。4.SERIALIZABLE（可序列化）SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行来避免上面提到的幻读问题。简单的说，SERIALIZABLE会在读取的每一行数据上加锁，所以可能会造成很多超时和锁争用的问题。这种隔离级别在实际应用中很少使用。只有在非常需要保证数据一致性，并且不能接受并发的情况下才会考虑。四种隔离级别比较脏读可能性不可重复读可能性幻读可能性锁定读READUNCOMMITTED√√√×READCOMMITTED×√√×REPEATABLEREAD××√×SERIALIZABLE×××√4.死锁1、什么是死锁死锁是指两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，造成恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，可能会发生死锁。当多个事务同时锁定同一资源时，也会发生死锁。2.死锁示例假设以下两个事务同时处理StockPrice表：事务1:STARTTRANSACTION;UPDATEStockPriceSETclose=45.50WHEREstock_id=4anddate='2002-05-01';UPDATEStockPriceSETclose=19.80WHEREstock_id=3anddate='2002-05-02';COMMIT;Transaction2STARTTRANSACTION;UPDATEStockPriceSEThigh=20.12WHEREstock_id=3anddate='2002-05-02';UPDATEStockPriceSEThigh=47.20WHEREstock_id=4和date='2002-05-01'；犯罪;如果碰巧两个事务都执行了第一个UPDATE语句，更新了一行数据，同时也锁定了这行数据，那么每个事务都尝试执行第一个UPDATE语句和两个UPDATE语句，但是发现该行已经被对方，然后两个事务都等待对方释放锁，同时持有对方要求的锁，然后陷入死循环。除非有外部因素介入，才有可能解除僵局。3、如何解决死锁问题为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。更复杂的系统，如InnoDB存储引擎，更能够检测死锁循环依赖并立即返回错误。这个解决方案效果很好，否则死锁会导致查询非常慢。另一种解决方案是当查询时间达到锁定等待超时设置时放弃锁定请求，这通常不太好。InnoDB目前通过回滚持有最少行级独占锁的事务来处理死锁（这是一种相对简单的死锁回滚算法）。锁的行为和顺序取决于存储引擎。按照同样的顺序执行语句，有的存储引擎会死锁，有的则不会。死锁的产生有两个原因：一些是由于真实的数据冲突，这通常很难避免，但一些纯粹是由于存储引擎的实现方式。发生死锁后，只有其中一个事务的部分或完全回滚才能打破死锁。对于事务系统来说，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下，只需要重新执行因死锁而回滚的事务即可。五、MySQL事务的特点1.自动提交（AUTOCOMMIT）MySQL默认采用自动提交（AUTOCOMMIT）模式。也就是说，除非显式启动事务，否则每个查询都会像事务一样提交。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式：mysql>SHOWVARIABLESLIKE'AUTOCOMMIT';+----------------+-------+|变量名|值|+----------------+------+|自动提交|ON|+---------------+--------+1rowinset(0.00sec)mysql>SETAUTOCOMMIT=0;QueryOK,0rowsaffected(0.00sec)mysql>显示像“自动提交”这样的变量；+-------------+--------+|变量名|值|+----------------+--------+|自动提交|OFF|+----------------+--------+1rowinset(0.00sec)mysql]>SETAUTOCOMMIT=1;QueryOK,0rowsaffected(0.00秒）mysql>显示像“自动提交”这样的变量；+----------------+--------+|变量名|价值|+----------------+--------+|自动提交|ON|+----------------+--------+1rowinset(0.00sec)1或ON表示启用，0或OFF表示禁用。当AUTOCOMMIT=0时，所有查询都在一个事务中，直到显式执行COMMIT提交或ROLLBACK，事务结束，同时开始另一个新的事务。修改AUTOCOMMIT对非事务表没有影响，例如MyISAM或内存表。对于这种类型的表，没有COMMIT或者ROLLBACK的概念，可以说相当于一直处于启用AUTOCOMMIT的模式。还有一些命令强制COMMIT在执行之前提交当前活动的事务。一个典型的例子，在数据定义语言（DDL）中，如果是一个会引起大量数据变化的操作，比如ALTERTABLE，就是这种情况。还有其他语句（例如LOCKTABLES）可以导致相同的结果。如有需要，请查看对应版本的官方文档，确认所有可能导致自动提交的语句列表。MySQL可以通过执行SETTRANSACTIONISOLATIONLEVEL命令来设置隔离级别。新的隔离级别将在下一个事务开始时生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：mysql>SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;2.隐式和显式锁定InnoDB使用了两阶段锁定协议（two-phaselockingprotocol）。在事务执行过程中，可以随时进行加锁。只有在执行COMMIT或ROLLBACK时才会释放锁，所有的锁会同时释放。上面介绍的锁都是隐式锁，InnoDB会根据隔离级别在需要的时候自动加锁。此外，InnoDB还支持通过特定语句进行显式锁定，不属于SQL规范（这些锁定提示经常被滥用，实际上应该尽可能避免）SELECT...LOCKINSHAREMODESELECT...FORUPDATEMySQL还支持LOCKTABLES和UNLOCKTABLES语句，都是在server层实现的，与存储引擎无关。它们有自己的用途，但不能替代事务处理。如果应用需要使用事务，还是应该选择事务型存储引擎。经常发现应用程序已经将表从MyISAM转换为InnoDB，但仍然显式使用LOCKTABLES语句。这不仅是不必要的，而且会严重影响性能，而InnoDB的行级锁实际上工作得更好。LOCKTABLES和事务之间的交互会变得非常复杂，甚至在某些MySQL版本中会产生不可预测的结果。因此，建议无论使用什么存储引擎，任何时候都不要显式执行LOCKTABLES，除非在事务中禁用AUTOCOMMIT，可以使用LOCKTABLES。参考《高性能MySQL》
