From 66f7e5799023d6f78ca42ab8bca0f6640c556933 Mon Sep 17 00:00:00 2001 From: jackpoz Date: Sat, 10 Feb 2018 16:07:56 +0100 Subject: 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) --- src/server/database/Database/Transaction.cpp | 31 ++++++++++++++++++++++++---- 1 file changed, 27 insertions(+), 4 deletions(-) (limited to 'src/server/database/Database/Transaction.cpp') 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 +#include +#include 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 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 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. -- cgit v1.2.3