1

I've read about this deadlock and as much as I could understand this is happening when two queries running at the same time and blocking each other`s access to a table or row by holding on the last table/row they effected.

I received this error when running these queries:

SET SQL_SAFE_UPDATES = 0;
Delete FROM dbo.users_status WHERE dbo.users_status.SiteId = 62;

There is a service that runs these queries every X seconds.

I've read that if you keep the order of queries the same there should not be a problem of the sort. this error started mostly when I switched the connection method between backend and database to Pooling connections but I even tried to limit the connectionLimit to 1...

this answer seems to be the most relevant to me so I tried changing my queries as follow:

SET SQL_SAFE_UPDATES = 0;
Delete FROM rtls.users_status
WHERE rtls.users_status.StatusIndex in (select * from (SELECT u.StatusIndex FROM rtls.users_status as u
                                                       WHERE u.SiteId = 62) as a);

And it did lower significantly the number of errors I get. but still, the error jumps here and there.

Can someone explain to me why there could be a deadlock in my case? it seems I'm missing something here.

Dzak
  • 412
  • 4
  • 17
  • Do you know what the other query is? Usually there are two different queries which are interlocking, you can use SHOW ENGINE INNODB STATUS and check the LATEST DETECTED DEADLOCK section and explore both queries in detail. – scipilot Dec 09 '19 at 11:31
  • Or more recently, enable innodb_print_all_deadlocks and check the error log. – scipilot Dec 09 '19 at 11:33

0 Answers0