MySQL——锁介绍

MySQL 锁介绍

锁分类：

MySQL表级锁

介绍

MySQL表级锁是有MySQL SQL Layer 层实现的

• MySQL的表级锁有两种：
  一种是表锁。
  一种是元数据锁（meta data lock，MDL)。
• MySQL 实现的表级锁定的争用状态变量：
  mysql> show status like ‘table%’;
  table_locks_immediate：产生表级锁定的次数；
  table_locks_waited：出现表级锁定争用而发生等待的次数

表锁介绍

• 表锁有两种表现形式：
  表共享读锁（Table Read Lock）
  表独占写锁（Table Write Lock）
• 手动增加表锁：
  lock table 表名称 read(write),表名称2 read(write)，其他;
• 查看表锁情况：
  show open tables;
• 删除表锁
  unlock tables

演示

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; --可以访问

• 表写锁
  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 (metaDataLock) 元数据：表结构
在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结
构变更操作的时候，加 MDL 写锁

演示

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) --修改完成
Records: 0 Duplicates: 0 Warnings: 0

MySQL行级锁

行级锁介绍

InnoDB存储引擎实现
InnoDB的行级锁，按照锁定范围来说，分为三种：
记录锁（Record Locks）:锁定索引中一条记录。 主键指定 where id=3
间隙锁（Gap Locks）: 锁定记录前、记录中、记录后的行 RR隔离级 （可重复读）
Next-Key 锁: 记录锁 + 间隙锁

行级锁分类

按照功能来说，分为两种：
共享读锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

`SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享读锁 手动添加
select * from table -- 无锁``sql

排他写锁（X）：允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁（不是
读）和排他写锁。

• 自动加 DML
  对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

• 手动加

SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该
  表的IS锁
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该
  表的IX锁

意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该
表是否某些记录上面有行锁。

	共享锁（s）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
共享锁（s）	兼容	冲突	兼容	冲突
排他锁（X）	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排他锁（IX）	冲突	冲突	兼容	兼容

两阶段锁（2PL）

锁操作分为两个阶段：加锁阶段与解锁阶段，
加锁阶段与解锁阶段不相交。
加锁阶段：只加锁，不放锁。
解锁阶段：只放锁，不加锁。

行级锁演示

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过
索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
where 索引 行锁 否则 表锁

行读锁

session1（Navicat）、session2（mysql）

• Innodb_row_lock_current_waits：当前正在等待锁定的数量；
• Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
• Innodb_row_lock_time_avg：每次等待所花平均时间；
• Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
• Innodb_row_lock_waits：系统启动后到现在总共等待的次数

查看行锁状态 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
注：使用索引加行锁 ，未锁定的行可以访问

行读锁升级为表锁

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; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
注：未使用索引行锁升级为表锁

行写锁

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：执行成功
主键索引产生记录锁

间隙锁

session1（Navicat）、session2（mysql）

案例演示：
mysql> create table news (id int, number int,primary key (id));
mysql> insert into news values(1,2);
......
--加非唯一索引
mysql> 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 id>1 and id < 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 ;
update news set xx where number=13 ;
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（Navicat）、session2（mysql）

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