![]() Generally speaking, try to acquire lock always in the same order even in different transaction (e.g. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Each time you insert/update/or delete a row, a lock is acquired. There are numerous questions and answers about deadlocks. 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.ĭeadlock happen when two transactions wait on each other to acquire a 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 the delete needs that page lock and the insert needs that key lock. The insert is going to acquire a lock on the page it is inserting into, and then attempt to acquire the key lock. ![]() 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. Theory about the deadlocks: I don't have a lot of background in MySQL but here goes. This avoids the dependency on the cron job and allows you to reschedule it to run less often. Select userid from onlineusers where datetime <= now - interval 900 second ĭelete from onlineusers where userid in (select userid from deletetemp) īreaking it up like this is less efficient but it avoids the need to hold a key-range lock during the delete.Īlso, modify your select queries to add a where clause excluding rows older than 900 seconds. 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) (Say, 3 retries on this particular error before giving up). 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. connection 2: locks key( 1), locks key( 2).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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |