锁分类
按粒度分
全局锁:锁整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;