Mysql架构＜九＞ Mysql中的锁

前言

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改一部分数据时，MySQL会通过锁定防止其他用户读取同一数据。

在处理并发读或者写时，通过实现一个由两种类型的锁组成的锁系统来解决问题。两种锁通常被称为共享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。

读锁是共享的，是互相不阻塞的。多个客户端在同一时刻可以同时读取同一个资源，而不互相干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

锁的分类

• 按照锁的范围去分类

• 全局锁：
  将数据库整体锁定。
  常发生再整库备份，或者不希望别人对数据库进行修改时。
  现在的数据库的整库备份有更好的方式：mysqldumo命令种添加--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。而对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。
• 表级锁
  对表进行锁定
• 行级锁
  锁一条或多条记录进行上锁。

表的全区锁，表级锁是在SERVER服务中实现功能的，而行级锁是在INNODB引擎中实现功能的。

• 按照锁的功能去分类：

写锁：
称为排他锁(exclusive lock)。也叫做X锁。
特点就是，我上所之后，别的事务别来修改，也别来读（防止数据延迟）。所以叫做排他锁。
读锁：
称为共享锁(shared lock)，也叫S锁。
特点就是：我上锁之后，你可以来读，但你别来改。

全局锁

上锁命令：

mysql> flush tables with read lock;

释放锁的命令：

mysql> unlock tables；

当你断开某一条会话session时，如果是由改会话产生的全局锁，全局锁也会释放掉。

表级锁

表级锁主要有表读锁、表写锁、元数据锁、自增锁这几种。

表读写锁

查看一个表的上锁状态的命令如下：

# 查看表锁定状态
mysql> show status like 'table%';

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数；

或者也可以是由下面的命令查看：

show open tables;

增加表的读锁或者写锁：

lock table 表名称 read(write),表名称2 read(write)，其他;
# 举例：
lock table t read; #为表t加读锁
lock table t write; #为表t加写锁

删除表锁：

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

• 表读锁实验：
  

• 表写锁实验：
  

元数据锁

• 元数据锁介绍

元数据锁不需要显式使用，在访问一个表的时候会被自动加上。锁的作用是保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 元数据锁，当对一个表做增删改查操作的时候，加 元数据 读锁；当要对表做结构变更操作的时候，加 元数据 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表加读锁，保证数据在读取的过程中不会被其他线程修改。

• 写锁之间，写锁与读锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

• 元数据锁实验：
  

自增锁

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

行级锁

行级锁概念

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁，按照锁定范围来说，分为四种：

• 记录锁（Record Locks）:锁定索引中一条记录。
• 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
• 临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
• 插入意向锁(InsertIntention Locks)：做insert操作时添加的对记录id的锁。

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

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

行级锁的实现语句：

添加读锁：

SELECT * FROM t1_simple WHERE id=4 LOCK IN SHARE MODE;

添加写锁：

SELECT * FROM t1_simple WHERE id=4 FOR UPDATE;

案例：

