• Home
  • /
  • Blog
  • /
  • Learn the Effective Ways to Overcome MySQL Deadlocks
MySQL Deadlocks

Learn the Effective Ways to Overcome MySQL Deadlocks

Spread the love

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.

MySQL Deadlocks

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.

Stanislaus Okwor is a Web Designer / Developer based in Lagos - Nigeria. He is the Director at Stanrich Online Technologies. He is knowledgeable in Content management System - Wordpress, Joomla and PHP/MySQL etc

6 Comments

  • Mathew

    December 25, 2014 at 11:17 am

    Thank you. I found your topic really nice. I have read your post and found it really interesting . I have also got some excellent information while reading blogs on remote dba experts. Keep writing and sharing such nice articles

    Reply
  • David Wicks

    February 7, 2015 at 7:00 am

    Great article!!! Nice topic and equally well described article to overcome SQL deadlocks. I like to read blogs on technology and I think that this post can help remote dba experts a lot. As now a days most experts are using various languages to make their database safe and secure. I would like to see more article on same topic. Thank you

    Reply
  • Alexander

    April 21, 2015 at 10:32 am

    Thank you for the post on sharing the ways to over come mysql deadlocks. As SQL is coomon in use of the remote dba support and experts so this article can help a lot to few friends in the field of database. SQL topics are always intersting and useful to read. Great article!

    Reply
  • Andrew Thompson

    September 5, 2015 at 11:55 am

    Deadlocks are an indispensable part and cannot be removed easily. So one can prevent the database from deadlock and once the deadlock occurs you should find the measures to remove the deadlock. This article gives the information to overcome the deadlock in mysql. Moreover the experts at remote dba support can help you with the database problems. There are some commands given in the article through which you can overcome the deadlock problem. Thank you Zopher for providing such a great information. Well done.

    Reply
  • Mango People

    September 15, 2015 at 11:56 am

    Nice post! SQL topics are always interesting and useful for me because I am a database administrator. This article gives the tips to overcome the deadlock in mysql. Deadlock is the biggest problem in the database. So you should prevent the database from deadlock and if your database is in deadlock, you can take some measures to get out of it or you can take help of remote dba services. Thank you Zopher for sharing this helpful information. Keep it up.

    Reply

Leave a Reply to Mango People Cancel reply

WhatsApp chat
Verified by MonsterInsights