【Mysql分享】之锁篇

目录索引
【Mysql分享】之索引篇
【Mysql分享】之锁篇
【Mysql分享】之事务分析篇

一、Mysql表级锁

表级锁由 Mysql Layer 层实现.

Mysql 表级锁有两种

一种是表锁

一种是元数据锁（MDL）

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;
  

表锁实践

• 案例表

  CREATE TABLE t_lock (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
  );
  INSERT INTO t_lock (id,name) VALUES (1, 'a'); 
  INSERT INTO t_lock (id,name) VALUES (2, 'b');
  INSERT INTO t_lock (id,name) VALUES (3, 'c'); 
  INSERT INTO t_lock (id,name) VALUES (4, 'd');
  

• 表读锁

  session1: lock table t_lock read; -- 给t_lock表加读锁
  session1: select * from t_lock; -- 可以查询
  session1: select * from t_user; --不能访问非锁定表(ERROR 1100 (HY000): Table 't_user' was not locked with LOCK TABLES)
  session1: update t_lock set name = 'c' where id =1; --不可执行（ERROR 1099 (HY000): Table 't_lock' was locked with a READ lock and can't be updated）
  session2: select * from t_lock; -- 可以查询没有锁
  session2: update t_lock set name='j' where id=1; -- 修改阻塞,自动加行写锁 
  session1: unlock tables; -- 释放表锁
  session2: Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成 
  session1: select * from t_user; --可以访问
  

• 表写锁

  session1: lock table t_lock write; -- 给mylock表加写锁 
  session1: select * from t_lock; -- 可以查询 
  session1: select * from t_user; --不能访问非锁定表 
  session1: update t_lock set name='c' where id=1; --可以执行 
  session2: select * from t_lock; -- 查询阻塞 
  session1: unlock tables; -- 释放表锁
  session2: 4 rows in set (18.41 sec) -- 查询执行完成 
  session1: select * from t_user; --可以访问；
  

元数据锁（MDL）

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。

如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，**在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁;当要对表做结构变更 操作的时候，加 MDL 写锁。**读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

session1: begin; --开启事务
session1: select * from t_lock; --加MDL读锁
session2: alter table t_lock add f int; -- 修改阻塞 
session1: commit; --提交事务 或者 rollback 释放读锁 
session2: Query OK, 0 rows affected (19.92 sec) --修改完成

二、行级锁

行级锁介绍

MySQL的行级锁，是由存储引擎来实现的，InnoDB的行级锁。

• InnoDB的行级锁，按照锁定范围来说，分为三种:

  记录锁(Record Locks):锁定索引中一条记录。 主键指定 where id=1
  间隙锁(Gap Locks): 锁定记录前、记录中、记录后的行 RR隔离级 (可重复读)-- MySQL默认隔离级 
  Next-Key 锁: 记录锁 + 间隙锁 (索引记录上的记录锁和在索引记录之前的间隙锁的组合)
  

• InnoDB的行级锁，按照功能来说，分为两种:

  共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  排他锁(X):允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  

行级锁实践

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

• 查看行锁状态

  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:系统启动后到现在总共等待的次数;
  

行读锁

• 索引加行锁，未锁定的行可以访问

session1: begin;--开启事务未提交
session1: select * from t_lock where id = 1 lock in share mode; --手动加id=1的行读锁,使用索引
session2: update t_lock set name = 'y' where id = 2; -- 未锁定该行可以修改 
session2: update t_lock set name = 'j' where id = 1; -- 锁定该行修改阻塞(ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction) -- 锁定超时
session1: commit; --提交事务 或者 rollback 释放读锁 
session2: update t_lock set name = 'y' where id = 1; --修改成功(Query OK, 1 row affected (0.00 sec))

• 行读锁升级为表锁(未使用索引行锁升级为表锁)

session1: begin;--开启事务未提交 --手动加name='c'的行读锁,未使用索引
session1: select * from t_lock where name ='c' lock in share mode; 
session2: update t_lock set name ='j' where id = 2; -- 修改阻塞 未用索引行锁升级为表锁 
session1: commit; --提交事务 或者 rollback 释放读锁
session2: update t_lock set name ='j' where id = 2; --修改成功

行写锁

