Powered By Blogger

Thursday, 12 April 2012

Avoiding Lock wait time out Issues

when even we are facing the lockwait timeout issues
we need to find who is locking and blocking the thread
The query to find the lock waittime out which mysqlid is locking and blocking the queries
The query is as follows:
SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;

In order to avoid the lock wait time out issue permanantely we have two solutions for this
1. Setting the transaction isolation level to read committed
The command is as follows
set global transaction isolation level read committed;

2. Setting  innodb_locks_unsafe_for_binlog = 1 in my.cnf file and restart mysql
For more inforamtion we can refer the following urls :
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-examples.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29