2 kucoll kucoll 于 2014.06.26 14:56 提问

mysql 多个事务更新同一条记录产生死锁

以下是用show innodb status 查看的死锁信息,都是通过主键索引userId去更新记录,没有其他索引的影响,不应该会产生死锁啊,请大神帮忙分析下原因。

表索引如下
(....
PRIMARY KEY (userId),
UNIQUE KEY userId_UNIQUE (userId),
UNIQUE KEY userName_UNIQUE USING BTREE (userName)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

LATEST DETECTED DEADLOCK

140626 11:12:09
*** (1) TRANSACTION:
TRANSACTION 0 168550, ACTIVE 13 sec, process no 21006, OS thread id 139721036994304 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 2
MySQL thread id 168698, query id 1710850 localhost 127.0.0.1 root Updating
update users set imei='A00000455A4CFE',last_address='上海市闸北区秣陵路303号',lat=31.254289,lon=121.46208,last_login='2014-06-26 11:11:56',userStatus=11,deviceId='2B54F8EDD4A2DC4150BC5D8A4E0FB340',platform='android',updated='2014-06-26 11:11:56' where userId=15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168550 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;

*** (2) TRANSACTION:
TRANSACTION 0 168551, ACTIVE 1 sec, process no 21006, OS thread id 139720929810176 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 2
MySQL thread id 168699, query id 1710851 localhost 127.0.0.1 root Updating
update users set imei='A00000455A4CFE',last_address='上海市闸北区秣陵路303号',lat=31.254289,lon=121.46208,last_login='2014-06-26 11:12:08',userStatus=11,deviceId='2B54F8EDD4A2DC4150BC5D8A4E0FB340',platform='android',updated='2014-06-26 11:12:08' where userId=15
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168551 lock mode S locks rec but not gap
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168551 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;

*** WE ROLL BACK TRANSACTION (2)

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
两个事物 update同一张表出现的死锁问题 (转载)
粘贴会有各种格式显示问题,原文链接:点击查看原文 引言 近来做省一级计算机一级考试系统的时候,学生端进行大批量判分的时候,出现了这样的问题(事务(进程 ID 262)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。): 这个就是我们在代码中写了大批量的update语句,用trace P...
两个事物 update同一张表出现的死锁问题
引言 近来做省一级计算机一级考试系统的时候,学生端进行大批量判分的时候,出现了这样的问题(事务(进程 ID 262)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。): 这个就是我们在代码中写了大批量的update语句,用trace Profiler ,我们对死锁追踪是这样的: 分析: 我们来分析一下上
同一个事务内修改两次,第二次改回原状会不会产生binlog,改前改后相同会不会产生binlog
实验一 1 修改前表内容 mysql> select * from user; +----------------------------+------+------------+-------+-------+ | gmt_create | name | gmt_modify | gmt_4 | gmt_3 | +-----------------------
多个事务并发执行update出现的数据库死锁问题排除
现象:死锁,时间:2017-08-3100:00:00.841,故障解决:按最小代价自行回滚事由:退优惠券,并发执行update语句.死锁日志(详细): Expand source** (1) TRANSACTION:TRANSACTION 6648945293, ACTIVE 0 sec starting index readmysql tables in use 3, locked 3LOCK...
死锁:多线程同时删除唯一索引上的同一行
1    死锁问题背景    1 1.1    一个不可思议的死锁    1 1.1.1    初步分析    3 1.2    如何阅读死锁日志    3 2    死锁原因深入剖析    4 2.1    Delete操作的加锁逻辑    4 2.2    死锁预防策略    5 2.3    剖析死锁的成因    6 3    总结    
用事务为防止并发时多次更新同一记录
--准备测试表及测试数据 USE tempdb GO IF OBJECT_ID('task') IS NOT NULL DROP TABLE task GO CREATE TABLE task ( taskId INT IDENTITY(1,1) PRIMARY KEY ,taskName NVARCHAR(50) NOT NULL ,d DATETIME NOT NULL DEFAULT(...
高并发update的 死锁产生原因
在说这个之前首先了解一下讲一下update语句sql中的情况。死锁产生的条件:出现循环等待资源。update对锁的流程:       当sql发出一个update请求之后,数据库会对表中的每条记录加上U锁。然后数据库会根据where条件,将符合条件的记录转换为X锁。对不满足条件的记录释放U锁。环境模拟1. 创建数据库环境--创建数据库 create database DeadLockTest;
如果同一条记录同时多个进程可能对不同的字段进行修改,怎么保证列锁?
如果同一条记录同时多个进程可能对不同的字段进行修改,怎么保证列锁? 数据库目前的锁机制有没有办法解决这种并发问题 关注者 13 被浏览 652 添加评论 分享 邀请回答 关注问题写回答
mysql同时更新多条记录的同一个字段
mysql同时更新多条记录的同一个字段,保证数据一致性
数据库(Hibernate)事务与并发问题处理(乐观锁与悲观锁)
目录 一、数据库事务的定义 二、数据库事务并发可能带来的问题 三、数据库事务隔离级别 四、使用Hibernate设置数据库隔离级别 五、使用悲观锁解决事务并发问题 六、使用乐观锁解决事务并发问题     Hibernate事务与并发问题处理(乐观锁与悲观锁) 一、数据库事务的定义   数据库事务(Database Transaction)