开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据。为此就有了锁（locking）机制，这也是数据库系统区别于文件系统的一个关键特性。InnoDB存储引擎较之MySQL数据库的其他存储引擎，在这方面技高一筹，其实现方式非常类似于Oracle数据库。只有正确了解内部这些锁的机制，才能完全发挥InnoDB存储引擎在锁方面的优势。
什么是锁
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。注意：这里说的是“共享资源”，而不仅仅是“行记录”。
InnoDB存储引擎会在行级别上对表数据上锁，这固然不错。不过InnoDB存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
虽然现在数据库系统做得越来越类似，但是有多少种数据库，就可能有多少种锁的实现方法。在SQL语法层面，因为SQL标准的存在，要熟悉多个关系数据库系统并不是一件难事。而对于锁，你可能对某个特定的关系数据库系统的锁定模型有一定的经验，但这并不意味着你知道其他数据库，它们对于锁的实现完全不同。
对于MyISAM引擎来说，其锁是表锁。并发情况下的读没有问题，但是并发插入时的性能就要差一些了，若插入是在“底部”的情况，MyISAM引擎还是可以有一定的并发操作。对于Microsoft SQL Server来说，在Microsoft SQL Server 2005版本之前都是页锁的，相对表锁的MyISAM引擎来说，并发性能有所提高。到2005版本，Microsoft SQL Server开始支持乐观并发和悲观并发。在乐观并发下开始支持行级锁，但是其实现方式与InnoDB存储引擎的实现方式完全不同。你会发现在Microsoft SQL Server下，锁是一种稀有的资源，锁越多，开销就越大，因此它会有锁升级。在这种情况下，行锁会升级到表锁，这时并发的性能又回到了以前。
InnoDB存储引擎锁的实现和Oracle非常类似，提供一致性的非锁定读、行级锁支持，行级锁没有相关的开销，可以同时得到并发性和一致性。
锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁：
- 共享锁（S Lock），允许事务读一行数据。
- 排他锁（X Lock），允许事务删除或者更新一行数据。
当一个事务已经获得了行r的共享锁，那么另外的事务可以立即获得行r的共享锁，因为读取并没有改变行r的数据，我们称这种情况为锁兼容。
但如果有事务想获得行r的排他锁，则它必须等待事务释放行r上的共享锁——这种情况我们称为锁不兼容。表6-1列出了共享锁和排他锁的兼容性。
InnoDB存储引擎支持多粒度锁定，这种锁定允许在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，我们称之为意向锁。意向锁是表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。
InnoDB存储引擎支持两种意向锁：
- 意向共享锁（IS Lock），事务想要获得一个表中某几行的共享锁。
- 意向排他锁（IX Lock），事务想要获得一个表中某几行的排他锁。
因为InnoDB存储引擎支持的是行级别的锁，所以意向锁其实不会阻塞除全表扫以外的任何请求。
可以通过SHOW ENGINE INNODB STATUS命令来查看当前请求锁的信息：
show engine innodb statusG
可以看到SQL语句select * from t where a＜4 lock in share mode在等待，RECORD LOCKS space id 30 page no 3 n bits 72 index'PRIMARY'of table'test'.'t'trx id 48B89BD lock_mode X locks rec but not gap表示锁住的资源。locks rec but not gap代表锁住是一个索引，不是一个范围。
在InnoDB Plugin之前，我们只能通过SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS等命令来查看当前的数据库请求，然后再判断当前事务中锁的情况。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架构下添加了INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，可以更简单地监控当前的事务并分析可能存在的锁的问题。
通过实例我们来分析这三张表，先看表INNODB_TRX，INNODB_TRX由8个字段组成：
trx_id：InnoDB存储引擎内部唯一的事务ID。
trx_state：当前事务的状态。
trx_started：事务的开始时间。
trx_requested_lock_id：等待事务的锁ID。如trx_state的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state不是LOCK WAIT，则该值为NULL。
trx_wait_started：事务等待开始的时间。
trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小的进行回滚。
trx_mysql_thread_id：MySQL中的线程ID，SHOW PROCESSLIST显示的结果。
trx_query：事务运行的SQL语句。在实际使用中发现，该值有时会显示为NULL（不知道是不是Bug）。
一个具体的例子如下：
select * from information_schema.INNODB_TRX;
可以看到，事务730FEE当前正在运行，而事务7311F4目前处于“LOCK WAIT”状态，运行的SQL语句是select * from parent lock in share mode。这个只是显示了当前运行的InnoDB的事务，并不能判断锁的一些情况。
如果需要查看锁，则需要INNODB_LOCKS表，该表由如下字段组成：
lock_id：锁的ID。
lock_trx_id：事务ID。
lock_mode：锁的模式。
lock_type：锁的类型，表锁还是行锁。
lock_table：要加锁的表。
lock_index：锁的索引。
lock_space：InnoDB存储引擎表空间的ID号。
lock_page：被锁住的页的数量。若是表锁，则该值为NULL。
lock_rec：被锁住的行的数量。若是表锁，则该值为NULL。
lock_data：被锁住的行的主键值。当是表锁时，该值为NULL。
接着上面的例子，我们继续查看INNODB_LOCKS表：
select * from information_schema.INNODB_LOCKS;
这次可能看到当前锁的信息了，ID为730FEE的事务向表parent加了一个X的行锁，ID为7311F4的事务向表parent申请了一个S的行锁。lock_data都是1，申请相同的资源，因此会有等待。这也可以解释INNODB_TRX中为什么一个事务的trx_state是“RUNNING”，另一个是“LOCK WAIT”了。
另外需要注意的是，我发现lock_data这个值并非是“可信”的值。例如当我们运行一个范围查找时，lock_data可能只返回第一行的主键值。另一个不能忽视的是，如果当前资源被锁住了，与此同时，由于锁住的页因为InnoDB存储引擎缓冲池的容量，而导致替换缓冲池该页，当查看INNODB_LOCKS表时，该值会显示为NULL，即InnoDB存储引擎不会从磁盘进行再一次查找。
查出了每张表上锁的情况后，我们可以来判断由此而引发的等待情况了。当事务较小时，我们人为地、直观地就可以进行判断了。但是当事务量非常大，锁和等待也时常发生时，这个时候不容易判断，但是通过INNODB_LOCK_WAITS，可以很直观地反映出当前的等待。INNODB_LOCK_WAITS由4个字段组成：
requesting_trx_id：申请锁资源的事务ID。
requesting_lock_id：申请的锁的ID。
blocking_trx_id：阻塞的事务ID。
blocking_trx_id：阻塞的锁的ID。
接着上面的例子，运行如下查询：
select * from information_schema.INNODB_LOCK_WAITS;
这次我们可以清楚直观地看到哪个事务阻塞了另一个事务。当然这里只给出了事务和锁的ID，如果需要根据INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS这三张表直观地看到详细信息，我们可以执行如下联合查询：
SELECT
r.trx_id waiting_trx_id ,
r.trx_mysql_thread_id waiting_thread ,
r.trx_query waiting_query ,
b.trx_id blocking_trx_id ,
b.trx_mysql_thread_id blocking_thread ,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
一致性的非锁定读操作
一致性的非锁定行读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作，这时读取操作不会因此等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据。
上图直观地展现了InnoDB存储引擎一致性的非锁定读。之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行之前版本的数据，该实现是通过Undo段来实现。而Undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。
可以看到，非锁定读的机制大大提高了数据读取的并发性，在InnoDB存储引擎默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。同样，即使都是使用一致性读，但是对于快照数据的定义也不相同。
，快照数据其实就是当前行数据之前的历史版本，可能有多个版本。就上图显示的，一个行可能有不止一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。
在Read Committed和Repeatable Read（InnoDB存储引擎的默认事务隔离级别）下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在Read Committed事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。在Repeatable Read事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
我们来看个例子，在一个MySQL的连接会话A中执行如下事务：
#Session A
begin;
select * from parent where id=1;
会话A中事务已Begin（开始），读取了id=1的数据，但是没有结束事务。这时我们再开启另一个会话B，这样可以模拟并发的情况，然后对会话B做如下操作：
begin;
update parent set id=3 where id=1;
会话B中将id=1的行修改为id=3，但是事务同样没有提交，这样id=1的行其实加了一个X锁。这时如果再在会话A中读取id=1的数据，根据InnoDB存储引擎的特性，在Read Committed和Repeatable Read的事务隔离级别下，会使用非锁定的一致性读。
我们回到会话A，接着上次未提交的事务，执行select * from parent where id=1的操作，这时不管使用Read Committed还是Repeatable的事务隔离级别，显示的数据应该都是：
select * from parent where id=1;
因为当前id=1的数据被修改了1次，因此只有一个版本的数据。接着，我们在会话B中提交上次的事务。如：
#Session B
commit;
会话B提交事务后，这时在会话A中再运行select * from parent where id=1的SQL语句，在Read Committed和Repeatable事务隔离级别下得到的结果就不一样了。对于Read Committed的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照（fresh snapshot）。在上述例子中，因为会话B已经提交了事务，所以Read Committed事务隔离级别下会得到如下结果：
select @@tx_isolation;
+--------------------------+
|@@tx_isolation
|READ-COMMITTED
+--------------------------+
select * from parent where id=1;
Empty set（0.00 sec）
对于Repeatable的事务隔离级别，总是读取事务开始时的行数据。因此对于Repeatable Read事务隔离级别，其结果如下：
select @@tx_isolation;
+---------------------------+
|@@tx_isolation
|REPEATABLE-READ
+---------------------------+
select * from parent where id=1;
1 row in set（0.00 sec）
下面将从时间的角度展现上述演示的示例。对于Read Committed的事务隔离级别而言，从数据库理论的角度来看，其实违反了事务ACID中的I的特性，即隔离性。
SELECT……FOR UPDATE＆SELECT……LOCK IN SHARE MODE
在默认情况下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，我们需要对读取操作进行加锁。InnoDB存储引擎对于SELECT语句支持两种加锁操作：
SELECT……FOR UPDATE对读取的行记录加一个X锁。其他事务想在这些行上加任何锁都会被阻塞。
SELECT……LOCK IN SHARE MODE对读取的行记录加一个S锁。其他事务可以向被锁定的记录加S锁，但是对于加X锁，则会被阻塞。
对于一致性非锁定读，即使读取的行已被使用SELECT……FOR UPDATE，也是可以进行读取的。另外，SELECT……FOR UPDATE，SELECT……LOCK IN SHARE MODE必须在一个事务中，当事务提交了，锁也就释放了。因为在使用上述两句SELECT锁定语句时，务必加上BEGIN、START TRANSACTION或者SET AUTOCOMMIT=0。
自增长和锁
自增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对含有自增长计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：
SELECT MAX(auto_inc_col) FROM t FOR UPDATE；
插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但这里还是存在一些问题。首先，对于有自增长值的列的并发插入性能较差，所以必须等待前一个插入的完成（虽然不用等待事务的完成）。其次，对于INSERT……SELECT的大数据量的插入，会影响插入的性能，因为另一个事务中的插入会被阻塞。
从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从MySQL 5.1.22版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode，默认值为1。
在继续讨论新的自增长实现方式之前，我们需要对自增长的插入进行分类：
INSERT-like：INSERT-like指所有的插入语句，如INSERT、REPLACE、INSERT……SELECT、REPLACE……SELECT、LOAD DATA等。
Simple inserts：Simple inserts指能在插入前就确定插入行数的语句。这些语句包括INSERT、REPLACE等。需要注意的是：Simple inserts不包含INSERT……ON DUPLICATE KEY UPDATE这类SQL语句。
Bulk inserts：Bulk inserts指在插入前不能确定得到插入行数的语句，如INSERT……SELECT，REPLACE……SELECT，LOAD DATA。
Mixed-mode inserts：Mixed-mode inserts指插入中有一部分的值是自增长的。有一部分是确定的，如：INSERT INTO t1（c1，c2）VALUES（1，'a'），（NULL，'b'），（5，'c'），（NULL，'d'），也可以是指INSERT……ON DUPLICATE KEY UPDATE这类SQL语句。
参数innodb_autoinc_lock_mode有三个可选值：
innodb_autoinc_lock_mode=0这是5.1.22版本之前自增长的实现方式，即通过表锁的AUTO-INC Locking方式。因为有了新的自增长实现方式，所以0这个选项不应该是你的首选项。
innodb_autoinc_lock_mode=1这是该参数的默认值。对于“Simple inserts”，该值会用互斥量（mutex）去对内存中的计数器进行累加的操作。对于“Bulk inserts”，还是使用传统表锁的AUTO-INC Locking方式。这样做，如果不考虑回滚操作，对于自增值的增长还是连续的。而且在这种方式下，Statement-Based方式的Replication还是能很好地工作。需要注意的是，如果已经使用AUTO-INC Locing的方式产生自增长的值，而这时需要再进行“Simple inserts”的操作时，还是要等待AUTO-INC Locking的释放。
innodb_autoinc_lock_mode=2在这个模式下，对于所有“INSERT-like”自增长值的产生都是通过互斥量，而不是AUTO-INC Locking的方式。显然，这是最高性能的方式。然而，这会带来一定的问题。因为并发插入的存在，所以每次插入时，自增长的值可能不是连续的。此外，最重要的是，基于Statement-Base Replication会出现问题。因此，使用这个模式，任何时候都应该使用Row-Base Replication。这样才能保证最大的并发性能和Replication数据的同步。
对于自增长另外需要注意的是，InnoDB存储引擎中的实现和MyISAM不同，MyISAM是表锁的，自增长不用考虑并发插入的问题。因此在Master用InnoDB存储引擎，Slave用MyISAM存储引擎的Replication架构下你必须考虑这种情况。
另外，InnoDB存储引擎下，自增长值的列必须是索引，并且是索引的第一个列，如果是第二个列则会报错；而MyISAM存储引擎则没有这个问题，如：
create table t(a int auto_increment,b int,key(b,a))engine=InnoDB;
ERROR 1075（42000）：Incorrect table definition；there can be only one auto
column and it must be defined as a key
create table t(a int auto_increment,b int,key(b,a))engine=MyISAM;
Query OK，0 rows affected（0.01 sec）
外键和锁
外键主要用于引用完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁——这比Oracle做得好，Oracle不会自动添加索引，用户必须自己手工添加，这也是导致很多死锁问题产生的原因。
对于外键值的插入或者更新，首先需要查询父表中的记录，即SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT……LOCK IN SHARE MODE方式，主动对父表加一个S锁。如果这时父表上已经这样加X锁，那么子表上的操作会被阻塞，如下面的例子所示：
上面的例子中，两个事务都没有COMMIT或者ROLLBACK，这时Session B的操作会被阻塞。因为id=3的父表上在Session A中已经加了一个X锁，而这时我们需要对父表中id=3的行加一个S锁，这时insert的操作会被阻塞。设想如果访问父表时，使用的是一致性的非锁定读，这时Session B会读到父表有id=3的记录，可以进行插入操作。但是如果Session A对事务提交了，则父表中就没有id=3的记录。数据在父子表就会存在不一致的情况。如果我们查询INNODB_LOCKS表，会得到如下结果：
select * from information_schema.INNODB_LOCKSG
***************************1.row*************************** lock_id：7573B8：96：3：4 lock_trx_id：7573B8 lock_mode：S lock_type：RECORD lock_table：'mytest'.'parent' lock_index：'PRIMARY' lock_space：96 lock_page：3 lock_rec：4 lock_data：3 ***************************2.row*************************** lock_id：7573B3：96：3：4 lock_trx_id：7573B3 lock_mode：X lock_type：RECORD lock_table：'mytest'.'parent' lock_index：'PRIMARY' lock_space：96 lock_page：3 lock_rec：4 lock_data：3