我需要专家的意见。
背景PHP/CodeigniterMYSQLiInnodb表fares_table存储过程
后端-每隔几分钟编写一个cronjob php脚本来插入/更新数据(来自服务)到fares_table中。(普通SQL查询)
前端-用户将能够读取这些数据(查询是以存储过程形式编写的，因为它涉及到许多表的连接，因此我的存储过程从fares_table的select语句创建了临时表，并连接到其他表)
获取锁时发现死锁;尝试重新启动事务
如果用户在更新/插入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的索引是否有帮助?
由于聚合函数MIN(fare)，创建临时表的查询实质上需要锁定表的主要部分，而fare更新需要等待它完成，因此没有简单的重新排序可以解决死锁。
最好是通过显式的锁定机制来围绕争用，也许在锁表上就是为了这个目的，而不是让事务竞争，然后必须回滚。特别是，create table语句不能回滚，这看起来很奇怪。参考LOCK TABLE文档
为了整齐地实现，将票价更新语句移动到一个存储过程中，并让票价更新和临时表创建存储过程循环检查和设置锁，执行它们的工作，然后解锁。