orphaned transactions despite explicit commit

[Miguel DeAvila]

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