mysql 表锁和行锁

一、表锁

表读锁 lock table read; 一个session设置读锁，当前session更新和插入都会报错，另一个session只能读，写会阻塞。

表写锁 lock table write; 一个session设置写锁，当前session可以增删改查都可以,另一个session不能读写都会阻塞，直到锁被释放。  unlock tables;

二、共享锁

行共享锁： select * from 表名 where id = 1 lock in share mode; 当一个session设置共享锁，另一个session也可以设置共享锁成功。查询操作不影响，当第一个session进行更新操作会阻塞，第二个session进行更新操作时会因为发生死锁而退出，此时第一个session设置成功。第一个session可以读写，第二个session也可以读，但是写操作直到第一个session commit之后才能进行操作。

三、排他锁

行排他锁  select * from 表名 where id = 1 for update; 当一个session设置排他锁，另一个session在设置锁时会阻塞，第一个session可以读写，第二个session也可以读，但是写操作直到第一个session commit之后才能进行操作。

注：1、mysql 会把 update insert delete 操作 隐式添加for update;

       2、mysql 加行锁时，如果改sql没有用到索引，那它会将转换成表锁。行锁并不是争对记录，而是对索引。

mysql 记录锁 间隙锁 临键锁

记录锁：record lock,即锁住一条记录
间隙锁：gap lock,即锁定一个区间，左开右开
临键锁（next-key lock）：记录锁+间隙锁锁定的区间，左开右闭

四、数据准备

