MySQL Forums
Forum List  »  InnoDB

What is the best way to handle deadlocks
Posted by: lee fei
Date: September 12, 2017 02:57AM

Dear Sir,

Wish to seek your advice how can we handle better of the following scenario, as we found too many deadlocks when doing the statements such as SELECT, INSERT ... ON DUPLICATE UPDATE.

Scenario:
We have a busy INNODB table receives incoming requests approximately 5-8 tps.
INSERT INTO ...
ON DUPLICATE UPDATE


Then we have a scheduler to process these requests with following steps:
START TRANSACTION;
UPDATE LockID = 'x' WHERE LockID = '' LIMIT 1000;
SELECT WHERE LockID = 'x';
{
// process the records
}
DELETE WHERE LockID = 'x';
COMMIT;


There are always many deadlocks found especially when SELECT.
Deadlock even occurs when INSERT INTO ... ON DUPLICATE

How shall we handle better of this kind of scenario?
Are we able to check once the table is not occupied by anyone then only perform the scheduler?

Options: ReplyQuote


Subject
Views
Written By
Posted
What is the best way to handle deadlocks
63
September 12, 2017 02:57AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.