Mysql – Working around MySQL error “Deadlock found when trying to get lock; try restarting transaction”

database-deadlocksdeadlockMySQL

I have a MySQL table with about 5,000,000 rows that are being constantly updated in small ways by parallel Perl processes connecting via DBI. The table has about 10 columns and several indexes.

One fairly common operation gives rise to the following error sometimes:

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at Db.pm line 276.

The SQL statement that triggers the error is something like this:

UPDATE file_table SET a_lock = 'process-1234' WHERE param1 = 'X' AND param2 = 'Y' AND param3 = 'Z' LIMIT 47

The error is triggered only sometimes. I'd estimate in 1% of calls or less. However, it never happened with a small table and has become more common as the database has grown.

Note that I am using the a_lock field in file_table to ensure that the four near-identical processes I am running do not try and work on the same row. The limit is designed to break their work into small chunks.

I haven't done much tuning on MySQL or DBD::mysql. MySQL is a standard Solaris deployment, and the database connection is set up as follows:

my $dsn = "DBI:mysql:database=" . $DbConfig::database . ";host=${DbConfig::hostname};port=${DbConfig::port}";
my $dbh = DBI->connect($dsn, $DbConfig::username, $DbConfig::password, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;

I have seen online that several other people have reported similar errors and that this may be a genuine deadlock situation.

I have two questions:

What exactly about my situation is causing the error above?
Is there a simple way to work around it or lessen its frequency? For example, how exactly do I go about "restarting transaction at Db.pm line 276"?

Thanks in advance.

Best Answer

If you are using InnoDB or any row-level transactional RDBMS, then it is possible that any write transaction can cause a deadlock, even in perfectly normal situations. Larger tables, larger writes, and long transaction blocks will often increase the likelihood of deadlocks occurring. In your situation, it's probably a combination of these.

The only way to truly handle deadlocks is to write your code to expect them. This generally isn't very difficult if your database code is well written. Often you can just put a try/catch around the query execution logic and look for a deadlock when errors occur. If you catch one, the normal thing to do is just attempt to execute the failed query again.

I highly recommend you read this page in the MySQL manual. It has a list of things to do to help cope with deadlocks and reduce their frequency.

Related Solutions

Mysql – How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);

If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);

it will be impossible to get a deadlock.

So this is what I suggest:

Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
Fix your delete statement to work in ascending order:

Change

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

DELETE FROM onlineusers 
WHERE id IN (
    SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND 
    ORDER BY id
) u;

Another thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

Php – Avoiding MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

Deadlocks occur when two or more processes requests locks in such a way that the resources being locked overlap, but occur in different orders, so that each process is waiting for a resource that's locked by another process, and that other process is waiting for a lock that the original process has open.

In real world terms, consider a construction site: You've got one screwdriver, and one screw. Two workers need to drive in a screw. Worker #1 grabs the screwdriver, and worker #2 grabs the screw. Worker #1 goes to grab the screw as well, but can't, because it's being held by worker #2. Worker #2 needs the screwdriver, but can't get it because worker #1 is holding it. So now they're deadlocked, unable to proceed, because they've got 1 of the 2 resources they need, and neither of them will be polite and "step back".

Given that you've got out-of-transaction changes occurring, it's possible that one (or more) of your updates/deletes are overlapping the locked areas you're reserving inside the transactions.

Best Answer

Related Solutions

Mysql – How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

Php – Avoiding MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

Related Topic