官方手册：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

在MariaDB/MySQL中有3种事务提交的方式。

1.显式开启和提交。

使用begin或者start transaction来显式开启一个事务，显式开启的事务必须使用commit或者rollback显式提交或回滚。几种特殊的情况除外：行版本隔离级别下的更新冲突和死锁会自动回滚。

在存储过程中开启事务时必须使用start transaction，因为begin会被存储过程解析为begin...end结构块。

另外，MariaDB/MySQL中的DDL语句会自动提交前面所有的事务（包括显示开启的事务），而在SQL Server中DDL语句还是需要显式提交的，也就是说在SQL Server中DDL语句也是可以回滚的。

2.自动提交。(MySQL默认的提交方式)

不需要显式begin或者start transaction来显式开启事务，也不需要显式提交或回滚事务，每次执行DML和DDL语句都会在执行语句前自动开启一个事务，执行语句结束后自动提交或回滚事务。

3.隐式提交事务

隐式提交事务是指执行某些语句会自动提交事务，包括已经显式开启的事务。

会隐式提交事务的语句主要有：

(1).DDL语句(其中有truncate table)。

(2).隐式修改mysql数据库架构的操作:create user,drop user,grant,rename user,revoke,set password。

(3).管理语句：analyze table、cache index、check table、load index into cache、optimize table、repair table。

通过设置 auto_commit

锁和事务的实现是存储引擎内的组件管理的，而MariaDB/MySQL是插件式的存储引擎实现方式，所以不同的存储引擎可以支持不同级别的锁和事务。

MariaDB/MySQL相比其他数据产品来说，支持的锁比较简单。

1.MyISAM、Aria(MariaDB中对myisam的改进版本)和memory存储引擎只支持表级别的锁。

2.innodb支持行级别的锁和表级别的锁，默认情况下在允许使用行级别锁的时候都会使用行级别的锁。

3.DBD存储引擎支持页级别和表级别的锁。

在MariaDB/MySQL中只有简单的几种锁类型：

1.共享锁(S)：即读锁，不涉及修改数据，在检索数据时才申请的锁。

2.独占锁(X)：增、删、改等涉及修改操作的时候，都会申请独占锁。

以上是支持表锁的存储引擎都会有的锁类型。以下两种是支持行锁或页锁才会有的锁类型，也就是说myisam没有下面的锁，而innodb有。

3.意向共享锁(IS)：获取低级别共享锁的同时，在高级别上也获取特殊的共享锁，这种特殊的共享锁是意向共享锁。

4.意向独占锁(IX)：获取低级别独占锁的同时，在高级别上也获取特殊的独占锁，这种特殊的独占锁是意向独占锁。

低级别锁表示的是行锁或页锁，意向锁可能是多条记录组成的范围锁，也可能直接就是表意向锁。

如下表：

独占锁和所有的锁都冲突，意向共享锁和共享锁兼容(这是肯定的)，还和意向独占锁兼容。所以加了意向共享锁的时候，可以修改行级非共享锁的记录。同理，加了意向独占锁的时候，可以检索这些加了独占锁的记录。

MariaDB/MySQL中myisam和innodb都支持表级锁。表级锁分为两种：读锁(read lock)和写锁(write lock)。本节所述均为myisam支持的，同样innodb也一样支持。

可以通过语句来实现表级锁的锁定和解锁，这些语句的操作环境是当前客户端会话(即作用范围是会话)。锁表的时候可以一次性锁定多张表，并使用不同的锁，而解锁的时候只能一次性解锁当前客户端会话的所有表。

lock tables命令可以锁表或锁视图，锁视图的时候会自动将视图内的基表加上对应类型的锁。由于MariaDB/MySQL中触发器是基于表的，所以lock tables锁定表的时候，触发器内使用的表也都会被锁定。

例如：table1上有一个如下触发器：

如果为table1加上写锁，则table2、table3都会加上写锁，而table4会加上读锁。

lock tables命令会隐式释放当前客户端会话中之前的所有锁。

现在创建3张表作为测试表。

给t1加上读锁。

此时当前会话将无法操作t1以外的任何表，连查询也不允许，因为只有t1表加了锁。而其他会话则可以进行查询，但不能进行更新。

当再次使用lock tables命令的时候，会先释放当前会话之前所有的锁，再对lock tables命令中的表申请锁。

例如，上面会话1锁了表t1，此时无法操作t2表。现在对t2表lock table。

此时就可以操作t2表而不能操作t1表了，因为对t1表的锁已经释放了。

使用lock tables给表加读锁的时候，还有一个选项local，该选项表示对当前现有的记录加上锁，不影响其他会话的插入记录语句。但是否真的能插入，由变量concurrent_insert决定，该变量默认值为auto。关于并发插入，见我翻译的官方手册：https://mariadb.com/kb/zh-cn/concurrent-inserts/。

如果设置为2，那么对myisam表的并发插入有一定提升。

现在测试默认的情况，即 concurrent_insert=auto

在另一个会话中插入一条记录，这是允许的操作。当然，在锁表的会话中肯定是不能插入的。

解锁，并删除中间的两条记录，形成空洞。然后再锁定表。

