Skip navigation links

MySQL Forums :: InnoDB :: Is this normal?


Advanced Search

Re: Is this normal?
Posted by: Richard Cook ()
Date: January 12, 2005 09:56AM

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)
============================

Options: ReplyQuote


Subject Views Written By Posted
Is this normal? 2586 Björn Danielsson 01/06/2005 12:32PM
Re: Is this normal? 2015 Radu Chiriac 01/07/2005 03:30PM
Re: Is this normal? 1941 Radu Chiriac 01/07/2005 03:36PM
Re: Is this normal? 2061 Björn Danielsson 01/07/2005 10:55PM
Re: Is this normal? 1953 Heikki Tuuri 01/10/2005 05:08PM
Re: Is this normal? 1991 Björn Danielsson 01/11/2005 10:35AM
Re: Is this normal? 2047 Richard Cook 01/12/2005 09:56AM
Re: Is this normal? 1993 Radu Chiriac 01/12/2005 04:46PM
Re: Is this normal? 2013 Heikki Tuuri 01/12/2005 05:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.