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)
www.un.org/Depts/DGACM/index_spanish.htm 我的临时固定地址:
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?
• 如何改变防止僵局的命令或复制<代码>飞行类型_idx索引 帮助?