Lecture
Another common example is the error "Lock wait timeout exceeded" when using InnoDB tables. Most often there is enough SHOW ENGINE INNODB STATUS, which will show the latest transactions. But the output of this command does not contain information about all requests in the transaction, but only about the current one. What if SHOW ENGINE INNODB STATUS does not provide all the 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 see here information about a competing transaction, but we don’t see what the specific problem is. General query log is our assistant 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, dissolve requests in time.
Reception number 13: use SHOW ENGINE INNODB STATUS to get information about transactions.
Reception number 14: use the general query log if the output of SHOW ENGINE INNODB STATUS is only part of the information about the problem transaction.
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application