Mysql笔记（四）锁机制

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');

1. 表读锁
   
   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; --可以访问

2. 表写锁
   
   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）
  允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁（不是读）和排他写锁。
  1. 自动加（DML）
     对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
  2. 手动加

     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

如何尽量避免死锁的产生：

1. 根本的原因是程序逻辑的顺序，最常见的是交差更新

Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A
这类问题要从程序上避免，所有的更新需要按照一定的顺序

2. 保持事务的轻量

越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
1.避免使用子查询，尽量使用主键等等，以提高SQL运行速度
2.尽量快提交事务，减少持有锁的时间