How to avoid mysql Deadlock found when trying to get lock try restarting transaction

Question

I have a innoDB table which records online users  It gets updated on every page refresh by a user to keep track of which pages they are on and their last access date to the site  I then have a cron that runs every 15 minutes to DELETE old records   I got a  Deadlock found when trying to get lock  try restarting transaction  for about 5 minutes last night and it appears to be when running INSERTs into this table  Can someone suggest how to avoid this error       EDIT      Here are the queries that are running   First Visit to site   INSERT INTO onlineusers SET ip   123 456 789 123  datetime   now    userid   321  page     thispage   area    thisarea   type   3   On each page refresh   UPDATE onlineusers SET ips   123 456 789 123  datetime   now    userid   321  page     thispage   area    thisarea   type   3 WHERE id   888   Cron every 15 minutes   DELETE FROM onlineusers WHERE datetime  lt   now   - INTERVAL 900 SECOND   It then does some counts to log some stats  ie  members online  visitors online

User · Answer

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.

User · Answer

For Java programmers using Spring  I ve avoided this problem using an AOP aspect that automatically retries transactions that run into transient deadlocks   See  RetryTransaction Javadoc for more info

User · Answer

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 is inserted update delete  the database needs 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

User · Answer

In case someone is still struggling with this issue   I faced similar issue where 2 requests were hitting the server at the same time  There was no situation like below   T1      BEGIN TRANSACTION     INSERT TABLE A     INSERT TABLE B     END TRANSACTION  T2      BEGIN TRANSACTION     INSERT TABLE B     INSERT TABLE A     END TRANSACTION   So  I was puzzled why deadlock is happening    Then I found that there was parent child relation ship between 2 tables because of foreign key  When I was inserting a record in child table  the transaction was acquiring a lock on parent table s row  Immediately after that I was trying to update the parent row which was triggering elevation of lock to EXCLUSIVE one  As 2nd concurrent transaction was already holding a SHARED lock  it was causing deadlock   Refer to  https   blog tekenlight com 2019 02 21 database-deadlock-mysql html

User · Answer

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 - gt  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  lt   now   - INTERVAL 900 SECOND  To DELETE FROM onlineusers  WHERE id IN       SELECT id FROM onlineusers     WHERE datetime  lt   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

User · Answer

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  lt   now - interval 900 second   delete from onlineusers where userid in  select userid from deletetemp     Breaking it up like this is less efficient but it avoids the need to hold a key-range lock during the delete    Also  modify your select queries to add a where clause excluding rows older than 900 seconds   This avoids the dependency on the cron job and allows you to reschedule it to run less often    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   http   dev mysql com doc refman 5 1 en innodb-next-key-locking html

User · Answer

I have a method  the internals of which are wrapped in a MySqlTransaction   The deadlock issue showed up for me when I ran the same method in parallel with itself   There was not an issue running a single instance of the method   When I removed MySqlTransaction  I was able to run the method in parallel with itself with no issues   Just sharing my experience  I m not advocating anything

User · Answer

cron is dangerous   If one instance of cron fails to finish before the next is due  they are likely to fight each other   It would be better to have a continuously running job that would delete some rows  sleep some  then repeat   Also  INDEX datetime  is very important for avoiding deadlocks   But  if the datetime test includes more than  say  20  of the table  the DELETE will do a table scan   Smaller chunks deleted more often is a workaround   Another reason for going with smaller chunks is to lock fewer rows   Bottom line    INDEX datetime  Continually running task -- delete  sleep a minute  repeat  To make sure that the above task has not died  have a cron job whose sole purpose is to restart it upon failure    Other deletion techniques   http   mysql rjweb org doc php deletebig

[mysql] How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'

Examples related to mysql

Examples related to deadlock