【事务系】基础篇2-锁

奔跑吧，小子

已于 2023-04-18 09:41:59 修改

阅读量257

点赞数 1

分类专栏：数据库文章标签：数据库 mysql

于 2022-09-27 21:10:43 首次发布

本文链接：https://blog.csdn.net/run_boy_2022/article/details/127062684

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.1 mysql 锁机制

此篇文章用来加深自己对mysql 锁的理解，同时为了让大家少搜索到一些杂乱无序的文章。如果文中有纰漏不严谨的地方或错误的地方，欢迎各位大佬批评指出。

InnoDB 存储引擎支持表锁和行锁，默认为行锁；MyISAM 只支持表锁。

什么是锁呢？
锁是一种协调多个进程或多个线程对某一资源的访问的机制。mysql使用锁和MVCC机制实现了事务的隔离级别

mysql锁的分类
从性能上分为悲观锁、乐观锁

悲观锁

需要依赖数据库提供的锁机制，保证数据库加锁后，其他应用系统无法修改数据库中的数据、悲观锁机制下，读取数据库数据需要加锁，此时不能修改操作，修改数据库数据，此时不能对这些数据进行读取操作

乐观锁

悲观锁极大降低数据库的性能，对于长事务而言，性能无法承受，就引出了乐观锁。乐观锁依赖我们需要对数据库表添加版本号的字段，更新数据的时候，我们通过版本号的条件再让当前版本号的值加1.

从对数据库的操作上分为读锁、写锁

读锁(共享锁、S锁)

多个读锁之间是互不影响的，共享锁用于事务并发读取，比如select ... in share mode。

写锁(排它锁、X锁)

写写互斥，写读互斥，排它锁用于事务并发更新或删除。比如select ... for update

从操作数据的粒度上看分为表锁、行锁、页级锁

表锁

在整个数据表上对数据进行加锁和释放锁、开销小，加锁快，一般不会出现死锁，锁定粒度大，并发读最低。表锁又分为共享锁(读锁)和独占锁(写锁)

语法为lock table 表名称 read(write)

行锁

行锁分为共享锁、排他锁。同样支持读读，读写互斥。innoDb的行锁是加在索引上面的，非索引的字段进行更新或索引失效，行锁可能会成为表锁。

意向共享锁、意向排他锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁：

意向共享锁(IS)：事务打算给数据行加行共享锁事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX)：事务打算给数据行加行排他锁事务在给一个数据行加排他锁前必须先取得该表的IX锁。意向排它锁互相之间是兼容的。
意向锁也是数据库隐式帮我们做了，不需要程序员关心！

页级锁

对一组连续的数据记录添加锁和释放锁、性能介于表锁和行锁之间

间隙锁(GAP)

间隙锁是锁住一个区间的锁。在我们使用范围查询语句时，如果请求共享锁或排他锁，inndb会给符合条件的已有数据的索引项加锁。如果键值在条件范围内，而这个范围并不存在记录，则认为出现了间隙。inndb就会给这个间隙加锁，这就是间隙锁。就是给两个值之间的空隙加锁，而间隙锁就是在mysql 可重复读的隔离级别解决幻读问题的。

在可重复读这种隔离级别下会启用间隙锁，而在读未提交和读已提交两种隔离级别下，即使使用select ... in share mode或select ... for update，也不会有间隙锁，无法防止幻读。

间隙锁实践

会话A 时间点1

start transaction;
select * from  account;

在这里插入图片描述

会话A 时间点2

update account set balance = balance + 100 where id > 5 and id < 16

会话B 时间点1
B在插入id=4 和id=20 、id=15 的时候发生了阻塞。因此得到间隙 (3,15],(15,20], (20,正无穷)三个区间加锁

start transaction;
insert into account(id,name,balance) values ('4','马云',30000);

在这里插入图片描述

临键锁

临键锁是间隙锁和行锁的组合，于是临键锁的区域是一个左开右闭的区间。

隔离级别是可重复读时，select ... in share mode或select ... for update会使用临键锁，防止幻读。普通select语句是快照读，不能防止幻读。

