mysql deadlock6_mysql deadlock、Lock wait timeout解决和分析

最新推荐文章于 2023-11-29 22:12:01 发布
最新推荐文章于 2023-11-29 22:12:01 发布
阅读量147 收藏
点赞数
文章标签： mysql deadlock6
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_34600909/article/details/113121505
版权

项目上线 线上遇到大量的deadlock 和wait timeout 但是看程序没什么问题 问dba也不能给出很好的解决方案！最终自己去了解mysql锁 以及看mysq锁日志 如果了解mysql锁的机制下分析就很好解决

mysql的几种锁

X锁(排他锁) :

与其他X锁和S锁互斥

S锁(共享锁):

与X锁互斥 当一个事物获得S锁 别的事物可以继续获得S锁 但是不能加X锁 X锁与X锁和S锁互斥

IX(意向排他锁)

IX是表级的　mysql引擎自动控制 在获得X锁之前 会先获得IX锁  IX只会与表级的S,X锁互斥.  当mysql对表级进行加锁(X或者S)的时候不用一行一行对数据判断是否加了X锁  直接根据是否有IX锁来进行判断，提高了效率

IS(意向共享锁)

IS是锁是表级的  mysql引擎自动控制 在获得S锁之前会先获得IS锁  IS锁只会与表级X锁互斥  当mysql锁对表级进行加X锁是 不用一行一行对数据判断是否加了S锁  直接判断是否存在表级的IS意向锁

gap(锁)

间隙锁 用于在指定索引位置区间加锁 (只会在插入是互斥)

id

age

1

10

2

20

3

30

4

40

这个时候gap锁就有[无穷小,10]，[10,20]，[20,30],[30,40][40,无穷大]

如果在RR模式下delete table where age=20 将不能插入10~20之间  20~30到之间的值

gap锁 只会在insert的时候互斥 (可以理解为gap在非Insert获取的都是共享锁  在Insert时获取的是排他锁)

next-key

行锁和gap锁的组合

当前读与快照读

快照读

简单的查询  select * from student

值得注意的是 在RC模式下 每次读取都是新的快照  在RR模式下 当一次快照读后 后面都会读快照

session1

session2

select * from student;

inser into student(name) values('小明')

update student set name='小明' where id=1;

delete student set name='小明2' where id=2;

commit;

select * from student;

commit;

RC模式 session1第二次查询 将会受到session2的操作的影响 因为RC模式每次读取是读取新的快照

RR模式 session1第二次查询不会受到session2的影响 因为后面每次读取都是读取快照(session1 update insert delete也会更新快照)

ps:以上结果经过在mysql innodb模式 实践

当前读

select * student lock in share mode(共享锁)

select * student for update(排他锁)

insert

delete

update

快照读是不加锁的 当前读 都会获取相应的锁

聚簇索引以及二级索引

mysql加锁不是锁住某一行数据而是在表的索引上面加锁  理解聚簇索引和二级索引能够很好的帮助我们理解锁

什么是聚簇索引?

在innodb下  数据的存储顺序跟索引的存储存储顺序是一样  聚簇索引的页节点就指向数据，每个表都会有一个聚簇索引 默认在主键上  如果没有找到将会在表中的唯一非空的列上加上聚簇索引 如果没有mysql将自动维护一个隐式的列作为聚簇索引

0c399f14b13ead2a301d1567de3cfdad.png

二级索引(非聚簇索引)

二级索引的 页节点 存储的是聚簇索引  当查询一条非聚簇索引的列 会先根据索引找到聚簇索引 再根据聚簇索引查找数据

accc404fc1f7f9263620a3810a8b3e6a.png

mysql RC和RR隔离级别的加锁方式

现在有student表有以下数据

id

name

1

a

2

b

3

d

4

f

查看当前默认事物隔离级别

SELECT @@tx_isolation;

278d806fe81cccbaf3e839d3825bed5c.png

对点前session设置隔离级别

可选:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

set session transaction isolation level read uncommitted;

RC模式

delete student where  id=1;

如果id列是主键

将会在id为1的的聚簇索引上加上X锁

如果id列为唯一索引

将会在唯一索引上加上X锁 同时根据唯一索引找到聚簇索引 并加锁    为什么在唯一索引上面加了X锁还要在聚簇索引上加锁  因为如果这个时候另外一个事物根据聚簇索引更新数据 将感知不到锁