在其他会话中插入记录。会发现被阻塞。当表解锁后立即成功插入。

将concurrent_insert设置为2，即always，此时不管是否有空洞都允许向myisam表尾部插入。

此时发现能够正常插入，且查询t1表发现，这些记录都插入在表的尾部。

默认情况下，使用表级锁的存储引擎中(所以innodb不支持)，写锁的优先级高于读锁。这意味着，当表上已经有一个写锁的时候，后续的写操作、读操作都会队列化，且队列中的写操作总是在读操作之前执行，即使写操作比读操作后到达MySQL/MariaDB服务器。可以改变这种优先级。详细内容见：。

innodb支持行级锁，也是在允许的情况下默认申请的锁。

SQL Server中的锁是一种稀有资源，且会在需要的时候锁升级，所以锁越多性能越差。而MariaDB/MySQL中的锁不是稀有资源，不会进行锁升级，因此锁的多少不会影响性能，1个锁和1000000个锁性能是一样的(不考虑锁占用的内存)，锁的多少只会影响并发性。

现在人为造成一个锁等待。

会话1执行：

会话2执行：

此时会话2被阻塞，进入锁等待状态。

要查看锁信息。有几种方法：

1.通过show engine innodb status来查看，其中的transactions片段可以看到事务，其中包括锁等待。

以下是没有激活任何事务的信息：

三个"---TRANSACTION"表示当前开启了3个mysql会话，但这3个会话都没有任何事务。

以下是某会话开启一个事务，但没有任何锁等待的事务信息：

不难看出，这个事务是一个需要写日志的DML事务。

以下是有锁等待的事务信息：

从上面的结果可以看到锁等待的信息。

"TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED"表示该事务申请锁已经等待了13秒。

"RECORD LOCKS space id 184 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`tt` trx id 14914 lock_mode X waiting"表示test.tt表上的记录要申请的行锁(recode lock)是独占锁并且正在waiting，并且标明了该行记录所在表数据文件中的物理位置：表空间id为184，页码为3。

关于这些信息的详细解释，后文会逐渐说明。

2.使用show processlist查看。

从上面的结果可以看出，update语句一直处于updating状态。所以，该方法查出来的并不一定是锁等待，有可能是更新的记录太多或者其他问题，总之这里看出来的是该语句还没有执行完成。

3.查看information_schema中的数据字典

在information_schema架构下，有3个表记录了事务和锁相关的信息。分别是INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS。

这三个表可能相对复杂，以下分别说明这3张表的各列。

根据上面实验过程中的锁查看该表的部分结果如下：

从结果中可以看出id为14914的事务正处于锁等待状态，该事务中要申请锁的语句是update语句，也就是说是因为该语句而导致的锁等待。

从innodb_trx表中只能查看到事务的信息，而不能看到锁相关的信息。要看锁的信息，需要查看表innodb_locks。

从上面的结果中看出，锁所在的事务ID为14914，并且锁模式为独占锁，类型为record即行锁，申请锁的表为tt表，而且锁定的页数为3页，锁定的行有2行，锁定行的主键值为0x000000000601。也许会奇怪，在前面实验过程中根本就没有建立主键，这里为什么会有主键值，这是因为MySQL在加锁的时候判断是否有索引，没有索引的时候会自动隐式的添加索引(聚集索引)，从上面锁的索引为"GEN_CLUST_INDEX"可以看出。

所以我们可以知道，MariaDB/MySQL中的行锁是通过键锁(Key)来实现的(在SQL Server中有堆表的概念，SQL Server对于没有索引的表，其行锁通过rid锁来实现)。

并且从上面的两段结果也可以看到，它们的申请锁资源所处位置是相同的，正因为位置相同，所以才有了锁等待。

现在在会话1上创建索引，然后人为造成锁等待再来查看innodb_locks表。

在会话1和会话2执行：

在会话1执行：

在会话2执行：

查看innodb_locks表。

此处发现，锁的索引类型为ind_tt，而锁住行的主键值已经变为1个1了。

查出了锁的信息后，就可以人为的判断出锁等待信息。但是当事务比较大的时候，锁的信息非常繁杂，这时候通过上面的两张表无法轻易判断相关锁信息。由此要借助第三张表 innodb_lock_waits

还是上面试验过程中造成的锁等待，查看那innodb_lock_waits表结果如下：

可以看到，申请锁的事务ID为14914，阻塞在前方的事务ID为14913。

有了这3张表，还可以将它们联接起来更直观的显示想要的结果。如下：

现在可以直观的看到14925事务被阻，语句为update，阻塞它的事务为14924。

还可以从以下联接语句中查看锁和事务的相关信息。

在innodb表中，创建外键的时候若外键列上没有索引，则会在创建过程中自动在外键列上隐式地创建索引。

存在这样一种情况，当向子表中插入数据的时候，会向父表查询该表中是否存在对应的值以判断将要插入的记录是否满足外键约束，也就是说会对父表中对应的记录加上依赖性的共享锁，并在表上加意向共享锁。如果此时父表上对应的记录正好有独占锁，那么插入就会失败。同理，从子表中删除或更新记录也是一样的。

