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.

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'. Worker #2 needs the screwdriver, but can't get it because worker #1 is holding it. Worker #1 goes to grab the screw as well, but can't, because it's being held by worker #2. Worker #1 grabs the screwdriver, and worker #2 grabs the screw. In real world terms, consider a construction site: You've got one screwdriver, and one screw. (Say, 3 retries on this particular error before giving up).ĭeadlocks 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. you can add this logic to your client code. WHERE datetime <= now() - INTERVAL 900 SECONDĪnother thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. if you do (and I suspect you do), order their WHERE in (k1,k2.kn) in ascending order.įix your delete statement to work in ascending order: Make sure you have no other queries that lock access more than one key at a time except for the delete statement. But i think MySQL driver decides this error as lost connection.

connection 2: locks key( 1), locks key( 2) There should be SQL statements and code should be safe for repeating (when a deadlock occurs).

Now, if you changed your queries such that the connections would lock the keys at the same order, ie: 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.

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

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

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie: One easy trick that can help with most deadlocks is sorting the operations in a specific order. 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'? What exactly about my situation is causing the error above? I have seen online that several other people have reported similar errors and that this may be a genuine deadlock situation. MySQL is a standard Solaris deployment, and the database connection is set up as follows: my $dsn = 'DBI:mysql:database='. I haven't done much tuning on MySQL or DBD::mysql. The limit is designed to break their work into small chunks. 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. However, it never happened with a small table and has become more common as the database has grown. 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 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 table has about 10 columns and several indexes. Var connection = mysql.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.

Var proxyMysqlDeadlockRetries = require('node-mysql-deadlock-retries') Npm install node-mysql-deadlock-retries -save Usage var mysql = require('mysql') The original project wasn't complete and seems abandoned so I finished it. Of course if the query does not issue that deadlock error there is no retry or sleep or delay. The retries are separated by a configurable, random length, sleep. It proxies the connection's query method and retries to execute the query a few times when it fails with that deadlock error. This module right here helps you if you use the node-mysql module for your database connections.

when you are mass-executing modifying queries like inserting or updating. The MySQL database can emit deadlock errors (errno 1205 - 1213, ER_LOCK_DEADLOCK, ERR_LOCK_WAIT_TIMEOUT) in certain scenarios, e.g.

Mysql deadlock error code