MySQL/MariaDB的锁
本文目录：
1.MariaDB/MySQL事务提交的方式
2.MariaDB/MySQL中的锁简介
2.1 不同存储引擎支持的锁级别
2.2 锁类型
2.3 锁兼容性
3.MyISAM的表级锁(lock tables和unlock)
4.innodb的锁
4.1 查看锁信息的几种方法
4.2 innodb表的外键和锁
4.3 innodb锁算法
4.4 innodb中的锁等待超时
1.事务提交的方式
在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 变量值为1或0来设置是否自动提交，为1表示自动提交，0表示关闭自动提交，即必须显式提交。但是不管设置为0还是1，显式开启的事务必须显式提交，而且隐式提交的事务不受任何人为控制。
2.MariaDB/MySQL中的锁
锁和事务的实现是存储引擎内的组件管理的，而MariaDB/MySQL是插件式的存储引擎实现方式，所以不同的存储引擎可以支持不同级别的锁和事务。
2.1 不同存储引擎支持的锁级别
MariaDB/MySQL相比其他数据产品来说，支持的锁比较简单。
1.MyISAM、Aria(MariaDB中对myisam的改进版本)和memory存储引擎只支持表级别的锁。
2.innodb支持行级别的锁和表级别的锁，默认情况下在允许使用行级别锁的时候都会使用行级别的锁。
3.DBD存储引擎支持页级别和表级别的锁。
2.2 锁类型
在MariaDB/MySQL中只有简单的几种锁类型：
1.共享锁(S)：即读锁，不涉及修改数据，在检索数据时才申请的锁。
2.独占锁(X)：增、删、改等涉及修改操作的时候，都会申请独占锁。
以上是支持表锁的存储引擎都会有的锁类型。以下两种是支持行锁或页锁才会有的锁类型，也就是说myisam没有下面的锁，而innodb有。
3.意向共享锁(IS)：获取低级别共享锁的同时，在高级别上也获取特殊的共享锁，这种特殊的共享锁是意向共享锁。
4.意向独占锁(IX)：获取低级别独占锁的同时，在高级别上也获取特殊的独占锁，这种特殊的独占锁是意向独占锁。
低级别锁表示的是行锁或页锁，意向锁可能是多条记录组成的范围锁，也可能直接就是表意向锁。
2.3 锁兼容性
如下表：
独占锁和所有的锁都冲突，意向共享锁和共享锁兼容(这是肯定的)，还和意向独占锁兼容。所以加了意向共享锁的时候，可以修改行级非共享锁的记录。同理，加了意向独占锁的时候，可以检索这些加了独占锁的记录。
3.MyISAM的表级锁(lock tables和unlock语句)
MariaDB/MySQL中myisam和innodb都支持表级锁。表级锁分为两种：读锁(read lock)和写锁(write lock)。本节所述均为myisam支持的，同样innodb也一样支持。
可以通过语句来实现表级锁的锁定和解锁，这些语句的操作环境是当前客户端会话(即作用范围是会话)。锁表的时候可以一次性锁定多张表，并使用不同的锁，而解锁的时候只能一次性解锁当前客户端会话的所有表。
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
lock tables命令可以锁表或锁视图，锁视图的时候会自动将视图内的基表加上对应类型的锁。由于MariaDB/MySQL中触发器是基于表的，所以lock tables锁定表的时候，触发器内使用的表也都会被锁定。
例如：table1上有一个如下触发器：
CREATE TRIGGER trigger1 AFTER INSERT ON table1 FOR EACH ROW
BEGIN
INSERT INTO table2 VALUES (1);
UPDATE table3 SET writes = writes+1
WHERE id = NEW.id AND EXISTS (SELECT id FROM table4);
END;
如果为table1加上写锁，则table2、table3都会加上写锁，而table4会加上读锁。
lock tables命令会隐式释放当前客户端会话中之前的所有锁。
现在创建3张表作为测试表。
DROP TABLE IF EXISTS t1,t2,t3; CREATE TABLE t1(a INT,b CHAR(5))ENGINE=MYISAM; CREATE TABLE t2(a INT,b CHAR(5))ENGINE=MYISAM; CREATE TABLE t3(a INT,b CHAR(5))ENGINE=MYISAM; INSERT INTO t1 VALUES(1,'a'); INSERT INTO t2 VALUES(1,'a'); INSERT INTO t3 VALUES(1,'a');
给t1加上读锁。
LOCK TABLES t1 READ;
此时当前会话将无法操作t1以外的任何表，连查询也不允许，因为只有t1表加了锁。而其他会话则可以进行查询，但不能进行更新。
当再次使用lock tables命令的时候，会先释放当前会话之前所有的锁，再对lock tables命令中的表申请锁。
例如，上面会话1锁了表t1，此时无法操作t2表。现在对t2表lock table。
lock tables t2 read;
此时就可以操作t2表而不能操作t1表了，因为对t1表的锁已经释放了。
使用lock tables给表加读锁的时候，还有一个选项local，该选项表示对当前现有的记录加上锁，不影响其他会话的插入记录语句。但是否真的能插入，由变量concurrent_insert决定，该变量默认值为auto。关于并发插入，见我翻译的官方手册：https://mariadb.com/kb/zh-cn/concurrent-inserts/。
如果设置为2，那么对myisam表的并发插入有一定提升。
现在测试默认的情况，即 concurrent_insert=auto 的情况。
insert into t1 values(2,'c'),(3,'d'),(4,'e'),(5,'f'); show variables like "%concurrent_insert%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | concurrent_insert | AUTO | +-------------------+-------+ 1 row in set lock tables t1 read local;
在另一个会话中插入一条记录，这是允许的操作。当然，在锁表的会话中肯定是不能插入的。
insert into t1 values(8,'h');
解锁，并删除中间的两条记录，形成空洞。然后再锁定表。
mysql> unlock tables; mysql> delete from t1 where a=3 or a=4; mysql> lock tables t1 read local;
在其他会话中插入记录。会发现被阻塞。当表解锁后立即成功插入。
insert into t1 values(3,'h'),(9,'i'),(8,'g');
将concurrent_insert设置为2，即always，此时不管是否有空洞都允许向myisam表尾部插入。
delete from t1 where a=3 or a=8 or a=9; set @@global.concurrent_insert=2; lock tables t1 read local; insert into t1 values(3,'d'),(8,'g'),(9,'i');
此时发现能够正常插入，且查询t1表发现，这些记录都插入在表的尾部。
4.innodb中的锁
innodb支持行级锁，也是在允许的情况下默认申请的锁。
SQL Server中的锁是一种稀有资源，且会在需要的时候锁升级，所以锁越多性能越差。而MariaDB/MySQL中的锁不是稀有资源，不会进行锁升级，因此锁的多少不会影响性能，1个锁和1000000个锁性能是一样的(不考虑锁占用的内存)，锁的多少只会影响并发性。
4.1 查看锁信息的几种方法
现在人为造成一个锁等待。
会话1执行：
begin; update tt set b='h' where a=1;
会话2执行：
begin; update tt set b= 'x' where a=1;
此时会话2被阻塞，进入锁等待状态。
要查看锁信息。有几种方法：
1.通过show engine innodb status来查看，其中的transactions片段可以看到事务，其中包括锁等待。
以下是没有激活任何事务的信息：
mysql> show engine innodb status; ------------ TRANSACTIONS ------------ Trx id counter 2856 Purge done for trx's n:o < 2856 undo n:o < 0 state: running History list length 25 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421383739060216, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421383739059200, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421383739057168, not started 0 lock struct(s), heap size 1136, 0 row lock(s)
三个"---TRANSACTION"表示当前开启了3个mysql会话，但这3个会话都没有任何事务。
以下是某会话开启一个事务，但没有任何锁等待的事务信息：
mysql> show engine innodb status; ------------ TRANSACTIONS ------------ Trx id counter 2857 Purge done for trx's n:o < 2856 undo n:o < 0 state: running History list length 25 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421383739060216, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421383739057168, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 2856, ACTIVE 10 sec 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1 MySQL thread id 39, OS thread handle 139909209945856, query id 1814112 localhost root Reset for next command
不难看出，这个事务是一个需要写日志的DML事务。
以下是有锁等待的事务信息：
mysql> show engine innodb status; ------------ TRANSACTIONS ------------ Trx id counter 14915 Purge done for trx's n:o < 14912 undo n:o < 0 state: running but idle History list length 896 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 14909, not started MySQL thread id 36, OS thread handle 0x7f5d57e4b700, query id 961 localhost root init show engine innodb status ---TRANSACTION 14914, ACTIVE 465 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s) MySQL thread id 34, OS thread handle 0x7f5d57e8c700, query id 959 localhost root updating update tt set b= 'x' where a=1 ------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: 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 Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000601; asc ;; 1: len 6; hex 000000003a41; asc :A;; 2: len 7; hex 2f000001580feb; asc / X ;; 3: len 4; hex 80000001; asc ;; 4: len 5; hex 6820202020; asc h ;; ------------------ ---TRANSACTION 14913, ACTIVE 490 sec 2 lock struct(s), heap size 360, 6 row lock(s), undo log entries 1 MySQL thread id 1, OS thread handle 0x7f5d57f4f700, query id 900 localhost root
从上面的结果可以看到锁等待的信息。
"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查看。
show full processlist;
从上面的结果可以看出，update语句一直处于updating状态。所以，该方法查出来的并不一定是锁等待，有可能是更新的记录太多或者其他问题，总之这里看出来的是该语句还没有执行完成。
3.查看information_schema中的数据字典
在information_schema架构下，有3个表记录了事务和锁相关的信息。分别是INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS。
这三个表可能相对复杂，以下分别说明这3张表的各列。
根据上面实验过程中的锁查看该表的部分结果如下：
mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 14914
trx_state: LOCK WAIT
trx_started: 2017-03-30 06:07:51
trx_requested_lock_id: 14914:184:3:2
trx_wait_started: 2017-03-30 06:39:25
trx_weight: 2
trx_mysql_thread_id: 34
trx_query: update tt set b= 'x' where a=1
*************************** 2. row ***************************
trx_id: 14913
trx_state: RUNNING
trx_started: 2017-03-30 06:07:26
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 1
trx_query: NULL
从结果中可以看出id为14914的事务正处于锁等待状态，该事务中要申请锁的语句是update语句，也就是说是因为该语句而导致的锁等待。
从innodb_trx表中只能查看到事务的信息，而不能看到锁相关的信息。要看锁的信息，需要查看表innodb_locks。
mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 14914:184:3:2
lock_trx_id: 14914
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: GEN_CLUST_INDEX
lock_space: 184
lock_page: 3
lock_rec: 2
lock_data: 0x000000000601
*************************** 2. row ***************************
lock_id: 14913:184:3:2
lock_trx_id: 14913
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: GEN_CLUST_INDEX
lock_space: 184
lock_page: 3
lock_rec: 2
lock_data: 0x000000000601
2 rows in set (0.00 sec)
从上面的结果中看出，锁所在的事务ID为14914，并且锁模式为独占锁，类型为record即行锁，申请锁的表为tt表，而且锁定的页数为3页，锁定的行有2行，锁定行的主键值为0x000000000601。也许会奇怪，在前面实验过程中根本就没有建立主键，这里为什么会有主键值，这是因为MySQL在加锁的时候判断是否有索引，没有索引的时候会自动隐式的添加索引(聚集索引)，从上面锁的索引为"GEN_CLUST_INDEX"可以看出。
所以我们可以知道，MariaDB/MySQL中的行锁是通过键锁(Key)来实现的(在SQL Server中有堆表的概念，SQL Server对于没有索引的表，其行锁通过rid锁来实现)。
并且从上面的两段结果也可以看到，它们的申请锁资源所处位置是相同的，正因为位置相同，所以才有了锁等待。
现在在会话1上创建索引，然后人为造成锁等待再来查看innodb_locks表。
在会话1和会话2执行：
rollback;
在会话1执行：
create index idx_tt on tt(a); begin; update tt set b='h' where a=1;
在会话2执行：
begin; update tt set b='x' where a=1;
查看innodb_locks表。
mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 14925:184:4:2
lock_trx_id: 14925
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: ind_tt
lock_space: 184
lock_page: 4
lock_rec: 2
lock_data: 1, 0x000000000601
*************************** 2. row ***************************
lock_id: 14924:184:4:2
lock_trx_id: 14924
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: ind_tt
lock_space: 184
lock_page: 4
lock_rec: 2
lock_data: 1, 0x000000000601
2 rows in set (0.00 sec)
此处发现，锁的索引类型为ind_tt，而锁住行的主键值已经变为1个1了。
查出了锁的信息后，就可以人为的判断出锁等待信息。但是当事务比较大的时候，锁的信息非常繁杂，这时候通过上面的两张表无法轻易判断相关锁信息。由此要借助第三张表 innodb_lock_waits ，该表只有4列，且意义直观明了。
还是上面试验过程中造成的锁等待，查看那innodb_lock_waits表结果如下：
mysql> select * from information_schema.INNODB_LOCK_WAITS\G *************************** 1. row *************************** requesting_trx_id: 14914 requested_lock_id: 14914:184:3:2 blocking_trx_id: 14913 blocking_lock_id: 14913:184:3:2 1 row in set (0.00 sec)
可以看到，申请锁的事务ID为14914，阻塞在前方的事务ID为14913。
有了这3张表，还可以将它们联接起来更直观的显示想要的结果。如下：
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
waiting_trx_id: 14925
waiting_thread: 34
waiting_query: update tt set b='x' where a=1
blocking_trx_id: 14924
blocking_thread: 1
blocking_query: NULL
现在可以直观的看到14925事务被阻，语句为update，阻塞它的事务为14924。
还可以从以下联接语句中查看锁和事务的相关信息。
SELECT
trx_id,
trx_state,
lock_id,
lock_mode,
lock_type,
lock_table,
trx_mysql_thread_id,
trx_query
FROM
information_schema.innodb_trx t
JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id;
4.2 innodb表的外键和锁
在innodb表中，创建外键的时候若外键列上没有索引，则会在创建过程中自动在外键列上隐式地创建索引。
存在这样一种情况，当向子表中插入数据的时候，会向父表查询该表中是否存在对应的值以判断将要插入的记录是否满足外键约束，也就是说会对父表中对应的记录加上依赖性的共享锁，并在表上加意向共享锁。如果此时父表上对应的记录正好有独占锁，那么插入就会失败。同理，从子表中删除或更新记录也是一样的。
现在创建父表parent和子表child，并不要在外键列(pid)上显式创建索引。
create table parent(pid int primary key);
create table child(cid int primary key,pid int,foreign key(pid) references parent(pid));
show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`cid` int(11) NOT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`),
KEY `pid` (`pid`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
从show的结果中可以发现，已经自动添加了索引列pid。
插入一些测试记录。
insert into parent values(1),(2),(3);
在会话1中执行：
begin; delete from parent where pid=3;
在会话2中执行：
begin; insert into child select 3,3;
这时会发现会话2被阻塞了。通过innodb_trx和innodb_locks表的联合，得到如下结果：
SELECT
trx_id,
trx_state,
lock_id,
lock_mode,
lock_type,
lock_table,
trx_mysql_thread_id,
trx_query
FROM
information_schema.innodb_trx t
JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id\G
*************************** 1. row ***************************
trx_id: 14951
trx_state: LOCK WAIT
lock_id: 14951:185:3:4
lock_mode: S
lock_type: RECORD
lock_table: `test`.`parent`
trx_mysql_thread_id: 34
trx_query: insert into child select 3,3
*************************** 2. row ***************************
trx_id: 14946
trx_state: RUNNING
lock_id: 14946:185:3:4
lock_mode: X
lock_type: RECORD
lock_table: `test`.`parent`
trx_mysql_thread_id: 1
trx_query: NULL
不难看出，insert语句想要在父表parent上的资源"14951:185:3:4"加共享锁，但是此时父表上该资源已经有了独占锁，所以被阻塞了。
并且也可以判断出，通过外键读取父表时的模式是lock in share mode，而不是基于快照的行版本读(什么是lock in share mode和行版本快照读见事务隔离级别内容)，假如是基于行版本的快照读，那么就可以查出存在pid=3的记录而导致子表插入成功，这样也可能导致父表和子表不满足外键约束。
4.3 innodb锁算法
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。然后插入几个被分隔的记录。
create table t(id int); create unique index idx_t on t(id); insert into t values(1),(2),(3),(4),(7),(8),(12),(15);
在会话1执行：无需知道lock in share mode是什么意思，只需知道它的作用是在读取的时候加上共享锁并且不释放，具体内容在事务章节中会说明。
begin; select * from t where id<5 lock in share mode;
在会话2执行：
insert into t values(9); insert into t values(6);
这时发现第一条插入语句是正常插入的，而第二条语句被阻塞。 show engine innodb status 看结果。
mysql> show engine innodb status; ------------ TRANSACTIONS ------------ Trx id counter 14992 Purge done for trx's n:o < 14987 undo n:o < 0 state: running but idle History list length 914 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 50, OS thread handle 0x7f5d57e0a700, query id 1495 localhost root init show engine innodb status ---TRANSACTION 14991, ACTIVE 17 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 49, OS thread handle 0x7f5d57d88700, query id 1491 localhost root update insert into t values(6) ------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 187 page no 4 n bits 80 index `idx_t` of table `test`.`t` trx id 14991 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000007; asc ;; 1: len 6; hex 00000000060c; asc ;; ------------------ ---TRANSACTION 14989, ACTIVE 32 sec 2 lock struct(s), heap size 360, 5 row lock(s) MySQL thread id 43, OS thread handle 0x7f5d57f0e700, query id 1489 localhost root
其中"locks gap"就表示阻塞insert语句的锁是gap锁，即范围锁。锁定的范围包括(-∞,4],(4,7](锁到操作行的下一个key，此处插入id=6，由于存在id=7的key，所以锁到7为止，这就是next-key的意思)。当测试插入或修改-1,0,5,6等小于7的值都会被阻塞，而插入或修改大于7的值就不会被阻塞。
如何判断锁定的范围大小？可以通过下面的查询语句：
mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 2856:109:4:6
lock_trx_id: 2856
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`t`
lock_index: idx_t
lock_space: 109
lock_page: 4
lock_rec: 6
lock_data: 7
*************************** 2. row ***************************
lock_id: 421383739058184:109:4:6
lock_trx_id: 421383739058184
lock_mode: S
lock_type: RECORD
lock_table: `test`.`t`
lock_index: idx_t
lock_space: 109
lock_page: 4
lock_rec: 6
lock_data: 7
2 rows in set (0.000 sec)
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上执行：
# 首先回滚 rollback; begin; select * from t where id>10 lock in share mode;
在会话2执行：
insert into t values(0); insert into t values(5); insert into t values(100);
会发现任何插入都是阻塞的。即锁定的范围为(-∞,+∞)，等价于锁定了整张表。
但是如果使用的等于号，那么在查找索引的时候发现只需锁定一条记录和下一条记录中间的范围即可。
在会话1执行：
# 首先回滚 rollback; begin; select * from t where id=5 lock in share mode;
在会话2执行：
insert into t values(0); insert into t values(10);
会发现上述插入都是允许的。
但如果插入id=6的记录，则阻塞，因为锁定的范围为[5,7]区间。
也就是说，在有索引的情况下，如果是非具体的行锁，那么就会将能扫描到的索引键值内的所有范围加锁。
下面测试没有索引的情况。
2.无索引的情况
首先创建没有索引的表，然后插入一些分隔的记录。
create table ttt(id int); insert into ttt values(1),(2),(3),(4),(7),(8),(12),(15);
在会话1上执行：
begin; select * from ttt where id=4 lock in share mode;
在会话2上执行：
insert into ttt values(5); insert into ttt values(100); insert into ttt values(0);
会发现不管是插入哪些记录，都会被阻塞。因为没有索引键值的时候，自动隐式创建索引会锁定整个区间。查看下innodb的事务状态。
mysql> show engine innodb status; ------------ TRANSACTIONS ------------ Trx id counter 15102 Purge done for trx's n:o < 15096 undo n:o < 0 state: running but idle History list length 944 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 15066, not started MySQL thread id 53, OS thread handle 0x7f5d57d47700, query id 1615 localhost root ---TRANSACTION 15065, not started MySQL thread id 52, OS thread handle 0x7f5d57dc9700, query id 1590 localhost root ---TRANSACTION 15097, not started MySQL thread id 51, OS thread handle 0x7f5d57ecd700, query id 1637 localhost root ---TRANSACTION 0, not started MySQL thread id 50, OS thread handle 0x7f5d57e0a700, query id 1642 localhost root init show engine innodb status ---TRANSACTION 15101, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 49, OS thread handle 0x7f5d57d88700, query id 1641 localhost root update insert into ttt values(0) ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 190 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`ttt` trx id 15101 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION 15087, ACTIVE 215 sec 2 lock struct(s), heap size 360, 9 row lock(s) MySQL thread id 43, OS thread handle 0x7f5d57f0e700, query id 1631 localhost root
可以发现，这时的锁不是范围锁，因为没有了locks gap，但却仍然是行锁而不是表锁，只不过此时等价于表锁。如下
mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
lock_id: 15102:190:3:1
lock_trx_id: 15102
lock_mode: X
lock_type: RECORD
lock_table: `test`.`ttt`
lock_index: GEN_CLUST_INDEX
lock_space: 190
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 15087:190:3:1
lock_trx_id: 15087
lock_mode: S
lock_type: RECORD
lock_table: `test`.`ttt`
lock_index: GEN_CLUST_INDEX
lock_space: 190
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
发现确实是行锁而非表锁。并且索引键值那里为"supermum pseudo-record"，这表示锁定的是"最大上界伪记录"，即锁定的是无穷值。
没索引的时候，哪怕查询具体的行记录都会锁定整个区间，更不用说锁定范围(例如：where id>5)。其实它们的结果都是一样的：锁定整个区间。
4.4 innodb中的锁等待超时
在innodb存储引擎中，当出现锁等待时，如果等待超时，将会结束事务，超时时长通过动态变量innodb_lock_wait_timeout值来决定，默认是等待50秒。关于锁等待超时，可以直接在语句中设置超时时间。可以设置锁等待超时时间的语句包括：wait n的n单位为秒，nowait表示永不超时。
ALTER TABLE tbl_name [WAIT n|NOWAIT] ... CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ... DROP INDEX ... [WAIT n|NOWAIT] DROP TABLE tbl_name [WAIT n|NOWAIT] ... LOCK TABLE ... [WAIT n|NOWAIT] OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT] RENAME TABLE tbl_name [WAIT n|NOWAIT] ... SELECT ... FOR UPDATE [WAIT n|NOWAIT] SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT] TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]
超时后结束事务的方式有中断性结束和回滚性结束两种方式，这也是通过变量来控制的，该变量为innodb_rollback_on_timeout，默认为off，即超时后不回滚，也即中断性结束。
mysql> show variables like "innodb%timeout"; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | +-----------------------------+-------+
回到Linux系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处：http://www.cnblogs.com/f-ck-need-u/p/8995475.html
注：若您觉得这篇文章还不错请点击右下角推荐，您的支持能激发作者更大的写作热情，非常感谢！
上一篇： 小米3S价格多少？ 或延续千元级神话
下一篇： 年终总结双旦将至 买这几款爆款新机来跨年