Re: Why does a txn say ACTIVE 60 sec when stmt has been running 240 secs?

On Thu, Aug 02, 2007 at 08:43:35AM -0400, Baron Schwartz wrote:
> Long statements seem to "restart" the InnoDB transaction several times
> during the time they are running, as though the statement is actually
> several transactions.
>
> For example, I just ran an UPDATE that took over 8 minutes to complete.
> Yet SHOW INNODB STATUS never showed an ACTIVE time greater than a couple of
> minutes. (I tried to grab a sample of the processlist and InnoDB status,
> but just then the statement completed).

As far as I can tell, InnoDB initializes trx->start_time = time(NULL) in two places: when creating a trx object or when starting a transaction. The latter case seems to be guarded by the test trx->conc_state == TRX_NOT_STARTED.

The field conc_state is initialized to TRX_NOT_STARTED in trx_create(), trx_commit_off_kernel(), and trx_cleanup_at_db_startup().

I would tend to believe that it is MySQL that is committing the transaction for some reason, or the "commit ALTER TABLE every 10,000 rows" hack in ha_innobase::write_row() is not working properly. Can you set a breakpoint on innobase_commit_low or add some debug printout there?

> This isn't just idle curiosity; I'm trying to track down something weird
> happening in replication, where timestamps are different on the slave, and
> I wonder if large transactions have anything to do with it. Your thoughts
> on either problem are appreciated. I'm working on getting together enough
> details for a bug report, but I don't have enough info yet to be very
> specific.

I'm not a replication expert, but as far as I understand, transactions are sent to the replication slave upon commit. The upcoming row-based replication should be less prone to errors than the old statement-based replication.

Best regards,

	Marko Mäkelä
	Innobase Oy/Oracle Corp.

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com