|
SERIALIZABLE，在这种情况下将在它所读取的记录索引上设置共享的
next-key locks。
SELECT ... FROM
... LOCK IN SHARE MODE : 在所读取的所有记录索引上设置同享的锁定。
SELECT ... FROM
... FOR UPDATE : 在所读取的所胡记录索引上设置独占地(exclusive)锁定。
INSERT INTO ...
VALUES (...) : 在插入的记录行上设置一个独占地锁定；注意这个锁定并不是一个 next-key lock ，并不会阻止其它用户在所插入行之前的间隙(gap)中插入新记录。如果产生一个重复键值错误，
在重复索引记录上设置一个共享的锁定。
如果在一个表中定义了一个 AUTO_INCREMENT
列，InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时， InnoDB 将设置一个特殊的表锁定模式
AUTO-INC ，这个锁定只持续到该 SQL 语句的结束而不是整个事务的结束。
INSERT INTO T SELECT
... FROM S WHERE ... 在已插入到表 T 中的每个记录上设置一个独占的(无 next-key)锁定。以一个
consistent read 搜索表 S ，但是如果 MySQL 打开了日志开关将在表 S 上设置一个共享的锁定。
在从备份中进行前滚(roll-forward)修复时，每个 SQL 语句必须严格按照原先所执行的顺序运行，所以 InnoDB 不得不设置锁定。
CREATE TABLE ...
SELECT ... 与上项相似，以 consistent read 或锁定方式完成 SELECT 。
REPLACE
如果没有一个 unique key 冲突，它的执行与 insert 一致。否则将在它所要更新的记录上设置一个独占的锁定。
UPDATE ... SET
... WHERE ... : 在搜索时所遭遇到的记录上设置一个独占的锁定。
DELETE FROM ...
WHERE ... : 在搜索时所遭遇到的每一个记录上设置一个独占的锁定。
如果一个表上有 FOREIGN
KEY 约束，所有需要检查约束条件的 insert, update, 或 delete 将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时，InnoDB
也设置这个锁定。
LOCK 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 可能会引起一个事务的死锁，这可能是我们所意想不到的。从 3.23.50 开始，在读取自增列值时将不再设置任何锁定，除非在某些情况下，比如在数据库启动后没有任何记录。
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 提交你的事务，那么 MySQL 将回滚你的事务。
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 语句，InnoDB
将依照你的查询检查数据库给你的事务一个时间点(timepoint)。因而，如果事务 B 在给你指定的时间点后删除了一行并提交，那么你并不能知道这一行已被删除。插入(insert)与更新(update)也是一致的。
你可以通过提交你的事务并重新发出一个 SELECT
来将你的时间点提前。
这就叫做 multiversioned 并发控制。
time
|
|
|
|
|
v
User A
User B
set autocommit=0;
set autocommit=0;
SELECT * FROM t;
empty set
INSERT INTO t VALUES (1, 2);
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set;
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
因而，只有当用户 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 来确定 MySQL 为你的查询挑选的适当的索引。
- 尽量少用锁定：如果可以通过一个
SELECT
在一个较老的数据快照中获得所需数据，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE
。在这时使用 READ COMMITTED 隔离级是较好的主意，因为在同一个事务中的每个 consistent read
只读取它最先确定的数据快照。
- 如果仍然没有什么补救效果，使用表级锁定连载你的事务(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 中对于表级锁定我们必须采取超时方式。
| 