--主键索引产生记录锁
session1: begin;--开启事务未提交 
session1: select * from t_lock where id = 1 for update; --手动加id=1的行写锁,
session2: select * from t_lock where id = 2; -- 可以访问 
session2: select * from t_lock where id = 1; -- 可以读 不加锁
session2: update t_lock set name ='1' where id = 1; --修改阻塞  
session2: select * from t_lock where id = 1 lock in share mode; -- 加读锁 被阻塞
session1: commit; -- 提交事务 或者 rollback 释放写锁 
session2: 执行成功

间隙锁

间隙锁防止两种情况:

1. 防止插入间隙内的数据
2. 防止已有数据更新为间隙内的数据

--间隙锁案例表
CREATE TABLE t_gap_lock (
id int(11) NOT NULL AUTO_INCREMENT,
num int DEFAULT NULL,
PRIMARY KEY (id)
);
--设置索引
alter table t_gap_lock add index idx_num(num);
--插入间隙条数据
insert into t_gap_lock (id,num) values (1,2);
insert into t_gap_lock (id,num) values (3,4);
insert into t_gap_lock (id,num) values (6,5);
insert into t_gap_lock (id,num) values (8,5);
insert into t_gap_lock (id,num) values (10,5);
insert into t_gap_lock (id,num) values (13,11);

• 非唯一索引等值间隙锁情况

  结论:只要num(where后面的)在间隙里(2、3 、4)，不包含最后一个数(5)则不管id是多少都会阻塞。如果num为5下边缘数据，则id在间隙内阻塞。

session1: start transaction;
session1: update t_gap_lock set num = 3 where num = 4;
session2: start transaction;
insert into t_gap_lock (id,num) value (2,3); --(均在间隙内，阻塞)
insert into t_gap_lock (id,num) value (7,8); --(均在间隙外，成功)
insert into t_gap_lock (id,num) value (2,8); --(id在间隙内，num在间隙外，成功)
insert into t_gap_lock (id,num) value (4,8); --(id在间隙内，num在间隙外，成功)
insert into t_gap_lock (id,num) value (9,3); --(id在间隙外，num在间隙内，阻塞)
insert into t_gap_lock (id,num) value (9,2); --(id在间隙外，num为上边缘数据，阻塞)
insert into t_gap_lock (id,num) value (3,2); --id在间隙内，num为上边缘数据，阻塞)
insert into t_gap_lock (id,num) value (9,5); --(id在间隙外，num为下边缘数据，成功)
insert into t_gap_lock (id,num) value (4,5); --(id在间隙内，num为下边缘数据，阻塞)

• 主键索引范围

  结论:id无边缘数据，因为主键不能重复,只要id(在where后面的)在间隙里(2、4、5)，则不管num是多少都会阻塞。

session1: start transaction;
session1: update t_gap_lock set num = 3 where id > 1 and id < 6 ;
session2: start transaction;
insert into t_gap_lock (id,num) value (2,3); --(均在间隙内，阻塞)
insert into t_gap_lock (id,num) value (7,8); --(均在间隙外，成功)
insert into t_gap_lock (id,num) value (2,8); --(id在间隙内，num在间隙外，阻塞)
insert into t_gap_lock (id,num) value (4,8); --(id在间隙内，num在间隙外，阻塞)
insert into t_gap_lock (id,num) value (9,3); --(id在间隙外，num在间隙内，成功）

• 非唯一索引无穷大

  结论:id和num同时满足，阻塞

session1: start transaction;
session1: update t_gap_lock set num = 3 where num = 13 ;--检索条件num=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因 此，session1的间隙锁的范围(11，无穷大)
session2: start transaction;
insert into t_gap_lock (id,num) value (11,5); --(id间隙外，num间隙外，成功)
insert into t_gap_lock (id,num) value (12,11); --(id间隙外,num间隙内，成功)
insert into t_gap_lock (id,num) value (14,11); --(id间隙内,num间隙内，阻塞)
insert into t_gap_lock (id,num) value (15,12); --(id在间隙内，num在间隙内，阻塞)

死锁

两个 session 互相等等待对方的资源释放之后，才能释放自己的资源,造成了死锁

session1: begin;--开启事务未提交
session1: update t_lock set name='j' where id=1; --手动加行写锁 id=1,使用索引
session2: begin;--开启事务未提交
session2: update t_lock set name='j1' where id=2; --手动加行写锁 id=2,使用索引
session1: update t_lock set name='j2' where id=2; -- 加写锁被阻塞 
session2: update t_lock set name='j3' where id=1; -- 加写锁会死锁,不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

系列文章
【Mysql分享】之索引篇
【Mysql分享】之锁篇
【Mysql分享】之事务分析篇