比如上面表 name为聚簇索引  delete student where  id=1;   id=1的索引将加上锁  这个但是聚簇索引name没有加上 update  student id=2 where name=a 这个时候 name=a 获取锁成功能将能修改成功

上面的解释但是会有疑惑 既然加锁都加载聚簇索引上为什么还要多此一举的在非聚簇索引列上加锁(个人理解 判断锁互斥时 直接根据条件的索引 而不用再次根据索引找到聚簇索引)

如果id列非唯一索引

跟唯一索引加锁机制一样

如果id列无索引

将在走聚簇索引 全表扫描不管是否满足条件的数据都会加上X锁  但是RC模式有优化 不满足条件的加锁之后又会释放

RR模式

如果id列是主键

将会在满足条件的聚簇索引上加上X锁

如果id列为唯一索引

在id列上加上X锁同时在 对应的聚簇索引加上X锁

如果id列非唯一索引

将在id列上加上next-key锁 同时在聚簇索引加上x锁

如果id列无索引

将在走聚簇索引 全表扫描不管是否满足条件的数据都会加上next-key锁  跟RC模式不同的是将不会释放

deadlock分析

环境:RR隔离级别

线上报大量的deadlock 通过命令登录mysql 通过此SHOW ENGINE INNODB STATUS\G将打印最近一次死锁

定位到代码 有这样一个操作

1.delete from dealer_order_item where dealer_order_code='1111'

2.insert dealer_order_item(dealer_order_code,.....) values('1111',....)

dealer_order_code 没有索引

通过上面的锁分析 RR模式下如果当前读没有满足条件的数据 整个表每一条数据都将会加上next-key锁如下

session1

session2

delete from dealer_order_item where dealer_order_code='1111'

delete from dealer_order_item where dealer_order_code='2222'

insert dealer_order_item(dealer_order_code,.....) values('1111',....)

结果:session1执行insert会死锁 session2执行delete会等待

1.session1 delete全表扫描获得所有行的gap锁和x锁

2.session2执行 delete全表扫描获得gap锁 然后锁等待session释放x锁

3.session2执行 insert 尝试获得gap锁 因为session2已经拿到gap锁但是未拿到x锁, 所以不能插入等待 因为session2也在等待session1释放x锁(所以死锁)

情况2(已经重现)

session1:

delete from prm_booking_wine_coin_record_item where type=0;

session2:

delete from prm_booking_wine_coin_record_item where type=3;

非insert模式下 申请的锁都是共享锁

session1

insert prm_booking_wine_coin_record_item valuse(21,2) 等待session 2释放 3的gap锁

session2

insert prm_booking_wine_coin_record_item valuse(22,2) 等待 session 1释放2的gap锁

死锁 session2释放锁  session提交成功

8792852fc56d5a3e4dd74b5d81f79973.png

waitlock分析

表数据id为主键索引 name为非唯一索引

93854a8452c00e8207c293afaadfa05b.png

select * from information_schema.innodb_trx;表

trx_id(事物id)

trx_state(事物状态)

trx_started(事物开始时间)

trx_requested_lock_id

trx_wait_started(事物开始等待时间)

trx_weight

trx_mysql_thread_id(事物线程id)

trx_query(具体sql)

trx_operation_state(事物当前操作状态)

trx_tables_in_use(事物中多少个表被使用)

trx_tables_locked(事物锁了多少个表)

trx_lock_structs

trx_lock_memory_bytes(事物锁住的内存大小)

trx_rows_locked(事物拥有多少个锁)

trx_rows_modified(事物修改的行数)

trx_concurrency_tickets(事物并发票数)

trx_isolation_level(事物隔离级别)

trx_unique_checks(是否唯一检查)

trx_foreign_key_checks(是否外键检查)

trx_last_foreign_key_error(最后的外键错误)

trx_adaptive_hash_latched

trx_adaptive_hash_timeout

trx_is_read_only

trx_autocommit_non_locking

7842656

LOCKWAIT

2019-01-14 10:22:04

7842656:25:4:4

2019-01-14 10:22:04

8

3733599

update demo set name='5555' where name='3333'

updating or deleting

1

1

5

1136

10

3

0

REPEATABLE READ

1

1

NULL

0

0

0

0

7842647

RUNNING

