List:General Discussion« Previous MessageNext Message »
From:Raphael Bauduin Date:September 3 2012 11:16am
Subject:Re: identifying the cause of old active transactions with locks
View as plain text  
On Mon, Sep 3, 2012 at 11:32 AM, Raphael Bauduin <rblists@stripped> wrote:
> 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).

duh, of course it's the same process, it's the mysqld process....
Still looking for more info

Raphaël

>
> 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



-- 
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
Thread
identifying the cause of old active transactions with locksRaphael Bauduin3 Sep
  • Re: identifying the cause of old active transactions with locksRaphael Bauduin3 Sep