MySQL 架构 - 事务处理

核心提示：事务处理在事务处理之前，你不能知道数据库系统有如此多的高级功能，MySQL 架构 - 事务处理，一个事物就是一组SQL查询，这一组被看做是原子的，可能有些意想不到的异常出现，所以我们建议除非你不使用事物和把AUTOCOMMIT关闭，也就是一个单独的工作单元，如果数据库引擎可以应用整个组的查询

事务处理

在事务处理之前，你不能知道数据库系统有如此多的高级功能。一个事物就是一组SQL查询。这一组被看做是原子的。也就是一个单独的工作单元。如果数据库引擎可以应用整个组的查询，就执行完毕。但是如果其中一条语句出现问题，整个组的语句都不会被执行。也就是要么全部执行，要么全部不执行。

这部分所讲到的事物很少是针对MySQL的，如果你已经熟悉了ACID事务处理，可以跳过这一部分。

银行的应用是解释为什么需要事务处理的经典案例。假设银行数据有两张表。checking以及savings.从Jane的checking账户转账200到她的saving账户。至少要三步

确定她的checking账户至少有200

从checking账户扣除200

把200添加到她的savings账户中。

整个操作封装为了一个事物。因此其中一个操作失败，整个操作都会回滚。

开始一个事物的语句是START TRANSACTION以及修改成功用COMMIT或者放弃改变用ROLLBACK。因此这个例子的SQL语句如下

Sql代码　　

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings  SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;

但是单独的事物并不能代表全部。如果在数据库服务器在执行到第四行的时候挂掉呢？用户可能就白白损失了200。以及如果有个处理出现在了3,4行之间，而不会去扣除checking帐户的余额。那么银行就白白给了用户200.

在系统没有通过ACID的测试之前，仅仅有事物还是不够的。ACID的意思是原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这些都是一个好的事务处理系统所要遵循的标准。

