Weird Locks Problem in MySQL

Question

Hi!

Quite often my Server hangs and MySQL shows the following process list:

Process 1: Time: 24 User: Worker Status: Locked Info: LOCK TABLES tRatings WRITE, tUsers WRITE

Process 2: Time: 24 User: Worker Status: Updating Info: UPDATE tUsers SET fGender = '1' WHERE fID = 232049

Process 3: Time: 24 User: Worker Status: Locked Info: LOCK TABLES tClients READ, tUsers uA READ, tUsers aB READ, tNexts READ

All tables are InnoDB and I use the explicit LOCK TABLES to prevent InnoDB Deadlocks from happening.

My problem is that I do not understand at all whats going wrong here! Can please someone explain, why he just isn't performing on operation after the other? Instead all for process do nothing for (in this example) 24 seconds.

Thanks,

Jo

Answer 1

Quite often my Server hangs [...] All tables are InnoDB and I use the explicit LOCK TABLES to prevent InnoDB Deadlocks from happening.

You aren't preventing deadlocks. You're preventing automatic deadlock detection. Let me explain:

A deadlock occurs when you have two (or more) processes contending for the same resource, but in a different order. For example:

• Process A needs resource 2, then resource 1. It succeeds at grabbing resource 2 ...
• But then there's a context switch! Process B comes along and grabs resource 1, and then tries to grab resource 2, but it can't because A has reserved it (either in a transaction or in a lock), so B is put on hold.
• Context switch! A comes along again and tries to grab resource 1, but it's held by process B.
• A now is waiting on B, B is now waiting on A. That's a deadlock.

When this happens inside a transaction, the database engine can detect that a deadlock is occurring and terminate one of the responsible connections, allowing the other to go ahead.

When you use LOCK TABLES, you don't allow the database engine to perform deadlock detection, so the deadlock remains until you manually come along and kill one of the deadlocked processes. Worse, LOCK TABLES can also prevent reads, and that can easily bring your entire application to a screeching halt.

Stop using LOCK TABLES.

Start using transactions in areas of your code where you need transactional integrity. Do not add them everywhere.

If you are receiving deadlocks, chances are that you have different code paths that perform related operations to related bits of data in a different order, or you aren't using transactions in the right places.

There are other alternatives. One is to engineer your database routines so that they detect when a deadlock kill has happened and start re-playing the events needed in order to go ahead once again. In other words, you'd rollback, restart the transaction, and try to do the same thing again. Chances are that the conditions that lead to the deadlock have been resolved by then, so you may be OK. Unfortunately, the other processes involved in the deadlock may have altered some of the data you were working with, so restarting might be a little on the complex side.

Finally, another alternative is to not use transactions or locks at all. You aren't dealing with money, are you? It looks like you're dealing with users and ratings. That's pretty trivial data. Nobody is going to care or notice if the current rating is missing the vote that was registered a split second after someone did something that causes the rating to be recalculated.