innodb: question about deadlock

MySQL & MariaDB Other MySQL® Questions
1

what i can do, if i see deadlock, which started long time ago?

2

Please post SHOW INNODB STATUS results here.

3

ok, i sending copy of status (from cca 17pm)… i thing when only deadlock part enought

[B]Quote:[/B]

.
.
.

LATEST DETECTED DEADLOCK

071128 14:05:16
*** (1) TRANSACTION:
TRANSACTION 0 125715557, ACTIVE 26 sec, process no 24189, OS thread id 2496023472 inserting
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 526468, query id 27045614 192.168.30.23 game_user update
insert into uzivatel_im_data (user_id, zustatek, zetony) values (25704,0,0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12378 n bits 144 index PRIMARY of table game/uzivatel_im_data trx id 0 125715557 lock_mode X insert intention waiting
Record lock, heap no 1
*** (2) TRANSACTION:
TRANSACTION 0 125715374, ACTIVE 36 sec, process no 24189, OS thread id 2537634736 fetching rows, thread declared inside InnoDB 457
mysql tables in use 1, locked 1
1835 lock struct(s), heap size 109888, undo log entries 3
MySQL thread id 526108, query id 27052772 192.168.30.22 game_user Updating
UPDATE uzivatel SET los_win = 3, los_date = ‘2007-11-28 14:05:07’, los_rule = 15 WHERE seccode = 754829331
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 12378 n bits 144 index PRIMARY of table game/uzivatel_im_data trx id 0 125715374 lock_mode X
Record lock, heap no 1
Record lock, heap no 2
Record lock, heap no 3
Record lock, heap no 4
Record lock, heap no 5
Record lock, heap no 6
Record lock, heap no 7
Record lock, heap no 8
Record lock, heap no 9
Record lock, heap no 10
Record lock, heap no 11
Record lock, heap no 12
Record lock, heap no 13
Record lock, heap no 14
Record lock, heap no 15
Record lock, heap no 16
Record lock, heap no 17
Record lock, heap no 18
Record lock, heap no 19
Record lock, heap no 20
Record lock, heap no 21
Record lock, heap no 22
Record lock, heap no 23
Record lock, heap no 24
Record lock, heap no 25
Record lock, heap no 26
Record lock, heap no 27
Record lock, heap no 28
Record lock, heap no 29
Record lock, heap no 30
Record lock, heap no 31
Record lock, heap no 32
Record lock, heap no 33
Record lock, heap no 34
Record lock, heap no 35
Record lock, heap no 36
Record lock, heap no 37
Record lock, heap no 38
Record lock, heap no 39
Record lock, heap no 40
Record lock, heap no 41
Record lock, heap no 42
Record lock, heap no 43
Record lock, heap no 44
Record lock, heap no 45
Record lock, heap no 46
Record lock, heap no 47
Record lock, heap no 48
Record lock, heap no 49
Record lock, heap no 50
Record lock, heap no 51
Record lock, heap no 52
Record lock, heap no 53
Record lock, heap no 54
Record lock, heap no 55
Record lock, heap no 56
Record lock, heap no 57
Record lock, heap no 58
Record lock, heap no 59
Record lock, heap no 60
Record lock, heap no 61
Record lock, heap no 62
Record lock, heap no 63
Record lock, heap no 64
Record lock, heap no 65
Record lock, heap no 66
Record lock, heap no 67
Record lock, heap no 68
Record lock, heap no 69
Record lock, heap no 70
Record lock, heap no 71
Record lock, heap no 72
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 19991 n bits 80 index PRIMARY of table game/uzivatel trx id 0 125715374 lock_mode X waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 57; compact format; info bits 0
.
.
.
.

*** WE ROLL BACK TRANSACTION (1)

. . . . . .
4

Posted info means that this deadlock has been resolved. What do you mean by “i see deadlock, which started long time ago”? If you use transactional storage engine, your application should be aware of such problems and should retry queries (most of the time).

5
[B]Quote:[/B]
071128 [B]14:05:16[/B] *** (1) TRANSACTION: TRANSACTION 0 125715557, ACTIVE 26 sec, process no 24189, OS thread id 2496023472 inserting mysql tables in use 1, locked 1 LOCK WAIT 11 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 526468, query id 27045614 192.168.30.23 game_user update insert into uzivatel_im_data (user_id, zustatek, zetony) values (25704,0,0)

from this ) i get this list innodb status on 17:00 and time wroted is 14… i thing when deadlock is alive about 3 hours…
I still don’t know if info about deadlock is for me as info or warning or error )