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