在 InnoDB 事务处理模式中， the goal has been to combine the best properties of a multiversioning database to traditional two-phase locking. InnoDB 进行行级的锁定，并以与 Oracle 非锁定读取(non-locking)类似的方式读取数据。 InnoDB 中的锁定表的存储是如此(space-efficiently)而不再需要扩大锁定： 典型特色是一些用户可能锁定数据库中的任意行或任意行的子集，而不会引起 InnoDB 内存运行溢出。
在 InnoDB 中，所有的用户操作均是以事务方式处理的。如果 MySQL 使用了自动提交(autocommit)方式，每个 SQL 语句将以一个单独的事务来处理。MySQL 通常是以自动提交方式建立一个服务连接的。
如果使用 SET AUTOCOMMIT = 0 关闭自动提交模式，就认为用户总是以事务方式操作。如果发出一个 COMMIT 或 ROLLBACK 的 SQL 语句，它将停止当前的事务而重新开始新事务。两个语句将会释放所有在当前事务中设置的 InnoDB 锁定。COMMIT 意味着永久改变在当前事务中的更改并为其它用户可见。ROLLBACK如果以 AUTOCOMMIT = 1 建立一个连接，那么用户仍然可以通过以 BEGIN 开始和 COMMIT 或 ROLLBACK在 SQL-1992 事务隔离级(transaction isolation levels)规定的条款中，InnoDB 默认为 REPEATABLE READ。从 4.0.5 开始， InnoDB 提供了 SQL-1992 标准中所有的 4 个不同的事务隔离级。你可以 my.cnf 的 [mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
用户也可以通过下面的 SQL 语句为单个连接或所有新建的连接改变隔离级：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}注意在这个 SQL 语句的语法中没有连字符。如果你在上述语句中详细指定关键字 GLOBAL ，它将决定新建连接的初始隔离级，但不会改变已有连接的隔离级。任何用户均可以更改自身会话的隔离级，即使是在一个事务处理过程中。在 3.23.50 以前的版本中 SET TRANSACTION 对 InnoDB 表无任何效果。在 4.0.5 以前的版本中只有 REPEATABLE READ 和SERIALIZABLE
可以通过下列语句查询全局和当前会话的事务隔离级：
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
在 InnoDB 的行锁中使用所谓的 next-key locking。这就意味着，除了索引记录外，InnoDB 还可以锁定该索引记录前部“间隙” ('gap') 以阻塞其它用户在索引记录前部的直接插入。next-key lock 意思是锁定一个索引记录以及该记录之前的间隙(gap)。gap lock 就是只锁定某些索引记录之前的间隙。
InnoDB 中的隔离级详细描述：
READ UNCOMMITTED
- 这通常称为 'dirty read'：non-locking
SELECT
- s 的执行使我们不会看到一个记录的可能更早的版本；因而在这个隔离度下是非 'consistent' reads；另外，这级隔离的运作如同
READ COMMITTED
- 。
READ COMMITTED
- 有些类似 Oracle 的隔离级。所有
SELECT ... FOR UPDATE
- 和
SELECT ... LOCK IN SHARE MODE
- 语句只锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。以一个唯一地搜索条件使用一个唯一索引(unique index)的
UPDATE
- 和
DELETE
- ，仅仅只锁定所找到的索引记录，而不锁定该索引之前的间隙。但是在范围型的
UPDATE
- and
DELETE
- 中，InnoDB 必须设置 next-key 或 gap locks 来阻塞其它用户对范围内的空隙插入。 自从为了 MySQL 进行复制(replication)与恢复(recovery)工作'phantom rows'必须被阻塞以来，这就是必须的了。Consistent reads 运作方式与 Oracle 有点类似： 每一个 consistent read，甚至是同一个事务中的，均设置并作用它自己的最新快照。
REPEATABLE READ
- 这是 InnoDB 默认的事务隔离级。.
SELECT ... FOR UPDATE
- ,
SELECT ... LOCK IN SHARE MODE
- ,
UPDATE
- , 和
DELETE
- ，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。 否则这些操作将使用 next-key 锁定，以 next-key 和 gap locks 锁定找到的索引范围，并阻塞其它用户的新建插入。在 consistent reads 中，与前一个隔离级相比这是一个重要的差别： 在这一级中，同一事务中所有的 consistent reads 均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的
SELECT
- s ，这些
SELECT
- s 的相互关系是一致的。
SERIALIZABLE
- 这一级与上一级相似，只是无格式(plain)的
SELECT
- s 被隐含地转换为
SELECT ... LOCK IN SHARE MODE
- 。
8.1 Consistent read
Consistent read 就是 InnoDB 使用它的多版本(multiversioning)方式提供给查询一个数据库在一个时间点的快照。 查询将会检查那些在这个时间点之前提交的事务所做的改动，以及在时间点之后改变或未提交的事务? 与这个规则相例外的是查询将检查查询自身发出的事务所做的改变。
如果以默认的 REPEATABLE READConsistent read 在 InnoDB 处理 SELECT 中的默认模式是 READ COMMITTED 和 REPEATABLE READ
8.2 Locking reads
Consistent read 在某些情况下是不太方便的。 假设你希望在表 CHILD 中插入 一个新行，而这个子表已有一个父表 PARENT。假设你使用 consistent read 了读取表 PARENT 并查看子表中对应记录。你真的能安全地在表 CHILD 中加入一个子行？不可能，因为在此期间可能有其它用户删除了表 PARENT解决的办法就是在锁定的方式 LOCK IN SHARE MODE 下运行一个 SELECT
SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;在共享模式下执行读取的意思就是读取最新的现有资料，并在所读取的行上设置一个共享模式的锁定。如果最新的数据属于其它用户仍未提交的事务，那将不得不等到这个事务被 提交 。共享模式的可以防止其它用户更新或删除我们当前所读取的行。当查询获得 'Jones'后，就可以安全地向子表 CHILD 中加入子行，然后提交事务。 这个例子显示如何在应用程序代码中实现参照完整性。另外一个例子： 在表 CHILD_CODES 有一个整型计数字段用于给在表 CHILD 中加入的每个子行赋于一个唯一的标识符。 显而易见地，用一个 consistent read 来读取父表中的值并不是一个好的主意，因两个用户有可能会读取出同一个计数值，当以同一个标识符插入两个字行时将会产生一个重复键值(duplicate key)的错误。如果两个用户同时读取了计数器，当尝试更新计数器时，他们中的一个必将在死锁中结束，所以在读取时使用 LOCK IN SHARE MODE在这和情况下有两种方法来实现读取并增加计数器：(1) 首先更新计数器然后再读取它；(2) 首先以一个 FOR UPDATE
SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;SELECT ... FOR UPDATE 将读取最新的现有数据，并在所读取的行上设置排它的锁定。同样在 SQL UPDATE
8.3 Next-key locking: avoiding the 'phantom problem'
在 InnoDB 的行级锁定上使用一个称作 next-key locking 算法。在 InnoDB 在搜索或扫描表的索引时将进行行锁，它将在所访问到的索引上设置共享或排它的锁定。因而行锁是更加精确地而又称为索引记录锁定。
InnoDB 在索引记录上设置的锁同样会影响索引记录之前的“间隙(gap)”。如果一个用户对索引记录 R 加了一个共享或排它的锁定，那其它用户将不能在 R 之前立即插入新的记录。这种间隙锁定用于防止所谓的“phantom problem”。假设需读取和锁定表 CHILD
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;假设表 CHILD 中有一个索引字段 ID。我们的查询将从 ID
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;在查询返回的记录集中将会有一个新的子记录。这与事务的隔离规则相违背的：一个事务必须能够顺串(run)， 因而在事务处理中所读取的数据将不会发生改变。而新的 'phantom' 子记录将会打破这个隔离规则。
当 InnoDB 扫描索引时，它同样会锁定在索引中在结尾记录(the last record)之后的间隙。这仅仅在上例中会发生： InnoDB 设置的锁定将阻止任何 ID
在应用程序中可以通过一个 next-key locking 来实现一个唯一性(uniqueness)检查：如果以一个共享模式读取数据并没有发现与将要插入的数据存在重复值， 那么在读取过程中 next-key lock 将被设置在你的记录的后继者(successor)上，这将阻止其它用户在期间插入相同的记录，因而你可以安全地插入你的记录。 所以， next-key locking 可以允许你 'lock' 你的表中并不存在的记录。
8.4 InnoDB
SELECT ... FROM ...
- : 这是一个 consistent read，不以锁定方式读取数据库的快照，除非事务的隔离级被设置为
SERIALIZABLE
- ，在这种情况下将在它所读取的记录索引上设置共享的 next-key locks。
SELECT ... FROM ... LOCK IN SHARE MODESELECT ... FROM ... FOR UPDATEINSERT INTO ... VALUES (...)
- 如果在一个表中定义了一个
AUTO_INCREMENT
- 列，InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时， InnoDB 将设置一个特殊的表锁定模式
AUTO-INCINSERT INTO T SELECT ... FROM S WHERE ...
- 在已插入到表
T
- 中的每个记录上设置一个独占的(无 next-key)锁定。以一个 consistent read 搜索表
S
- ，但是如果 MySQL 打开了日志开关将在表
SCREATE TABLE ... SELECT ...
- 与上项相似，以 consistent read 或锁定方式完成
SELECTREPLACEUPDATE ... SET ... WHERE ...DELETE FROM ... WHERE ...
- 如果一个表上有
FOREIGN KEYLOCK TABLES ...
- : 设置表锁定。在 MySQL 的代码层(layer of code)设置这些锁定。InnoDB 的自动死锁检测无法检测出有关下列情形的表锁定：查看下面的一个章节。同时查看第 14 章节 'InnoDB 限制与不足' 有关下列内容： 自从 MySQL 提供行锁以来，将有可能发生当其他用户设置了行级锁定时你又对该表设置了锁定。But that does not put transaction integerity into danger.
- 在 3.23.50 版本以前，
SHOW TABLE STATUS
- 应用于一个自增表时将在自增列的最大记录索引上设置一个独占的行级锁定。 这就意味着
SHOW TABLE STATUS
8.5 MySQL 什么时候隐含地提交(commit)或回滚(rollback)事务？
- 如果你不使用
SET AUTOCOMMIT=0
- ，MySQL 将会在一个会话中打开自动提交模式。在自动提交模式下，如果一条 SQL 语句没有返回任何错误，MySQL 将在这条 SQL 语句后立即提交。
- 如果一条 SQL 语句返回一个错误，那么 commit/rollback 依赖于这个错误。查看第国家13 章节详细描述。
- 下列的 SQL 语句在 MySQL 引起中当前事务的隐含提交：
CREATE TABLE
- (如果使用了 MySQL 二进制日志'binlogging'),
ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES
- 。 在 InnoDB 中
CREATE TABLE
- 语句是作为一个单独的事务来处理的。这就意味着一个用户无法在他的事务中使用
ROLLBACK
- 撤销
CREATE TABLE
- 如果你关闭了自动提交模式，而在关闭一个连接之前又未使用
COMMIT
8.6 死锁检测与回滚
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。从 4.0.5 版开始，InnoDB 将设法提取小的事务来进行回滚。一个事务的大小由它所插入(insert)、更新(update)和删除(delete)的数据行数决定。 Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions.
InnoDB 不能检测出由 MySQL 的 LOCK TABLES 语句引起的死锁，或其它的表类型中的锁定所引起的死锁。你不得不通过在 my.cnf 中设置 innodb_lock_wait_timeout
当 InnoDB 执行一个事务完整的回滚，这个事务所有所加的锁将被释放。然而，如果只一句的 SQL 语句因结果返回错误而进行回滚的，由这条 SQL 语句所设置的锁定可能会被保持。这是因为 InnoDB r的行锁存储格式无法知道锁定是由哪个 SQL 语句所设置。
8.7 consistent read 在 InnoDB
假设你以默认的 REPEATABLE READ 事务隔离级水平运行。当你发出一个 consistent read 时，即一个普通的 SELECT你可以通过提交你的事务并重新发出一个 SELECT
这就叫做 multiversioned 并发控制。
| | |
| | |
| | |
| | |
| | |
| | |
| |
因而，只有当用户 B 提交了他的插入，并且用户 A 也提交了他的事务从而使时间点越过 B 提交时的时间点之后，用户 A 才能看到用户 B 所插入的新行。如果你希望查看数据库“最新的(freshest)”状态，你必须使用 READ COMMITTED
SELECT * FROM t LOCK IN SHARE MODE;
8.8 如何应付死锁？
死锁是事务处理型数据库系统的一个经典问题，但是它们并不是很危险的， 除非它们如此地频繁以至于你根本处理不了几个事务。 当因死锁而产生了回滚时，你通常可以在你的应用程序中重新发出一个事务即可。
InnoDB 使用自动地行级锁定。你可能恰好在插入或删除单一一条记录时产生死锁。 这是因为这些操作并不是真正“原子(atomic)”级的：他们会自动地在锁定 inserted/deleted 行的索引记录(可能有几个)。
可以通过下面所示的技巧来应付死锁或减少死锁的次数：
- 在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用
SHOW INNODB STATUS
- 总是准备在因死锁而发生错误时重新发出一个事务。死锁并不危险。仅仅只需重试一遍。
- 经常提交你的事务。小的事务有较少的碰撞可能。
- 如果使用锁定读取
SELECT ... FOR UPDATE
- 或
... LOCK IN SHARE MODE
- ，尽量使用较低的隔离级
READ COMMITTED
- 。
- 以一个固定秩序(a fixed order)访问你的表和记录。这样事务将形成一个较精细的队列，而避免死锁。
- 为你的表添加合适的索引。那么你的查询只需要扫描较少的索引，因而设置较少的锁定。使用
EXPLAIN SELECT
- 尽量少用锁定：如果可以通过一个
SELECT
- 在一个较老的数据快照中获得所需数据，就不要再添加子句
FOR UPDATE
- 或
LOCK IN SHARE MODE
- 。在这时使用
READ COMMITTED
- 如果仍然没有什么补救效果，使用表级锁定连载你的事务(serialize transactions)：
LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES
- 。表级锁定可以使你的事务形成精细的队列。注意
LOCK TABLES
- 隐含地启动一个事务，就如同命令
BEGIN
- ，
UNLOCK TABLES
- 如同
COMMIT
- 连载事务(serialize transactions)的另一个解决办法就是建立一个仅有一行记录的辅助“信号量(semaphore)” 表。每一个事务在访问其它表之前均更新这个记录。通过这种方式所有的事务将持续执行。注意同时 InnoDB 实时死锁检测算法也在工作着，因为这个持续锁定(serializing lock)是一个行锁定。在 MySQL 中对于表级锁定我们必须采取超时方式。
