mysql Innodb引擎

斜体字表示个人观点和注释
翻译自 MYSQL 5.7英文文档
事务隔离级别 中的 READ COMMITTED 节中有提到 RR 和 RC 隔离级别下，innodb 如何执行 update 操作。

使用Innodb引擎的优点

1.如果您的服务器因硬件或软件问题而崩溃，无论当时数据库中发生了什么情况，重新启动数据库后无需执行任何特殊操作。 InnoDB 崩溃恢复会自动完成崩溃前提交的所有更改，并撤消正在处理但未提交的任何更改。只需重新启动并从你离开的地方继续。

2.Innodb 引擎维护有自己的缓存池(buffer pool)，用来在主存中缓存表和索引数据。经常使用的数据会直接在内存中处理。此缓存适用于许多类型的信息并加速处理。在专用的数据库服务器中，会赋予缓存池多达80%的物理内存。

3.外键

4.如果数据在磁盘或内存中损坏，则校验和机制在使用前会提醒。

5.引用 WHERE 子句，ORDER BY 子句，GROUP BY 子句和连接操作中的主键列是非常快的。

6.插入、删除和修改会被 change buffering机制优化。Innodb 会缓存改变的数据来精简I/O

7.当从表中反复访问相同的行时，称为自适应哈希索引的功能会接手以使这些查找更快，就像它们从哈希表中出来一样。

8.可以压缩表和关联的索引

9.创建和删除索引对性能和可用性影响更小

10.截断每个表的文件表空间非常快，并且可以释放磁盘空间供操作系统重用，而不是释放只有InnoDB才能重用的系统表空间内的空间。

11.使用 DYNAMIC 行格式，表格数据的存储布局对于 BLOB 和长文本字段更高效。

12.可以通过查询 INFORMATION_SCHEMA 表来监控存储引擎的内部工作

13.可以通过查询 Performance Schema 表来监控存储引擎的性能细节

14.可以将Innodb引擎的表和别的引擎的表混合使用，即使是同一个statement中

15.InnoDB在处理大量数据时专为提高CPU效率和最大性能而设计。

16.即使在文件大小限制为2GB的操作系统上，InnoDB表也可以处理大量数据。

Innodb引擎的最佳实践方法

1.指定查询中最常使用的一列或几列作为主键，没有的话就使用一个自增值做主键。

2.当从多表中基于相同ID取数据时，使用joins连接多表。为了提升join性能，在join列上定义外键，且join列有相同的数据类型。增加外键来确保引用的列是被索引的。外键会导致多表的级联删除或更新。

3.关闭 autocommit.。每秒提交数百次会限制性能。

4.通过使用START TRANSACTION和COMMIT语句将相关的DML操作集合分组到事物中，避免提交过于频繁，也要避免执行了几个小时还没有提交的 Insert 和 update 语句。

5.不要使用 LOCK TABLE 语句。Innodb 支持多个会话对表的同时读写，而不牺牲可靠性和性能。使用 SELECT … FOR UPDATE 语法获取一些行的独占的写权限，这只锁住你计划要 UPDATE 的行。

6.开启 innodb_file_per_table 选项(默认开启)或使用通用表空间，来把不同表的数据和索引放到不同的文件中，不要使用系统表空间(system space)。

7.评估数据和连接模式是否能够从 Innodb 表或页的压缩特性中获益。可以在不牺牲读写性能的情况下压缩 Innodb 表。

8.运行服务器时带上--sql_mode=NO_ENGINE_SUBSTITUTION 选项来防止带有 ENGINE= 的建表语句建立不同引擎的表。

Innodb 引擎与ACID

1.Atomicity:
- 自动提交设置
- COMMIT 语句
- ROLLBACK 语句
- INFORMATION_SCHEMA 中的操作数据

2.Consistency:
- doublewrite buffer(见下文)
- 容灾恢复

3.Isolation:
- 自动提交设置
- SET ISOLATION LEVEL 语句
- Innodb 锁的底层细节。在性能调优期间，您可以通过 INFORMATION_SCHEMA 表格查看这些详细信息。

4.Durability
- doublewrite buffer
- innodb_flush_log_at_trx_commit 配置项
- sync_binlog 选项
- innodb_file_per_table 选项
- 存储设备中的写缓存
- 存储设备中的电池备份缓存
- 运行mysql的操作系统，尤其是是否支持fsync()系统调用
- 不间断电源（UPS）保护运行MySQL服务器和存储MySQL数据的所有计算机服务器和存储设备
- 您的备份策略，例如备份频率和类型以及备份保留期
- 对于分布式或托管数据应用程序，MySQL服务器硬件所在数据中心的特定特征以及数据中心之间的网络连接。
-

doublewrite buffer

转自http://blog.csdn.net/linuxheik/article/details/62444753

当 mysql 在写一个 innodb page 到磁盘上时，如果在写这个 page 的过程中发生了意外的事件，比如断电，mysql 崩溃等
就会使这个 page 数据出现不一样的情形.从而形成一个”断裂”的 page .使数据产生混乱.这个时候 innodb 对这种块错误错误是无能为力的.
通过引入 doublewrite buffer 的方案，每次 innodb 在准备写出一个 page 时，先把 page 写到 doublewrite buffer 中.如果在写 doublewrite buffer 时，发生了意外，但是数据文件中的原来的 page 不受影响，这样在下次启动时，可以通过 innodb 的 redolog 进行恢复.如果在写 doublewrite buffer 成功后，mysql 会把 doublewrite buffer 的内容写到数据文件中，如果在这个过程又出现了意外，没有关系，重启后 mysql 可以通过从 doublewrite buffer 找到好的 page ，再用该好的 page 去覆盖磁盘上坏的page即可.所以在正常的情况下，mysql 写数据 page 时，会写两遍到磁盘上，第一遍是写到 doublewrite buffer，第二遍是从 doublewrite buffer 写到真正的数据文件中。
在某些情况下可以关闭 doublewrite buffer ，从而提高性能，比如比较稳定的系统中，有比较好的主从备份等。

---

InnoDB Multi-Versioning

