CREATE TABLE t1_lock ( id int unsigned not null auto_increment, vid int unsigned not null default 0, name varchar(10) not null default '', other varchar(20) not null default '', primary key(id), key vid(vid) ) engine =InnoDB;
insert into t1_lock(vid,name,other)
values(1,'xiaoli','My name is xiaoli'),(2,'xiaozhang','My name is xiaozhang'),(3,'dog','My name is dog'),(4,'cat','My name is cat'),(4,'fish','My name is fish');
root@localhost:mysql.sock 23:08:51 [school]>root@localhost:mysql.sock 23:08:51 [school]>show index from t1_lock; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | t1_lock | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | t1_lock | 1 | vid | 1 | vid | A | 2 | NULL | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
查看事务,锁,锁等待的系统表
select * from INFORMATION_SCHEMA.innodb_trx; select * from INFORMATION_SCHEMA.innodb_locks; select * from INFORMATION_SCHEMA.innodb_lock_waits;
测试1:一致性非锁定读
一致性的非锁定行读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作，这是读取操作不会因此而会等待行上锁的释放，相反，InnoDB会去读取行的一个快照数据。
之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指改行之前版本的数据，该实现是通过undo段来实现的。但是在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。
例如：
对于read committed的事务隔离级别，他总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。
对于repeatable read(innoDB存储引擎的默认隔离级别),总是读取事务开始时的行数据。
非锁定读的机制大大提高了数据读取的并发性，在Innodb存储引擎默认设置下，这是默认的读取方式，但是在某些情况下，可以对读进行加锁，比如：
1、显式对读进行加锁，如使用 select --- for update ;select --- lock in share mode
2、在外键的插入和更新上，因为在外键的插入和更新上，对于数据的隔离性要求较高，在插入前需要扫描父表中的记录是否存在，所以，在外键的插入删除上，InnoDB会使用加S锁的方式来实现。
session1: root@localhost:mysql.sock 23:11:17 [school]>begin; #id =1 记录加排他锁 root@localhost:mysql.sock 23:11:23 [school]>select * from t1_lock where id=1 for update; +----+-----+--------+-------------------+ | id | vid | name | other | +----+-----+--------+-------------------+ | 1 | 1 | xiaoli | My name is xiaoli | +----+-----+--------+-------------------+ 1 row in set (0.08 sec)
session2: root@localhost:mysql.sock 23:11:10 [school]>begin; #以下查询不会被柱塞,这是一致性非锁定读 root@localhost:mysql.sock 23:11:49 [school]>select * from t1_lock where id=1; +----+-----+--------+-------------------+ | id | vid | name | other | +----+-----+--------+-------------------+ | 1 | 1 | xiaoli | My name is xiaoli | +----+-----+--------+-------------------+ 1 row in set (0.00 sec)
#以下查询会被柱塞,加共享锁和session1的排它锁互斥 root@localhost:mysql.sock 23:12:12 [school]>select * from t1_lock where id=1 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
测试2:无索引列查询
session1: root@localhost:mysql.sock 23:36:30 [school]>begin; root@localhost:mysql.sock 23:36:37 [school]>select * from t1_lock where name='xiaoli' for update; +----+-----+--------+-------------------+ | id | vid | name | other | +----+-----+--------+-------------------+ | 1 | 1 | xiaoli | My name is xiaoli | +----+-----+--------+-------------------+ 1 row in set (0.11 sec) session2: root@localhost:mysql.sock 23:37:07 [school]>begin; root@localhost:mysql.sock 23:37:10 [school]>select * from t1_lock where name='xiaozhang' for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
查询列上没有索引时,锁被升级(RR下,检索条件没有索引时,会导致锁扩大,全部行记录都被锁定,并且加gap锁),全部 name 的记录都会被锁定.
测试3:一个条件有索引,另一个条件没有索引
session1: root@localhost:mysql.sock 23:50:55 [school]>begin; root@localhost:mysql.sock 23:50:57 [school]>select * from t1_lock where vid=1 and name='xiaoli' for update; +----+-----+--------+-------------------+ | id | vid | name | other | +----+-----+--------+-------------------+ | 1 | 1 | xiaoli | My name is xiaoli | +----+-----+--------+-------------------+ 1 row in set (0.13 sec) session2: root@localhost:mysql.sock 23:50:47 [school]>begin; root@localhost:mysql.sock 23:51:27 [school]>select * from t1_lock where vid=1 and name='xiaozhang' for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 注意到vid = 1的时候，name = 'xiaoli',只有这一条记录. 但是session2检索的条件,vid = 1 and name = 'xiaozhang' 这样的记录并不存在,但是被锁定了,因为vid上有索引而name上没有索引,锁扩大锁定vid=1的所有记录.
如果查询vid=2 and name='xiaozhang' for update ,则不会锁定.(如测试4)
测试4:一个条件有索引,另一个条件没有索引
session1: root@localhost:mysql.sock 00:13:13 [school]>begin; root@localhost:mysql.sock 00:13:17 [school]>select * from t1_lock where vid=1 and name='xiaoli' for update; +----+-----+--------+-------------------+ | id | vid | name | other | +----+-----+--------+-------------------+ | 1 | 1 | xiaoli | My name is xiaoli | +----+-----+--------+-------------------+ 1 row in set (0.08 sec) session2: root@localhost:mysql.sock 00:13:08 [school]>begin; root@localhost:mysql.sock 00:13:28 [school]>select * from t1_lock where vid=2 and name='xiaoli' for update; Empty set (0.07 sec)
session2没有被session1柱塞
测试5:间隙锁(存在于RR隔离级别),目的防止幻读,对于插入和更新操作生效
session1: oot@localhost:mysql.sock 23:49:48 [school]>begin; root@localhost:mysql.sock 23:49:52 [school]>update t1_lock set name='s02' where vid=2;
#此处会在辅助索引vid=2上加排他锁及间隙锁(锁定范围:[1,2,3]),同时对应主键上也会加行锁
session2:
#vid=1 在间隙锁之内,被阻塞 root@localhost:mysql.sock 23:50:53 [school]>begin; root@localhost:mysql.sock 23:50:56 [school]>insert into t1_lock(vid,name,other) values(1,'s01','s01'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
#vid=2 在间隙锁之内,被阻塞
root@localhost:mysql.sock 23:58:37 [school]>begin;
root@localhost:mysql.sock 23:59:08 [school]>insert into t1_lock(vid,name,other) values(2,'s02','s02');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
#vid=3 在间隙锁之内,被阻塞
root@localhost:mysql.sock 23:51:49 [school]>begin; root@localhost:mysql.sock 23:51:52 [school]>insert into t1_lock(vid,name,other) values(3,'s03','s03'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #vid=4 不在间隙锁之内,没有被阻塞 root@localhost:mysql.sock 23:52:53 [school]>begin; root@localhost:mysql.sock 23:56:15 [school]>insert into t1_lock(vid,name,other) values(4,'s04','s04'); Query OK, 1 row affected (0.01 sec) root@localhost:mysql.sock 23:56:22 [school]>commit; Query OK, 0 rows affected (0.00 sec)
锁解释:
1.Record Lock 单个记录上的锁
2.Gap Lock 间隙锁，锁定一个范围，但不包含记录本身(gap Lock为了解决幻读问题,只有在RR隔离级别下才有间隙锁)
3.Next-key Lock,锁定一个范围和本身 Record Lock + Gap Lock
innodb 锁的特点:
1.主键索引是record lock(RR隔离级别下)
2.唯一辅助索引是record lock(RR隔离级别下)
3.普通辅助索引是next-key lock(RR隔离级别下)
4.没有索引的话,则是全表范围next-key lock(RR隔离级别下)
5.RC隔离级别下,只有record lock
6.RR & innodb_locks_unsafe_for_binlog = 1,只有record lock
详细测试gap锁
测试数据 CREATE TABLE t_gap( a INT, b INT, PRIMARY KEY (a), KEY(b)); insert into t_gap values(1,1), (3,1), (5,3), (7,6),(10,8); root@localhost:mysql.sock 00:19:34 [school]>select * from t_gap; +----+------+ | a | b | +----+------+ | 1 | 1 | | 3 | 1 | | 5 | 3 | | 7 | 6 | | 10 | 8 | +----+------+ 5 rows in set (0.00 sec)
gap测试
session1: T1:begin;SELECT * FROM t_gap WHERE b =3 FOR UPDATE; | 5 | 3 | 这条记录
T1 会加上如下锁:
b = 3, X, next-key lock,间隙锁范围[1,6]
a = 5, X , record lock
session2: T2:begin; SELECT * FROM t_gap WHERE A=5 LOCK IN SHARE MODE; 会等待
T2:begin;INSERT INTO t_gap SELECT 4,2;
会等待
T2:begin;INSERT INTO t_gap SELECT 6,5;
会等待
T2:begin;INSERT INTO t_gap SELECT 2,2;
会等待
T2:begin;INSERT INTO t_gap SELECT 4,6;
会等待
如果此例中的b列上唯一性的所有,则T2都不会等待,因为此时T1使用行锁只会锁定一条记录.
上面同样的sessoion1 session2: T2:begin;INSERT INTO t_gap SELECT 8,6;
没有被柱塞,原因是8,6 不在gap锁之间,它在7,6之外,如果插入(2,6),(4,6),(6,6)就会被阻塞
T2:begin;INSERTINTO t_gap SELECT2,0;
没有被阻塞
T2:begin;INSERT INTO t_gap SELECT 6,7; 没有被阻塞
浙公网安备 33010602011771号