MySQL Forums
Forum List  »  InnoDB

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
4313
January 06, 2005 12:32PM
2969
January 07, 2005 03:30PM
2841
January 07, 2005 03:36PM
3276
January 07, 2005 10:55PM
2931
January 10, 2005 05:08PM
2991
January 11, 2005 10:35AM
Re: Is this normal?
3201
January 12, 2005 09:56AM
2918
January 12, 2005 04:46PM
3518
January 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.