一致读 consistent read
插入撤销日志 insert undo log
更新撤销日志 update undo log

Innodb 是一个多版本存储引擎：它保留有关旧版本更改行的信息，以支持事务性功能，如并发和回滚。该信息存储在表空间中的称为回滚段的数据结构中。InnoDB使用回滚段中的信息执行事务回滚所需的撤消操作。它还使用这些信息构建一个行的早期版本以进行一致的读取。
Innodb 内部会在数据库的每行数据里增加三个区域。
一个6字节的 DB_TRX_ID 字段，来表示最近一次 UPDATE 或 INSERT 该行的事务的识别码。另外，删除在内部被视为更新，其中行中的特殊位被用来将其标记为已删除。
一个7字节的DB_ROLL_PTR 字段，叫做回滚指针(roll pointer)，指向写入回滚段中的撤销日志记录。如果该行被 UPDATE 过，撤销日志记录包含重建该行被 UPDATE 前的内容的必要信息。
一个6字节的 DB_ROW_ID，包含一个随新行插入而单调递增的行ID。如果InnoDB自动生成聚集索引，则索引包含行ID值。否则， DB_ROW_ID 不出现在任何索引中。

回滚段中的撤销日志分为插入和更新撤消日志。插入撤消日志只在事务回滚中需要，并且只要事务提交就可以丢弃。更新撤消日志也用于一致性读取，但只有当没有事务存在时才会丢弃它们，InnoDB 会为其(指的是事务)分配一个快照，在一致读中可能需要更新撤消日志中的信息来构建早期版本的数据库行(事务都完成或者提交了，更新撤销日志也就没用了吧)。

定期提交事务，包括那些只有一致读的事务。否则，InnoDB不能从更新撤消日志中丢弃数据，并且回滚段可能变得太大，填满你的表空间。

回滚段中撤销日志记录的物理大小通常小于相应的插入或更新行。您可以使用此信息来计算回滚段所需的空间。

在 InnoDB 多版本方案中，当您使用SQL语句删除行时，行不会立即从数据库中物理删除。当它丢弃为删除而写入的更新撤销日志时，Innodb 才会物理删除相应行和索引记录。这个删除操作被叫做 purge，它相当快，通常与实现删除的sql语句的时间顺序一致。

如果在表中以大致相同的速率插入和删除小批量行，则清除线程(purge thread)可能会滞后，并且由于所有“死”行，表可能变得越来越大，从而使所有东西都变成磁盘绑定的(内存不足需要记录到磁盘上？)，而且非常慢。在这种情况下，通过调整 innodb_max_purge_lag 系统变量来限制新行操作，并为清除线程分配更多资源。

Multi-Versioning 和 Secondary Indexes

InnoDB 多版本并发控制(MVCC)将二级索引视为与聚簇索引不同。聚簇索引中的记录在原地更新，其隐藏的系统列指向撤销日志记录，从中可以重建早期版本的记录(数据本身就是按照聚簇索引顺序存放的，二级索引只有主键值，需要根据主键值再到聚簇索引中查询数据)。与聚集索引记录不同，二级索引记录不包含隐藏的系统列，也不就地更新。

当更新二级索引列时，旧的二级索引记录被删除标记，插入新记录后才会清除带有删除标记的记录。当辅助索引记录被删除标记或辅助索引页面被更新的事务更新时，InnoDB 将在聚簇索引中查找数据库记录。在聚簇索引中，检查记录的DB_TRX_ID，如果记录在读取事务启动后被修改，则从撤消日志中检索正确版本的记录。

如果辅助索引记录被标记为删除或者辅助索引页面被更新的事务更新，则不使用覆盖索引技术。 InnoDB 不会从索引结构返回值，而需要在聚簇索引中查找数据记录。(如果能够使用覆盖索引的话就可以不需要再到聚簇索引中查询数据了，二级索引已经包含了全部所需的数据)
但是，如果启用了索引条件下推（ICP）优化，并且可以仅使用索引中的字段来评估 WHERE 条件的某些部分，那么MySQL服务器仍然会将这部分 WHERE 条件向下推送到存储引擎，存储引擎则会使用索引评估。如果没有找到符合条件的记录，就不再需要到聚簇索引中查询了。如果找到匹配记录，即使带有删除标记，InnoDB 也会在聚簇索引中查找记录。

---

Innodb 锁和事务模型

对于实现大型，繁忙或高度可靠的数据库应用程序，移植来自不同数据库系统的实际代码或调整MySQL性能，了解 InnoDB 锁和 InnoDB 事务模型非常重要。

Innodb 锁

主要描述 Innodb 使用的各类型的锁

• 共享和独占锁
  Innodb 用共享锁(shared locks, s)和排他锁(exclusive locks, x)实现标准的行级锁。
  共享锁允许持有该锁的事务READ行。
  排他锁允许持有该锁的事务UPDATE或DELETE行。

如果事务 T1 在 r 行上持有共享锁，事务 T2 在 r 行上对锁的请求将被如下处理：
- 如果 T2 请求的是共享锁，将被立即授予。最终 T1 和 T2 都持有 r 行的共享锁。
- 如果 T2 请求的是排他锁，不会被立即授予。(需要等 T1 释放锁)

如果 T1 在 r 行上持有排他锁，T2 对锁的请求都需要等待 T1 锁的释放。

• 意向锁(Intention Locks)
  Innodb 支持允许行锁和表锁共存的多粒度锁(multiple granularity locking)。举例，例如LOCK TABLES ... WRITE语句会在表上持有排他锁。Innodb 使用意向锁来实现多个粒度级别的锁定。意向锁是表级锁，表明一个事务将要在表中的某行请求共享锁还是排他锁。有两种类型的意向锁：
  
  • 意向共享锁（IS）表示事务意图在表中的单个行上设置共享锁。
  • 意向排他锁（IX）表明事务意图在表中的单个行上设置独占锁。

