CODEWITHSUNDEEP
phpmysqlmariadbdeadlockgalera
Brian Leishman
Brian Leishman

Reputation: 8555

MariaDB Avoid Deadlocks

My original error was

Error No: 1213 - Deadlock found when trying to get lock; try restarting transaction

Okay, so I wrote a loop with max retries and a wait in between to try and get through the deadlocks. 

$Try = 0;
while (!$Result = $dbs->query($MySQL)) {
    $Try++;
    if ($Try === MYSQL_MAX_RETRIES)
        HandleMySQLError($dbs->error, $MySQL, false, $Test, $Trace);
    else 
        sleep(MYSQL_RETRY_WAIT);
}

But now I'm constantly getting some of the original error still, and a new error

Got error 35 "Resource deadlock avoided" during COMMIT

But I can't really seem to find out what this means or how to fix it?

EDIT

I left out a ton of information when I first wrote this, but the server is a RedHat 7 AWS EC2 (well, 3 of them) in a Galera & MariaDB cluster.

The query I am running is a call to a stored procedure 

call`getchatmessages`('<ChatID>','<UserID>',from_unixtime('<Some Timestamp>'));

And the stored procedure is as follows

CREATE DEFINER=`root`@`%` PROCEDURE `getchatmessages`(IN `__ChatID` CHAR(36), IN `__UserID` CHAR(36), IN `__Timestamp` TIMESTAMP(6))
BEGIN

DECLARE `__NewChatMessages` TINYINT(1) DEFAULT 0;
DECLARE `__i` INT(11) DEFAULT 0;

DECLARE `__Interval` INT(11) DEFAULT 100; -- ms
DECLARE `__Timeout` INT(11) DEFAULT 15000; -- ms

while `__NewChatMessages`=0 and `__i`<`__Timeout`/`__Interval` do
    select 1 into `__NewChatMessages` from `chatmessages` where `ChatID`=`__ChatID` and `DateTimeAdded`>ifnull(`__Timestamp`,0) limit 1;
    update `chatusers` set `DateTimeRead`=now(6) where `ChatID`=`__ChatID` and `UserID`=`__UserID`;
    do sleep(`__Interval`/1000);
    set `__i`=`__i`+1;
end while;

select `chatmessages`.`Body`, `chatmessages`.`ChatID`, `chatmessages`.`UserID`, 
`chatmessages`.`ChatMessageID`, `chatmessages`.`DateTimeAdded`, UNIX_TIMESTAMP(`chatmessages`.`DateTimeAdded`) `Timestamp`, `users`.`FirstName`,
`users`.`LastName`
from `chatmessages` 
join `users` using (`UserID`) 
where `chatmessages`.`ChatID`=`__ChatID` 
and `chatmessages`.`DateTimeAdded`>ifnull(`__Timestamp`,0) 
order by `chatmessages`.`DateTimeAdded` desc
limit 100;

END

Upvotes: 3

Views: 4647

Answers (2)

Rick James
Rick James

Reputation: 142296

Is this Proc being called inside a transaction? If so, I argue strongly with its design. You have a loop with a sleep hanging onto the transaction.

Instead, have the UPDATE be a transaction by itself.

This may virtually eliminate the deadlocks. However you should still deal with deadlocks, as discussed by other answer(s).

Edit Since there are no BEGINs, and autocommit=ON, the OP is already following this advice. Alas.

Upvotes: 0

Alexander Rubin
Alexander Rubin

Reputation: 421

Deadlock in Galera Cluster (MariaDB Galera Cluster, 3 nodes) is not a typical deadlock, but a way of communicating the multi-master conflicts:

http://galeracluster.com/documentation-webpages/dealingwithmultimasterconflicts.html

The easiest way to avoid deadlocks is to write to 1 node at a time, i.e. configure HA proxy to write to 1 node only. In your case you will run sp on Node1 (does not matter which node, but always on 1 node, sort of "sticky sessions").

More information here: https://severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads

Upvotes: 3

Related Questions