Mysql-锁

锁分类

按粒度分

全局锁：锁整Database，由MySQL的SQL layer层实现
表级锁：锁某Table，由MySQL的SQL layer层实现
行级锁：锁某Row的索引，也可锁定行索引之间的间隙，由存储引擎实现【InnoDB】

按功能分

共享锁Shared Locks（S锁，也叫读锁）： 为了方便理解，下文我们全部使用读锁来称呼
加了读锁的记录，允许其他事务再加读锁
加锁方式：select…lock in share mode
排他锁Exclusive Locks（X锁，也叫写锁）：为了方便理解，下文我们全部使用写锁来称呼
加了写锁的记录，不允许其他事务再加读锁或者写锁
加锁方式：select…for update

全局锁

全局锁对整个数据库加锁，加锁以后数据库处于只读状态，所有对数据库的更新事务全被阻塞。

#加锁
flush tables with read lock;
#释放锁 断开加锁的Session，会自动释放全局锁
unlock tables;

使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性，但是对于innodb我们不这样用，这样会影响业务，因为所有的更新操作被阻塞。Innodb的做法如下：

#如果MyISAM 采用下面的命令全库备份
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables >
/root/db.sql
#如果innodb 采用下面的命令全库备份
mysqldump -uroot -p --host=localhost --all-databases --single-transaction >
/root/db.sql

表级锁

表读锁、写锁

#查看表锁定状态
show status like 'table%';

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁有两种表现形式：

• 表读锁
• 表写锁

lock table t read; #为表t加读锁
lock table t write; #为表t加写锁
#查看表锁情况
show open tables;
#删除表锁
unlock tables;

如果加了读锁另一个会话去写会阻塞：

-- Session01
# 获得表mylock的Read Lock锁定：
lock table mylock read;
# 当前Session可以查询该表记录：
select * from mylock;
# 当前Session不能查询其他没有锁定的表：
select * from t;
# 当前Session插入或更新锁定的表会提示错误：
insert into mylock (name) values('e');
# 释放锁：
unlock tables;
-- Session02
# 其他Session也可以查询该表的记录：
select * from mylock;
# 其他Session可以查询或更新未锁定的表：
update t set c='张飞' where id=1;
# 其他Session插入或更新锁定表会一直等待获取锁：
insert into mylock (name) values('e');

如果加了写锁另一个会话去读会阻塞：

-- Session01
# 获得表mylock的write锁：
lock table mylock write;
# 当前session对锁定表的查询+更新+插入操作都可以执行：
select * from mylock where id=1;
insert into mylock (name) values('e');
# 释放锁：
unlock tables;
-- Session02
# 注意：待session1开启锁后，session2再获取连接
# 其他session对锁定表的查询被阻塞，需要等待锁被释放
select * from mylock where id=1;
# 获得锁，返回查询结果：

元数据锁

元数据锁不需要显式指定，在访问一个表的时候会被自动加上，锁的作用是保证读写的正确性。
保证我们在修改表的时候，我们的表结构不会被修改。

当对一个表做增删改查操作的时候，加元数据 读锁；当要对表做结构变更操作的时候，加元数据写锁。
如果加了写锁，因为写锁是排它的，相当于这里的操作进行了串行化。

-- Session01
# 开启事务：
begin;
# 加元数据读锁：
select * from mylock;
# 提交/回滚事务：
commit;
# 释放锁
-- Session02
# 修改表结构：
alter table mylock add f int;
# 获取锁，修改完成

自增锁

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

行级锁

案例表

