my MySQL deadlock scenario - advice needed

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 tempo开发者_开发问答rary 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?

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.