新购 续费 升级
超多折扣优惠
阿里云服务器限时两折起
年付每月仅需24元,低至0.73元/天起
阿里云服务器ECS    
弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新 [咨询更多]
阿里云存储OSS
简单易用、多重冗余、数据备份高可靠、多层次安全防护安全性更强、低成本 [咨询更多]
阿里云数据库RDS
稳定可靠、可弹性伸缩、更拥有容灾、备份、恢复、监控、迁移等方面的全套解决方案 [咨询更多]
阿里云安全产品
DDoS高防IP、web应用防火墙、安骑士、sll证书、态势感知众多阿里云安全产品热销中 [咨询更多]
阿里云折扣优惠    
云服务器ECS、数据库、负载均衡等产品新购、续费、升级联系客服获取更多专属折扣 [咨询更多]
MySQL事务
2020-9-9
  MySQL事务  在理解事务的概念之前,接触数据库系统的其他高级特性还言之过早。事务就是一组原子性的SQL查询,或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句,那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行,那么所有的语句都不会执行。也就是说,事务内的语句,要么全部执行成功,要么全部执行失败。
  
  银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表:支票(checking)表和储蓄(savings)表。现在要从用户Jane的支票账户转移200美元到她的储蓄账户,那么需要至少三个步骤:
  
  1.检查支票账户的余额高于200美元。
  
  2.从支票账户余额中减去200美元。
  
  3.在储蓄账户余额中增加200美元。
  
  上述三个步骤的操作必须打包在一个事务中,任何一个步骤失败,则必须回滚所有的步骤。
  
  可以用START TRANSACTION语句开始一个事务,然后要么使用
  
  COMMIT提交事务将修改的数据持久保留,要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下:  
    1 START TRANSACTION;
    2 SELECT balance FROM checking WHERE customer_id = 10233276;
    3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
    4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
    5 COMMIT;

  单纯的事务概念并不是故事的全部。试想一下,如果执行到第四条语句时服务器崩溃了,会发生什么?天知道,用户可能会损失200美元。再假如,在执行到第三条语句和第四条语句之间时,另外一个进程要删除支票账户的所有余额,那么结果可能就是银行在不知道这个逻辑的情况下白白给了Jane 200美元。
  
  除非系统通过严格的ACID测试,否则空谈事务的概念是不够的。ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。一个运行良好的事务处理系统,必须具备这些标准特征。
  
  原子性(atomicity)
  
  一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性。
  
  现这一点非常难,甚至可以说是不可能完成的任务。一个兼容ACID的数据库系统,需要做很多复杂但可能用户并没有觉察到的工作,才能确保ACID的实现。
  
  就像锁粒度的升级会增加系统开销一样,这种事务处理过程中额外的安全性,也会需要数据库系统做更多的额外工作。一个实现了ACID的数据库,相比没有实现ACID的数据库,通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。正如本章不断重复的,这也正是MySQL的存储引擎架构可以发挥优势的地方。用户可以根据业务是否需要事务处理,来选择合适的存储引擎。对于一些不需要事务的查询类应用,选择一个非事务型的存储引擎,可以获得更高的性能。即使存储引擎不支持事务,也可以通过LOCK TABLES语句为应用提供一定程度的保护,这些选择用户都可以自主决定。
  
  1 隔离级别
  
  隔离性其实比想象的要复杂。在SQL标准中定义了四种隔离级别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低。
  
  每种存储引擎实现的隔离级别不尽相同。如果熟悉其他的数据库产品,可能会发现某些特性和你期望的会有些不一样。
  
  下面简单地介绍一下四种隔离级别。
  
  READ UNCOMMITTED(未提交读)
  
  在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读(Dirty Read)。这个级别会导致很多问题,从性能上来说,READ UNCOMMITTED不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。
  
  READ COMMITTED(提交读)
  
  大多数数据库系统的默认隔离级别都是READ COMMITTED(但MySQL不是)。READ COMMITTED满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。
  
  REPEATABLE READ(可重复读)
  
  REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。
  
  InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)解决了幻读的问题。本章稍后会做进一步的讨论。
  
  可重复读是MySQL的默认事务隔离级别。
  
  SERIALIZABLE(可串行化)
  
  SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单来说,SERIALIZABLE会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。
  
  SERIALIZABLE(可串行化)
  
  SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单来说,SERIALIZABLE会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。
  
  表1-1:ANSI SQL隔离级别
