Learn the Effective Ways to Overcome MySQL Deadlocks

When two or more transactions request for locks in MySQL, then deadlock appears at that time in the transactional database structure. This creates an interdependent environment as the locks mutually hold each other simultaneously. Deadlocks are an indispensable part and cannot be avoided totally. But you can easily learn some ways suggested by the experts to deal with MySQL deadlocks.

In the previous versions of MySQL, the deadlock can only be reviewed through the command SHOW ENGINE INNODB STATUS. But now, with the latest version MySQL 5.6 and the Percona toolkit, you can get the information through pt-deadlock-logger and can collect the information of a specific duration through the same SHOW ENGINE INNODB STATUS command.

Learn the Effective Ways to Overcome MySQL Deadlocks

You are required to remember the few techniques for overcoming deadlocks.

• Keep the transactions short in size and duration for avoiding collision
• To avoid collision, activate the transactions just after modifying a set of changes. The MySQL session should not remain unused for a long time
• If your locking command appears like (SELECT….FOR UPDATE) or ( SELECT…LOCK IN SHARE MODE); for smooth performance, you should use decreased isolation level like READ COMMITTED
• If multiple tables are getting modified or the rows are getting edited in the same table of your transaction, the activities need to be done in a consistent manner without many breaks. This will help the transactions to run smoothly without forming deadlocks. For example, you can convert the database operations into the functions as allowed by your applications. The sequence INSERT , UPDATE and DELETE can be used as commands in various places of multiple coding.
• You need to select the index properly for your tables. After adding them, the scan of the queries can take place with index records and fewer amounts of locks. You can use the command EXPLAIN SELECT for determining which index in the MySQL will be most suitable for your queries
• You can use SELECT for retrieving old data instead of using the clause FOR UPDATE or LOCK IN SHARE MODE. The isolation level READ COMMITTED can be used because of their consistency. The number of disk flushes can also be reduced by setting the value innodb_support_xa into zero. This helps in the proper adjustment between disk data and the binary log.
• The table-level locks can be serialized by the help of the command LOCK TABLES within your transaction. The tables can be InnoDB tables and the transactions can be started as SET autocommit =0 instead of START TRANSACTION. After that LOCK TABLES can be activated and after the successful completion of the transactions UNLOCK TABLES can be used. For example if you are editing the transactions in table t1 and getting the information from t2, you can perform like this

SET autocommit = 0

LOCK TABLES t1 WRITE, t2 READ

…..you can perform tasks in t1 and t2 here and then;

COMMIT;

UNLOCK TABLES

This helps in preventing the locks as well as updating your current tables in the less responsive systems.

• You can generate auxiliary ‘semaphore’ tables which include a single row only. Each transaction can be updated depending on their rows in a serial manner. In this ‘semaphore’ tables the deadlock reviewing function InnoDB also takes place within the algorithm in a right way. The time-out method can thus be used for resolving the deadlocks.

Therefore, the changes in the application can reduce the frequency of the deadlocks. The table schema modification and adding indexes also helps in the proper functioning of the transactions. The isolation level can be altered in case of gap locking but then the format needs to be converted into ROW or MIXED.

Author Bio: Zopher Anderson is one of the well known database experts who specialize in MySQL servers. In this article he focused on the ways to deal with the deadlocks occurring during the transactions in MySQL.