CREATE TABLE `book` (
  `id` int(11) NOT NULL,
  `isbn` varchar(25) DEFAULT NULL,
  `author` varchar(25) DEFAULT NULL,
  `score` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_isbn_key` (`isbn`),
  KEY `idx_author_key` (`author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('10', 'N0001', 'Bob', '3.40');
INSERT INTO `book` VALUES ('18', 'N0002', 'Alice', '7.70');
INSERT INTO `book` VALUES ('25', 'N0003', 'Jim', '5.00');
INSERT INTO `book` VALUES ('30', 'N0004', 'Eric', '9.10');
INSERT INTO `book` VALUES ('41', 'N0005', 'Tom', '2.20');
INSERT INTO `book` VALUES ('49', 'N0008', 'Tom', '8.30');

记录锁 select * from bookwhere id=1 for update；//锁住一行，通过主键索引和唯一索引实现。

1、主键命中索引（记录锁）

1.1 查询命中主键索引 

事务1

begin;
select * from book where id = 18 for update;

事务2
select、update、delte 锁住区间 等于 id = 18 和 id 在 > (负无穷大,18] 区间阻塞，id 在 < [10,正无穷大) 区间阻塞.但不包括 不存在的值等于操作

-------------------------------------------------
insert 阻塞区间 id=18

2、主键未命中索引

2.1 查询未命中索引

事务一
begin;
select * from book where id = 12 for update;


事务二、
select、update、delte 锁住区间 等于 和 > 和 < 无阻塞
-----------------------------------------------------

insert 锁住区间 (10,18)

 3、二级命中唯一索引（记录锁）

 3.1 二级唯一索引，查询命中 

事务一、
begin;
select * from book where isbn = 'N0003' for update;

事务二、
select、update、delte 锁住区间 isbn 等于 'N0003' 在 > (负无穷大,'N0004'] 区间阻塞，isbn 在 < ['N0002',正无穷大) 区间阻塞.但不包括 不存在的值等于操作
-----------------------------------------------------
insert 锁住区间 ['N0003','N0003']

4、二级未命中唯一索引

4.1 查询未命中（中间位）

事务一
begin;
select * from book where isbn = 'N0006' for update;


事务二、
select、update、delte 锁住区间 等于 和 > 和 < 无阻塞
--------------------------------------------
insert 锁住区间 ['N0005','N0008']

4.1.2   查询未命中（边位）

事务一
begin;
select * from book where isbn = 'N00005' for update;

事务二
select、update、delte 锁住区间 等于 和 > 和 < 无阻塞
--------------------------------------------
insert 锁住区间 [负无穷大,'N0001')

5、命中二级索引

5.1 查询命中

5.1.1 中间位

事务一、
select * from book where Author = 'Jim' for update;


事务二、
select、update、delte 锁住区间 Author 等于 'Jim' 在 > (负无穷大,'Jim'] 区间阻塞，Author 在 < ['Eric',正无穷大) 区间阻塞.但不包括 不存在的值等于操作
-----------------------------------------------------
insert 锁住区间 ['Eric','Tom']

insert into book VALUE(42,'N00049','Tom',1.1);
当author = 'Eric'时，会阻塞id>30记录，id < 30 不会阻塞

当author = 'Tom'时，会阻塞id<41记录，id > 41 不会阻塞

5.1.2  边位

事务一
begin;
select * from book where Author = 'Alice' for update;

事务二、
select * from book where Author = 'Eric' for update;-- 阻塞

select * from book where Author = 'Eric1' for update;-- 不阻塞

UPDATE book SET score = 9.2 WHERE Author = 'Eric'; -- 阻塞

UPDATE book SET score = 9.2 WHERE Author = 'Eric1'; -- 不阻塞

delete from book where  Author = 'Eric'; -- 阻塞

delete from book where  Author = 'C1'; -- 不阻塞

insert into book VALUE(11,'N0009','Bob',1.1);-- 阻塞 锁住区间(Bob,Jim) 再按照id区间比较(10,25)


insert into book VALUE(11,'N0009','Jam',1.1);-- 阻塞 锁住区间(Bob,Jim) 再按照id区间比较(10,25)


insert into book VALUE(42,'N0009','A1',1.1);-- 不阻塞

insert into book VALUE(11,'N0009','A1',1.1);-- 不阻塞

  

5.2 更新命中和查询命中 同理

6、未命中二级索引（和主键索引也关系）

6.1 查询未命中

6.1.1 中间位

事务一、
begin;
select * from book where Author = 'C1' for update;

事务二、
select * from book where Author = 'C1' for update;-- 不阻塞

select * from book where Author = 'D1' for update;-- 不阻塞

UPDATE book SET score = 9.2 WHERE Author = 'C1'; -- 不阻塞

UPDATE book SET score = 9.2 WHERE Author = 'Bob'; -- 不阻塞

delete from book where  Author = 'C1'; -- 不阻塞

delete from book where  Author = 'Eric'; -- 不阻塞

insert into book VALUE(11,'N0009','Bob',1.1);-- 阻塞 锁住区间(Bob,Jim),再按照id区间比较，见下图

insert into book VALUE(9,'N0009','Bob',1.1);-- 不阻塞 ？？？？

insert into book VALUE(11,'N0009','Eric1',1.1);-- 阻塞 锁住区间(Bob,Jim),再按照id区间比较，见下图

insert into book VALUE(11,'N0009','F1',1.1);-- 不阻塞

从上文两条sql语句中

insert into book VALUE(11,'N0009','Bob',1.1);

insert into book VALUE(9,'N0009','Bob',1.1); 

6.1.2 边位

事务一
begin;
select * from book where Author = 'U1' for update;


事务二、
select * from book where Author = 'U1' for update;-- 不阻塞

select * from book where Author = 'V1' for update;-- 不阻塞

UPDATE book SET score = 9.2 WHERE Author = 'U1'; -- 不阻塞

UPDATE book SET score = 9.2 WHERE Author = 'Bob'; -- 不阻塞

delete from book where  Author = 'U1'; -- 不阻塞

delete from book where  Author = 'Tom1'; -- 不阻塞

insert into book VALUE(11,'N0009','Tom1',1.1);-- 阻塞 锁住区间(Tom,无穷大)

insert into book VALUE(11,'N0009','V1',1.1);-- 阻塞 锁住区间(Tom,无穷大)

insert into book VALUE(11,'N0009','Tom',1.1);-- 不阻塞

7、主键索引 范围

事务一、
begin;
select * from book where id > 18 for update;

事务二、
select * from book where id > 18 for update; -- 阻塞  

select * from book where id < 26 for update; -- 阻塞

select * from book where id < 19 for update; -- 阻塞

select * from book where id = 26 for update; -- 不阻塞

select * from book where id = 25 for update; -- 阻塞

select * from book where id = 50 for update; -- 不阻塞

select * from book where id > 49 for update; -- 不阻塞

select * from book where id = 10 for update; -- 不阻塞


UPDATE book SET score = 9.2 WHERE id =  26; -- (update和select同理)

select、update、delte 锁住区间 等于25、30、41、49 和 id 在 > (负无穷大,48] 区间阻塞，id 在 < [19,正无穷大) 区间阻塞.但不包括 不存在的值等于操作

-----------------------------------------------------------------------------------------

insert into book VALUE(26,'N00051','Jim',1.1);-- 阻塞

insert into book VALUE(8,'N00081','Eric',1.1);-- 阻塞

insert 锁住区间 (18,无穷大) 


【2】、主键范围未命中
事务1
begin;
select * from book where id < 26 for update;

事务二、
select * from book where id > 18 for update; -- 阻塞  

select * from book where id < 25 for update; -- 阻塞

select * from book where id < -1 for update; -- 阻塞

select * from book where id = 24 for update; -- 不阻塞

select * from book where id = 25 for update; -- 阻塞

select * from book where id = 30 for update; -- 阻塞

select * from book where id > 26 for update; -- 阻塞

select * from book where id > 30 for update; -- 不阻塞


UPDATE book SET score = 9.2 WHERE id =  26; -- (update和select同理)

select、update、delte 锁住区间 等于10、18、25 和  id 在 > (负无穷大,29] 区间阻塞，id 在 < (负无穷大,正无穷大) 区间阻塞，但不包括 不存在的值等于操作

-----------------------------------------------------------------------------------------

insert into book VALUE(-1,'N00051','Jim',1.1);-- 阻塞

insert into book VALUE(8,'N00081','Eric',1.1);-- 阻塞

insert 锁住区间 (负无穷大,31) 



【3】、多个主键取范围
事务一、
begin;
select * from book WHERE id >=18 and id <41 for update; 

事务二、
select * from book where id > 1 for update; -- 阻塞  

select * from book where id < 11 for update; -- 阻塞 

select * from book where id < 10 for update; -- 不阻塞 

select * from book where id = 10 for update; -- 不阻塞 

select * from book where id = 11 for update; -- 不阻塞 

select * from book where id > 8 for update; -- 阻塞

select * from book where id >= 49 for update; -- 不阻塞

select * from book where id > 41 for update; -- 不阻塞

select * from book where id = 35 for update; -- 阻塞

select * from book where id = 40 for update; -- 阻塞

select * from book where id > 30 for update; -- 不阻塞


select * from book where id < 88 for update; -- 阻塞


UPDATE book SET score = 9.2 WHERE id =  26; -- (update和select同理)

select、update、delte 锁住区间 等于18、25、30、41 和 id 在 > (负无穷大，40]区间会阻塞 , id在<[11,正无穷大)会阻塞 但不包括 不存在的值等于操作

-----------------------------------------------------------------------------------------

insert into book VALUE(18,'N00051','Jim',1.1);-- 阻塞

insert into book VALUE(41,'N00081','Eric',1.1);-- 阻塞

insert 锁住区间 [18,41]


【4】、多个不存在主键取范围
事务一、
begin;
select * from book where id > 19 and id < 31 for update;

事务二、
select、update、delte 锁住区间 等于25、30、41 和 id 在 > (负无穷大,40] 区间阻塞，id 在 < [19,正无穷大) 区间阻塞，
-----------------------------------------------------------------------------------------
insert 锁住区间 (18,41]

【5】、一个存在与不存在主键取范围
事务一、
begin;
select * from book WHERE id >18 and id <29 for update; 

事务二、
select、update、delte 锁住区间 等于25、30 和 id 在 > (负无穷大,29] 区间阻塞，id 在 < [18,正无穷大) 区间阻塞，
-----------------------------------------------------------------------------------------
insert 锁住区间 [19,30]

8、二级唯一索引 范围

【1】二级唯一索引命中
事务一、
begin;
select * from book WHERE isbn < 'N0004' for update;

事务二、
select、update、delte 锁住区间 等于N0001 N0002 N0003 N0004 N0005 N0008 和 isbn 在 > (负无穷大,'N0008'] 区间阻塞，isbn 在 < ['N0001',正无穷大) 区间阻塞.但不包括 不存在的值等于操作

-------------------------------------------------------------------------------------
insert 锁住区间 (负无穷大,正无穷大]


【2】二级唯一索引没命中
事务一、
begin;
select * from book WHERE isbn < 'N00031' for update;

事务二、
select、update、delte 锁住区间 等于N0001 N0002 N0003 N0004 N0005 N0008 和 isbn 在 > (负无穷大,'N0008'] 区间阻塞，isbn 在 < ['N0001',正无穷大) 区间阻塞.但不包括 不存在的值等于操作

-------------------------------------------------------------------------------------
insert 锁住区间 (负无穷大,正无穷大]

【3】多个二级唯一索引命中
事务一、
begin;
select * from book WHERE isbn > 'N0003' and isbn < 'N0005' for update;

事务二、
select、update、delte 锁住区间 等于 N0004 N0005  和 isbn 在 > (负无穷大,'N0005'] 区间阻塞，isbn 在 < ['N0003',正无穷大) 区间阻塞.但不包括 不存在的值等于操作
-------------------------------------------------------------------------------------
insert 锁住区间 ('N0003',正无穷大)


【4】多个二级唯一索引没命中
事务一、
begin;
select * from book WHERE isbn > 'N00021' and isbn < 'N00049' for update;

事务二、
select、update、delte 锁住区间 等于 N0003 N0004 N0005  和 isbn 在 > (负无穷大,'N0005') 区间阻塞，isbn 在 < ['N0003',正无穷大) 区间阻塞.但不包括 不存在的值等于操作

-----------------------------------------------------------------------------------------

insert 锁住区间 ('N0002','N0005')

【5】一个二级唯一索引没命中和一个二级索引命中
事务一、
begin;
select * from book WHERE isbn > 'N0002' and isbn < 'N00049' for update;

事务二、
select、update、delte 锁住区间 等于 N0003 N0004 N0005  和 isbn 在 > (负无穷大,'N0005') 区间阻塞，isbn 在 < ['N0002',正无穷大) 区间阻塞.但不包括 不存在的值等于操作
-----------------------------------------------------------------------------------------
insert 锁住区间 ('N0002','N0005')

9、二级索引 范围

【1】单个命中
1、事务1
begin;
select * from book WHERE author < 'Eric' for update;

2、事务2
select、update、delte 锁住区间 等于 Alice Bob Eric  和 author 在 > (负无穷大,'Jim'] 区间阻塞，author 在 < [负无穷大,正无穷大) 区间阻塞.但不包括 不存在的值等于操作

----------------------------------------------------------------------------------------

insert 锁住区间 (负无穷大,'Eric') 
30	N0004	Eric	9.1
注意： 
如果在author = 'Eric'情况下， insert into book VALUE(29,'N00049','Eric',1.1);在主键id < 30情况下会阻塞。
insert into book VALUE(31,'N00049','Eric',1.1); --不会阻塞


【2】单个没命中
事务1
begin;
select * from book WHERE author < 'Ca' for update;

事务2
select、update、delte 锁住区间 等于 Alice Bob Eric 和 author 在 > (负无穷大,'Jim'] 区间阻塞，author 在 < [负无穷大,正无穷大) 区间阻塞.但不包括 不存在的值等于操作
----------------------------------------------------------------------------------------
insert 锁住区间 (负无穷大,'Eric')
insert into book VALUE(29,'N00049','Eric',1.1);在主键id < 30情况下会阻塞。
insert into book VALUE(31,'N00049','Eric',1.1); --不会阻塞


【3】多个命中
事务1
begin;
select * from book WHERE author > 'Bob' and author < 'Jim' for update;

事务2
select、update、delte 锁住区间 等于 Eric Jim 和 author 在 > (负无穷大,'Jim'] 区间阻塞，author 在 < [Bob,正无穷大) 区间阻塞.但不包括 不存在的值等于操作
----------------------------------------------------------------------------------------

insert 锁住区间 ('Bob','Jim')
当author = 'Bob'时，会阻塞id>10记录，id < 10 不会阻塞
当author = 'Jim'时，会阻塞id<25记录，id > 25 不会阻塞

【4】一个命中和一个未命中
事务1
begin;
select * from book WHERE author > 'Bob' and author < 'Ki' for update;

事务2
select、update、delte 锁住区间 等于 Eric Jim Tom 和 author 在 > (负无穷大,'Tom'] 区间阻塞，author 在 < [Bob,正无穷大) 区间阻塞.但不包括 不存在的值等于操作

----------------------------------------------------------------------------------------
insert 锁住区间 ('Bob','Jim')
当author = 'Bob'时，会阻塞id>10记录，id < 10 不会阻塞
当author = 'Tom'时，会阻塞id<41记录，id > 41 不会阻塞


【5】多个未命中
事务1
begin;
select * from book WHERE author > 'Alice1' and author < 'Ki' for update;

事务2
select、update、delte 锁住区间 等于 Alice Bob Eric Jim Tom 和 author 在 > (负无穷大,'Tom'] 区间阻塞，author 在 < ['Alice',正无穷大) 区间阻塞.但不包括 不存在的值等于操作

----------------------------------------------------------------------------------------
insert 锁住区间 (负无穷大,正无穷大)

10、组合索引 todo

1、事务1

start TRANSACTION;

select * from book where author = 'Tom' and score = 8.3 for update;

2、事务2
start TRANSACTION;

select * from book where author = 'Bob' for update;#不阻塞

start TRANSACTION;
select * from book where author = 'Tom' for update; #阻塞


start TRANSACTION;
select * from book where score = 7.7 for update;#不阻塞

start TRANSACTION;
select * from book where score = 8.3 for update;#阻塞


总结
对score 排序后
41	N0005	Tom	    2.2
10	N0001	Bob	    3.4
25	N0003	Jim	    5
18	N0002	Alice	7.7
49	N0006	Tom	    8.3
60	N0007	Rose	8.9
30	N0004	Eric	9.1
todo 猜想只是相当于行锁