List:General Discussion« Previous MessageNext Message »
From:Raphael Bauduin Date:September 3 2012 9:32am
Subject:identifying the cause of old active transactions with locks
View as plain text  
Hi,

In the output of
  SHOW ENGINE INNODB STATUS \G

I have several old active transactions, each having locked some rows.
All or from the same process id, but with different thread ids. This
is the oldest one, which is 2.5 days old:

---TRANSACTION 0 1532609805, ACTIVE 227995 sec, process no 29517, OS
thread id 140076393318144
5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 2573, query id 7268517 10.49.11.54 destockjeans
Trx read view will not see trx with id >= 0 1532609807, sees < 0 1532609631

An strace on the process shows some activity, so it's not completely
locked (see below).

How can I debug this further? In a show full process list I don't see
any more related information.
Can I see which query the transaction is executing? How can a lock be
hold so long?

Any help is welcome, thanks in advance!

Raph











strace on process:
--------------------------
select(16, [13 15], NULL, NULL, NULL)   = 1 (in [13])
fcntl(13, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(13, {sa_family=AF_INET, sin_port=htons(49952),
sin_addr=inet_addr("10.49.11.54")}, [16]) = 43
fcntl(13, F_SETFL, O_RDWR)              = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART,
0x7f6614c20af0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART,
0x7f6614c20af0}, 8) = 0
getpeername(43, {sa_family=AF_INET, sin_port=htons(49952),
sin_addr=inet_addr("10.49.11.54")}, [16]) = 0
getsockname(43, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr("10.49.11.54")}, [16]) = 0
open("/etc/hosts.allow", O_RDONLY)      = 102
fstat(102, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x7f66165d0000
read(102, "# /etc/hosts.allow: list of host"..., 4096) = 580
read(102, "", 4096)                     = 0
close(102)                              = 0
munmap(0x7f66165d0000, 4096)            = 0
open("/etc/hosts.deny", O_RDONLY)       = 102
fstat(102, {st_mode=S_IFREG|0644, st_size=1339, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x7f66165d0000
read(102, "# /etc/hosts.deny: list of hosts"..., 4096) = 1339
read(102, "", 4096)                     = 0
close(102)                              = 0
munmap(0x7f66165d0000, 4096)            = 0
getsockname(43, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr("10.49.11.54")}, [16]) = 0
fcntl(43, F_SETFL, O_RDONLY)            = 0
fcntl(43, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(43, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(43, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(43, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f661717e244, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f661717e240,
{FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
select(16, [13 15], NULL, NULL, NULL
Thread
identifying the cause of old active transactions with locksRaphael Bauduin3 Sep
  • Re: identifying the cause of old active transactions with locksRaphael Bauduin3 Sep