MySQL Forums
Forum List  »  InnoDB

orphaned transactions despite explicit commit
Posted by: Miguel DeAvila
Date: July 18, 2005 09:42AM

Hi,

I have a web application running on linux (kernel 2.4.20) that uses mysql (4.0.24), connector/j jdbc driver (3.1.7), and innodb tables.

I frequently see long-running (upto 8 hours) transactions in the output of 'show innodb status'. However, when I examine the mysql binary log and search for the mysql thread id that is referenced in the transaction I do indeed see an explicit commit.

Here's an example,

[ partial ouput from 'show innodb status' ]

---TRANSACTION 0 50428153, ACTIVE 2290 sec, process no 8684, OS thread id 21037069
MySQL thread id 1275, query id 1426499 localhost 127.0.0.1 miguel
Trx read view will not see trx with id >= 0 50428154, sees < 0 50428028

The 'show innodb status' command was issued at ~9:33 am PST. Therefore the transaction started ~ 38 minutes and 10 seconds prior, or around 8:55 am PST.

Looking in the mysql binary log for thread id 1275, I find the beginning of the transaction,

# at 2522261
#050718 8:53:38 server id 1 log_pos 2522261 Query thread_id=1275 exec_time=0 error_code=0
SET TIMESTAMP=1121702018;
BEGIN;
# at 2522303
#050718 8:53:37 server id 1 log_pos 2522261 Intvar
SET INSERT_ID=14653863;
# at 2522331
#050718 8:53:37 server id 1 log_pos 2522261 Query thread_id=1275 exec_time=0 error_code=0
SET TIMESTAMP=1121702017;
insert into ALIAS values ( );

Several items later, I find the last reference to this thread id,

# at 2527301#050718 8:53:38 server id 1 log_pos 2527301 Query thread_id=1275 exec_time=0 error_code=0
SET TIMESTAMP=1121702018;
COMMIT;

So the transaction is indeed getting comitted.

Why is the transaction still indicated in 'show innodb status'?

Advice?

thanks,

Miguel DeAvila

Options: ReplyQuote


Subject
Views
Written By
Posted
orphaned transactions despite explicit commit
3272
July 18, 2005 09:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.