前言

上篇说到数据库事务中的特性ACID和4个隔离级别，今儿就来看一下事务中的锁。

MySQL中的锁

锁是MySQL在服务器层和存储引擎层的并发控制，锁可以保证数据并发访问的一致性、有效性；

锁冲突也是影响数据库并发访问性能的一个重要因素

MySQL有三种级别的锁：「表级锁、行级锁、页级锁」

表级锁行级锁业级锁特点开销小、加锁快开销大、加锁慢加锁时间介于其余两者之间是否会死锁否是是并发度粒度大、锁冲突概率最高、并发低粒度小、锁冲突概率低、并发高粒度介于其余两者之间、并发一般存储引擎Innodb、MyISAMInnodbBDB

「术语：」

DDL，Data Definition Language，数据库定义语言

比如：CREATE，ALERT，DROP，TRUNCATE

DML，Data Manipulation Language，数据库操作语言

比如：SELECT，INSERT，UPDATE，DELETE，CALL，EXPLAIN PLAN，LOCK

DCL，Data Control Language，数据库控制语言

比如：COMMIT，SAVEPOINT，ROLLBACK，SET TRANSACTION

Innodb中的锁

表锁和行锁

表锁

Innodb有两种内部使用的意向锁（Intention Locks），都是表锁。

表锁分成三种：

「意向共享锁（IS）：」

事务计划给数据行加行共享锁，加共享锁之前必先获取该锁

「意向排他锁（IX）：」

事务打算给数据行加行排他锁，加排他锁之前必先获取该锁

「自增锁（AUTO-INC Locks）：」

特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。

在加行锁之前必须先获得表级意向锁，否则等待 innodb_lock_wait_timeout 超时后根据innodb_rollback_on_timeout 决定是否回滚事务。

如何添加表锁

lock tables table_name read/write

「释放锁：」

释放锁不需要添加参数，其会释放当前用户的所有锁。

unlock tables

「例如：」

1、给student表添加读锁，看当前用户和其他用户是否能插入数据：

当前用户：报错无法插入

其他用户：一直等待

释放锁之后：

其他用户：插入成功

2、多个用户获取写锁

root用户获取写锁：

然后试一下lsy用户能否获取相同表的写锁

可看到是一直在等待。

当root用户释放写锁后：

lsy用户立马就获得了写锁：

行锁

共享锁（S）和排它锁（X）。

「共享锁（S）：」

多个事务可以一起读，共享锁之间不互斥，共享锁会阻塞排它锁。

「排他锁（X）：」

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

加锁方式：

自动加锁

对于UPDATE、DELETE、INSERT语句，自动给相关数据加上排他锁

对于普通的SELECT语句，不加锁，属于快照读

「手动加锁：」

共享锁：

select * from table_name [where] lock in share mode;

排他锁：(这是我之前比较常用的)

select * from table_name [where] for updete

通过对索引数据页上的记录（record）加锁实现的。

主要实现算法有 3 种：

「Record Lock 锁：」

单个行记录的锁（锁数据，不锁 Gap）。

例如for update就是此锁

「Gap Lock 锁：」

间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap）。

保证某个间隙内的数据在锁定期间不会发生任何变化。

当使用唯一索引进行搜索的时候，不会产生间隙锁

例如：student的id列是唯一索引

select * from student where id = 1;

当使用非唯一索引或者没有索引进行搜索的时候，会产生间隙锁

间隙范围：

根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B] 左开右闭。

例如：test的id列是没有索引

使用如下sql查询的时候

在lsy用户下执行

select * from test where id = 3 for update;

那么它的间隙范围就是(1，6]

如果在其他用户想往这区间插入数据就会阻塞，比如插入id是4的。

「Next-key Lock 锁：」

同时锁住数据，并且锁住数据前面的 Gap。

死锁

InnoDB 是逐行加锁的，极容易产生死锁。那么死锁产生的四个条件是什么呢？

「互斥条件：」

一个资源每次只能被一个进程使用；

「请求与保持条件：」

一个进程因请求资源而阻塞时，对已获得的资源保持不放；

「不剥夺条件：」

进程已获得的资源，在没使用完之前，不能强行剥夺；

「循环等待条件：」

多个进程之间形成的一种互相循环等待资源的关系。

发生死锁后，会出现CPU使用率高，QPS急剧下降，回滚请求失败的情况

避免死锁

加锁顺序一致

尽量基于primary或unique key更新数据。

单次操作数据量不宜过多，涉及表尽量少。

减少表上索引，减少锁定资源。

「死锁情况下打印错误日志」

Show engine innodb status\G或者innodb_print_all_deadlocks=ON 打印到错误日志

例如：

有两张表，分别是student和test表

1、在事务1中先删除student表中id=10的数据

2、在事务2中删除test表中id=6的数据

3、在事务1中删除test表中id=6的数据

4、在事务2中删除student表中id=10的数据

此时就会报死锁错误：

事务1：

事务2：

用Show engine innodb status\G查看日志：

元数据锁

Metadata Lock

用于解决或者保证DDL操作与DML操作之间的一致性。

当对一个表做增删改查操作的时候，加 MDL 读锁；

当要对表做结构变更操作的时候，加 MDL 写锁。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性

快速发现锁等待

「Innodb锁：」

select b.trx_mysql_thread_id as '被阻塞线程' ,b.trx_query as '被阻塞SQL'
,c.trx_mysql_thread_id as '阻塞线程' ,c.trx_query as '阻塞SQL'
,(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间'
from
information_schema.innodb_lock_waits a
join
information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id
join
information_schema.innodb_trx c on a.blocking_trx_id=c.trx_id
where
(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started))>10;

或

select * from sys.innodb_lock_waits\G

「元数据锁：」

select * from performance_schema.metadata_locks;

根据上一条sql获取的线程id查询线程详细信息：

select * from performance_schema.threads where thread_id in (thread_ids)

注意：元数据锁信息需要开启performance_schema

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

 

 

===============================

我是Liusy，一个喜欢健身的程序员。

获取更多干货以及最新消息，公_众_号：上古伪神

如果对您有帮助，点个关注、转发就是对我最大的支持！！！谢谢