需要帮助解决死锁并了解导致死锁的原因吗
表格:
CREATE TABLE `payments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`billing_id` int(10) unsigned NOT NULL,
`foreign_transaction_id` varchar(255) NOT NULL,
`product_type` enum('invoice') DEFAULT NULL,
`product_id` int(10) unsigned DEFAULT NULL,
`subscription_id` int(10) unsigned DEFAULT NULL,
`credit_id` int(10) unsigned DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`type` enum('payment') DEFAULT NULL,
`amount` decimal(8,2) NOT NULL DEFAULT '0.00',
`fee` decimal(8,2) NOT NULL DEFAULT '0.00',
`vat` decimal(8,2) DEFAULT '0.00',
`status` enum('active','deleted') NOT NULL,
`test` enum('no','yes') NOT NULL DEFAULT 'no',
PRIMARY KEY (`id`),
UNIQUE KEY `billing_id` (`billing_id`,`foreign_transaction_id`),
KEY `subscription_id_type` (`subscription_id`,`type`),
KEY `credit_id_type` (`credit_id`,`type`),
KEY `product_type` (`product_type`,`product_id`),
KEY `time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=3679531 DEFAULT CHARSET=utf8;
死锁发生后,“innodb status”报告中的文本:
LATEST DETECTED DEADLOCK
------------------------
2018-12-17 12:10:38 0x2f8
***(1) TRANSACTION:
TRANSACTION 153838477815, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 446845346, OS thread handle 974, query id 88919281792 123.123.123.123 root update
INSERT INTO mydb.`payments`
SET `billing_id` = '11', `foreign_transaction_id` = '1416436', `product_type` = 'invoice', `product_id` = '232886', `type` = 'payment', `amount` = '9.99', `fee` = '0.454545', `vat` = '0', `status` = 'active', `test` = 'no'
ON DUPLICATE KEY UPDATE `billing_id` = '11', `foreign_transaction_id` = '1416436', `product_type` = 'invoice', `product_id` = '232886', `type` = 'payment', `amount` = '9.99', `fee` = '0.454545', `vat` = '0', `status` = 'active', `test` = 'no', `id` = LAST_INSERT_ID(`id`)
***(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1898595 page no 25610 n bits 488 index billing_id of table `mydb`.`payments` trx id 153838477815 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 61 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 0000000b; asc ;;
1: len 6; hex 313431363434; asc 141644;;
2: len 4; hex 00239264; asc # d;;
***(2) TRANSACTION:
TRANSACTION 153838477576, ACTIVE 1 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 446846795, OS thread handle 760, query id 88919279740 123.123.123.123 root update
INSERT INTO mydb.`payments`
SET `billing_id` = '11', `foreign_transaction_id` = '1416430', `product_type` = 'invoice', `product_id` = '1317214', `type` = 'payment', `amount` = '9.99', `fee` = '0.454545', `vat` = '0', `status` = 'active', `test` = 'no'
ON DUPLICATE KEY UPDATE `billing_id` = '11', `foreign_transaction_id` = '1416430', `product_type` = 'invoice', `product_id` = '1317214', `type` = 'payment', `amount` = '9.99', `fee` = '0.454545', `vat` = '0', `status` = 'active', `test` = 'no', `id` = LAST_INSERT_ID(`id`)
***(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1898595 page no 25610 n bits 488 index billing_id of table `mydb`.`payments` trx id 153838477576 lock_mode X locks gap before rec
Record lock, heap no 61 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 0000000b; asc ;;
1: len 6; hex 313431363434; asc 141644;;
2: len 4; hex 00239264; asc # d;;
***(2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1898595 page no 25610 n bits 488 index billing_id of table `mydb`.`payments` trx id 153838477576 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 61 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 0000000b; asc ;;
1: len 6; hex 313431363434; asc 141644;;
2: len 4; hex 00239264; asc # d;;
***WE ROLL BACK TRANSACTION (1)
我猜原因在于复合唯一索引“billing\u id”。我想知道在这种情况下mysql是如何一步一步地创建锁间隙的。又该怎么解决呢
1条答案
按热度按时间ncecgwcz1#
并不是所有的死锁都可以避免。
在某些情况下,innodb会对其锁定的内容感到悲观——这可能比更努力地避免一些复杂的冲突要有效得多。
通过在每个sql之后测试错误并准备好
ROLLBACK
然后重新启动事务。查看事务中的所有sql可能会有所帮助。可能是你错过了一次机会
FOR UPDATE
这会把死锁变成“锁定等待超时”,它会无声地“正常工作”。补充
-->
也就是说,不要重复唯一/主键;使用
VALUES
而不是重复这些值(我不知道这是否有帮助,但它似乎是“正确的”。)