mysql临界锁_mysql锁

锁是什么

锁是管理不同事务之间的并发访问

理解表锁和行锁

在innodb:表锁是特殊的行锁,因为是把所有的行都锁掉的表锁

锁定粒度:表锁 > 行锁

锁定效率:表锁 > 行锁

冲突概率:表锁 > 行锁

并发性能:表锁 < 行锁

锁类型

行锁:共享锁(share locks)和排他锁(exclusive locks)

共享锁

对同一条读读共享,读写排斥(修改和删除),类似于ReentrantReadWriteLock

加锁方式:

select * from users WHERE id=1 LOCK IN SHARE MODE;

例子:

--共享锁加锁

1 BEGIN

select * from users WHERE id=1 LOCK IN SHARE MODE;

2其他事务执行

select * from users where id =1;

update users set age=19 where id =1;

因为读写不能共享

排他锁

读读排他,类似于ReentrantLock

加锁方式:

Insert,delete,update默认加入 for update

Select需要手动添加:如select * from users where id =1 for update;

例子:

1  set session autocommit = OFF;

update users set age = 23 where id =1;--可以

select * from users where id =1;--可以,行锁是针对事务,类似于java的锁是针对线程

update users set age = 26 where id =1;

2 --其他事务执行

select * from users where id =1 lock in share mode;--不可以,因为排他

select * from users where id =1 for update;--不可以,因为排他

select * from users where id =1;--可以,来自于快照

Innodb行锁的原理

锁的是主键索引的索引项

通过对索引的索引项(关键字)加锁来实现,若检索时,需要索引条件则使用行锁,否则则是表锁(锁住索引的所有记录)

例子:

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(32) NOT NULL,

`age` int(11) NOT NULL,

`phoneNum` varchar(32) NOT NULL,

`lastUpdate` datetime NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_eq_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;

----例子1

set session autocommit = OFF;

update users set lastUpdate=NOW() where phoneNum = '13666666666';--表锁

rollback;

--其他事务执行

update users set lastUpdate=NOW() where id =2;--不可以,全部的索引项已经锁住了

update users set lastUpdate=NOW() where id =1;--不可以,全部的索引项已经锁住了

-----例子2 主键索引

set session autocommit = OFF;

update users set lastUpdate=NOW() where id = 1;--行锁,锁id=1的索引

rollback;

--其他查询执行

update users set lastUpdate=NOW()  where id =2;--可以

update users set lastUpdate=NOW()  where id =1;--不可以,因为id=1的索引已被锁住

--------例子3 辅助索引

set session autocommit = OFF;

update users set lastUpdate=NOW() where `name` = 'seven';--行锁,name是唯一性索引(辅助索引),所对应的是id=1的主键索引,索引锁的是id=1的主键索引项

rollback;

--其他查询执行

update users set lastUpdate=NOW() where `name` = 'seven';--不可以,行锁

update users set lastUpdate=NOW() where id =1;--不可以,行锁

update users set lastUpdate=NOW() where `name` = 'qingshan';--可以

update users set lastUpdate=NOW() where id =2;;--可以

表锁

Lock tables xxx read/write

表锁:若有行锁或表锁,则阻塞

意向共享锁(intention share locks):在事务操作之前,自动添加的,作用是加共享锁之前,必须先获取这把锁

意向排他锁(intention exclusive locks):在事务操作之前,自动添加的,作用是加排他锁之前,必须先获取这把锁

意义:针对锁表功能,锁表前,先判断意向锁是否存在,若存在,则阻塞,若不存在则开始锁表

例子:

set session autocommit = OFF;

update users set lastUpdate=NOW() where id = 1;--锁行

rollback;

--其他会话执行

update users set lastUpdate=NOW() where phoneNum = '13777777777';--阻塞

自增锁(auto-inc locks):

表锁,类似于通策的主键获取

查看自增模式

show variables like 'innodb_autoinc_lock_mode';

值为1,表示连续提交,事务未提交,丢失

如

begin;

insert into users(name , age ,phoneNum ,lastUpdate ) values ('tom2',30,'1344444444',now());--主键:10

ROLLBACK;

begin;

insert into users(name , age ,phoneNum ,lastUpdate ) values ('xxx',30,'13444444444',now());--主键:11

ROLLBACK;

-- 其他事务执行

insert into users(name , age ,phoneNum ,lastUpdate ) values ('yyy',30,'13444444444',now());--主键:12

上面的例子解释了为什么自增主键主键不连续

行锁的算法

确定锁的区间大小:左开右闭,左开右开,只有一条记录

临界锁(next-key locks)

1 前提:命中记录 + 范围查找[between and,]

2 特点:锁左开右闭的范围和下一个区间

查询结果为7,所在的区间是(4,7],加下一个区间(7,10]

3 间隙的设定:类似于数学的象限,找出临界点,然后开始找到区间

4 Next-key lock(左开右闭) = gap lock(左开右开) + record lock(一个点,只锁命中的记录)

5 为什么锁下一个区间:解决幻读

例子:

begin;

select * from t2 where id>5 and id<9 for update;

ROLLBACK

-- 其他事务执行

set session autocommit=off;

select * from t2 where id=4 for update;--不阻塞

select * from t2 where id=7 for update;--阻塞

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

INSERT INTO `t2` (`id`, `name`) VALUES (9, '9');--阻塞

间隙锁(gap locks)

1 前提:不命中记录 + (范围查找或等值查找)

2 特点:左开右开+查询范围最近的第一个区间

例子:

begin;

select * from t2 where id >4 and id <6 for update;

--或者

select * from t2 where id =6 for update;

ROLLBACK;

--其他会话执行

INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');

INSERT INTO `t2` (`id`, `name`) VALUES (6, '6');

记录锁(record locks)

1 条件:命中记录 + 等值匹配 + 唯一性索引

2 特点:只锁住这条记录

例子:

begin;

select * from t2 where id =4 for update;

rollback;

--其他事务执行

select * from t2 where id =7 for update;--不锁住

select * from t2 where id =4 for update;--锁住