锁的分类
按锁的粒度来分:
-
全局锁:作用于整个database,由sql layer层实现
-
表级锁:作用于整个table,由sql layer 层实现。开销小,加锁快;不会出现死锁;锁定粒度大,发省锁冲突的概率最高,并发度最低;
-
行级锁:作用于某行,或者行间隙,由某层存储引擎实现。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
按功能分: -
共享读锁
-
排他写锁
按实现方式分: -
1悲观锁
-
- 表级锁
- 表锁(MySQL layer)手动加
- read lock (加读锁以后可以加读锁,不可加写锁)
- write lock (加写锁后,不可再加读锁和写锁)
- 元数据锁(MySQL layer)自动加
- CURD 加读锁
- DDL加写锁
- 意向锁(InnoDB)内部使用
- 共享读锁(IS)
- 排他写锁(IX)
-
- 行级锁
- 共享读锁(S) 手动加
- select …lock in share mode
- 排他写锁(X) 自动加
- DML(insert 、updata、delet)
- select … for update
-
-
2乐观锁
- 程序实现
MySQL表级锁
查看表级锁定争用状态变量:
show status like 'table%';
- table_locks_immediate:产生表级锁定的次数;
- table_locks_waited:出现表级锁定争争用而发生等待的次数;
手动增加表锁
lock table 'tablename1' read(write),'tablename2' read(write);
查看表锁情况
show open tables;
删除表锁
unlock tables;
1、session1: lock table mylock read; -- 给mylock表加读锁
2、session1: select * from mylock; -- 可以查询
3、session1:select * from tdep; --不能访问非锁定表
4、session2:select * from mylock; -- 可以查询 没有锁
5、session2:update mylock set name='x' where id=2; -- 修改阻塞,自动动加行写锁
6、session1:unlock tables; -- 释放表锁
7、session2:Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成
8、session1:select * from tdep; --可以访问
1、session1: lock table mylock write; -- 给mylock表加写锁
2、session1: select * from mylock; -- 可以查询
3、session1:select * from tdep; --不能访问非锁定表
4、session1:update mylock set name='y' where id=2; --可以执行
5、session2:select * from mylock; -- 查询阻塞
6、session1:unlock tables; -- 释放表锁
7、session2:4 rows in set (22.57 sec) -- 查询执行完成
8、session1:select * from tdep; --可以访问
元数据锁
MDL不需要显式使用,在访问一个表的时候会被自动动加上。MDL的作用是,保证读写的正确性。
在 MySQL 5.5 版本中引用了 MDL,当对一个个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。
-
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
-
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执型。
1、session1: begin;--开启事务
select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改,被阻塞
3、session1:commit; --提交事务 或者 rollback 释放读锁
4、session2:Query OK, 0 rows affected (38.67 sec) --修改完成
Records: 0 Duplicates: 0 Warnings: 0
begin 开始一个事务
commit 提交一个事务
行级锁
按照范围分
- 记录锁(Record Locks):锁定索引中一条记录。 id=1
- 间隙锁(Gap Locks) :要么锁住索引记录中间的值,要么锁住第一个索引记录前一的值或者最后一个索引记录后一的值。
- Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。
按功能分:
- 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁(不是读)
和排他写锁。
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁,事务可以通过以下语句显示给记录集加共享锁或排他锁。
自动添加共享锁(S):
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
自动添加排他锁(x):
SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDB也实现了表级锁,也就是意向锁,意向锁是mysql内部使用的,不需要用户干预:
- 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁和行锁可以共存,意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更
新数据时,需要先检索该表是否某些记录上面有行锁。
共享锁(s) | 排他锁(x) | 意向共享锁(IS) | 意向排他锁(IX) | |
---|---|---|---|---|
共享锁(s) | JR | CT | JR | CT |
排他锁(x) | CT | CT | CT | CT |
意向共享锁(IS) | JR | CT | JR | JR |
意向排他锁(IX) | CT | CT | JR | JR |
InnoDB 行锁是通过给索引上的索引项加锁来实现的,因此InnoDB这种⾏锁实现特点意味着:只
有通过索引条件检索的数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
Innodb所使用的行级锁定争用状态查看:
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:系统启动后到现在总共等待的次数;
两阶段锁
传统RDBMS加锁的一个原则,就是2PL (Two-Phase Locking,二阶段锁)。即锁操作分为两个阶段:加锁阶段与解锁阶段,并且保证加锁阶段与解锁阶段不相交。
加锁阶段:只加锁,不放锁。(insert ,update delete 加锁)
解锁阶段:只放锁,不加锁。(释放掉所有操作增加的锁)
#查看行锁状态
show STATUS like 'innodb_row_lock%'; 1、session1: begin;--开启事务未提交
select * from mylock where ID=1 lock in share mode; --自动动加id=1的行
读锁,使用索引
2、session2:update mylock set name='y' where id=2; -- 未锁定该行可以修改
3、session2:update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 锁定超时
4、session1: commit; --提交事务 或者 rollback 释放读锁
5、session2:update mylock set name='y' where id=1; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
注:使用索引加行锁 ,未锁定的行可以访问
行读锁升级为表锁
1、session1: begin;--开启事务未提交
--自动动加name='c'的行读锁,未使用索引
select * from mylock where name='c' lock in share mode;
2、session2:update mylock set name='y' where id=2; -- 修改阻塞 未用索引行锁升级为表
锁3、session1: commit; --提交事务 或者 rollback 释放读锁
4、session2:update mylock set name='y' where id=2; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
行写锁
1、session1: begin;--开启事务未提交
select * from mylock where id=1 for update;
2、session2:select * from mylock where id=2 ; -- 可以访问
3、session2: select * from mylock where id=1 ; -- 可以读 不加锁
4、session2: select * from mylock where id=1 lock in share mode ; -- 加读
锁被阻塞
5、session1:commit; -- 提交事务 或者 rollback 释放写锁
5、session2:执行成功
主键索引产生记录锁
间隙锁
1、防止插入间隙内的数据
2、防止已有数据更新为间隙内的数据
create table news (id int, number int,primary key (id));
insert into news values(1,2);
--加非唯一索引
alter table news add index idx_num(number);
session 1: start transaction ;
select * from news where number=4 for update ;
session 2: start transaction ;
insert into news value(2,4);#(阻塞)
insert into news value(2,2);#(阻塞)
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(7,5);#(执行成功)
insert into news value(9,5);#(执行成功)
insert into news value(11,5);#(执型成功)
注:id和number都在间隙内则阻塞。
session 1:
start transaction ;
select * from news where number=13 for update ;
#select * from news where id>1 and id < 8 for update;
session 2:
start transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
检索条件number=13,向左取得最靠近的值11作为左区间,向右由于没有记录因此取得无穷大作为右区间,
因此,session 1的间隙锁的范围(11,无穷大)
注:非主键索引产生间隙锁,作用于主键范围
死锁
两个会话session等待对方资源释放,才能释放自己的资源造成的死锁.
session1:
begin; 开启事务
update mylock set name="m" where id =1; 手动加锁(索引)
sesssion2:
begin;
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