分类
操作类型:
读锁(共享锁,Shared Lock):对同一个数据,多个读操作可以同时进行,互不干扰
写锁(互斥锁,Exclusive Lock): 如果当前写操作没有完毕,则无法进行其他的读操作、写操作
操作范围:
- 表锁 :一次性对一张表整体加锁。如MyISAM存储引擎使用表锁,开销小、加锁快;无死锁;但锁的范围大,容易发生锁冲突、并发度低。
- 行锁:一次性对一条数据加锁。如InnoDB存储引擎使用行锁,开销大,加锁慢;容易出现死锁;锁的范围较小,不易发生锁冲突,并发度高(很小概率 发生高并发问题:脏读、幻读、不可重复度、丢失更新等问题)。
记录锁(Record Lock):记录锁也是属于行锁的一种,锁的只是表中的某一条记录。精准条件命中,并且命中的条件字段是唯一索引。加了记录锁后数据可以避免数据在查询时被修改的重复读问题,也避免了在修改的事务未提交前被其他事务读取的脏读的问题。
页锁:开销、并发度和加锁时间界于表锁和行锁之间,会出现死锁
间隙锁(Gap Lock):属于行锁的一种,是在事务加锁后其锁住的是表记录的某一个区间,当表的相邻ID之间出现间隙则会形成一个区间,遵循左开右闭原则。只会出现在重复读的事务级别中。
临建锁(Next-Key Lock):行锁的一种,是Innodb的行锁默认算法,就是记录锁和间隙锁的结合,临建锁会把查询出来的记录锁住,同时也会把该范围内的所有间隙也锁住,再之它会把相邻的下一个区间也会锁住。触发条件:范围查询并命中,查询命中了索引。结合记录锁和间隙锁的特性,临建锁避免了再范围查询时出现脏读,重复读,幻读问题。加了临建锁后,再范围区间内数据不允许被修改和插入。
基本的指令
增加锁和释放锁
lock table 表1 read/write ,表2 read/write ...
unlock tables
查看加锁的表
show open tables列举在表缓存中当前被打开的非TEMPORARY表。
如果您没有表的权限,则它不会显示在输出中
show open tables
# 返回结果
mysql> show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| MyDB | test | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
- Database:含有该表的数据库。
- Table:表名称。
- In_use:表当前被查询使用的次数。为1表示上锁了。
- Name_locked:表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
分析表锁定的严重程度
show status like 'table%'
--Table_locks_immediate :即可能获取到的锁数
--Table_locks_waited:需要等待的表锁数(如果该值越大,说明存在越大的锁竞争)
表锁
加读锁
-- tablelock引擎是myisam
create table tablelock
(
id int primary key auto_increment ,
name varchar(20)
)engine myisam;
会话0:
lock table tablelock read ;
select * from tablelock; --读(查),可以
delete from tablelock where id =1 ; --写(增删改),不可以
select * from emp ; --读,不可以
delete from emp where eid = 1; --写,不可以
结论1:
--如果某一个会话 对A表加了read锁,则 该会话 可以对A表进行读操作、不能进行写操作; 且 该会话不能对其他表进行读、写操作。
--即如果给A表加了读锁,则当前会话只能对A表进行读操作。
会话1(其他会话):
select * from tablelock; --读(查),可以
delete from tablelock where id =1 ; --写,会“等待”会话0将锁释放
会话1(其他会话):
select * from emp ; --读(查),可以
delete from emp where eno = 1; --写,可以
结论2:
--总结:会话0给A表加了锁;其他会话的操作:a.可以对其他表(A表以外的表)进行读、写操作 b.对A表:读-可以; 写-需要等待释放锁。
释放锁: unlock tables ;
加写锁
会话0:
lock table tablelock write ;
--当前会话(会话0) 可以对加了写锁的表 进行任何操作(增删改查);但是不能 操作(增删改查)其他表
其他会话:
对会话0中加写锁的表,可以进行增删改查的前提是:等待会话0释放写锁
MySQL表级锁的锁模式
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(DML)前,会自动给涉及的表加写锁。
所以对MyISAM表进行操作,会有以下情况:
- 对MyISAM表的读操作(加读锁),不会阻塞其他进程(会话)对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
- 对MyISAM表的写操作(加写锁),会阻塞其他进程(会话)对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。
行锁(InnoDB)
create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values('1') ;
insert into linelock(name) values('2') ;
insert into linelock(name) values('3') ;
insert into linelock(name) values('4') ;
insert into linelock(name) values('5') ;
--mysql默认自动commit; oracle默认不会自动commit ;
为了研究行锁,暂时将自动commit关闭;
set autocommit =0 ;
start transaction ;
begin ;
--上边三种都可以,关闭后需要手动通过commit提交
- 如果会话x对某条数据a进行 DML操作(研究时:关闭了自动commit的情况下),则其他会话必须等待会话x结束事务(commit/rollback)后 才能对数据a进行操作。
- 表锁 是通过unlock tables,也可以通过事务解锁 ; 行锁 是通过事务解锁。
行锁的注意事项
如果没有索引,则行锁会转为表锁
show index from linelock ;
alter table linelock add index idx_linelock_name(name);
会话0: 写操作
update linelock set name = 'ai' where name = '3' ;
会话1: 写操作, 不同的数据
update linelock set name = 'aX' where name = '4' ;
--上边不会阻塞。
会话0: 写操作
update linelock set name = 'ai' where name = 3 ;
会话1: 写操作, 不同的数据
update linelock set name = 'aX' where name = 4 ;
--可以发现,数据被阻塞了(加锁)
-- 原因:如果索引类 发生了类型转换,则索引失效。 因此 此次操作,会从行锁 转为表锁。
行锁的一种特殊情况:间隙锁(值在范围内,但却不存在)
--此时linelock表中 没有id=7的数据
update linelock set name ='x' where id >1 and id<9 ; --即在此where范围中,没有id=7的数据,则id=7的数据成为间隙。
完全避过了主键记录, 锁住数据不存在的区间
间隙:Mysql会自动给 间隙 加锁 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁(行锁)。如果有where,则实际加锁的范围就是where后面的范围(不是实际的值)
锁其实是锁住了索引
如果不使用索引
走表锁使用了索引
走行锁, 锁住了是索引, 不是锁住了一行
for update
查询数据想加锁的话用for update
select * from linelock where id =2 for update ;
行锁分析:
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 : 等待次数。从系统启到现在一共等待的次数