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

涉及的语句为
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;;
时间： 2024-09-20 05:56:57

MYSQL RC模式insert update 可能死锁的情况的相关文章

mysql字符集，insert,update,delete,select

发现有错误:数据太长了.//查看数据库的所有编码:show variables like 'character%';-----+| character_set_client     | utf8    设置客户端的字符集     || character_set_connection | utf8    设置连接的字符集     || character_set_database   | utf8 设置数据库的字符集     || character_set_filesystem | binar

关于叶老师一个RR模式下UPDATE锁范围扩大案例的研究

原创转载请注明出处有误请指出 一.前言 叶金荣老师分享了一篇文章如下:https://mp.weixin.qq.com/s/09DJCyMq8kBn4mlezgzUgg 这里只研究下锁的模式,借用叶老师的表和语句 mysql> select * from t1; +----+----+----+----+ | c1 | c2 | c3 | c4 | +----+----+----+----+ | 0 | 0 | 0 | 0 | | 1 | 1 | 1 | 0 | | 3 | 3 | 3 | 0

MySQL 4.1.0 中文参考手册 --- 6.4 数据操纵：SELECT, INSERT, UPDATE, DELETE

mysql|select|参考|参考手册|数据|中文 MySQL 4.1.0 中文参考手册 --- 犬犬(心帆)翻译 MySQL Reference Manual for version 4.1.0-alpha. 6.4 数据操纵:SELECT, INSERT, UPDATE, DELETE6.4.1 SELECT 句法 SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACH

MySQL里面的Replace 的用法和insert .. update 的用法

MySQL里面的Replace 的用法和insert .. update 的用法   drop table if exists t;   create table t(id int not null auto_increment primary key, name varchar(100));   create unique index idx_t on t(name);   replace into t (name) values('first'); -- 数据的id=1   select *

insert into 并发死锁 问题

问题描述 insert into 并发死锁 问题 同时向同一个数据库的同一张表插入数据,用insert into 或者 load data infile 都会出现 锁索引的情况,造成死锁,哪些大神能够帮忙解决下.大大有赏.在线等,急! 下面是死锁的具体信息: LATEST DETECTED DEADLOCK 160503 14:58:26 *** (1) TRANSACTION: TRANSACTION FC644, ACTIVE 1 sec inserting mysql tables in

用Trigger规范insert, update的值

假设有表 A, CREATE TABLE A( ID INT NOT NULL IDENTITY(1,1), ProductID INT NOT NULL, RetailerID INT NOT NULL, Date DATETIME NOT NULL CONSTRAINT A_PK PRIMARY KEY (ProductID, RetailerID, Date) ); 我的意思是想让 ProductID, RetailerID 和 Date(不包括小时,分钟等)成为主键. 即:某一天里, r

深入mysql "ON DUPLICATE KEY UPDATE" 语法的分析

mysql "ON DUPLICATE KEY UPDATE" 语法如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE:如果不会导致唯一值列重复的问题,则插入新行. 例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果: 复制代码 代码如下: INSERT INTO TABLE (a,c) VALUES

深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析

mysql "ON DUPLICATE KEY UPDATE" 语法 如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE:如果不会导致唯一值列重复的问题,则插入新行. 例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果: INSERT INTO TABLE (a,c) VALUES (1,3) ON

如何提高mysql大批量数据更新（update）的效率？

问题描述 如何提高mysql大批量数据更新(update)的效率? 本人所写的sql语句如下: UPDATE resulttmpSET result.forward_count=ROUND(tmp.fca_s) result.comment_count=ROUND(tmp.cca_s) result.like_count=ROUND(tmp.lca_s)WHERE result.uid=tmp.uid; 两个表的数据都达到了一百万条,该语句执行起来非常慢,故请问各位,如何提高update效率.