Environment :CentOS release 6.6, 5.6.28-76.1-56-log Percona XtraDB Cluster
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Was reading about this issue on varies websites they where mentioning this might be caused due to the tx_isolation level suggesting to change it to “READ COMMITED” to avoid the SELECT query locking etc. And some pages have mentioned the mysql is well tested for default setting which is " Read Repeatable" so changing this would bring bugs or other issues in replication etc. So would like to know below.
- How do we find which session is actually blocking the current session as “show engine innodb status” displays alot of information. Can some one point me how to read this innodb status or other methods to find the locking.
- If this is due to tx_isolation levels can I modify it what is the impact of changing to READ COMMITED should I be worried about replication etc ?.