mysql锁的各种情况

        最近在面试中问到了锁的一些问题，今天就锁做进一步的测试和总结。 

锁模式的含义：

• IX：意向排他锁
• X：锁定记录本身和记录之前的间隙，排他
• S：锁定记录本身和记录之前的间隙，共享
• X,REC_NOT_GAP:锁定记录本身，排他
• S,REC_NOT_GAP:锁定记录本身，共享
• X，GAP:不锁定记录本身，排他
• S，GAP:不锁定记录本身，共享
• X，GAP,INSERT_INTENTION:插入意向锁

MyISAM引擎

        对于MyISAM引擎，select语句加lock in share mode或for update是没有意义的，MyISAM必须用lock table来锁表操作。

INNODB引擎

开启INNODB的状态日志

测试数据

查看innodb状态配置

RR隔离级别情况

1.无显示主键 

创建测试表

--测试表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

(1)不附加加查询条件

set autocommit=0;
begin;
select * from t for update;
show engine innodb status;

加锁状态

这里对记录添加的是临间锁，实际是对隐藏的row_id进行加锁。 

第一段是对(-∞,10]直接进行加锁

第二段是对(10,20]直接进行加锁

第三段是对(20,30]直接进行加锁

asc supremun指的是对(30，+∞)进行加锁

(2)附加加查询条件

set autocommit=0;
begin;
select * from t where id = 10 for update;
show engine innodb status;

加锁状态

        从锁的状态来看加锁状态并没有改变，由此可见 ，在RR模式下，对无显示主键的记录加锁无论加不加条件都是对所有记录加间隙锁。

2.存在显示主键

(1) 对所有记录加锁

--测试表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

针对所有的记录查询

         此时的日志信息和无显示主键的效果一致。

(2)针对行记录加锁

--此时先依照主键ID进行行查询
select * from t where id = 20 for update;

针对id=20加锁

         对20这行记录加锁，显示对改行加记录锁。

 (3)索引失效的情况

select * from t where id < 20 or name is not null for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 20 |    1 |  16 |
| 30 |    2 |  17 |
| 40 |    3 |  18 |
+----+------+-----+

与无显示主键效果一致

 3.不存主键，单存在普通索引

(1).对所有记录进行加锁

--测试表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  key t_id_IDX (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

对各记录区间加间隙锁

 (2).对普通索引加锁

select * from t where id = 10 for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
+----+------+-----+
1 row in set (0.00 sec)

对普通索引加锁

1. index t_id_IDX of table `employees`.`t` trx id 10546 lock_mode X

对t_id_IDX普通索引(-∞,10]区间加临间锁 
2. index GEN_CLUST_INDEX of table `employees`.`t` trx id 10546 lock_mode X locks rec but not gap
对聚簇索引加行锁
3.index t_id_IDX of table `employees`.`t` trx id 10546 lock_mode X locks gap before rec
对t_id_IDX普通索引(10,20)

 4.不存主键，单存在唯一索引

--测试表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  UNIQUE KEY `t_UN` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

对唯一索引加锁

对(-∞,10)10(10,20)20(20,30)30(30,+∞)加锁

5.存在主键和唯一索引

--测试表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t_UN` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

对主键进行加锁

对(-∞,10)10(10,20)20(20,30)30(30,+∞)加锁

6.存在主键和非唯一索引

--测试表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX KEY `t_UN` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t where name='1' for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
+----+------+-----+

index t_name_IDX of table `employees`.`t` trx id 10676 lock_mode X
对t_name_IDX (-∞,'1']加锁
index PRIMARY of table `employees`.`t` trx id 10676 lock_mode X locks rec but not gap
对index PRIMARY 10行加锁
index t_name_IDX of table `employees`.`t` trx id 10676 lock_mode X locks gap before rec
对t_name_IDX ('1','2')加间隙锁

RC隔离级别的情况

1.无显示主键

对row_id加行锁

2.存在显示主键

对主键加行锁