2019-01-14 10:21:37

NULL

NULL

7

3733596

NULL

NULL

0

1

4

1136

7

3

0

REPEATABLE READ

1

1

NULL

0

0

0

0

select * from information_schema.innodb_lock_waits;表

requesting_trx_id(请求锁的事物id)

requested_lock_id(请求锁的锁id)

blocking_trx_id(当前拥有锁的事物id)

blocking_lock_id(当前拥有锁锁id)

7842656

7842656:25:4:4

7842647

7842647:25:4:4

select * from information_schema.innodb_locks;表

lock_id(锁id)

lock_trx_id

lock_mode(锁模式)

lock_type(锁类型)

lock_table(被做锁的表)

lock_index(被锁的索引)

lock_space(被锁的表空间号)

lock_page(被锁的页号)

lock_rec(被锁的记录号)

lock_data(被锁的数据)

7842656:25:4:4

7842656(拥有锁的事物id)

X,GAP

RECORD

`dms`.`demo`

index_name

25

4

4

'6666', 2

7842647:25:4:4

7842647

X

RECORD

`dms`.`demo`

index_name

25

4

4

'6666', 2

1. 先看 表1  事物id 7842656等待7842647释放锁 trx_rows_locked字段看命名像是锁了多少数据 不过我根据数据分析应该是获得了多少个锁 name索引加上聚簇索引id的锁+3个gap锁等于10

ctrl+f 搜索:7842647 看关系更佳

f92ffb4efc1e56d0890872dc4fd9f03c.png

2.分析表2 事物id7842656等待事物id7842647释放7842647:25:4:4这个锁

3.分析表3就清晰很多了 事物id7842656是请求这个锁7842656:25:4:4

4.结论 session 2 尝试修改name为5555会获得索引为5555的x锁和gap锁 但是被seesion1获得没释放 所以造成锁等待

mysql 锁等待分析相关表

information_schema.innodb_trx表

包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务

5efe5432f5fd7886c202084661d9df89.png

information_schema.innodb_lock_waits表

包含了blocked的事务的锁等待的状态

c93ee183ed87d44e53776dc2959df189.png

information_schema.innodb_locks表

主要包含了InnoDB事务锁的具体情况，包括事务正在申请加的锁和事务加上的锁。

b874d59b4743ab0a3314192885ee6e01.png

如何避免deadlock和wait lock

delete update 避免使用非索引字段为条件

RR隔离级别将会走聚簇索引 全表扫描为每一行加上next-key锁  注:RC隔离级别会逐行加X锁 并释放

可以这样理解:delete update  扫描一条就会为一条加上锁   当没有索引会全表扫描 RR隔离级别扫描一条就会为这条加上锁 并不会释放   RC隔离级别扫描一条就会为这条加上锁 如果不满足条件的加上锁之后 会自动释放

c291df9d840b2b1d022cf5f32ef61d32.png

name非索引条件  用于修改条件 虽然有满足条件数据 也会导致全表扫描并逐行加X锁

我们为name加上索引条件再进行测试

alter table demo add index index_name(name);

可以发现修改成功并不会等待

460f0ff2a00026c00166e560cd17785c.png

避免批量修改删除避免无序

1.session修改id为1的数据 session2修改id为4的数据各自拿到next-key锁

f914af3b84ae90c3f23aaae1d7181f84.png

2.session1 修改id为4的数据 等待session2释放next-key锁  session2修改id为1的数据等待session1释放next-key锁 造成死锁

17233bec39edd0322261255bfb38333a.png

批量修改或者删除 统一排序一下 比如这里根据id 就不会出现交叉修改依赖

避免隐式转换

70fa78851882c5843569dbf974ba9ac8.png

session1 name为varchar  确传入number导致隐式转换走聚簇索引全表扫描 导致整个表都锁了

记录一个小插曲

0900896918614c9f6ca74109c5e0532d.png

可以看到 name有索引也没有隐式转换 也锁了整个表。因为这个是mysql的优化机制当扫描的数据超过全表的20%~30%时 即便有二级索引也会走扫描整个聚簇索引( 个人测试针对当前读是这样 select不受影响)

避免where条件全表扫描

其实总结上面 可以发现 当前读是根据where条件 扫描一条就加一个锁

避免操作不存在的数据

修改或者编辑 最好先判断数据不存在

