MySQL之架构设计与历史(三)-EW帮帮网

MySQL之架构设计与历史

死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定一个资源时，也会产生死锁。例如，设想下面两个事务同时处理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目前处理死锁的方法时，将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。
锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因:有些是因为真正的数据冲突，这种情况通常很难避免，但有些完全是由于存储引擎的实现方式导致的。死锁放生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头，所以次啊用事务日志的方式相对来说要快得多。事务日志持久化以后，内存中被修改得数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现地，我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。

如果数据地修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动回复这部分修改地数据。具体的回复方式则视存储引擎而定。

MySQL中的事务

MySQL提供了两种事务型的存储引擎:InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT.

1.自动提交(AUTOCOMMIT):
MySQL默认采用自动提交(AUTOCOMMIT)模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式:

mysql > SHOW VARIABLES LIKE 'AUTOCOMMIT';
Variable_name:autocommit
Value: ON
mysql> SET AUTOCOMMIT = 1;

1或者ON表示启用，0或者OFF表示禁用。当AUTOCOMMIT=0时，所有地查询都是在一个事务中，直到显式地执行COMMIT提交或者ROLLBACK回滚，该事务结束，同时又开始了另一个新事务。修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会又任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，也可以说是相当于一直处于AUTOCOMMIT启用的模式。

另外还有一些命令，在执行之前回强制执行COMMIT提交当前的活动事务。典型的例子，在数据定义语言(DDL)中，如果是回导致大量数据改变的操作，比如ALTER TABLE，就是如此。另外还有LOCK TABLES等其他语句也会导致同样的结果。如果有需要，请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一次事务开始的时候生效。可以在配置文件中设置整个数据库的隔离界别，也可以只改变当前会话的隔离级别:

mysql>SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL能够识别所有的4个ANSI隔离级别，InnoDB尹千影也支持所有的隔离界别。

2.在事务中混合使用存储引擎:
MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表(例如InnoDB和MyISAM表)，在正常提交的情况下不会有什么问题，但如果该事务需要回滚，非事务型表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以为每张表选择合适的存储引擎非常重要。
在非事务型的表上执行事务相关操作的时候，MySQL通常不会发出提醒，也不会报错。有时候只有回滚的时候才会发出一个警告:“某些非事务型的表上的变更不能被回滚”。但大多数情况下，对非事务型表的操作都不会有提示。
3.隐式和显式锁定:
InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，锁只有执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。另外，InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范(这些锁定提示经常被滥用，实际上应当尽量避免使用)
3.1 SELECT … LOCK IN SHARE MODE
3.2 SELECT … FOR UPDATE
MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，和存储引擎无关。它们有自己的用途，但并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。经常可以发现，应用已经将表从MyISAM转换到InnoDB，但还是显式地使用LOCK TABLES语句。这不但没有必要，还会严重影响性能，实际上InnoDB的行级锁工作得更好。

LOCK TABLES和事务之间相互影响得化，情况会变得非常复杂，在某些MySQL版本中甚至会产生无法预料的结果。因此，建议除了事务中禁用AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎。

MySQL之架构设计与历史(三)