Hello,
I'm running MySQL 5.1.49-2-log x64 on a Debian server (you'll find version details at the bottom of this message), having SSD drives (this might have importance, I don't know, this is this kind of server precisely:
http://www.ovh.com/fr/produits/eg_ssd.xml).
The server handles an auction website with pretty heavy traffic (despite that, the server is far to be heavy loaded, running at 2-4% CPU at peaks) and, that's VERY strange, but a f****** deadlock occurs between transactions that shouldn't deadlock each other (basically, when we insert a bid, a TRIGGER also updates the Auctions table to enable accessing some informations faster). This trigger is very simple and I copy/paste it below.
For those who will ask, yes, there is only ONE method doing INSERT on the Bids table, and the method always accesses the tables in the same order.
This is really driving me crazy, I don't know what can be causing this problem. Any help greatly appreciated :)
Thanks!
*** The trigger ***
BEGIN
IF (@DISABLE_TRIGER IS NULL OR @DISABLE_TRIGER!=1) THEN
UPDATE Auctions SET DateLast=COALESCE(NEW.Date1,NEW.Date2),CurrentWinnerId=NEW.UserId,CurrentId=NEW.Id WHERE Id=NEW.AuctionId;
END IF;
END
*** Partial output of SHOW ENGINE INNODB STATUS; ***
------------------------
LATEST DETECTED DEADLOCK
------------------------
101220 18:24:23
*** (1) TRANSACTION:
TRANSACTION 0 594192469, ACTIVE 0 sec, process no 2821, OS thread id 140394051421968 starting index read
mysql tables in use 9, locked 9
LOCK WAIT 25 lock struct(s), heap size 6752, 13959 row lock(s)
MySQL thread id 143038, query id 14672207 www-Y.mydomain.com 1.2.3.5 my-user Updating
UPDATE Auctions SET DateLast=COALESCE(NEW.Date1,NEW.Date2),CurrentWinnerId=NEW.UserId,CurrentId=NEW.Id WHERE Id=NEW.AuctionId
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23857 n bits 224 index `PRIMARY` of table `MyDB`.`Auctions` trx id 0 594192469 lock_mode X locks rec but not gap waiting
Record lock, heap no 153 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
0: len 4; hex 000008e5; asc ;; 1: len 6; hex 0000236aa274; asc #j t;; 2: len 7; hex 00000440060eb1; asc @ ;; 3: len 8; hex 800012482d8cc06c; asc H- l;; 4: len 8; hex 800012482e16a935; asc H. 5;; 5: len 4; hex 0005b147; asc G;; 6: SQL NULL; 7: len 8; hex 0000000000d88540; asc @;; 8: len 4; hex 00000040; asc @;; 9: len 8; hex 7b14ae47e17a843f; asc { G z ?;; 10: len 4; hex 0000000a; asc ;; 11: len 4; hex 00000001; asc ;; 12: len 1; hex 80; asc ;; 13: len 1; hex 80; asc ;; 14: len 1; hex 81; asc ;; 15: len 1; hex 81; asc ;; 16: len 1; hex 03; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 594192473, ACTIVE 0 sec, process no 2821, OS thread id 140394057844496 starting index read, thread declared inside InnoDB 500
mysql tables in use 9, locked 9
27 lock struct(s), heap size 6752, 13960 row lock(s)
MySQL thread id 150597, query id 14672211 www-X.mydomain.com 1.2.3.4 my-user Updating
UPDATE Auctions SET DateLast=COALESCE(NEW.Date1,NEW.Date2),CurrentWinnerId=NEW.UserId,CurrentId=NEW.Id WHERE Id=NEW.AuctionId
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 23857 n bits 224 index `PRIMARY` of table `MyDB`.`Auctions` trx id 0 594192473 lock mode S locks rec but not gap
Record lock, heap no 153 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
0: len 4; hex 000008e5; asc ;; 1: len 6; hex 0000236aa274; asc #j t;; 2: len 7; hex 00000440060eb1; asc @ ;; 3: len 8; hex 800012482d8cc06c; asc H- l;; 4: len 8; hex 800012482e16a935; asc H. 5;; 5: len 4; hex 0005b147; asc G;; 6: SQL NULL; 7: len 8; hex 0000000000d88540; asc @;; 8: len 4; hex 00000040; asc @;; 9: len 8; hex 7b14ae47e17a843f; asc { G z ?;; 10: len 4; hex 0000000a; asc ;; 11: len 4; hex 00000001; asc ;; 12: len 1; hex 80; asc ;; 13: len 1; hex 80; asc ;; 14: len 1; hex 81; asc ;; 15: len 1; hex 81; asc ;; 16: len 1; hex 03; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23857 n bits 224 index `PRIMARY` of table `MyDB`.`Auctions` trx id 0 594192473 lock_mode X locks rec but not gap waiting
Record lock, heap no 153 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
0: len 4; hex 000008e5; asc ;; 1: len 6; hex 0000236aa274; asc #j t;; 2: len 7; hex 00000440060eb1; asc @ ;; 3: len 8; hex 800012482d8cc06c; asc H- l;; 4: len 8; hex 800012482e16a935; asc H. 5;; 5: len 4; hex 0005b147; asc G;; 6: SQL NULL; 7: len 8; hex 0000000000d88540; asc @;; 8: len 4; hex 00000040; asc @;; 9: len 8; hex 7b14ae47e17a843f; asc { G z ?;; 10: len 4; hex 0000000a; asc ;; 11: len 4; hex 00000001; asc ;; 12: len 1; hex 80; asc ;; 13: len 1; hex 80; asc ;; 14: len 1; hex 81; asc ;; 15: len 1; hex 81; asc ;; 16: len 1; hex 03; asc ;;
*** WE ROLL BACK TRANSACTION (1)
*** MySQL and Debian versions ***
'protocol_version', '10'
'version', '5.1.49-2-log'
'version_comment', '(Debian)'
'version_compile_machine', 'x86_64'
'version_compile_os', 'debian-linux-gnu'
Linux mysql.mydomain.com 2.6.33.5-xxxx-grs-ipv4-64 #2 SMP Fri Jun 18 13:06:54 UTC 2010 x86_64 GNU/Linux