cf29da3a522da475cec3929724aba9bf.png

我们观察一下锁的情况

eb62dc933d08d38a3f93eb238802793e.png

可以发现操作不存在数据会触发gap对应索引排序的gap锁 锁无穷大 影响插入数据(应该在只会在RR模式上出现  不过在不确定数据是否存在 操作之前先判断是否存在 是个好习惯)

melo car
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 死锁时间_mysql死锁deadlock相关几个系统变量innodb_lock_wait_timeout
weixin_35919843的博客
01-19 682
前言随着对于mysql死锁知识的进一步了解，发现与死锁相关的几个系统变量。下面梳理这几个技术概念，便于更进一步掌握mysql死锁的概念。这样在出现一些复杂的死锁问题时，也有助于更快速解决故障。innodb_deadlock_detect系统变量mysql 5.7.15引入此系统变量，可取值有2个，分别为：off与on。默认值为on,表明开启互锁检测功能。但是有运维经验的同学或许碰到过，在一些高并发...
MySQL DeadLock
Inspired Rocks
12-10 792
mysql for update, unique index, deadlock, concurrent insert
解决mysql出现大量TIME_WAIT
06-27
解决mysql出现大量TIME_WAIT
mysql报错：Deadlock found when trying to get lock; try restarting transaction的解决方法
09-09
主要给大家介绍了关于mysql出现报错：Deadlock found when trying to get lock; try restarting transaction的解决方法，文中通过示例代码介绍的非常详细，对大家具有一定的参考学习价值，需要的朋友们下面来一起看看吧。
mysql deadlock、Lock wait timeout解决和分析
12-17 928
项目上线 线上遇到大量的deadlock 和wait timeout 但是看程序没什么问题 问dba也不能给出很好的解决方案！最终自己去了解mysql锁 以及看mysq锁日志 如果了解mysql锁的机制下分析就很好解决 mysql的几种锁 X锁(排他锁) : 　　与其他X锁和S锁互斥 S锁(共享锁): 　　与X锁互斥 当一个事物获得S锁 别的事物可以继续获得S锁 但是...
Mysql卡死，执行sql报错，lock wait timeout，死锁
qq_28597013的博客
11-29 751
【代码】Mysql卡死，执行sql报错，lock wait timeout，死锁。
mysql超时死锁_MySQL上的死锁与锁定等待超时
weixin_39884373的博客
01-18 164
Can anyone explain me on details the difference of Deadlock and Lockwait errors found on MySQL 5.1. Is it just the same? When does the deadlock error occur and when does the lockwait timeout occur?解决方...
mysql deadlock处理
weixin_33881041的博客
01-08 522
1、SHOW ENGINE INNODB STATUS  得到最后一次死锁发生的状况 =====================================140110 11:43:07 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last...
MySql deadlock
工作点滴
09-02 1892
执行SHOW ENGINE INNODB STATUS 遍历 SHOW ENGINE INNODB STATUS LATEST DETECTED DEADLOCK ------------------------ 2017-09-02 12:16:36 7f425dd25700 *** (1) TRANSACTION: TRANSACTION 464821461
mysql Deadlock
SAN_YUN的专栏
06-17 84
[2013-06-17 22:19:05,826] {ERROR api_views.api_like - 934} Traceback (most recent call last):  File "/data1/dist/app/main/duitang/api/api_views.py", line 932, in api_like    liked = form.save()  Fil...
MySQL DeadLock故障排查全过程记录
09-09
主要给大家介绍了关于MySQL DeadLock故障排查的全过程，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
mysql Lock wait timeout exceeded； try restarting transaction
一个喜欢诗和远方的程序媛
05-12 3247
也就是遇到了mysql死锁，等待资源，事务锁的问题。可能原因：意外处理没有关闭连接，导致连接过多、或是要更新的表的锁在其它线程手里、系统异常导致事务未提交，再次请求相同记录等等。当中trx_mysql_thread_id为事务线程的id，參照show full processlist命令中的线程信息查看。innodb_lock_waits表则由performance_schema.data_lock_waits表代替。综上大体可以清楚的找到等待的事务即没有获取锁的事务，进一步调整业务逻辑代码。
数据库报1205-Lock wait timeout exceeded； try restarting transaction解决方案
热门推荐
境里婆娑
10-23 1万+
MySQL错误码1205代表了一个死锁错误（Deadlock）。死锁是多个事务相互等待彼此释放锁资源的情况，导致它们都无法继续执行。这通常发生在并发访问数据库时，多个事务试图同时修改相同的数据行或资源。
(十)全解MySQL之死锁问题分析、事务隔离与锁机制的底层原理剖析
BASK2311的博客
10-24 918
对于死锁的定义，这里就不展开叙述了，因为在之前《并发编程-死锁、活锁、锁饥饿》中曾详细描述过，如下：死锁是指两个或两个以上的线程（或进程）在运行过程中，因为资源竞争而造成相互等待、相互僵持的现象，一般当程序中出现死锁问题后，若无外力介入，则不会解除“僵持”状态，它们之间会一直相互等待下去，直到天荒地老、海枯石烂~当然，为了照顾一些不想看并发编程文章的小伙伴，这里也把之前的死锁栗子搬过来~
Lock wait timeout exceeded mysql锁表和解决方案
cbb
10-24 2957
mysql 锁表和查询超时解决办法
MySQL DEADLOCK
mengml_smile的博客
07-24 368
几个文章参考 浅谈MySQL的七种锁 https://yq.aliyun.com/articles/646976 MYSQL自增列引起死锁 https://www.cnblogs.com/hhbk/p/8376416.html 两个INSERT发生死锁原因剖析https://blog.csdn.net/n88Lpo/article/details/78099088 Innodb 锁的介绍 http...
deadlock mysql_mysql deadlock
weixin_39804059的博客
01-19 77
4种事务隔离级别：读数据一致性及允许的并发副作用隔离级别读数据一致性脏读不可重复读幻读未提交读(Read uncommitted)最低级别，只能保证不读取物理上损坏的数据是是是已提交度(Read committed)语句级否是是可重复读(Repeatable read)事务级否否是？可序列化(Serializable)最高级别，事务级否否否Mysql的repeatable read是不存在幻读的，...
406_智能小区管家服务系统的设计与实现-源码.zip
最新发布
06-02
提供的源码资源涵盖了安卓应用、小程序、Python应用和Java应用等多个领域，每个领域都包含了丰富的实例和项目。这些源码都是基于各自平台的最新技术和标准编写，确保了在对应环境下能够无缝运行。同时，源码中配备了详细的注释和文档，帮助用户快速理解代码结构和实现逻辑。 适用人群： 这些源码资源特别适合大学生群体。无论你是计算机相关专业的学生，还是对其他领域编程感兴趣的学生，这些资源都能为你提供宝贵的学习和实践机会。通过学习和运行这些源码，你可以掌握各平台开发的基础知识，提升编程能力和项目实战经验。 使用场景及目标： 在学习阶段，你可以利用这些源码资源进行课程实践、课外项目或毕业设计。通过分析和运行源码，你将深入了解各平台开发的技术细节和最佳实践，逐步培养起自己的项目开发和问题解决能力。此外，在求职或创业过程中，具备跨平台开发能力的大学生将更具竞争力。 其他说明： 为了确保源码资源的可运行性和易用性，特别注意了以下几点：首先，每份源码都提供了详细的运行环境和依赖说明，确保用户能够轻松搭建起开发环境；其次，源码中的注释和文档都非常完善，方便用户快速上手和理解代码；最后，我会定期更新这些源码资源，以适应各平台技术的最新发展和市场需求。
Lock wait timeout exceeded; try restarting transaction
03-29
This error message is typically encountered in database systems such as MySQL, and it indicates that a transaction has been blocked by another transaction for too long. When this happens, the database engine will automatically terminate the blocked transaction to prevent it from causing further delays. The most common cause of this error is a deadlock, which occurs when two or more transactions are waiting for each other to release resources (such as locks) that they need to complete their operations. In some cases, the problem can be resolved by simply restarting the transaction or by increasing the length of the timeout period. However, in more serious cases, it may be necessary to analyze the database schema and application code to identify and resolve the underlying issue. To address this error, you can try the following steps: 1. Restart the transaction by rolling back the current one and starting a new one. 2. Increase the timeout period to allow the transaction more time to complete. 3. Analyze the database schema and application code to identify and resolve the underlying issue causing the deadlock. 4. Optimize the queries being used in the transaction to reduce the amount of time required to acquire and release locks on resources.

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值