CREATE TABLE `t1_simple` (
`id` int(11) NOT NULL,
`pubtime` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_pu`(`pubtime`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `t1_simple` VALUES (1, 10);
INSERT INTO `t1_simple` VALUES (4, 3);
INSERT INTO `t1_simple` VALUES (6, 100);
INSERT INTO `t1_simple` VALUES (8, 5);
INSERT INTO `t1_simple` VALUES (10, 1);
INSERT INTO `t1_simple` VALUES (100, 20);

InnoDB的行级锁，按照功能来说，分为两种：
读锁：允许一个事务去读一行，阻止其他事务更新目标行数据。同时阻止其他事务加写锁，但不阻止其他事务加读锁。
写锁：允许获得排他锁的事务更新数据，阻止其他事务获取或修改数据。同时阻止其他事务加读锁和写锁。
读锁

select * from t1_simple where id = 4 lock in share mode;

写锁

select * from t1_simple where id = 4 for update;

记录锁

记录锁（Record Locks）仅仅锁住索引记录的一行，在单条索引记录上加锁。记录锁锁住的永远是索引，而非记录本身，即使该表上没有任何显示索引，那么innodb会在后台创建一个隐藏的聚簇索引索引，那么锁住的就是这个隐藏的聚簇索引索引。
下面这些操作都是加记录锁：

-- 加记录读锁
select * from t1_simple where id = 1 lock in share mode;
-- 加记录写锁
select * from t1_simple where id = 1 for update;

-- 新增，修改，删除加记录写锁
insert into t1_simple values (1, 22);
update t1_simple set pubtime=33 where id =1;
delete from t1_simple where id =1;

间隙锁

（1）间隙锁(Gap Locks)，仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
（3）间隙锁可用于防止幻读，保证索引间隙不会被插入数据。
（4）在可重复读（REPEATABLE READ）这个隔离级别下生效。

事务一你需要查询数据，如上图你可以查询到6,8,10,100：

#锁的区间为(4,100+)
select * from t1_simple where id > 4 for update; -- 加间隙锁

事务二你需要插入数据：

insert into t1_simple values (7,100); -- 阻塞
insert into t1_simple values (3,100); -- 成功

为了保证你查询的数据中间不被插入数据产生幻读/虚读，就需要加间隙锁。

临键锁

（1）临键锁(Next-Key Locks)相当于记录锁 + 间隙锁【左开右闭区间】，例如（5,8]
（2）默认情况下，innodb使用临键锁来锁定记录，但在不同的场景中会退化，其实也就是说这个是再某个场景下才有的状态，也就是说大多数还是为记录锁和间隙锁
（3）当查询的索引含有唯一属性的时候，临键锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。也就是说如果不是唯一值索引默认会加临键锁。

如果存在就是锁这条记录，如果没有则是间隙，因为要防止之后查询的时候这条记录被插入了就又会被查到。

事务一

begin;
select * from t1_simple where pubtime = 20 for update;
#因为不是唯一值索引所所以加临键锁 20这个记录的前一个区间和后一个区间都被锁住
-- 临键锁区间(10,20],(20,100]
commit;

事务二

begin;
insert into t1_simple values (16, 19); -- 阻塞 因为这个区间被锁住了
select * from t1_simple where pubtime = 20 for update; -- 阻塞 这个记录同样也被锁住
insert into t1_simple values (16, 50); -- 阻塞 这个区间也被锁住
insert into t1_simple values (16, 101); -- 成功 这个区间没有被锁住所以能够修改成功
commit;

插入意向锁

看下面这个例子，我们想要插入60和70但是在插入60的时候锁间隙的话会把11-99都锁起来，这个时候70的插入被阻塞，但是其实他们之前本来不存在竞争的，这样锁的范围太大了。

所以有了插入意向锁：
（1）插入意向锁（Insert Intention Locks）是一种在 INSERT 操作之前设置的一种特殊的间隙锁。
（2）插入意向锁表示了一种插入意图，即当多个不同的事务，同时往同一个索引的同一个间隙中插入数据的时候，它们互相之间无需等待，即不会阻塞。
（3）插入意向锁不会阻止插入意向锁，但是插入意向锁会阻止其他间隙写锁（排他锁）、记录锁。
插入意向锁和插入意向锁不冲突
事务一

begin;
insert into t1_simple values (60, 200);
-- 插入意向锁区间(10,100)
commit;

事务二

begin;
insert into t1_simple values (70, 300); -- 没有发生阻塞
-- 插入意向锁区间(10,100)
commit;

插入意向锁和写锁冲突

begin
select * from t1_simple where id > 10 for update;
-- 临键锁（区间）写锁区间(10,100+)
commit;

begin
insert into t1_simple values (90, 300); -- 被阻塞，阻塞的原因在于，插入意向锁和其他
-写锁之间是互斥的！
commit;
#查看状态可以看到
show engine innodb status\G

这里写了行的写锁锁住了间隙，意向锁在等待行写锁的释放。

加锁的规则

主键索引

等值条件，命中，加记录锁
等值条件，未命中，加间隙锁
范围条件，命中，包含where条件的临键区间，加临键锁
范围条件，没有命中，加间隙锁

辅助索引

等值条件，命中，命中记录的辅助索引项 + 主键索引项加记录锁，辅助索引项两侧加间隙锁
等值条件，未命中，加间隙锁
范围条件，命中，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁
范围条件，没有命中，加间隙锁

表级别锁-意向锁

InnoDB也实现了表级锁，也就是意向锁【Intention Locks】。意向锁是MySQL内部使用的，不需要用
户干预。意向锁和行锁可以共存，意向锁的主要作用是为了全表更新数据时的提升性能。否则在全表更
新数据时，需要先检索该范是否某些记录上面有行锁。那么将是一件非常繁琐且耗时操作。

举个栗子：
事务A修改user表的记录r，会给记录r上一把行级的写锁，同时会给user表上一把意向写锁（IX），这时
事务B要给user表上一个表级的写锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存，且满足
事务隔离性的要求。
当我们需要加一个写锁时，需要根据意向锁去判断表中有没有数据行被锁定；
（1）如果行锁，则需要遍历每一行数据去确认；
（2）如果表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

意向锁的作用：
表明：“某个事务正在某些行持有了锁、或该事务准备去持有锁”
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存

意向锁相互兼容：因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。
表级S锁和X、IX锁不兼容：因为上了表级S锁后，不允许其他事务再加X锁。
表级X锁和 IS、IX、S、X不兼容：因为上了表级X锁后，会修改数据。
总结：
意向锁之前相互排斥，其本质是标识这个资源现在是什么访问情况
一但到了行所遵循S与IX和X互斥（因为一个要读，一个要写或者想要写），X与任何锁互斥。

锁相关参数

mysql> show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
查看事务、锁的sql：

# 查看锁的SQL
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
# 查看事务SQL
select * from information_schema.innodb_trx;
# 查看未关闭的事务详情
SELECT
a.trx_id,a.trx_state,a.trx_started,a.trx_query,
b.ID,b.USER,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO,
c.PROCESSLIST_USER,c.PROCESSLIST_HOST,c.PROCESSLIST_DB,d.SQL_TEXT
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID =
c.THREAD_ID;