Shared/Database: Improve dead-lock error handling

Improve dead-lock error handling in async transactions by retrying the transaction for up to 60 seconds instead of just 5 times

(cherry picked from commit 54b0b8f5ead41f936b12e97d1b4fbacd9b89ab53)

1 files changed, 27 insertions, 4 deletions

diff --git a/src/server/database/Database/Transaction.cpp b/src/server/database/Database/Transaction.cpp
index da72b018ea6..3dc0b6e01ec 100644
--- a/src/server/database/Database/Transaction.cpp
+++ b/src/server/database/Database/Transaction.cpp
@@ -16,12 +16,18 @@
  */
 
 #include "Transaction.h"
+#include "Log.h"
 #include "MySQLConnection.h"
 #include "PreparedStatement.h"
+#include "Timer.h"
 #include <mysqld_error.h>
+#include <sstream>
+#include <thread>
 
 std::mutex TransactionTask::_deadlockLock;
 
+#define DEADLOCK_MAX_RETRY_TIME_MS 60000
+
 //- Append a raw ad-hoc query to the transaction
 void TransactionBase::Append(char const* sql)
 {
@@ -71,12 +77,22 @@ bool TransactionTask::Execute()
 
     if (errorCode == ER_LOCK_DEADLOCK)
     {
+        std::ostringstream threadIdStream;
+        threadIdStream << std::this_thread::get_id();
+        std::string threadId = threadIdStream.str();
+
         // Make sure only 1 async thread retries a transaction so they don't keep dead-locking each other
         std::lock_guard<std::mutex> lock(_deadlockLock);
-        uint8 loopBreaker = 5;  // Handle MySQL Errno 1213 without extending deadlock to the core itself
-        for (uint8 i = 0; i < loopBreaker; ++i)
+
+        for (uint32 loopDuration = 0, startMSTime = getMSTime(); loopDuration <= DEADLOCK_MAX_RETRY_TIME_MS; loopDuration = GetMSTimeDiffToNow(startMSTime))
+        {
             if (!TryExecute())
                 return true;
+
+            TC_LOG_WARN("sql.sql", "Deadlocked SQL Transaction, retrying. Loop timer: %u ms, Thread Id: %s", loopDuration, threadId.c_str());
+        }
+
+        TC_LOG_ERROR("sql.sql", "Fatal deadlocked SQL Transaction, it will not be retried anymore. Thread Id: %s", threadId.c_str());
     }
 
     // Clean up now.
@@ -106,17 +122,24 @@ bool TransactionWithResultTask::Execute()
 
     if (errorCode == ER_LOCK_DEADLOCK)
     {
+        std::ostringstream threadIdStream;
+        threadIdStream << std::this_thread::get_id();
+        std::string threadId = threadIdStream.str();
+
         // Make sure only 1 async thread retries a transaction so they don't keep dead-locking each other
         std::lock_guard<std::mutex> lock(_deadlockLock);
-        uint8 loopBreaker = 5;  // Handle MySQL Errno 1213 without extending deadlock to the core itself
-        for (uint8 i = 0; i < loopBreaker; ++i)
+        for (uint32 loopDuration = 0, startMSTime = getMSTime(); loopDuration <= DEADLOCK_MAX_RETRY_TIME_MS; loopDuration = GetMSTimeDiffToNow(startMSTime))
         {
             if (!TryExecute())
             {
                 m_result.set_value(true);
                 return true;
             }
+
+            TC_LOG_WARN("sql.sql", "Deadlocked SQL Transaction, retrying. Loop timer: %u ms, Thread Id: %s", loopDuration, threadId.c_str());
         }
+
+        TC_LOG_ERROR("sql.sql", "Fatal deadlocked SQL Transaction, it will not be retried anymore. Thread Id: %s", threadId.c_str());
     }
 
     // Clean up now.