| Russian Version |
Yet another frequent example of similar problem is "Lock wait timeout exceeded" error while you use InnoDB tables. Most often running query SHOW ENGINE INNODB STATUS is enough to find the problem, because it will show last transactions. But output of this command does not contain information about all queries in the same transaction, but only about last one. What to do if SHOW ENGINE INNODB STATUS doesn't provide all information?
mysql> insert into t1 values(2,'1994-12-30', '1994-12-03');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show engine innodb status \G
****************** 1. row ******************
Type: InnoDB
Name:
Status:
=====================================
091001 15:54:26 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
....
------------
TRANSACTIONS
------------
Trx id counter 0 295696
Purge done for trx's n:o < 0 295690 undo n:o < 0 0
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 101121024
MySQL thread id 1314, query id 217 localhost root
show engine innodb status
---TRANSACTION 0 295695, not started, OS thread id 101606912
MySQL thread id 1311, query id 216 localhost root
---TRANSACTION 0 295694, ACTIVE 13 sec, OS thread id 101122048
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1312, query id 215 localhost root
We can see here information about concurrent transaction, but can not see what is exactly is error. General query log can help us again:
mysql> select * from mysql.general_log where thread_id = 1312 order by event_time \G
******************* 1. row *******************
event_time: 2009-10-01 15:54:11
user_host: root[root] @ localhost []
thread_id: 1312
server_id: 51
command_type: Query
argument: begin
******************* 2. row *******************
event_time: 2009-10-01 15:54:13
user_host: root[root] @ localhost []
thread_id: 1312
server_id: 51
command_type: Query
argument: insert into t1 values(2,'1994-12-30', '1994-12-03')
2 rows in set (0.12 sec)
Or easier:
mysql> select argument from mysql.general_log where thread_id = 1312 order by event_time;
+-----------------------------------------------------+
| argument |
+-----------------------------------------------------+
| begin |
| insert into t1 values(2,'1994-12-30', '1994-12-03') |
+-----------------------------------------------------+
2 rows in set (0.01 sec)
What to do? Again be sure these queries don't run at the same time.
Mehtod #13: use query SHOW ENGINE INNODB STATUS to get information about transactions.
Method #14: use general query log if output of SHOW ENGINE INNODB STATUS contains only part of information about problem transaction.
| Back | Content | Forward |
|
Author 2010 Sveta Smirnova COPYRIGHT © 2010 S.Smirnova and S. Lasunov sveta_at_js-client_dot_com |