MYSQL RC模式insert update 可能死锁的情况

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 涉及的语句为 RC模式下 update根据主键更新和insert 其实这样的问题在RC模式下,要么是简单update问题,要么是insert造成的主键和唯一键检查唯一性时出现问题。
涉及的语句为
RC模式下
update根据主键更新和insert
其实这样的问题在RC模式下,要么是简单update问题,要么是insert造成的主键和唯一键检查唯一性时出现问题。
下面以主键问题为列子进行分析一下可能出现的情况。

update  where条件更新为主键,锁结构出现在单行主键上,辅助索引包含隐含锁结构,当前读RC非唯一索引模式没有GAP锁,
insert  插入印象锁,主键和辅助索引上会出现隐含锁结构,

但是在RC模式下没有GAP所以插入印象锁一般不会成为问题


表结构:
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                      |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testlll | CREATE TABLE `testlll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


情况1
insert
update


TX1:                                                                  TX2:
insert into testlll(name) values('gaopeng');
                                                                          insert into testlll(name) values('gaopeng');
update testlll set name='gaopeng1' where id=25;(堵塞)
                                                                           update testlll set name='gaopeng1' where id=24;(堵塞)
                                               
死锁

锁结构:

点击(此处)折叠或打开

  1. ---TRANSACTION 322809, ACTIVE 30 sec starting index read
  2. mysql tables in use 1, locked 1
  3. 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
  4. MySQL thread id 3, OS thread handle 140734663714560, query id 409 localhost root updating
  5. update testlll set name='gaopeng1' where id=24
  6. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  7. TABLE LOCK table `test`.`testlll` trx id 322809 lock mode IX
  8. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  9. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322809 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
  10. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  11.  0: len 4; hex 80000019; asc ;;
  12.  1: len 6; hex 00000004ecf9; asc ;;
  13.  2: len 7; hex f0000001f90110; asc ;;
  14.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  15. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  16. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322809 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  17. Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  18.  0: len 4; hex 80000018; asc ;;
  19.  1: len 6; hex 00000004ecf8; asc ;;
  20.  2: len 7; hex ef000001f80110; asc ;;
  21.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  22. ---TRANSACTION 322808, ACTIVE 43 sec starting index read
  23. mysql tables in use 1, locked 1
  24. LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
  25. MySQL thread id 2, OS thread handle 140734663980800, query id 408 localhost root updating
  26. update testlll set name='gaopeng1' where id=25
  27. ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
  28. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  29. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  30.  0: len 4; hex 80000019; asc ;;
  31.  1: len 6; hex 00000004ecf9; asc ;;
  32.  2: len 7; hex f0000001f90110; asc ;;
  33.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  34. ------------------
  35. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  36. TABLE LOCK table `test`.`testlll` trx id 322808 lock mode IX
  37. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  38. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  39. Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  40.  0: len 4; hex 80000019; asc ;;
  41.  1: len 6; hex 00000004ecf9; asc ;;
  42.  2: len 7; hex f0000001f90110; asc ;;
  43.  3: len 7; hex 67616f70656e67; asc gaopeng;;

  44. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  45. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
  46. Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  47.  0: len 4; hex 80000018; asc ;;
  48.  1: len 6; hex 00000004ecf8; asc ;;
  49.  2: len 7; hex ef000001f80110; asc ;;
  50.  3: len 7; hex 67616f70656e67; asc gaopeng;;
情况2
update
update

TX1:                                                                    TX2:
update testlll set name='gaopeng1' where id=22;
                                                                            update testlll set name='gaopeng1' where id=25;
update testlll set name='gaopeng1' where id=25;(堵塞)
                                                                             update testlll set name='gaopeng1' where id=22;(堵塞)
死锁


这种情况比较简单不打印出锁结构


情况3
insert
insert


TX1:                                                      TX2:
insert into testlll values(26,'gaopeng');
                                                             insert into testlll values(27,'gaopeng');
nsert into testlll values(27,'gaopeng');(堵塞)
                                                             insert into testlll values(26,'gaopeng');(堵塞)


死锁

锁结构:

点击(此处)折叠或打开

  1. ---TRANSACTION 422212176315800, not started
  2. 0 lock struct(s), heap size 1160, 0 row lock(s)
  3. ---TRANSACTION 323284, ACTIVE 10 sec inserting
  4. mysql tables in use 1, locked 1
  5. 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
  6. MySQL thread id 2, OS thread handle 140734663980800, query id 369 localhost root update
  7. insert into testlll values(26,'gaopeng')
  8. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  9. TABLE LOCK table `test`.`testlll` trx id 323284 lock mode IX
  10. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  11. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323284 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
  12. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  13.  0: len 4; hex 8000001b; asc ;;
  14.  1: len 6; hex 00000004eed4; asc ;;
  15.  2: len 7; hex d3000002a10110; asc ;;
  16.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  17. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  18. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323284 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  19. Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  20.  0: len 4; hex 8000001a; asc ;;
  21.  1: len 6; hex 00000004eed3; asc ;;
  22.  2: len 7; hex d2000002330110; asc 3 ;;
  23.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  24. ---TRANSACTION 323283, ACTIVE 14 sec inserting
  25. mysql tables in use 1, locked 1
  26. LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
  27. MySQL thread id 3, OS thread handle 140734663714560, query id 368 localhost root update
  28. insert into testlll values(27,'gaopeng')
  29. ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
  30. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  31. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  32.  0: len 4; hex 8000001b; asc ;;
  33.  1: len 6; hex 00000004eed4; asc ;;
  34.  2: len 7; hex d3000002a10110; asc ;;
  35.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  36. ------------------
  37. ---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  38. TABLE LOCK table `test`.`testlll` trx id 323283 lock mode IX
  39. ---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  40. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
  41. Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  42.  0: len 4; hex 8000001b; asc ;;
  43.  1: len 6; hex 00000004eed4; asc ;;
  44.  2: len 7; hex d3000002a10110; asc ;;
  45.  3: len 7; hex 67616f70656e67; asc gaopeng;;


  46. ---lock strcut(3):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx
  47. RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
  48. Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  49.  0: len 4; hex 8000001a; asc ;;
  50.  1: len 6; hex 00000004eed3; asc ;;
  51.  2: len 7; hex d2000002330110; asc 3 ;;
  52.  3: len 7; hex 67616f70656e67; asc gaopeng;;


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
1月前
|
SQL Oracle 关系型数据库
MySQL的sql_mode模式说明及设置
MySQL的sql_mode模式说明及设置
443 112
|
2月前
|
关系型数据库 MySQL Java
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
|
4月前
|
存储 关系型数据库 MySQL
【赵渝强老师】OceanBase数据库从零开始:MySQL模式
《OceanBase数据库从零开始:MySQL模式》是一门包含11章的课程,涵盖OceanBase分布式数据库的核心内容。从体系架构、安装部署到租户管理、用户安全,再到数据库对象操作、事务与锁机制,以及应用程序开发、备份恢复、数据迁移等方面进行详细讲解。此外,还涉及连接路由管理和监控诊断等高级主题,帮助学员全面掌握OceanBase数据库的使用与管理。
269 5
|
9月前
|
存储 关系型数据库 MySQL
从新手到高手:彻底掌握MySQL表死锁
通过本文的介绍,希望你能深入理解MySQL表死锁的概念、原因、检测方法及解决方案,并在实际开发中灵活应用这些知识,提升系统的稳定性和性能。
874 9
|
10月前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
11月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
700 3
|
11月前
|
监控 关系型数据库 MySQL
一次彻底讲清如何处理mysql 的死锁问题
【10月更文挑战第16天】本文详细介绍了如何处理 MySQL 中的死锁问题,涵盖死锁的概念、原因、检测方法及解决策略,强调通过优化事务设计、调整数据库参数、手动处理和预防措施等手段,有效减少死锁,提升数据库性能与稳定性。
2051 0
|
11月前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
2194 0
|
24天前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
|
9天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
40 3

推荐镜像

更多