死锁
  
  2 死锁
  
  死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。例如,设想下面两个事务同时处理StockPrice表:
  
  事务1
    START TRANSACTION;
    UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
    UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';

   COMMIT;

  事务2

    START TRANSACTION;
    UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
    UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
    COMMIT;

  如果凑巧,两个事务都执行了第一条UPDATE语句,更新了一行数据,同时也锁定了该行数据,接着每个事务都尝试去执行第二条UPDATE语句,却发现该行已经被对方锁定,然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环。除非有外部因素介入才可能解除死锁。
  
  为了解决这种问题,数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统,比如InnoDB存储引擎,越能检测到死锁的循环依赖,并立即返回一个错误。这种解决方式很有效,否则死锁会导致出现非常慢的查询。还有一种解决方式,就是当查询的时间达到锁等待超时的设定后放弃锁请求,这种方式通常来说不太好。InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。
  
  锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句,有些存储引擎会产生死锁,有些则不会。死锁的产生有双重原因:有些是因为真正的数据冲突,这种情况通常很难避免,但有些则完全是由于存储引擎的实现方式导致的。
  
  死锁发生以后,只有部分或者完全回滚其中一个事务,才能打破死锁。对于事务型的系统,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。
  
  3 事务日志
  
  事务日志可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。事务日志持久以后,内存中被修改的数据在后台可以慢 慢地刷回到磁盘。目前大多数存储引擎都是这样实现的,我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。
  
  如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。
  
  4 MySQL中的事务
  
  MySQL提供了两种事务型的存储引擎:InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务,比较知名的包括XtraDB和PBXT。后面将详细讨论它们各自的一些特点。
  
  自动提交(AUTOCOMMIT)
  
  MySQL默认采用自动提交(AUTOCOMMIT)模式。也就是说,如果不是显式地开始一个事务,则每个查询都被当作一个事务执行提交操作。在当前连接中,可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式:
  启用或者禁用自动提交模式
  1或者ON表示启用,0或者OFF表示禁用。当AUTOCOMMIT=0时,所有的查询都是在一个事务中,直到显式地执行COMMIT提交或者ROLLBACK回滚,该事务结束,同时又开始了另一个新事务。修改AUTOCOMMIT对非事务型的表,比如MyISAM或者内存表,不会有任何影响。对这类表来说,没有COMMIT或者ROLLBACK的概念,也可以说是相当于一直处于AUTOCOMMIT启用的模式。
  
  另外还有一些命令,在执行之前会强制执行COMMIT提交当前的活动事务。典型的例子,在数据定义语言(DDL)中,如果是会导致大量数据改变的操作,比如ALTER TABLE,就是如此。另外还有LOCK TABL TABLES等其他语句也会导致同样的结果。如果有需要,请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。
  
  MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别,也可以只改变当前会话的隔离级别:
  
  mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  MySQL能够识别所有的4个ANSI隔离级别,InnoDB引擎也支持所有的隔离级别。
  
  在事务中混合使用存储引擎
  MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中,使用多种存储引擎是不可靠的。
  
  如果在事务中混合使用了事务型和非事务型的表(例如InnoDB和MyISAM表),在正常提交的情况下不会有什么问题。
  
  但如果该事务需要回滚,非事务型的表上的变更就无法撤销,这会导致数据库处于不一致的状态,这种情况很难修复,事务的最终结果将无法确定。所以,为每张表选择合适的存储引擎非常重要。
  
  在非事务型的表上执行事务相关操作的时候,MySQL通常不会发出提醒,也不会报错。有时候只有回滚的时候才会发出一个警告:“某些非事务型的表上的变更不能被回滚”。但大多数情况下,对非事务型表的操作都不会有提示。
  
  隐式和显式锁定
  

  InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定,InnoDB会根据隔离级别在需要的时候自动加锁。
  
  另外,InnoDB也支持通过特定的语句进行显式锁定,这些语句不属于SQL规范:
  
  •SELECT ... LOCK IN SHARE MODE
  
  •SELECT ... FOR UPDATE
  
  MySQL也支持LOCK TABLES和UNLOCK TABLES语句,这是在服务器层实现的,和存储引擎无关。它们有自己的用途,但并不能替代事务处理。如果应用需要用到事务,还是应该选择事务型存储引擎。
  
  经常可以发现,应用已经将表从MyISAM转换到InnoDB,但还是显式地使用LOCK TABLES语句。这不但没有必要,还会严重影响性能,实际上InnoDB的行级锁工作得更好。
  
  LOCK TABLES和事务之间相互影响的话,情况会变得非常复杂,在某些MySQL版本中甚至会产生无法预料的结果。因此,建议,除了事务中禁用了AUTOCOMMIT,可以使用LOCK TABLES之外,其他任何时候都不要显式地执行LOCK TABLES,不管使用的是什么存储引擎。
联系客服免费领取更多阿里云产品新购、续费升级折扣,叠加官网活动折上折更优惠