数据库事务锁详解

时间:2024-01-28 19:55:34

前言

上篇说到数据库事务中的特性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 = 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,一个喜欢健身的程序员。

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

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