Re: Is this normal?
We are also seeing the same problem with our application, using 4.0.22. If we lower the isolation level to READ COMMITTED, then the issue goes away. But it seems like this shouldn't be happening at all, because it looks like the locks are being acquired in opposite orders, which is clear case of deadlock.
Does anyone know the exact difference between the locks acquired under READ COMMITTED and REPEATABLE READ, ie, these supremum locks, etc.?
Rich
LATEST DETECTED DEADLOCK
------------------------
050111 15:58:00
*** (1) TRANSACTION:
TRANSACTION 0 3032387, ACTIVE 0 sec, OS thread id 3724 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 320
MySQL thread id 10, query id 27112 localhost 127.0.0.1 ami update
INSERT INTO play_detail_event SET id=NULL, jukebox_stats_report_id=150, jukebox_id=4, mac_address='XX-XX', hard_disk_id='104-hard-disk-id', company_id=3, type_code='400', song_id='4', paid_credits=0, free_credits=0, play_cost=0, result_code='100', is_msf=0, is_mod=0, downlo
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 802 n bits 136 index `PRIMARY` of table `calliope_test/play_detail_event` trx id 0 3032387 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** (2) TRANSACTION:
TRANSACTION 0 3032368, ACTIVE 5 sec, OS thread id 3936 setting auto-inc lock, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
16 lock struct(s), heap size 2496, undo log entries 348
MySQL thread id 8, query id 27113 localhost 127.0.0.1 ami update
INSERT INTO play_detail_event SET id=NULL, jukebox_stats_report_id=146, jukebox_id=1, mac_address='XX-X2', hard_disk_id='101-hard-disk-id', company_id=3, type_code='400', song_id='1', paid_credits=0, free_credits=0, play_cost=0, result_code='100', is_msf=0, is_mod=0, downlo
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 802 n bits 72 index `PRIMARY` of table `calliope_test/play_detail_event` trx id 0 3032368 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `calliope_test/play_detail_event` trx id 0 3032368 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)
============================