举例，SELECT ... LOCK IN SHARE MODE会设置一个意向共享锁，SELECT ... FOR UPDATE会设置一个意向排他锁。
意向锁协议如下：
- 在事务能够获取在表上某行的共享锁之前，它必须先在表上获取意向共享锁或更强的锁(获取意向共享锁或意向排他锁)。
- 在事务能够获取在表上某行的排他锁之前，它必须先在表上获取意向排他锁

下表是表级锁的兼容性：

`	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

如果事务请求的锁能与已存在的锁兼容，则事务将被授予该锁；如果冲突，就需要等待已存在的锁释放。如果锁定请求与现有的锁冲突并且因为会导致死锁而无法被授予，则会发生错误。

意向锁只会阻塞全表请求(例如LOCK TABLES ... WRITE)。意向锁的主要目的是显示正在锁定一行，或者将要锁定表中的一行。

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

• 记录锁(Record Locks)
  记录锁是加在一条索引记录上的锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;组织任何在满足c1 = 10条件上的行的INSERT，UPDATE，DELETE.

记录锁总是锁住索引记录，即使表没有被定义索引。对于这种情况，Innodb 会创建一个隐藏的聚簇索引，记录锁会加到这个隐藏索引上。

• 间隙锁(Gap Locks)
  间隙锁是索引记录之间的间隙的锁，以及第一条记录之前和最后一条索引记录之后的间隙的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;会阻止其他事物插入c1 = 15的记录，无论是否已经存在c1 = 15的记录，因为所有满足 where 条件的记录的间隙都会加上间隙锁。

间隙锁是性能和并发性之间折中的一部分，只在某些事务隔离级别中使用。

对于使用唯一索引锁定行以搜索唯一行的语句，不需要使用间隙锁定。 （这不包括搜索条件仅包含多列唯一索引中的某些列的情况;在这种情况下，会发生间隙锁定）。例如，如果 id 列具有唯一索引，则以下语句仅会对于 ID 为100的行的加上索引记录锁，无论其他会话是否在上述间隔中插入行：

SELECT * FROM child WHERE id = 100;

如果id未被索引或者具有非唯一索引，则该语句会产生间隙锁。

间隙可以持有冲突锁。例如，事务A可以在间隙上保存共享间隙锁（间隙S锁），而事务B在同一间隙上保留独占间隙锁（间隙X锁）。允许冲突间隙锁定的原因是，如果从索引中清除记录，则必须合并由不同事务记录保存的间隙锁定。

间隙锁只会阻止其他事务插入到间隙中，不同的事务可以在同一个间隙上持有间隙锁。因此，间隙X锁具有与间隙S锁相同的效果。
(看样子间隙锁主要是用来解决幻读问题的)

可以显式禁用间隙锁。如果将事务隔离级别更改为 READ COMMITTED 或启用 innodb_locks_unsafe_for_binlog 系统变量（现在已弃用），则会发生这种情况。在这些情况下，对搜索和索引扫描禁用间隙锁，间隙锁仅用于外键约束检查和重复键检查。

• Next-Key Locks
  Next-Key Lock 是索引记录上的记录锁和索引记录前的间隙锁的组合。
  当 Innodb 搜索或扫描一个表的索引时，它会通过在遇到的索引记录上增加共享锁或排他锁来实现行级锁定。因此行级锁实际上是索引记录锁(index-record locks)。在索引记录上的 Next-Key Lock 也会影响在该索引记录前的间隙：除了给索引记录加锁，也会给索引记录前的间隙加锁。如果一个会话在索引记录 R 上有一个共享锁或排他锁，另一个会话不能在记录 R 前插入一个新的索引记录。
  假设一个索引包含值10，11，13和20，那么 Next-Key Lock 可能锁住的间隔如下：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

默认情况下，InnoDB在REPEATABLE READ事务隔离级别下运行。在这种情况下，InnoDB使用next-key锁进行搜索和索引扫描，这可以防止幻读。

• 插入意向锁(Insert Intention Locks)
  插入意向锁是插入行之前由INSERT操作设置的一种间隙锁。该锁以这种方式表示插入的意图：当多个事务向同一个索引间隙中插入时，只要不在间隙里的同一个位置上插入，插入时就不需要相互等待。假设有4和7两个索引记录。当事务A、B分别尝试插入索引值为5和6的两条记录时，都会在获取被插入行的排他锁前，用插入意向锁锁住4和7之间的间隙。但并不会互相阻塞，因为这两行没有冲突。(插入行的索引值不同就不会相互等待或阻塞)

以下例子演示一个事务在获取用于插入的排他锁前，如何获取一个插入意向锁。这个例子涉及两个客户端， A 和 B 。
客户端 A 创建了一个包含90和102两条索引记录的表，然后启动一个事务，该事务会在索引值大于100的记录上加上排他锁。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户端 B 启动了一个事务来向间隙中插入一条记录。当该事物等待获取一个排他锁时，会加上一个插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

• (自增锁)AUTO-INC Locks
  自增锁是事务插入带有自增列的记录时加的特殊表级锁。在最简单的情况下，当一个事务插入时，则任何其他事务都必须等待该表执行完插入操作，以便第一个事务插入的行接收连续的主键值。
  innodb_autoinc_lock_mode 配置选项控制用于自增锁的算法。它允许您选择如何在可预测的自增值序列和插入操作的最大并发之间进行权衡。

• 用于空间索引的谓词锁(Predicate Locks for Spatial Indexes)
  从没使用过，难以理解，就不翻译了

---

Innodb 事务模型

InnoDB 事务模型的目标是将多版本数据库的最佳属性与传统的两阶段锁定相结合。Innodb 使用行级锁，默认使用不加锁(nonlocking)一致读的方式运行查询。 InnoDB 中的锁信息以节省空间的方式存储，因此不需要锁升级。(不懂。。)。通常，允许多个用户锁定 InnoDB 表中的每一行或任何行的随机子集，而不会导致 InnoDB 内存耗尽。

事务隔离级别

事务隔离是数据库处理的基础之一。隔离是ACID中的I;隔离级别是在多个事务进行更改并同时执行查询时，对性能与可靠性，一致性和结果重复性之间的平衡进行微调的设置。
InnoDB 提供了 SQL：1992 标准描述的所有四种事务隔离级别：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE。 InnoDB 的默认隔离级别是 REPEATABLE READ。
用户可以使用 SET TRANSACTION 语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请在命令行或选项文件中使用--transaction-isolation选项。有关隔离级别和级别设置语法的详细信息，请参见第13.3.6节“ SET TRANSACTION 语法”。
InnoDB 通过不同的锁定策略来支持各个事务隔离级别。或者，当结果的精确性、一致性和可重复性不如减少锁定开销重要时，可以放宽一致性规则，即使用 READ COMMITTED 或 READ UNCOMMITTED。 SERIALIZABLE 执行比REPEATABLE READ 更严格的规则，主要用于特殊情况下，如 XA 事务和解决并发和死锁问题。

以下描述了MySQL如何支持不同的事务级别。该列表从最常用的级别到最少使用的级别。
- 可重复读(REPEATABLE READ)
Innodb 的默认隔离级别。在同一事务中的读，都会读取第一次读取时建立的快照。
对于锁定读取（SELECT FOR WITH UPDATE或LOCK IN SHARE MODE），UPDATE 和 DELETE 语句，锁定取决于语句使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。对于具有唯一搜索条件的唯一索引，InnoDB 只给记录加锁，不给间隙加锁。 对于其他搜索条件，InnoDB 锁定扫描的索引范围，使用间隙锁或 Next-Key Lock来阻止其他会话插入到范围所覆盖的间隙中。

• Read Committed
  每个一致读，即使是在同一个事务中，都会设置并读取自己的新快照。

  对于加锁读（SELECT ... LOCK IN SHARE MODE，SELECT ... FOR UPDATE），UPDATE语句和DELETE语句，innodb 只锁索引记录，不对间隙加锁，因此允许在加锁记录前后自由插入。间隙锁只用来外键约束检查和重复键（duplicate-key）检查，因此会出现幻行。

  如果使用 READ COMMITTED，则必须使用基于行的二进制日志记录。

  使用 READ COMMITTED 还有额外的影响：

  • 对于 UPDATE 或 DELETE 语句，innodb 只会在要 update 或 delete 的行上加锁。不匹配行的记录锁在 mysql 评估完 WHERE 语句后会被释放。这将大大减少死锁发生的几率。
  • 对于 UPDATE 语句，如果一行已经被加锁了，innodb 执行一个半一致化读（semi-consistent read），返回最近提交版本给 mysql ，来方便 mysql 判断行是否匹配 WHERE 语句。如果行匹配（要被 UPDATE），mysql 再次读取行，innodb 会在行上加锁或等待加锁。

  考虑下面的例子。表语句如下：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

表没有索引，因此搜索和索引扫描使用隐藏的聚簇索引来加记录锁。

假设会话 A 执行如下语句：

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

然后会话 B 执行如下语句：

# Session B
UPDATE t SET b = 4 WHERE b = 2;

当 innodb 执行 UPDATE 语句时，先在读取的每一行上获取一个排它锁，然后决定是否进行修改。如果 innodb 不修改该行，将会释放该锁；否则，innodb 将会持有该锁直到事务结束。这会影响事务处理，如下所示。

当使用默认 REPEATABLE READ 隔离级别时，会话 A 的 UPDATE 语句在读取的每一行上获取 x-lock，不释放：

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

会话 B 的 UPDATE 语句将会卡主，直到事务 A 提交或回滚：

x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果使用的是 READ COMMITTED ，会话 B 会在读取的每一行上加 x-lock 锁，然后释放不需要修改的行：

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

对于会话 B 的 UPDATE 语句，innodb 执行一个半一致性读，返回给 mysql 每行的最近提交版本，mysql 判断哪些行符合 WHERE 条件：

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

然而，如果 WHERE 包含一个索引列且 innodb 使用该索引，当获取并保持记录锁时，只考虑索引列。在下面的例子中，第一个 UPDATE 语句在满足 where 条件的行上获取并保持 x-lock。第二个 UPDATE 语句会在获取同样记录的 x-lock 上卡主，因为也使用了列 b 上的索引。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

• Read Uncommitted
• Serializable
  (未使用过的隔离级别暂不翻译了)

自动提交，提交和回滚

在InnoDB中，所有的用户活动都发生在一个事务中。如果启用自动提交模式，则每个SQL语句将自行形成一个事务。MySQL 开启每个新连接的会话时都会默认启用自动提交。因此如果该语句没有返回错误，则MySQL会在每个SQL语句后执行提交。如果语句返回错误，则提交或回滚行为取决于错误。
启用自动提交的会话可以以下方式来执行一个多语句事务：通过以明确的 START TRANSACTION 或 BEGIN 语句启动，并使用 COMMIT 或 ROLLBACK 语句结束。
如果在SET autocommit = 0的会话中禁用自动提交模式，则会话始终在一个事务中。 COMMIT 或 ROLLBACK 语句结束当前事务并开始一个新事务。 如果具有禁用自动提交功能的会话在未显式提交最终事务的情况下结束，则MySQL会回滚该事务。 一些语句隐式地结束一个事务，就好像你在执行语句之前做了一个COMMIT一样。有关详细信息，请参见第13.3.3节“导致隐式提交的语句”。 COMMIT 意味着在当前交易中所做的更改将永久化并对其他会话可见。另一方面，ROLLBACK语句会取消当前事务所做的所有修改。 COMMIT 和 ROLLBACK 都释放当前事务期间设置的所有 InnoDB 锁。

用事务对DML操作进行分组
默认情况下，与MySQL服务器的连接从启用自动提交模式开始，当您执行它时会自动提交每条SQL语句。如果您对其他数据库系统有经验，那么这种操作模式可能并不熟悉，在这种模式下，发布一系列 DML 语句并提交它们或将它们一起回滚是标准做法。
要使用多语句事务，请使用 SQL 语句SET autocommit = 0关闭自动提交，并根据需要使用 COMMIT 或 ROLLBACK 结束每个事务。要开启自动提交，请使用START TRANSACTION开始每个事务并使用 COMMIT 或 ROLLBACK 结束它。以下示例显示了两个事务。第一个提交;第二个回滚。

mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

客户端语言中的事务
在诸如PHP，Perl DBI，JDBC，ODBC或MySQL的标准C调用接口之类的API中，您可以像使用其他SQL语句（如SELECT或INSERT）一样将事务控制语句（如COMMIT）作为字符串发送到MySQL服务器。一些API还提供单独的特殊事务提交和回滚函数或方法。

一致不加锁读

一致读取意味着 InnoDB 使用多版本化在某个时间点向查询呈现数据库的快照。该查询可以查看在该时间点之前提交的事务所做的更改，无法看到之后或未提交的事务所做的更改，但是可以看到同一事务中之前的语句所做的更改。

如果事务隔离级别为 REPEATABLE READ（默认级别），则同一事务中的所有一致读取将读取该事务中第一次读取所创建的快照。您可以通过提交当前事务并在发出新查询之后为您的查询获得更新鲜的快照。
通过 READ COMMITTED 隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。

一致读是 InnoDB 在 READ COMMITTED 和 REPEATABLE READ 隔离级别中处理 SELECT 语句的默认模式。 一致读不会在它访问的表上设置任何锁，因此其他会话可以自由修改这些表，同时在表上执行一致读。 假设您正在运行默认的 REPEATABLE READ 隔离级别。当您发出一致读（即普通的 SELECT 语句）时，InnoDB 会为您的事务提供一个根据您的查询查看数据库的时间点。如果另一个事务删除了一行并在分配了时间点后提交，则不会看到该行已被删除。插入和更新的处理方式相似。

注意：
数据库状态的快照适用于事务中的SELECT语句，而不一定适用于DML语句。如果插入或修改某些行然后提交该事务，则从另一个并发REPEATABLE READ事务发出的DELETE或UPDATE语句可能会影响那些刚刚提交的行，即使会话无法查询它们。如果事务更新或删除由不同事务提交的行，则这些更改对当前事务变得可见。例如，您可能会遇到如下情况：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

您可以通过提交事务来推进您的时间点，然后再进行另一个 SELECT 或 START TRANSACTION WITH CONSISTENT SNAPSHOT。

这称为多版本并发控制。

在下面的例子中，只有当 A、B 都提交时，事务A 才能看到 B 插入的行(使时间点越过 B 提交的时间)。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

如果要查看数据库的“最新”状态，请使用READ COMMITTED隔离级别或锁定读取：

SELECT * FROM t FOR SHARE;

在 READ COMMIT 隔离级别下，每个一致读都会设置并读取自己的快照。在LOCK IN SHARE MODE下就会使用锁定读：SELECT 会阻塞，直到包含最新行的事务结束。

一致的读取不适用于某些 DDL 语句：

• 一致性读取不能在DROP TABLE上工作，因为MySQL不能使用已经被删除的表。
• 一致读不能在ALTER TABLE上工作。因为该语句创建了原表的临时副本，并在副本创建完后删除原表。在事务中再次使用一致读时，新表中的行不可见，因为这些行不存在事务快照中。在这种情况下，事务返回错误：ER_TABLE_DEF_CHANGED，“表定义已更改，请重试事务”。

如果语句中不包含FOR UPDATE或LOCK IN SHARE MODE，在INSERT INTO ... SELECT ，UPDATE ... (SELECT)和 CREATE TABLE ... SELECT语句中的读类型会默认采用更强的锁(就像READ COMMITTED)：每个一致读都会使用自己新的快照，即使在一个事务中。如果想要在这种情况下使用一致读，开启 innodb_locks_unsafe_for_binlog 选项并将隔离级别设置为 READ UNCOMMITTED，READ COMMITTED 或 REPEATABLE READ 。在这种情况下，从被选择表中读取的行将不会上锁。

加锁读

如果你查询数据然后在同一事务中插入或更新相关数据，则常规的 select 语句不能提供足够的保护。另一些事务能够更新或删除查询的行。(假设事务A中查询+更新，事务B可以在事务A还没有执行更新语句时，删除A查询的行。在一般情况下好像这也不会有什么问题？)。Innodb 支持两种类型的加锁读，能够提供额外的安全性：

• SELECT ... LOCK IN SHARE MODE
  在所有读取的行上设置一个共享锁。其他会话能够读取行，但是不能修改直到你的事务提交。但如果这些行中的任意行被别的事务修改了还未提交，你的查询就必须等该事务结束。
• SELECT ... FOR UPDATE
  这和你执行 UPDATE 语句一样，对于搜索遇到的索引记录，锁定行和任何关联的索引条目。其他想要更新、执行SELECT ... LOCK IN SHARE MODE或者从特定隔离级别下读取这些行的事务都会被阻塞。一致读忽略读取视图中存在的记录上设置的任何锁。(一条记录的旧版本不会被锁；它们由撤销日志在记录的内存副本中重建)

这些子句在处理树形结构或图形化结构时非常有用，无论在一个表或分成多个表(没遇到过。。)。您可以将边或树的分支从一处移到另一处，同时保留返回并更改这些“指针”值的权利。

注意
SELECT FOR UPDATE对行的加锁只在自动提交关闭时有效(设置 autocommit 为0，或者用START TRANSACTION开启事务)。如果开启了自动提交，匹配行将不会被加锁。

锁定读示例
假设你想要在表child中插入一条新行，同时确保该行在表parent中有一条父行。你的应用代码使用这一系列的操作来确保参照完整性(referential integrity)。
首先，在表parent中执行一致读来确保父行的存在。然后你就可以安全的在表child中插入行吗？不，其他会话能够在你的 SELECT 操作和 INSERT 操作之间删除父行。
为了防止这个潜在的问题，需要在 SELECT 语句中带上LOCK IN SHARE MODE：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

当LOCK IN SHARE MODE的查询返回父行时，你就可以安全的在表child中插入记录并提交。任意事务想要获取排他锁都必须等你完成，这就是说，直到表中的所有数据处于一致状态。

另一个示例，考虑在表child_codes中的整数计数字段，用于为插入表中的每行记录分配唯一标识符。不要使用一致读或共享模式读来获取该字段的当前值，因为两个用户可能会看到计数器的同一个值，然后会出现一个键值重复的错误如果两个事物尝试在表中插入相同标识符的行。

在这里，LOCK IN SHARE MODE不是一个好的解决方案，因为如果两个用户同时读取计数器，至少有一个用户在尝试更新计数器时会死锁。

为了实现读取并增加计数器，先用FOR UPDATE执行一个锁定读，然后在增加计数器。举例：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

一个SELECT ... FOR UPDATE读取最新可用的数据(会使用最新的快照而不是事物最开始设置的快照)，在读取的每一行上设置一个排他锁。因此，它在行上设置的锁和 UPDATE 语句相同。

在 MYSQL 中，产生一个唯一的标识符实际上可以通过对表的单一连接完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

SELECT语句仅检索标识符信息（特定于当前连接）。它不访问任何表。(别的连接中插入的行，并不会改变该连接 LAST_INSERT_ID() 的值)

---

Innodb 中不同sql语句设置的锁

加锁读、UPDATE 或 DELETE 通常会在 sql 语句处理过程中给每条扫描到的索引记录加记录锁，无论 where 条件是否会排除该行。Innodb 不会记住详细的 where 条件，但是知道扫描的索引范围。这些锁通常是 Next-Key Lock，也会阻止在间隙中插入记录。然而， 间隙锁能被显示禁用，导致 Next-Key Lock 无法使用。事务隔离级别也会影响设置的锁。

如果查询中使用了一个第二索引，而且索引记录锁是排他性的，innodb 也会获取相应的聚簇索引的记录并加锁。

如果你在语句中没有使用合适的索引而且 MYSQL 必须扫描整个表来处理语句，表的每行都会加锁，其他用户的插入都会被阻塞。一个好的索引很重要，能够避免你的查询扫描很多不必要的行。

对于SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE，扫描时会加锁，结果集中不满足条件的行的锁应被释放(举例，当不满足 where 子句给出的条件时)。然而，在一些案例中，行可能不会被马上解锁，由于查询期间结果行和原始源之间的关系丢失了。举例，在一个 UNION 中，扫描和被加锁的行在被评估是否满足结果集条件前会被插入临时表中。在这种情况下，临时表和原表间行的关系丢失了，后来的行将不会被解锁直到查询执行结束。

Innodb 会根据查询类型设置特定类型的锁：

• SELECT ... FROM 是一个一致读，读取数据库快照，不会加锁除非事务隔离级别是 SERIALIZABLE。在 SERIALIZABLE 下，查询会在遇到的记录上设置共享的 Next-Key Lock ，然而对于使用唯一索引查询唯一行的语句，只需要一个索引记录锁。
• SELECT ... FROM ... LOCK IN SHARE MODE会在遇到的所有索引记录上设置共享的 Next-Key Lock。然而对于使用唯一索引查询唯一行的语句，只需要一个索引记录锁。
• SELECT ... FROM ... FOR UPDATE会在遇到的所有索引记录上设置排他的 Next-Key Lock。然而对于使用唯一索引查询唯一行的语句，只需要一个索引记录锁。
  对于遇到的索引记录，SELECT ... FROM ... FOR UPDATE会阻塞其他执行SELECT ... FROM ... IN SHARE MODE的会话，也会在特定事务隔离级别下阻塞读取。一致性读忽略在读视图中加在记录上的任意锁。
• UPDATE ... WHERE ...会在遇到的所有索引记录上设置排他的 Next-Key Lock。然而对于使用唯一索引查询唯一行的语句，只需要一个索引记录锁。
• 当 UPDATE 修改一条聚簇索引记录时，会在第二索引上增加隐式锁。当插入新的第二索引记录时，或在插入新的第二索引记录前执行重复性检查扫描时，UPDATE 操作会在受影响的第二索引记录上加共享锁。
• INSERT 会在插入行上加排他锁。这个锁是索引记录锁，不是一个 Next-Key Lock(即，没有间隙锁)，不会阻塞其他想要在行边间隙插入行的会话。
  在插入前，会在间隙上加插入意向锁。该锁的意思是：当多个事务同时在某个间隙中插入时，只要不在间隙的同一个位置中插入行(唯一索引不同)，就不需要互相等待。假设有4和7两个索引记录。当事务A、B分别尝试插入索引值为5和6的两条记录时，都会在获取被插入行的排他锁前，用插入意向锁锁住4和7之间的间隙。但并不会互相阻塞，因为这两行没有冲突。
  当出现重复键值错误时，会在重复的索引记录上加共享锁。如果另一个会话已具有排他锁，则如果有多个会话尝试插入同一行，则此共享锁的使用可能导致死锁。如果另一个会话删除该行，则会发生这种情况。(不理解的话看下面的例子)。假设一个 InnoDB 表t1具有以下结构：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

现在假设三个会话按顺序执行下列操作：
会话1：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话1：

ROLLBACK;

会话1的第一个操作获取了行的排他锁。会话2和3的操作都会导致重复键错误，并且它们都会为该行请求共享锁。当会话1回滚时，它释放了行上的排他锁，然后会授予会话2和3共享锁。此时，会话2和3死锁了：都不能获取行的排他锁，因为对方有共享锁。

相似的情况，当表已经有了键值为1的行，三个会话按顺序执行下列操作：
会话1：

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

会话2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话1：

commit;

• INSERT ... ON DUPLICATE KEY UPDATE与简单的 INSERT 操作不同，当出现重复键错误时，会在行上加一个排他锁而不是共享锁。对于重复的主键，会加一个排他的索引记录锁。对于唯一索引，会加一个排他的 Next-Key Lock。
• REPLACE 和 INSERT 操作类似，如果没有在唯一索引上冲突的话。否则会在行上加一个排他的 Next-Key Lock。

• INSERT INTO T SELECT ... FORM ... S WHERE ...会在被插入表T的每行上加排他的索引记录锁(没有间隙锁)。如果事务隔离级别是 READ COMMITTED，或者 innodb_locks_unsafe_for_binlog 被启用，并且事务隔离级别不是 SERIALIZABLE，InnoDB 将把在S上执行的搜索作为一致读（无锁）。否则会在S上设置共享的 Next-Key Lock。Innodb 在下面的情况下必须加锁：在从备份中前滚恢复时，必须按照原来的方式执行每个SQL语句。
  CREATE TABLE ... SELECT ...使用共享的 Next-Key Lock 或作为一致读取执行SELECT，如同INSERT ... SELECT。
  当在REPLACE INTO t SELECT ... FROM s WHERE ...或在UPDATE t ... WHERE col IN (SELECT ... FROM s ...)使用 SELECT 时，Innodb 会在表s上设置共享的 Next-Key Lock。

• 在初始化一个表的先前指定的 AUTO_INCREMENT 列时，InnoDB 在与 AUTO_INCREMENT 列关联的索引的末尾设置排它锁。在访问自增计数器时，InnoDB 使用特定的 AUTO-INC 表锁模式，其中锁只持续到当前SQL语句的末尾，而不是整个事务的末尾。当有 AUTO-INC 锁时，其他会话不能在表中插入。
  InnoDB获取先前初始化的 AUTO_INCREMENT 列的值时不设置任何锁。

• 如果在表上定义了 FOREIGN KEY 约束，则需要检查约束条件的任何插入，更新或删除都会在它检查约束的记录上设置共享记录锁。在约束失败的情况下，InnoDB 也会设置这些锁。
• LOCK TABLES设置表锁，但它是 InnoDB 层之上的 MYSQL 层设置的。如果innodb_table_locks = 1（缺省值）和autocommit = 0，InnoDB知道表锁，InnoDB上面的MySQL层知道行级锁。
  否则，InnoDB 的自动死锁检测无法检测涉及这种表锁的死锁。另外，因为在这种情况下，较高的MySQL层不知道行级别锁定，所以可以在当另一个会话具有行锁的表上获得表锁。但是，这不会危及事务的完整性。

---

幻行

事务中的幻行问题：当相同的查询在不同时间产生不同的行时。例如，如果SELECT执行了两次，但是第二次返回的是第一次未返回的行，则该行是”幻行”。
假设在子表的 id 列上有一个索引，并且您希望读取并锁定标识符值大于100的表中的所有行，以便稍后更新所选行中的某一列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查询从 ID 大于100的第一条记录开始扫描索引。让该表包含 id 值为 90 和 102 的行。如果在扫描范围内的索引记录上设置的锁不锁定间隙中的插入（在这种情况下，间隙在90和102之间），则另一个会话可以在表中插入一个新行，其ID为101。如果要在同一个事务中执行相同的 SELECT，则会看到一个新行–查询返回的结果集中的ID为 101的行 （“幻行”​​）。如果我们将一组行视为一个数据项，那么新的幻行将违反事务的隔离原则：事务处理期间它所读取的数据不会改变。
为了防止幻行，InnoDB 使用一种名为 next-key locking 的算法，将索引行锁与间隙锁相结合。 InnoDB 以这样的方式执行行级锁定，即当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。另外，索引记录上的 next-key lock 也会影响该索引记录之前的“间隙”。也就是说，next-key lock 是索引记录锁加上索引记录之前的间隙上的间隙锁。如果一个会话对索引中的记录 R 具有共享或独占锁定，则另一个会话不能在索引顺序中的 R 之前的间隙中插入新的索引记录。
当InnoDB扫描索引时，它也可以锁定索引中最后一条记录后的间隔。在前面的例子中就是这样：为了防止任何插入到ID大于100的表中，InnoDB 会在id值102之后的间隙中加上锁。
您可以使用 next-key lock 在应用程序中实现唯一性检查：如果您以共享模式读取数据，并且没有看到要插入的行的重复项，那么您可以安全地插入行并知道在读取过程中设置在行的后继上的 next-key lock 可以防止任何人同时插入相同的行。因此，next-key lock 使您能够“锁定”表中不存在的东西。 可以禁用间隙锁定，如第14.5.1节“InnoDB 锁定”中所述。这可能会导致幻行，因为当禁用间隙锁定时，其他会话可以将新行插入到间隙中。

---

Innodb 中的死锁

死锁是由于每个事务持有别的事务需要的锁而导致不同事务无法继续的情况。因为两个事务都在等待资源变为可用，所以都不释放它拥有的锁。
当事务锁定多个表中的行时（通过诸如UPDATE或SELECT ... FOR UPDATE之类的语句），可能会发生死锁，但顺序相反。当这些语句锁定范围内的索引记录和间隔，也会发生死锁，每个事务由于计时问题而只获取了一些锁。有关死锁示例，请参见第14.5.5.1节“InnoDB 死锁示例”。
为了减少死锁的可能性，使用事务而不是LOCK TABLES语句;保持插入或更新数据的事务足够小以至于不会持续很长时间;当不同的事务更新多个表或大范围的行时，请在每个事务中使用相同的操作顺序（例如SELECT ... FOR UPDATE）;在SELECT ... FOR UPDATE和UPDATE ... WHERE语句中使用的列上创建索引。死锁的可能性不受隔离级别的影响，因为隔离级别改变了读操作的行为，而死锁因为写操作而发生。有关避免和从死锁状态恢复的更多信息，请参见第14.5.5.3节“如何最小化和处理死锁”。
当启用死锁检测（默认）并发生死锁时，InnoDB检测到这种情况并回滚其中一个事务（受害者）。如果使用 innodb_deadlock_detect 配置选项禁用死锁检测，InnoDB 将依靠 innodb_lock_wait_timeout 设置来回滚发生死锁的事务。因此，即使您的应用程序逻辑正确，您仍然必须处理重启事务的情况。要查看 InnoDB 用户事务中的最后一个死锁，请使用SHOW ENGINE INNODB STATUS 命令。如果频繁的死锁突显出事务结构或应用程序错误处理的问题，请在启用 innodb_print_all_deadlocks 设置的情况下运行，以将有关所有死锁的信息打印到 mysqld 错误日志中。有关死锁如何自动检测和处理的更多信息，请参见第14.5.5.2节“死锁检测和回滚”。

死锁示例

下面的例子展示了当一个锁请求造成死锁时，错误是如何出现的。例子涉及两个客户端 A 和 B。
首先，客户端 A 创建了一个包含一行的表，然后启动了一个事务。在事务中，A 通过SELECT ... LOCK IN SHARE MODE获取了行的 S 锁：

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

之后，客户端 B 启动一个事务尝试删除行：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

删除操作需要一个 X 锁。无法被授予因为与 A 持有的 S 锁冲突，所以进入了锁请求的队列中，并阻塞了 B。
最终， A 也尝试删除行：

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

因为 A 也需要 X 锁来删除行，死锁发生了。然而，锁请求无法完成，因为 B 已经有一个对 X 锁的请求并正在等待 A 释放 S 锁。B 无法获取 X 锁，因而 A 也无法将 S 锁升级成 X锁。(A 对 X 锁的请求排在 B 后面)。最终，Innodb 在其中一个客户端中报错并释放了它的锁。返回错误如下：

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

此时，另一个客户端的锁请求得以完成，并删除了行。

死锁检测和回滚

当启用死锁检测时（默认），InnoDB 会自动检测事务死锁并回滚一个或多个事务来打破死锁。 InnoDB 会尝试选择小型事务进行回滚，其中事务的大小由插入，更新或删除的行数决定。
如果 innodb_table_locks = 1（缺省值）和 autocommit = 0，InnoDB 知道表锁，并且它上面的 MySQL 层知道行级锁。否则，InnoDB 无法检测由 MySQL LOCK TABLES 语句设置的表锁或涉及到由 InnoDB 以外的存储引擎设置的锁的死锁。通过设置 innodb_lock_wait_timeout 系统变量的值来解决这些情况。

当InnoDB执行事务的完整回滚时，将释放事务设置的所有锁。但是，如果只有一条SQL语句由于错误而回滚，则可能会保留语句设置的一些锁。发生这种情况是因为此时 InnoDB 以某种格式存储行锁，它无法知道哪个锁由哪个语句设置。
如果SELECT在事务中调用存储的函数，并且函数中的语句失败，那么该语句将回滚。此外，如果在此之后执行 ROLLBACK ，则整个事务回滚。 如果 InnoDB Monitor 输出的”LATEST DETECTED DEADLOCK”部分包含一条消息：“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,”，这表示等待中的事务数量列表已达到200的限制。超过200个事务的等待列表被视为死锁，并且试图检查等待列表的事务将被回滚。如果锁定线程必须查看的等待列表上的事务所拥有的锁超过1,000,000个，则也会发生同样的错误。
有关组织数据库操作以避免死锁的技术，请参见第14.5.5节“InnoDB中的死锁”。

禁用死锁检测
在高并发系统上，当大量线程等待相同的锁时，死锁检测会导致速度下降。有时，在死锁发生时，禁用死锁检测并依赖 innodb_lock_wait_timeout 设置进行事务回滚可能更有效。使用 innodb_deadlock_detect 配置选项可以禁用死锁检测。

如何最小化和处理死锁

本节基于第14.5.5.2节“死锁检测和回滚”中有关死锁的概念信息。它解释了如何组织数据库操作以最大限度地减少死锁以及应用程序中所需的后续错误处理。

死锁是事务型数据库中的一个典型问题，但它们并不危险，除非它们频繁发生，以至于根本无法运行某些事务。通常，您必须编写应用程序，以便当它们由于死锁而回滚时，可以随时准备重新发出一个事务。 InnoDB 使用自动的行级锁定。即使在插入或删除单行的事务中，也可能会出现死锁。那是因为这些行动并非真正的“原子”。他们自动在插入或删除的行的索引记录（可能有几个）上设置锁。

您可以通过以下技术来应对死锁并减少它们发生的可能性：

• 在任何时候，发出SHOW ENGINE INNODB STATUS命令来确定最近发生死锁的原因。这可以帮助您调整应用程序以避免死锁。
• 如果经常出现死锁警告，请通过启用 innodb_print_all_deadlocks 配置选项来收集更多的调试信息。关于每个死锁的信息，不仅仅是最新的，都会记录在 MySQL 错误日志中。完成调试后禁用此选项。
• 如果事务由于死锁而失败，请始终准备重新发出事务。死锁并不危险。再试一次。
• 保持事务小且持续时间短，以减少冲突。
• 在进行一系列相关更改后立即提交事务，以减少冲突。特别是，不要让交互式 mysql 会话在未提交事务的情况下长时间处于打开状态。
• 如果使用锁定读（SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED。
• 在事务中修改多个表或同一表中的不同行时，每次都要以一致的顺序执行这些操作。然后事务形成明确定义的队列并且不会发生死锁。例如，将数据库操作组织到应用程序中的函数中，或调用存储例程，而不是在不同位置编码多个相似的INSERT，UPDATE 和 DELETE 语句序列。
• 将精心挑选的索引添加到表格中。然后，您的查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT来确定在 MySQL 服务器上哪些索引最适合您的查询。
• 使用更少的锁定。如果您可以允许 SELECT 从旧快照返回数据，请不要添加FOR UPDATE或LOCK IN SHARE MODE子句。在这里使用 READ COMMITTED 隔离级别是很好的，因为在同一事务中每次一致读都会从它自己的新快照中读取。
• 如果没有别的方法，请使用表级锁序列化您的事务。在事务表中使用 LOCK TABLES，例如在 InnoDB 表中的正确方法：用set auto_commit=0开始事务(而不是start transaction),之后LOCK TABLES，不调用UNLOCK TABLES直到你明确地提交事务。例如，如果您需要写入表t1并从表t2读取，则可以这样做：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

表级锁定可防止对表进行并发更新，从而避免死锁，但会降低繁忙系统的响应速度。

• 序列化事务的另一种方法是创建一个仅包含单行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有的交易都是以串行的方式进行的。请注意，在这种情况下，InnoDB 即时死锁检测算法也适用，因为序列化锁是行级锁。使用 MySQL 表级锁定时，必须使用超时方法来解决死锁问题。