Deadlock question
am 07.07.2009 18:20:01 von Matthias UrlichsHi,
I have the following table and two concurrent jobs,
trying to insert data into it. They deadlock on accessing an index which
shouldn't collide (the date is part of the index, and the jobs process
data for different dates).
This is mysql 5.0.51a-24+lenny1~bpo40+1-log.
Any ideas? Is this likely to not happen with 5.1?
CREATE TABLE `test` (
`kunde` int(11) NOT NULL default '0',
`quelle` int(11) NOT NULL default '0',
`datum` int(10) unsigned NOT NULL default '0',
`ziel` tinyint(4) default NULL,
`pakete` bigint(20) default NULL,
`bytes` bigint(20) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
KEY `qkd` (`kunde`,`datum`),
KEY `qqd` (`quelle`,`datum`),
KEY `timestamp` (`timestamp`),
KEY `datum` (`datum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1
*************************** 1. row ***************************
Status:
=====================================
090612 13:52:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2911205, signal count 2566010
Mutex spin waits 0, rounds 827176046, OS waits 1619184
RW-shared spins 8458591, OS waits 749052; RW-excl spins 15010669, OS waits 188067
------------------------
LATEST DETECTED DEADLOCK
------------------------
090612 8:35:15
*** (1) TRANSACTION:
TRANSACTION 0 534404698, ACTIVE 44 sec, process no 4871, OS thread id 1229306208 inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 3024, undo log entries 2
MySQL thread id 2773037, query id 633323918 acct1.backup.noris.net 10.1.1.95 kunde update
insert low_priority into test set kunde=9, quelle=-715188982, datum=20090602, ziel=119, pakete=192, bytes=10752
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 692344 n bits 456 index `qkd` of table `einzel/test` trx id 0 534404698 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 00000337b2b8; asc 7 ;;
*** (2) TRANSACTION:
TRANSACTION 0 534294520, ACTIVE 4074 sec, process no 4871, OS thread id 1249306976 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
35927 lock struct(s), heap size 6256624, undo log entries 411437
MySQL thread id 2763571, query id 633351909 acct1.backup.noris.net 10.1.1.95 kunde update
insert low_priority into test set kunde=9, quelle=-1066572043, datum=20090601, ziel=100, pakete=64, bytes=4096
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 692344 n bits 416 index `qkd` of table `einzel/test` trx id 0 534294520 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 00000337b2b8; asc 7 ;;
Record lock, heap no 347 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd1af4; asc ;;
Record lock, heap no 348 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd25bd; asc % ;;
Record lock, heap no 349 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd25be; asc % ;;
Record lock, heap no 350 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd25c3; asc % ;;
Record lock, heap no 351 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd25c4; asc % ;;
Record lock, heap no 352 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd25c5; asc % ;;
Record lock, heap no 353 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd25cb; asc % ;;
Record lock, heap no 354 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2633; asc &3;;
Record lock, heap no 355 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2634; asc &4;;
Record lock, heap no 356 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2635; asc &5;;
Record lock, heap no 357 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2636; asc &6;;
Record lock, heap no 358 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2637; asc &7;;
Record lock, heap no 359 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2638; asc &8;;
Record lock, heap no 360 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd2639; asc &9;;
Record lock, heap no 361 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd263a; asc &:;;
Record lock, heap no 362 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cd6915; asc i ;;
Record lock, heap no 363 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cdaeff; asc ;;
Record lock, heap no 364 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ce08bd; asc ;;
Record lock, heap no 365 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ce23c2; asc # ;;
Record lock, heap no 366 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ce72a7; asc r ;;
Record lock, heap no 367 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ce7a16; asc z ;;
Record lock, heap no 368 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ce8ca1; asc ;;
Record lock, heap no 369 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ce8cbf; asc ;;
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ceb7d6; asc ;;
Record lock, heap no 371 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cec4f4; asc ;;
Record lock, heap no 372 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cec943; asc C;;
Record lock, heap no 373 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cecab0; asc ;;
Record lock, heap no 374 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cecbe3; asc ;;
Record lock, heap no 375 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cecfb8; asc ;;
Record lock, heap no 376 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ced2d3; asc ;;
Record lock, heap no 377 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ced335; asc 5;;
Record lock, heap no 378 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ced336; asc 6;;
Record lock, heap no 379 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cedd19; asc ;;
Record lock, heap no 380 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cee26a; asc j;;
Record lock, heap no 381 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cee770; asc p;;
Record lock, heap no 382 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008cee9f7; asc ;;
Record lock, heap no 383 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ceea2d; asc -;;
Record lock, heap no 384 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 000008ceee27; asc ';;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 692344 n bits 456 index `qkd` of table `einzel/test` trx id 0 534294520 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 00000337b2b8; asc 7 ;;
*** WE ROLL BACK TRANSACTION (1)
--
Duh?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org