1.2 死锁

死锁是并发系统中常见的问题，同样也会出现在数据库系统的并发读写、写写请求场景中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现"死锁"。

既然，死锁是发生在并发场景下的问题，发生死锁一定是同时满足4个条件

互斥、不可剥夺、循环等待、请求和保持

互斥

计算机的某一个资源只能被一个进程占用，如果其他进程请求该资源就只能等待

不可剥夺

某个进程获取到的资源在没有使用完毕之前，其他进程不能强夺，只能由获取到资源的进程主动释放

请求与保持

进程已经获取到至少一个资源，但是他还要请求其他资源，但是其他资源被另外的进程占有，此时就会阻塞，但是他不会放弃自己获得的资源

循环等待

A、B进程，A进程请求的资源被B占用，B进程请求的资源被A占用，形成了循环等待

死锁如何处理呢？

预防、避免、检测、解除

预防死锁
破坏四个造成死锁条件的一个或者多个就可以防止死锁
避免死锁
再资源分配的过程中，使用某种策略或者方法防止系统进入死锁条件
检测死锁
采取适当的措施清除死锁，比如设置超时时间，主动释放一方的资源
解除死锁
采用有序资源分配法和Dijkstra（1965）提出的银行家算法来避免死锁

1.3 mysql死锁问题

mysql默认存储引擎是innDb,i该引擎采用等待图的方法来自动检测死锁，如果发现死锁现象，就是自动回滚一个事务。

MySQL5.5版本以后默认用InnoDB存储引擎，并且采用可重复读的隔离级别，在进行update操作会进行加锁的

我们演示一个死锁的案例
A会话时间点1

set session transaction isolation level repeatable read;
start transaction ;

update account set balance = 300  where id = 1 ;

在这里插入图片描述

B会话时间点1

set session transaction isolation level repeatable read;
start transaction ;

update account set balance = 360  where id = 2 ;

在这里插入图片描述

A会话时间点2
可以看到线程会一直卡着

update account set balance = 350  where id = 2 ;

在这里插入图片描述

B会话时间点2

update account set balance = 400  where id = 1 ;

在这里插入图片描述

1.4 如何查看死锁日志？

##查看锁的情况
select * from information_schema.innodb_locks;

##查看死锁日志
show engine innodb status ;

在这里插入图片描述
复制出来status列的内容,这里我贴出有用的部分查看LATEST DETECTED DEADLOCK选项下面的信息

## 最后一次死锁的信息
------------------------
LATEST DETECTED DEADLOCK
------------------------

## 死锁发生的时间
2022-09-18 11:08:36 0x38b4

## 事务1 A会话的信息
*** (1) TRANSACTION:
## 事务id为13696014 活跃时间为12秒  事务当前正在根据索引读取数据
TRANSACTION 13696014, ACTIVE 12 sec starting index read
## starting index read这个描述还有其他情况：
## fetching rows 表示事务状态在row_search_for_mysql中被设置，表示正在查找记录。
## updating or deleting 表示事务已经真正进入了Update/delete的函数逻辑（row_update_for_mysql）
## thread declared inside InnoDB 说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的

## 表示当前事务有一个表上有锁，并且是一把锁
mysql tables in use 1, locked 1

## heap size 1136事务执行中分配给锁的内存大小 2 row lock 当前事务持有锁的个数
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
## thread id 2 表示MySQL的进程ID   query id 8896 表示SQL的id 
MySQL thread id 2, OS thread handle 20112, query id 8896 localhost ::1 recycle updating

## 表示事务中正在执行(等待)的SQL
update account set balance = 350  where id = 2

## 正在等待锁释放
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

## innoDB一共有四种类型的行锁：记录锁，间隙锁，Next-key 锁和插入意向锁，在死锁日志里面分别对应一下几种形式：

## 记录锁（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
## 间隙锁（LOCK_GAP）: lock_mode X locks gap before rec
## Next-key 锁（LOCK_ORNIDARY）: lock_mode X
## 插入意向锁（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention


