[MYSQl]锁[锁分类|加锁规则]

目录

全局锁

使用场景

实现方式

1.flush tables with read lock

2.mysqldump --single-transation 

表锁

表锁语法

注意点

另一种表锁MDL（metaDataLock）《mysql 5.5开始有的》

加锁场景

注意场景

行锁

两阶段锁

死锁处理

间隙锁

加锁规则

原则

案例

case1

case2

case3

case4

case5

case6

case7

case8

case9 

锁释放时机（InnoDB）

rr

rc

---

---

 

全局锁

使用场景

主要全库的逻辑备份

 

实现方式

1.flush tables with read lock

这种方式全库都处于只读状态，比较危险

2.mysqldump --single-transation 

带上rr的事务隔离级别最佳，不过这个只能在支持事务的引擎中使用（MyISAM就不行）

 

表锁

表锁语法

lock tables read/write； 【lock tables t read; t1 write】

注意点

如上面的锁表语法 即便是开启锁表的事务 也只能对t进行读 对t1进行读写,无法操作别的表。

另一种表锁MDL（metaDataLock）《mysql 5.5开始有的》

加锁场景

这个不需要显示的添加，执行表结构变更会自动加这个锁，（一般语句开始执行的时候加锁，事务结束的时候释放）

crud也会加 （所以也会有个问题，如果存在长事务持有MDL读锁 ，这个时候执行DML（比如加字段）操作，会等待MDL写锁，别的sql请求也会被block住）

 

注意场景

即便对一个小表查询 也可能会导致整个库出问题。

eg：一个高频查询的配置表，如果需要添加一个字段。

假设一个查询的长事务先来 拿到MDL锁，DDL在后面被block，后继又有高频查询session又被DDL block住，不停的new新连接，最终会搞死DB

上述场景解决方案
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

 

行锁

myIsam不支持行锁，innoDB支持

select * from t where uk_column = 'xx' for update;

update t set c1 = 'xx' where uk_column = 'xx';

 

两阶段锁

事务开启不会加锁，开始执行具体SQL才会加锁，事务提交才会释放锁。

所以 有ABC三个SQL在一个事务的话，影响最大的sql [最容易造成锁冲突] 放在最后。（eg：比如更新热点账号）

 

死锁处理

以前我们做活动，类似好友间发权益（A+1 B+1）（B+1 A+1），两个事务互相等待 就会形成死锁。

（比较简单的解法就是，永远先执行A 在执行B，具体的就是 账号大小顺序 执行）

DB里面一般遇到死锁处理方案

1.加上锁等待检测 innodb_lock_wait_timeout (默认50秒)

2.加上死锁检测 innodb_deadlock_detect=on，弊端是很耗费cpu （结合散列 控制并发度）

 

间隙锁

具体可以看下这篇 https://blog.csdn.net/u013657993/article/details/108357003

 

---

加锁规则

原则

1.加锁的基本单位是next-key lock（间隙锁+行锁 前开后闭）

2.访问到的数据才会加锁，锁是附加在索引上

3.UK索引的等值查询，next-key lock会退化成行锁

4. 索引的等值查询，会扫描到右边第一个不满足条件的行，退化成间隙锁

5.UK索引的范围查询会扫描到第一个不满足条件的行

 

案例

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

case1

A加锁（5，10） ，主键索引加next-key lock(5,10]， 不满足等值查询条件退化成间隙锁（5，10）

 

case2

A加了 （0，10）的锁，加了(0,5]的锁 因为C不是UK 所以还需要再往右扫描一行 （c=10），所以还会加上(5,10],最后就是(0,5] (5,10]

注意SQL只涉及到c和id，这个是索引C可以覆盖到的，只锁访问到的数据<索引树C>

B更新的主键索引，不冲突

《lock in share mode只锁索引，如果是for update 引擎认为要修改行，也会把主键索引一并锁了》 

 

case3

A加锁[10,15],  加锁是 (5,10] 和 (10,15] 第一阶段退化成行锁[10,15]

 

case4

A加锁(5,15] <加了(5,10] (10,15]两个next-key lock> ,左开右闭 扫描到5～15结束，和上面case3类似 但是不会有退化成行锁

 

case5

A加锁 （10，20] 范围查询 主键索引也不退化，UK索引的范围查询还会往右扫描一行 会扫描到20 加上(15,20]

 

case6

A锁(5,15)

 

case7

加一行 insert into t values(30,10,30);

原先锁(5,15) 加范围直接锁两个c=10的记录，建议最好加limit

 

case8

A锁 c=(5,15) B锁 间隙锁(5,10) 行锁10，A再执行被B给锁了。B等A的行锁，A等B的间隙锁，这个很容易出锅，innoDb可以认为没有间隙锁，没有这个烦扰

 

case9 

sessionA加锁(5，25]

这里有order by c desc，c的扫描范围就是[10 ~ 25]  10这行对应的锁 (5,10] 20对应的是(15,20]和(20,25]

 

锁释放时机（InnoDB）

rr

事务结束（提交或者回滚）时释放锁

 

rc

语句执行期间加锁，如果语句执行完，不需要的行会释放锁。（锁的范围更小，锁的时间更短）