MySQL锁

介绍

锁分类

乐观锁

程序实现。使用版本号或者时间戳。

mysql实现效率并不好，redis实现更好。

表锁

MySQLLayer层

MySQL 实现的表级锁定的争用状态变量：

表锁可查询。

1	mysql> show status like 'table%';

Variable_name				Value
Table_locks_immediate		76
Table_locks_waited			0
Table_open_cache_hits		0
Table_open_cache_misses		0
Table_open_cache_overflows	0

1 2	- table_locks_immediate：产生表级锁定的次数(包括意向锁)； - table_locks_waited：出现表级锁定争用而发生等待的次数；

行锁升级为表锁的原因：update语句的条件没有使用索引。update where name = “han”，name没有使用索引。

元数据锁

MySQLLayer层

意向锁

用处：显著提高对整个表修改时的性能

意向锁：

InnoDB内部使用。
有两种：共享读锁，排他写锁。
意向锁不是真正的锁，只是一个标记。
获得行锁中的共享读锁之前会先获得意向读锁，获得行锁中的排他写锁之前会先获得意向写锁。

原理：做整个表的update操作时，不用一行行看有没有行锁中的排他写锁。只需要看一次有没有意向写锁即可。

行锁

InnoDB内部，行锁是通过锁住索引上的索引项来实现的。

记录锁

读锁

共享读：lock in share mode

写锁

排它写：

手动：for update
自动：DML(insert、update、delete语句),select … for update
行锁升级为表锁的原因：update语句的条件没有使用索引。update where name = “han”，name没有使用索引。

可能发生锁等待情况：

行锁升级为表锁
update 长事务

间隙锁

mysql默认的RR隔离级别+一些条件产生间隙锁，可防止幻读。RC隔离级别不产生。

id	name
1	1
3	3
5	5
7	7

id 主键，name 普通索引

在update gap set name = 2 where name = 5时，间隙锁锁住相邻数据。

相邻数据：where 条件 name = 5 对应的id =5，相邻数据为id=3和id=7的记录。

具体锁住的数据：id=任意 &&name = [3,6]和 name = 7 && id= [3,7]。

name = 7,id<3 || id>7,不锁。

1
2
3

#session1:
begin;
update gap g SET g.name = 2 where g.name = 5;

1 2	#session2: insert into gap values(8,7);#可执行成功。

死锁

发生的原因：两个(或以上)的Session【加锁的顺序】不一致。

记录锁产生死锁：

session1: begin;--开启事务未提交 --手动加行写锁 id=1 ，使用索引 update mylock set name='m' where id=1; 
session2：begin;--开启事务未提交 --手动加行写锁 id=2 ，使用索引 update mylock set name='m' where id=2; 
session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞 
session2：update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

间隙锁产生死锁：

session1：start transaction ; select * from news where number=6 for update--产生间隙锁 
session2：start transaction ; select * from news where number=7 for update--产生间隙锁 
session1：insert into news values(9,7);--阻塞 
session2：insert into news values(9,7); 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看死锁情况：show engine innodb status

如何避免死锁：

注意程序的逻辑，不要交叉更新。如trx1:更新表A，更新表B，trx2：更新表B，更新表A。
保持事务轻量，占用少的锁资源。
提供运行速度，尽量避免子查询，尽量使用主键
尽快提交事务。
RR隔离级有Gap锁，RC隔离级无Gap锁，RC隔离级更不容易产生死锁。

SQL加锁分析

加锁的条件：

1.系统的隔离级别？

2.where 条件中的字段是否有索引？

简单SQL

RC隔离级

字段为主键

1	delete from user where id = 1;// id = 1的记录加x锁（记录锁）

唯一索引

1	delete from user where username = 'zs'; //1.在唯一索引树的username='zs'的记录加记录锁；2.通过唯一索引找到主键id = 1,在主键索引树上id=1的记录加记录锁

普通索引

delete from user where name = '张三'; //1.在普通索引树的name = '张三'的记录加记录锁（可能多条）；2.通过普通索引找到主键id = 10 和id =11,在主键索引树上id=10 和 id =11的记录加记录锁

无索引

1	delete from user where age = 80; //锁表可查询

RR隔离级

字段为主键

1	delete from user where id = 3; // x锁+Gap锁（Next key Lock算法）间隙锁

唯一索引唯一索引树X锁+Gap锁，主键索引树X锁+Gap锁
普通索引普通索引树匹配的多条记录X锁+Gap锁，主键索引树X锁+Gap锁
无索引锁表

Serializable(LBCC)

每一条SQL都加锁，包括select

复杂SQL

# 准备环境
CREATE TABLE `blog` (
  `id` int(11) NOT NULL,
  `userid` int(11) DEFAULT NULL,
  `blogid` int(11) DEFAULT NULL,
  `pub` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_pub_userid` (`pub`,`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1	delete from blog where pub > 1 and pub < 15 and userid = 1 and comment is not NULL;

数据库版本：5.6（包括5.6）

首先判断pub>1 and pub <15,将组合索引树的 pub = {3，5，10}加X锁，[1,20)加Gap锁（Mysql5.1，范围索引没有下推，5.6有索引下推，减少使用普通索引的回表次数）
然后 userid = 1，对组合索引树的pub = {5,10}加记录锁，通过ICP索引下推，不加Gap锁
comment is not null 无索引。将存储引擎的数据取到server层，对取出来的数据加X锁和Gap锁。

Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。

Index Filter：userid = ‘hdc’ 。此条件，可以在index_pub_userid索引上进行过滤，但不属于Index Key。

Table Filter：comment is not NULL。此条件，在index_pub_userid索引上无法过滤，只能在聚簇索引上过滤。

Go after

Mysql-锁