CREATE TABLE `t1_simple` (
`id` int(11) NOT NULL,
`pubtime` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_pu`(`pubtime`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `t1_simple` VALUES (1, 10);
INSERT INTO `t1_simple` VALUES (4, 3);
INSERT INTO `t1_simple` VALUES (6, 100);
INSERT INTO `t1_simple` VALUES (8, 5);
INSERT INTO `t1_simple` VALUES (10, 1);
INSERT INTO `t1_simple` VALUES (100, 20);

意向锁

• 概念

InnoDB也实现了表级锁，也就是意向锁【Intention Locks】。意向锁是mysql内部使用的，不需要用户干预。意向锁和行锁可以共存，意向锁的主要作用是为了全表更新数据时的提升性能。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。

举个栗子：
事务A修改user表的记录r，会给记录r上一把行级的写锁，同时会给user表上一把意向写锁（IX），这时事务B要给user表上一个表级的写锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存，且满足事务隔离性的要求。

意向锁有意向写锁（IX）和意向读锁（IS）。
其作用为：

• 表明：“某个事务正在某些行持有了锁、或该事务准备去持有锁”
• 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存

举个栗子：
当我们需要加一个写锁时，需要根据意向锁去判断表中有没有数据行被锁定；
（1）如果行锁，则需要遍历每一行数据去确认；
（2）如果表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

表读锁与表写锁与一行级意向锁之间的兼容关系如下：

• 意向锁相互兼容：因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。
• 表级S锁和X、IX锁不兼容：因为上了表级S锁后，不允许其他事务再加X锁。
• 表级X锁和 IS、IX、S、X不兼容：因为上了表级X锁后，会修改数据。

注意：上了行级写锁后，行级写锁不会因为有别的事务上了意向写锁而堵塞，一个mysql是允许多个行级写锁同时存在的，只要他们不是针对相同的数据行。

记录锁

记录锁(Record Locks)，仅仅锁住索引记录的一行，在单条索引记录上加锁。记录锁锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加写锁。

• 加记录共享锁

select * from t1_simple where id = 1 lock in share mode;

• 加记录排它锁

select * from t1_simple where id = 1 for update;

间隙锁

间隙锁就是解决幻读的一个方法。
（1）间隙锁(Gap Locks)，仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录
本身。
（3）间隙锁可用于防止幻读，保证索引间不会被插入数据。

主键id索引的行锁区间划分图：

实践：

# session 1 执行：
begin;
select * from t1_simple where id > 4 for update;

# session 2 执行：

## 加了间隙锁，不能再此区间插入。
insert into t1_simple values (7,100); 
## session 1 提交后就成功了。
insert into t1_simple values (7,100); 

临界锁

（1）临键锁(Next-Key Locks)相当于记录锁 + 间隙锁【左开右闭区间】的形式，例如（5,8]。
（2）默认情况下，innodb使用临键锁来锁定记录。
（3）但当查询的索引含有唯一属性的时候，临键锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。
（4）临键锁在不同的场景中会退化:

普通索引index(pubtime)行锁的区间划分图

实践：

# 当前数据库中的记录信息：
mysql> select * from t1_simple;

# session 1 执行
begin;
select * from t1_simple where pubtime = 20 for update;
# -- 间隙锁(10,20],(20,100]

# session 2 执行
insert into t1_simple values (16, 19); --阻塞
select * from t1_simple where pubtime = 20 for update; --阻塞
insert into t1_simple values (16, 50); --阻塞
insert into t1_simple values (16, 101); --成功

加锁规则

主键索引

• 等值查询，命中，加记录锁
• 等值查询，未命中，加间隙锁
• 范围查询，命中，包含where条件的临键区间，加临键锁
• 范围查询，没有命中，加间隙锁

辅助索引

• 等值查询，命中，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁
• 等值查询，未命中，加间隙锁
• 范围查询，命中，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁
• 范围查询，没有命中，加间隙锁

插入意向锁

（1）插入意向锁(Insert Intention Locks)是一种间隙锁，不是意向锁，在insert操作时产生。
（2）在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
（3）假设有一个记录索引包含键值10和100，不同的事务分别插入60和70，每个事务都会产生一个加在10-100之间的插入意向锁，获取在插入行上的写锁，但是不会被互相锁住，因为数据行并不冲突。
（4）插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

锁相关参数

# Innodb所使用的行级锁定争用状态查看：
mysql> 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：系统启动后到现在总共等待的次数；

# 查看锁的SQL
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

# 查看事务SQL
select * from information_schema.innodb_trx;

# 查看未关闭的事务详情
SELECT
a.trx_id,a.trx_state,a.trx_started,a.trx_query,
b.ID,b.USER,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO,
c.PROCESSLIST_USER,c.PROCESSLIST_HOST,c.PROCESSLIST_DB,d.SQL_TEXT
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID =c.THREAD_ID;

会当凌绝顶，一览众山小！加油！