…(29); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:38:03>insert into t values(23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:44:37>insert into t values… blocked. The SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… running transaction, two identical statements get different values, because some other transaction has modified the table’s rows. For example: transaction1> START TRANSACTION… detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… you are using InnoDB, it is also important that your transaction logs (ib_logfile*) are on the same logical volume, if… MySQL data. This way you don’t have to deactivate/activate the original logical volume to start the merging. So let… my salaries table back. Because leaving a production server with active snapshots can affect performance, this is not really an advisable…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… online system you should not see transactions spending more than couple of seconds in ACTIVE state. Especially ACTIVE transactions which do not currently run any query are suspicious. There are however cases when screaming fire about ACTIVE transactions…
Post: Queries Active vs Transactions Active
September 14, 2009 By Leave a Comment
… open inside InnoDB” which is a fancy way of saying “active transactions” which is 100. Typically you would want this ratio to… 2 which would correspond all active transactions doing active work and spending little time waiting on the application. Waiting transactions are bad because they…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… will find in SHOW INNODB STATUS is information about currently active transactions: ———— TRANSACTIONS ———— Trx id counter 0 80157601 Purge done for trx’s… state if there is no active Innodb transaction for this connection, or ACTIVE if transaction is active. Note transaction can be active even if connection is in…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 2 Comments
… other transactions for execution, this however only applies to read transactions while problem with undo space growth exists for read only transactions as well. This is how long transaction looks: —TRANSACTION 17402D749, ACTIVE 15867…
Post: MVCC: Transaction IDs, Log Sequence numbers and Snapshots
December 19, 2007 By 3 Comments
… transaction 50. We also need to track when given transaction has committed, in the list of transactions which were active at the time current transaction was started. This is needed for example to handle READ-COMMITTED isolation mode. Lets look at transaction…
Post: When does Innodb Start Transaction ?
January 11, 2011 By 9 Comments
… in transaction list. It is only when you read (or write) to INNODB table you can see transaction is started: —TRANSACTION F56AED, ACTIVE 4… will NOT start transaction however reading or writing Innodb table will. I would call it delayed transaction creation – having transaction open is costly…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
…_mutex when it starts/stop transactions, and when InnoDB starts the transaction, usually there is loop through ALL active transactions, and this loop is… kernel_mutex in action, we need many concurrent but short transactions. For this we will take sysbench running only simple select…
