Mysql锁介绍
Mysql锁分类
Mysql表级锁
表级锁介绍
- MySQL的表级锁有两种
表锁
元数据锁
(Meta Data Lock,MDL)
- MySQL实现的表级锁定的争用状态变量查询:
mysql> show status like 'table_locks%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Table_locks_immediate | 102330 |
| Table_locks_waited | 0 |
+-----------------------+--------+
Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而发生等待的请求数;
表锁介绍
- 表锁有两种表现形式:
表共享读锁
(Table Read Lock)表独占写锁
(Table Write Lock)
- 手动增加表锁
lock table 表名称1 read(write),表名称2 read(write),其他;
- 查看表锁情况
show open tables;
- 删除表锁
unlock tables;
表锁情况
环境:
--新建表
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');
- 表读锁
session1(Navicat)、session2(mysql)
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; --可以访问
- 表写锁
session1(Navicat)、session2(mysql)
1、session1: lock table mylock write; – 给mylock表加写锁
2、session1: select * from mylock; –当前session可以查询
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; --可以访问
Mysql元数据锁
元数据锁介绍
MDL (MetaDataLock) ,元数据(表结构)
MDL是在MySQL 5.5
才引入的,之前版本也有类似保护元数据的机制,只是没有明确提出MDL概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是,5.1对于元数据的保护是语句级别
的,5.5对于metadata的保护是事务级别
的。所谓语句级别,即语句执行完成后,无论事务是否提交或回滚,其表结构可以被其他会话更新
;而事务级别则是在事务结束后才释放MDL
。
引入MDL后,主要解决了2个问题
:
- 一个是事务隔离问题,比如在
可重复隔离级别
下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致
,无法满足可重复读的要求; - 另外一个是
数据复制的问题
,比如会话A先执行了更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。
元数据锁情况
session1(Navicat)、session2(mysql)
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) --修改完成
Mysql行锁
行级锁介绍
InnoDB存储引擎实现
InnoDB的行级锁,按照锁定范围来说,分为三种:
记录锁(Record Locks)
:锁定索引中一条记录。 例如主键或唯一索引等值条件指定 where id=3间隙锁(Gap Locks)
: 锁定索引记录之间的间隙,或者是某一条记录之前和之后加锁,该机制主要为了解决幻读问题。Next-Key 锁
: 记录锁 + 间隙锁,RR隔离级下默认使用Next-Key锁,即锁定当前记录也锁定记录间隙。
行级锁分类
按照功能来说,分为两种:
共享读锁
(S)
允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
。
//共享读锁 手动添加
SELECT * FROM table_name WHERE …LOCK IN SHARE MODE
SELECT * FROM table_name –MVCC下无锁
排他写锁
(X)
允许获得排他写锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁
(不是读)和排他写锁
。- 自动加(
DML
)
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X); - 手动加
SELECT * FROM table_name WHERE …
FOR UPDATE
- 自动加(
InnoDB也实现了表级锁
,也就是意向锁
,意向锁是mysql内部使用的,不需要用户干预。
意向共享锁
(IS)
事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁
(IX)
事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁的主要作用是为了提升获取表锁时的性能。否则在获取表锁时,需要先检索该表是否某些记录上面有行锁。而且意向锁本身不互斥
,比如IX与IX或IS与IX不互斥,可以多个存在,主要的目的就是为了让行锁与表锁更好的兼容存在
。
行锁情况
InnoDB行锁
是通过给索引上的索引项加锁
来实现的,因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索的数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
行读锁
:
session1(Navicat)、session2(mysql)
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; --修改成功
行读锁升级为表锁
session1(Navicat)、session2(mysql)
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; --修改成功
注:未使用索引行锁升级为表锁
行写锁
session1(Navicat)、session2(mysql)
1、session1: begin;--开启事务未提交
--手动加id=1的行写锁,
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:执行成功
注:行产生写锁时,在MVCC下仍然可读,因为MVCC下的select读不需要获取锁(无锁),所以不会阻塞
间隙锁(Gap Lock)
在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。gap lock的机制主要是解决
可重复读模式下的的幻读问题
。
示例:
图一
在RR级别下,mysql通过间隙锁可以实现锁定number=4之前的间隙,number=4记录之间的间隙,number=4之后的间隙,从而使的新的记录无法被插入进来以防止幻读(Insert)
。
间隙具体划分如图一中,根据number列,我们可以分为几个区间:(无穷小,2),(2,4),(4,5),(5,5),(5,11),(11,无穷大)。
只要这些区间对应的两个临界记录中间可以插入记录
,就认为区间对应的记录之间有间隙。
例如:区间(2,4)分别对应的临界记录是(id=1,number=2),(id=3,number=4),这两条记录中间可以插入(id=2,number=3)等记录,那么就认为(id=1,number=2)与(id=3,number=4)之间存在间隙。(id=6,number=5)与(id=8,number=5)之间可以插入记录(id=7,number=5),因此(id=6,number=5)与(id=8,number=5)之间有间隙的,
间隙锁锁定的区域
根据检索条件向左寻找最靠近检索条件的记录值A,作为左区间,向右寻找最靠近检索条件的记录值B作为右区间,即锁定的间隙为(A,B)。
图一中,where number=5的话,那么间隙锁的区间范围为(4,11);
间隙锁的目的是为了防止幻读
,其主要通过两个方面实现这个目的:
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据
(例如防止numer=3的记录通过update变成number=5)
innodb自动使用间隙锁的条件
:
(1)必须在RR级别下
(2)检索条件必须有索引
(没有索引的话,mysql会全表扫描,那样会锁定整张表,此时其他事务不能修改不能删除不能添加)
死锁
两个 session 互相等等待对方的资源释放之后,才能释放自己的资源,造成了死锁
1、session1: begin;--开启事务未提交
--手动加行写锁 id=1 ,使用索引
update mylock set name='m' where id=1;
2、session2:begin;--开启事务未提交
--手动加行写锁 id=2 ,使用索引
update mylock set name='m' where id=2;
3、session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞
4、session2:update mylock set name='nn' where id=1; -- 加写锁会死锁,不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
如何尽量避免死锁的产生:
- 根本的原因是程序逻辑的顺序,最常见的是交差更新
Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A
这类问题要从程序上避免,所有的更新需要按照一定的顺序
- 保持事务的轻量
越是轻量的事务,占有越少的锁资源,这样发生死锁的几率就越小
1.避免使用子查询,尽量使用主键等等,以提高SQL运行速度
2.尽量快提交事务,减少持有锁的时间