我的 MySQL 死锁场景 - 需要建议
我需要这方面的专家建议。
背景 PHP/代码点火器 MYSQL Innodb 表 - fares_table 存储过程
后端 - 编写一个 cronjob php 脚本，每隔几分钟将数据（来自服务）插入/更新到fares_table 中。 （普通 sql 查询）
前端 - 用户将能够读取这些数据 （查询以存储过程形式编写，因为它涉及许多表的联接，因此我的存储过程从fares_table中的select语句创建临时表并联接到其他表）
问题
Deadlock found when trying to get lock; try restarting transaction
如果当 fares_table 正在更新/插入时，用户碰巧偶然发现了前端。 update语句发生死锁
死锁是由于存储过程在尝试使用fares_table中的select语句创建临时表时试图等待锁释放而导致的 当后端执行插入或更新时尝试等待锁释放。
LATEST DETECTED DEADLOCK
------------------------
110408 9:05:45
*** (1) TRANSACTION:
TRANSACTION 0 203543446, ACTIVE 0 sec, OS thread id 6584 fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 761 lock struct(s), heap size 60736, 30170 row lock(s)
MySQL thread id 86268, query id 135039790 XXXXXXX Copying to t
CREATE TEMPORARY TABLE tmp_tb1 AS SELECT MIN( fare ) as cheapest_fare,flighttype origin,destination ....
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 18433 n bits 240 index `PRIMARY` of table `db_name`.`fares_table`
Record lock, heap no 85 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 8025d996; asc % ;; 1: len 6; hex 00000c21d3a9; asc ! ;; 2: len 7; hex 0000000b031
*** (2) TRANSACTION:
TRANSACTION 0 203543465, ACTIVE 0 sec, OS thread id 3080 updating or deleting, thread declared inside
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 85631, query id 135039816 XXXXX Updating
UPDATE `fares_table` SET `fare` = 2552.85, `currency` = 'AUD'..
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 18433 n bits 240 index `PRIMARY` of table `db_name`.`fares_table`
Record lock, heap no 85 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 8025d996; asc % ;; 1: len 6; hex 00000c21d3a9; asc ! ;; 2: len 7; hex 0000000b031
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2086 n bits 600 index `flighttype_idx` of table `db_name`.`fares_table`
Record lock, heap no 218 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 7; hex 4f6e6520776179; asc One way;; 1: len 3; hex 424e45; asc BNE;; 2: len 8; hex 8000124a588
*** WE ROLL BACK TRANSACTION (2)
我的临时修复
捕获数据库错误 1213 并重试更新查询。它现在可以工作，但我想找到一个更好的解决方案来防止死锁。 有什么专业建议吗？
如何更改顺序以防止死锁，或者 flighttype_idx 的重复索引是否有帮助？
I need a expert advice on this.
Background
PHP/ Codeigniter
MYSQLi
Innodb table - fares_table
Stored Procedure
Backend - A cronjob php script is written to insert/update data(from a service) into the fares_table every few minutes. (normal sql query)
Frontend - Users will be able to read these data
(the query is written in a stored procedure form because it involves joining of many tables, hence my stored procedure has create temporary table from select statements from fares_table and joined to other tables)
Problem
Deadlock found when trying to get lock; try restarting transaction
A deadlock may occur if a user happen to stumble upon the frontend while the fares_table are updating/inserting. Deadlocks happen to the update statement
The deadlock is caused by stored procedure trying to wait for lock to released while trying to create temporary tables with select statements from fares_table
while the backend doing an insert or update trying to wait for lock to release.
LATEST DETECTED DEADLOCK
------------------------
110408 9:05:45
*** (1) TRANSACTION:
TRANSACTION 0 203543446, ACTIVE 0 sec, OS thread id 6584 fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 761 lock struct(s), heap size 60736, 30170 row lock(s)
MySQL thread id 86268, query id 135039790 XXXXXXX Copying to t
CREATE TEMPORARY TABLE tmp_tb1 AS SELECT MIN( fare ) as cheapest_fare,flighttype origin,destination ....
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 18433 n bits 240 index `PRIMARY` of table `db_name`.`fares_table`
Record lock, heap no 85 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 8025d996; asc % ;; 1: len 6; hex 00000c21d3a9; asc ! ;; 2: len 7; hex 0000000b031
*** (2) TRANSACTION:
TRANSACTION 0 203543465, ACTIVE 0 sec, OS thread id 3080 updating or deleting, thread declared inside
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 85631, query id 135039816 XXXXX Updating
UPDATE `fares_table` SET `fare` = 2552.85, `currency` = 'AUD'..
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 18433 n bits 240 index `PRIMARY` of table `db_name`.`fares_table`
Record lock, heap no 85 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 4; hex 8025d996; asc % ;; 1: len 6; hex 00000c21d3a9; asc ! ;; 2: len 7; hex 0000000b031
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2086 n bits 600 index `flighttype_idx` of table `db_name`.`fares_table`
Record lock, heap no 218 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 7; hex 4f6e6520776179; asc One way;; 1: len 3; hex 424e45; asc BNE;; 2: len 8; hex 8000124a588
*** WE ROLL BACK TRANSACTION (2)
My temporary fix
Catch database error 1213 and retry the updating query. It works now , but i would like to find a much better solution of preventing deadlock instead.
Any expertise advice?
How do i change the order to prevent deadlock or will duplicating index of flighttype_idx helps?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于由于聚合函数
MIN(fare)，创建临时表的查询本质上需要锁定表的主要部分，并且票价更新需要等待其完成，因此没有简单的方法重新排序可以解决僵局。最好通过显式锁定机制（可能只是出于此目的在锁定表上）围绕争用，而不是让事务竞争然后必须回滚。特别是建表语句无法回滚，尽管这看起来很奇怪。请参阅锁表文档。
为了整齐地实现，请将票价更新语句移至存储过程中，并让票价更新和临时表创建存储过程循环检查和设置锁定，完成其工作，然后解锁。
Since the query to create a temporary table needs to essentially lock major parts of the table due to the aggregate function
MIN(fare), and the fare update needs to wait for that to complete, there is no simple reordering which can resolve the deadlock.It would be better to surround the contention by an explicit locking mechanism, perhaps on a lock table just for this purpose, rather than let the transactions compete and then have to roll back. In particular, the create table statement cannot be rolled back, strange as that may seem. See the LOCK TABLE documentation.
To tidily implement, move the fare update statement(s) into a stored procedure and have the fare update and temp table creation stored procedures loop on checking and setting a lock, doing their work, and then unlock.