首页 / MYSQL / Mysql中插入的死锁
Mysql中插入的死锁
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql中插入的死锁，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3430字，纯文字阅读大概需要5分钟。
内容图文
我正在使用mysql 5.0.92.最近,我们对一个表有很多插入死锁,相对快速地插入(并更新或删除)行.我在StackOverflow,mysql文档和论坛中研究了这些问题,但没有理解这个问题.令我困惑的一件事是,其中一个表没有根据innodb状态锁定任何资源.
以下是SHOW INNODB STATUS的输出：
*** (1) TRANSACTION:
TRANSACTION 0 2326105503, ACTIVE 0 sec, process no 18871, OS thread id 1078532416 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 225129, query id 126720476 192.168.999.999 the-user update
insert into the_table (creation_date, expiration_date, iid, ma_c, ma_cid, ma_ed, ma_lat, ma_long, ma_ln, ma_sd, ma_sid, uid, id) values ('2011-08-30 16:54:18.0', '2011-09-01 23:59:59.0', 1001, '', 'US', '2011-09-01 23:59:59.0', 33.72, -92.61, 'CITY_CENTER', '2011-08-31 23:59:59.0', '', 1010, 'xxx')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15002 page no 5749 n bits 376 index `idx_iu` of table `the_db/the_table` trx id 0 2326105503 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 0 2326105502, ACTIVE 0 sec, process no 18871, OS thread id 1243085120 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 2
MySQL thread id 223875, query id 126720499 192.168.999.999 the-user update
insert into the_table (creation_date, expiration_date, iid, ma_c, ma_cid,ma_ed, ma_lat, ma_long, ma_ln,ma_sd, ma_sid, uid, id) values ('2011-08-30 16:54:18.0','2011-12-14 23:59:59.0', 2002, 'Amsterdam', 'NL', '2011-10-04 16:45:00.0', 52.37, 4.89, 'CITY_CENTER', '2011-09-06 23:59:59.0', '', 2020, 'yyy')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15002 page no 5749 n bits 376 index `idx_iu` of table `the_db/the_table` trx id 0 2326105502 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 283 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 8; hex 80000000008b22f0; asc " ;; 1: len 4; hex 8004ab2a; asc *;; 2: len 30; hex 313331343732333235383338393330353936323430363039352020202020; asc 1314723258389305962406095 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15002 page no 5749 n bits 376 index `idx_iu` of table `the_db/the_table` trx id 0 2326105502 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
该表定义如下：
CREATE TABLE `the_table` (
`id` char(30) NOT NULL default '',
`iid` bigint(20) NOT NULL,
`uid` int(11) NOT NULL,
`creation_date` datetime default NULL,
`expiration_date` datetime default NULL,
`ma_sd` datetime default NULL,
`ma_ed` datetime default NULL,
`ma_c` varchar(255) default NULL,
`ma_sid` varchar(3) default NULL,
`ma_cid` varchar(3) default NULL,
`ma_long` double default NULL,
`ma_lat` double default NULL,
`ma_ln` varchar(50) default NULL,
PRIMARY KEY (`id`),
KEY `idx_iu` (`iid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
将非常感谢帮助.
解决方法:
通常,当您使用相同的表具有不同的事务时会发生死锁,但是当表以不同的顺序更改时.
例如.如果事务1更新表1然后更新表2,并且如果有另一个事务首先更新表2然后再更新表1,那么如果这些事务频繁运行,则可能会出现死锁.
内容总结
以上是互联网集市为您收集整理的Mysql中插入的死锁全部内容，希望文章能够帮你解决Mysql中插入的死锁所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。