## RECORD LOCKS 记录锁  index PRIMARY 锁的是主键索引  table `test`.`account`锁的是这个表 trx id事务id13696014   lock_mode X locks 锁模式排它锁   but not gap 并不是间隙锁
## 这句大概描述就是 当前事务的执行语句  在申请X锁  记录space id 为814,page序号为3(一般跟事务2上的hold lock信息相互对应)
RECORD LOCKS space id 814 page no 3 n bits 80 index PRIMARY of table `test`.`account` trx id 13696014 lock_mode X locks rec but not gap waiting

## 表示记录锁的物理记录信息 n_fields 记录是5列  
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

## hex 0002对应表中字段的值，存储为16进制，可以解析为十进制来对应表中锁的数据
## 0至xx指的是表的第1至第xx个字段，靠前的是索引信息  记录锁的字段信息, 0开头的这行表示id列  可见锁的是id=2的那一行，可知这里的事务1就是上面的会话A。
 0: len 4; hex 80000002; asc     ;;  
 1: len 6; hex 000000d0fc0f; asc       ;;
 2: len 7; hex 2f000001bf21c1; asc /    ! ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;
 4: len 5; hex 8000016800; asc    h ;;


## 事务2 B会话的信息
*** (2) TRANSACTION:
## 事务id为13696015 活跃时间为8秒  事务当前正在根据索引读取数据,thread declared inside InnoDB说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的
TRANSACTION 13696015, ACTIVE 8 sec starting index read, thread declared inside InnoDB 5000
## 表示当前事务有一个表上有锁，并且是一把锁
mysql tables in use 1, locked 1
## heap size 1136事务执行中分配给锁的内存大小 2 row lock 当前事务持有锁的个数
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 14516, query id 9255 localhost ::1 recycle updating

## 表示事务中正在执行(等待)的SQL
update account set balance = 400  where id = 1

*** (2) HOLDS THE LOCK(S):
## HOLDS THE LOCK用来显示这个事务持有哪些锁？trx id 13696015持有记录锁 那个记录的锁呢？id = 2的记录锁
RECORD LOCKS space id 814 page no 3 n bits 80 index PRIMARY of table `test`.`account` trx id 13696015 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000d0fc0f; asc       ;;
 2: len 7; hex 2f000001bf21c1; asc /    ! ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;
 4: len 5; hex 8000016800; asc    h ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

## 事务trx id 13696015等待记录锁释放呢，等待那个呢？id= 1的那行记录锁
RECORD LOCKS space id 814 page no 3 n bits 80 index PRIMARY of table `test`.`account` trx id 13696015 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000d0fc0e; asc       ;;
 2: len 7; hex 2e000001b3079c; asc .      ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 5; hex 8000012c00; asc    , ;;

## 表示MySQL最终决定回滚事务2
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
## 记录的当前SESSION和事务列表
Trx id counter 13696017
Purge done for trx's n:o < 13696017 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283918552543592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283918552542720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283918552541848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

## 表示id为13696014的事务（也就是事务1）还没提交
---TRANSACTION 13696014, ACTIVE 49 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 20112, query id 13222 localhost ::1 recycle starting


## 整段日志描述下来13696014这个事务1在申请id=2的那行记录锁释放，13696015这个事务2持有id=2 的那行记录锁，他在等待13696014事务1 id=1的那行记录锁释放
## 而事务1没有提交，也就是事务1 id=1这行记录锁没有释放  就形成了死锁，mysql最终选择回滚事务2

1.5 如何避免死锁

尽量让数据表中的数据检索通过索引来完成，避免无效索引导致行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。（降低加锁的级别，非唯一索引条件的修改操作会产生间隙锁，而这是导致大多死锁的主要原因）
尽量控制事务大小，减少一次事务锁定的资源数量和锁定的时间(大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。 )
在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本。
如果一条sql语句涉及事务加锁操作，尽量将其放在整个事务的最后执行
以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序为1，2；事务B更新数据的顺序为2，1。这样更可能会造成死锁。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。