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.

One easy trick that can help with most deadlocks is sorting the operations in a specific order. You get a deadlock is 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 onilneusers WHERE datetime You can add this logic to your client code. (say, 3 retries on this particular error before giving up).

It is likely that the delete statement will affect a large fraction of the total rows in the table. Eventually this might lead to a table lock being acquired when deleting. Holding on to a lock (in this case row- or page locks) and acquiring more locks is always a deadlock risk.

However I can't explain why the insert statement leads to a lock escalation - it might have to do with page splitting/adding, but someone knowing Mysql better will have to fill in there. For a start it can be worth trying to explicitly acquire a table lock right away for the delete statement. See LOCK TABLES and Table locking issues.

Deadlock happen when two transactions wait on each other to acquire a lock. Example: Tx 1: lock A, then B Tx 2: lock B, then A There are numerous questions and answers about deadlocks. Each time you insert/update/or delete a row, a lock is acquired.To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock.

Generally speaking, try to acquire lock always in the same order even in different transaction (e.g. Always table A first, then table B). Another reason for deadlock in database can be missing indexes. When a row in inserted/update/delete, the database need to check the relational constraints, that is, make sure the relations are consistent.

To do so, the database needs to check the foreign keys in the related tables.It might result in other lock being acquired than the row that is modified. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lock instead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.

So perhaps my problem is that the User has refreshed the page and thus triggering an UPDATE of a record at the same time the cron is trying to run a DELETE on the record. However, im getting the error on INSERTS, so the cron wouldn't be DELETING records that have just been created. So how can a deadlock happen on a record that is yet to be inserted?

– David Feb 25 '10 at 9:32 Can you provide a bit more information about the table(s) and what the transactions exactly do? – ewernli Feb 25 '10 at 9:42 I have edited the initial post with query information. – David Feb 25 '10 at 10:22 I don't see how a deadlock could happen if there is only one statement per transaction.No other operations on other tables?

No special foreign keys or unique constraints? No cascade delete constraints? – ewernli Feb 25 '10 at 10:56 nope, nothing else special...I suppose its down to the nature of the usage of the table.

A row is being inserted/updated every page refresh from a visitor. Around 1000+ visitors are on at any one time. – David Feb 25 '107 at 9:28.

You might try having that delete job operate by first inserting the key of each row to be deleted into a temp table like this pseudocode create temporary table deletetemp (userid int); insert into deletetemp (userid) select userid from onlineusers where datetime Theory about the deadlocks: I don't have a lot of background in MySQL but here goes... The delete is going to hold a key-range lock for datetime, to prevent rows matching its where clause from being added in the middle of the transaction, and as it finds rows to delete it will attempt to acquire a lock on each page it is modifying. The insert is going to acquire a lock on the page it is inserting into, and then attempt to acquire the key lock. Normally the insert will wait patiently for that key lock to open up but this will deadlock if the delete tries to lock the same page the insert is using because thedelete needs that page lock and the insert needs that key lock.

This doesn't seem right for inserts though, the delete and insert are using datetime ranges that don't overlap so maybe something else is going on. dev.mysql.com/doc/refman/5.1/en/innodb-n....

If you don't use it in transactions you could change the type to MyISAM.

2 s use-case demands high concurrency, MyISAMs table based locking would probably not be appropriate – Jeremy Jan 7 at 15:49.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.