原子性(Atomicity）：

一个事物必须做为一个单独不可分割的工作单元来运行。因此整个事物要么全部成功要么全部失败。当事物具有原子性的时候，不能一部分的执行。要么全部执行，要么全部失败。

一致性（Consistency）：

数据库应该总是从一个一致的状态到另一个。在我们的例子中，一致性要确保程序在3,4行崩溃并不会使checking帐户减少200。因为事物没有提交。数据库没有任何变化。

隔离性（Isolation）：

当这个事物没有完成的时候，它的结果对于其他的事物是不可见的。这点确保了在运行完第3行，以及在第4行之前。200还是在checking帐户中。当我们讨论隔离性的时候，我们常常用到不可见（invisible）这个词。

持久性（Durability）：

当事物提交，这个事物的改变就被持久化了。意思就是更改被记录了。数据不会丢失了。持久性是个挺模糊的概念。因为它有很多级别。一些持久性策略比其他的有更强的安全性保证。但是绝对没有100%的持久。我们会在以后的章节讨论在MySQL中持久性的意思。

ACID事物保证了银行不会损失钱。这点在业务逻辑上很难或者根本做不到。一个有ACID的数据库服务器已经把各种各样复杂的事情都解决了，使你没有必要自己去确保ACID。

随着锁的颗粒度上升，数据库服务器要在安全性上做更多的工作了。数据库的ACID事物也需要更多的CPU，内存，硬盘空间。我们多次说过MySQL存储引擎架构的优势。你可以决定到底是否使用事物。如果不需要可以选择更好的没有事务支持的存储引擎。在没有事物的时候，你可以使用LOCK TABLES来保护数据。决定权在于你。

隔离级别

隔离要比看上去复杂的多。SQL定义了4种隔离级别。这些规则让更改对事物的内部和外部可见和不可见。低级别的隔离可以高并发低消耗。

（每个存储引擎的隔离级别实现都是不同的，如果你过去经常使用其他数据库产品，存储引擎不一定符合你的需求，你应该看手册决定使用哪个存储引擎。）

未提交读(READ UNCOMMITTED)

在这个级别中，事物可以看到未提交事物的结果。这个级别有很多问题会发生，除非你真的真的明白你在做什么以及有足够的理由去做。这级别在实践中很少使用。因为性能相对于其他级别也没什么优势。读取未提交的数据，也叫脏读（dirty read）

已提交读（READ COMMITTED）

有许多数据库系统默认的隔离级别都是已提交读（MySQL并不是）。它满足了早期使用的隔离简单定义：一个事物可以看到事物提交后的改变。这种改变在提交之前对于其他事物是不可见的。这级别也经常叫做不可重复读（nonrepeatable read）。意思就是你运行同一语句两次，可以看到不同的数据。

可重复读（REPEATABLE READ）

可重复读解决了未提交读的问题。它保证了在同一个事物中，连续任意行的读的数据都是相同的。但这种方式也引起了其他恶心的问题。幻读（phantom reads.）。简单地说就是，当你选择一定范围的行，另一个事物想这个范围新增加了一行。之后有查询相同的范围。你就会发现出现了像幻觉一样的一行。InnoDB和Falcon用多版本并发控制来解决了幻读的问题。

可重复读是MySQL默认的事物隔离级别。InnoDB和Falcon也是遵循这个设置。以后会讲到怎样修改这个设置。其他的引擎也是这样的，但是决定权在于引擎。

可序列化（SERIALIZABLE）

最高的隔离级别。解决的幻读的问题。强迫事物是有序的。因此他们不可能冲突。简单的意思就是可序列化把每一行加一个锁。这一级别许多超时和锁的竞争将出现。我们很少看见人们使用这个级别的隔离。你的应用也有可能强迫你把隔离级别设置那么高，而忽视并发性。重视数据的稳定性。

隔离级别	脏读	不可重复读	幻读	读取锁
未提交读	是	是	是	否
已提交读	否	是	是	否
可重复读	否	否	是	否
可序列化	否	否	否	是

死锁（Deadlocks）

死锁就是当两个以上的事物相互的等待和请求同一资源，产生了循环依赖就导致死锁。死锁存在事物尝试不同的顺序去锁定资源的时候。在任何时候多事物锁定同一资源都会发生死锁。举个例子。。有两个事物运行在StockPrice的表。

事物一

Sql代码　　

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;

事物二

Sql代码　

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;

　

如果你足够的倒霉，可能会发生如下情况。每个事物都完成第一条语句更新了StockPrice表。给它加了锁。每个事物都试图执行第二行。发现它被加锁了。这两个事物都会等待除自己另外一个事物结束。除非有操作能破坏这个死锁状况。

为了解决这个问题，数据库系统实现了许多种死锁的检测和超时处理。在更复杂的系统中，如InnoDB存储引擎。会发现循环依赖并且及时的返回错误。真是非常好的方式，否则死锁会降低语句的执行效率。其他的方式是在锁等待超时之后，放弃操作。这个方式不太好。InnoDB处理死锁的方式是，回滚有最少行级锁的事物。

锁的行为和顺序是存储引擎特定的。因此有些存储引擎可能在特定的一系列语句发生死锁，其他的也许不会。死锁有双重特性：一些是不可避免的，因为确实是数据冲突。一些是由于存储引擎工作方式所引起的。

事务日志

事物日志的帮助可以让事物更有效率。存储引擎更改存储在内存中的数据拷贝，取代每次修改都要更新硬盘上的表。这种方法非常高效。之后，存储引擎就向事务日志添加一条记录。这个日志存放在硬盘中，因此是持久的。这个操作相对来说能快点。因为附加记录事件使用小范围的连续IO取代了大范围的随机IO。之后，在晚一点，会更新硬盘上的表。所以大部分存储引擎都使用这个技术（write-ahead logging 预写式记录），会两次向硬盘写入更改的信息。

在更新日志之后，更改数据之前发生了错误。存储引擎仍然在重启后恢复更改。各个存储引擎的恢复方法各不相同。

MySQL中的事物

MySQL提供了三种支持事物的存储引擎：　InnoDB, NDB Cluster, Falcon。许多第三方的引擎也可以使用，比较有名的就是solidDB 以及PBXT。在下一部分会详细介绍这些存储引擎。

AUTOCOMMIT

MySQL的AUTOCOMMIT是默认的。意思就是无论你是否开始一个事物，在每个语句都会自动执行。当然你可以设置这个AUTOCOMMIT的变量，方法如下

1和ON是一样的。0也就是OFF。当AUTOCOMMIT=0的时候，你必须COMMIT或ROLLBACK，不然的话你就总在一个事物中，语句不会执行。如果表的存储引擎不支持事物，改变AUTOCOMMIT的值，不会有任何的效果。这些存储引擎是MyISAM或者Memory.它们总是自动提交。

一些命令，当开始一个事物，在执行之前，MySQL会自动提交事物。这些命令就是DDL。如果ALTER TABLE等，还有一些特殊如LOCK TABLES也会有这样的效果。具体的查看数据库版本的文档。来查看哪些命令是自动提交的。

MySQL允许设置隔离级别。命令是SET TRANSACTION ISOLATION LEVEL 。这会影响下一个事物的开始时间。你也可以通过配置文件来设置整个服务器的隔离级别，这个将在以后说。也可以针对当前会话。如

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL能识别所有标准的隔离级别，以及InnoDB存储引擎支持这些隔离级别。其他的存储引擎不同程度上支持不同的隔离级别。

在事物中混合使用存储引擎

在服务器级别，MySQL不能管理事物。而事物是由存储引擎所实现的。意思就是你不能在一个单独事物中混合使用不同的存储引擎。MySQL AB给服务器添加了一个更高级别的事务管理服务。这样就会在一个事物中，混合和匹配事务表更加安全。在此之前一定要小心。

如果在一个事物中混合了事物和非事物表。如果一切正常，这个事物就没有问题，但是如果执行回滚操作，非事物表改变的数据并不会回滚。数据库的一致性遭到了破坏，很难恢复和呈现完整的事物。这就是为什么给表选择存储引擎是如此的重要。

如果你在非事物表上做事物操作，MySQL并不会提示或者抛出异常。有的时候，回滚会有一定的提示。“Some nontransactional changed tables couldn’t be rolled back”。但是一般都不会有什么提示。

隐式和显式的锁定

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.这没什么必要，因为行级别的锁定。LOCK TABLE也会引起性能问题。

（LOCK TABLES和事物之间的交互是复杂的。可能有些意想不到的异常出现。所以我们建议除非你不使用事物和把AUTOCOMMIT关闭，否则永远不要使用LOCK TABLES。）