InnoDB存储引擎之 —— 锁（一）

• SQL Server
• MyISAM
• InnoDB
  • InnoDB 锁的类型
  • 几张查看锁和事务的表
  • 一致性非锁定读
  • 一致性锁定读
  • 自增锁（2种实现方式）
    • AUTO- INC Locking（特殊的表锁）
    • 互斥量
  • 外键与锁

SQL Server

2005 之前是页锁，页锁容易实现，并发写入性能比 MyISAM 要好。但是对于热点数据更新还是无能为力。

2005 之后是乐观并发和悲观并发，乐观并发下支持行级锁，但与 InnoDB 的实现方式完全不同。在 SQL Server 下，锁是一种稀缺资源，锁越多开销越大，因此会导致锁升级，行锁会升级到表锁。

MyISAM

MyISAM 引擎是表锁设计，并发读性能没问题，并发写性能有问题，若插入在“底部”，还能有一定的并发写入性能。

InnoDB

InnoDB 锁的实现与 Oracle 比较像，提供一致性非锁定读和行级锁，行级锁没有额外开销，支持并发性和一致性。

InnoDB 锁的类型

1. 共享锁(S Lock)
   允许事务读一行数据
2. 排它锁(X Lock)
   允许事务删除或更改一条数据
3. 意向共享锁(IS Lock)
   事务想要获取一张表中某几行的共享锁
4. 意向排它锁(IX Lock)
   事务想要获取一张表中某几行的排它锁
5. 自增锁

例如，如果一个表上已经有了S表锁，那么尝试修改该表的几行记录，是不可以的，因为IX锁与S锁是不兼容的。

几张查看锁和事务的表

1. INNODB_TRX
   可以查看事务的状态
   
2. INNODB_LOCKS
   可以查看哪些表哪些记录被加了哪些锁。
   
3. INNODB_LOCK_WAITS
   已经有了前面两张表，判断事务的等待情况，可以很清楚的看到一个事务被哪个事务和锁阻塞
   

一致性非锁定读

InnoDB 存储引擎通过多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。而是去读取行的一个快照数据，如图所示：

快照数据是指该行的之前版本的数据，该实现是通过 undo 段来完成。而 undo 用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control, MVCC)

非锁定读机制极大地提高了数据库的并发性。在事务隔离级别 READ COMMITTED 和 REPEATABLE READ (INNODB 存储引擎的默认事务隔离级别）下，INNODB 存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。对于 READ COMMITTED 的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照（也就是当前事务自己建立的那个快照）。而对于 REPEATABLE READ 的事务隔离级别，总是读取事务开始时的行数据（这里应该也包含自己建立的快照数据）。

一致性锁定读

如下两种语句必须在事务中使用，当事务提交了，锁也就释放了。

• SELECT … FOR UPDATE
  对读取的行记录加一个 X 锁，其他事务不能对已锁定的行加上任何锁。
  即使读取的行已被执行了 SELECT… FOR UPDATE，还是可以进行一致性非锁定读的。
• SELECT … LOCK IN SHARE MODE
  对读取的行记录加一个 S 锁，其他事务可以向被锁定的行加 S 锁，但是如果加 X 锁，则会被阻塞。

自增锁（2种实现方式）

AUTO- INC Locking（特殊的表锁）

在 INNODB 存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值

SELECT MAX (auto inc col) FROM t FOR UPDATE:

每张表的自増长值并不保存在磁盘上进行持久化，而是在每次 INNODB 存储引檠启动时初始化。
—— 来自另一本硬核源码书

插人操作会依据这个自增长的计数器值加 1 赋予自增长列。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后オ释放，而是在完成对自增长值插入的 SQL 语句后立即释放。但对于有自增长值的列的并发插人性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。

互斥量

从 MYSQL5.1.22 版本开始，INNODB 存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，INNODB 存储引擎提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式，该参数的默认值为 1

三种插入类型

innodb_autoinc_lock_mode 说明

需要注意 InnoDB 存储引擎中自增长的实现和 MYISAM 不同。MYISAM 存储引擎是表锁设计，自增长不用考虑并发插人的问题。另外，在 INNODB 存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列。如果不是第一个列，则 MYSQL 数据库会抛出异常，而 MYISAM 存储引擎没有这问题。

外键与锁

外键主要用于引用完整性的约束检査。在 INNODB 存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，INNODB 存储引擎自动对其加一个索引，因为这样可以避免表锁，Oracle 数据库不会自动添加索引，用户必须自己手动添加，这也导致了 Oracle 数据库中可能产生死锁。

对于外键值的插入或更新，首先需要査询父表中的记录，即 SELECT 父表。但是对于父表的 SELECT 操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是 SELECT… LOCK IN SHARE MODE 方式，即主动对父表加个 S 锁。如果这时父表上已经加了 X 锁，子表上的操作会被阻塞。