现在创建父表parent和子表child，并不要在外键列(pid)上显式创建索引。

从show的结果中可以发现，已经自动添加了索引列pid。

插入一些测试记录。

在会话1中执行：

在会话2中执行：

这时会发现会话2被阻塞了。通过innodb_trx和innodb_locks表的联合，得到如下结果：

不难看出，insert语句想要在父表parent上的资源"14951:185:3:4"加共享锁，但是此时父表上该资源已经有了独占锁，所以被阻塞了。

并且也可以判断出，通过外键读取父表时的模式是lock in share mode，而不是基于快照的行版本读(什么是lock in share mode和行版本快照读见事务隔离级别内容)，假如是基于行版本的快照读，那么就可以查出存在pid=3的记录而导致子表插入成功，这样也可能导致父表和子表不满足外键约束。

innodb支持行级锁，但是它还支持范围锁。即对范围内的行记录加行锁。

有三种锁算法：

• 1.record lock：即行锁
• 2.gap lock：范围锁，但是不锁定行记录本身
• 3.next-key lock：范围锁加行锁，即范围锁并锁定记录本身，gap lock + record lock。

record lock是行锁，但是它的行锁锁定的是key，即基于唯一性索引键列来锁定(SQL Server还有基于堆表的rid类型行锁)。如果没有唯一性索引键列，则会自动在隐式列上创建索引并完成锁定。

next-key lock是行锁和范围锁的结合，innodb对行的锁申请默认都是这种算法。如果有索引，则只锁定指定范围内的索引键值，如果没有索引，则自动创建索引并对整个表进行范围锁定。之所以锁定了表还称为范围锁定，是因为它实际上锁的不是表，而是把所有可能的区间都锁定了，从主键值的负无穷到正无穷的所有区间都锁定，等价于锁定了表。

以下示例过程将演示范围锁的情况。

1.有索引的情况

首先创建一个有索引的表t。然后插入几个被分隔的记录。

在会话1执行：无需知道lock in share mode是什么意思，只需知道它的作用是在读取的时候加上共享锁并且不释放，具体内容在事务章节中会说明。

在会话2执行：

这时发现第一条插入语句是正常插入的，而第二条语句被阻塞。 show engine innodb status

其中"locks gap"就表示阻塞insert语句的锁是gap锁，即范围锁。锁定的范围包括(-∞,4],(4,7](锁到操作行的下一个key，此处插入id=6，由于存在id=7的key，所以锁到7为止，这就是next-key的意思)。当测试插入或修改-1,0,5,6等小于7的值都会被阻塞，而插入或修改大于7的值就不会被阻塞。

如何判断锁定的范围大小？可以通过下面的查询语句：

lock_mode为"X+GAP"，表示next-key lock算法。其中lock_data值为7，表示锁定了值为7的记录，这是最大锁定范围边界。lock_rec的值为6，表示锁定了6行记录，其中1,2,3,4,7共5行记录是通过gap锁锁定的范围，加上待插入的id=6(该行为key锁锁定)，共锁定6行记录。

而如果使用的是大于号，由于操作任何一条记录，它的下一个key都会被锁定，这等价于锁定了整个无穷区间，即实现了表锁的功能。如下：

在会话1上执行：

在会话2执行：

会发现任何插入都是阻塞的。即锁定的范围为(-∞,+∞)，等价于锁定了整张表。

但是如果使用的等于号，那么在查找索引的时候发现只需锁定一条记录和下一条记录中间的范围即可。

在会话1执行：

在会话2执行：

会发现上述插入都是允许的。

但如果插入id=6的记录，则阻塞，因为锁定的范围为[5,7]区间。

也就是说，在有索引的情况下，如果是非具体的行锁，那么就会将能扫描到的索引键值内的所有范围加锁。

下面测试没有索引的情况。

2.无索引的情况

首先创建没有索引的表，然后插入一些分隔的记录。

在会话1上执行：

在会话2上执行：

会发现不管是插入哪些记录，都会被阻塞。因为没有索引键值的时候，自动隐式创建索引会锁定整个区间。查看下innodb的事务状态。

可以发现，这时的锁不是范围锁，因为没有了locks gap，但却仍然是行锁而不是表锁，只不过此时等价于表锁。如下

发现确实是行锁而非表锁。并且索引键值那里为"supermum pseudo-record"，这表示锁定的是"最大上界伪记录"，即锁定的是无穷值。

没索引的时候，哪怕查询具体的行记录都会锁定整个区间，更不用说锁定范围(例如：where id>5)。其实它们的结果都是一样的：锁定整个区间。

在innodb存储引擎中，当出现锁等待时，如果等待超时，将会结束事务，超时时长通过动态变量innodb_lock_wait_timeout值来决定，默认是等待50秒。关于锁等待超时，可以直接在语句中设置超时时间。可以设置锁等待超时时间的语句包括：wait n的n单位为秒，nowait表示永不超时。

超时后结束事务的方式有中断性结束和回滚性结束两种方式，这也是通过变量来控制的，该变量为innodb_rollback_on_timeout，默认为off，即超时后不回滚，也即中断性结束。