[prev in list] [next in list] [prev in thread] [next in thread]
List: kde-commits
Subject: [akonadi] server/src/storage: Handle database transaction deadlocks
From: Dan_Vrátil <dvratil () redhat ! com>
Date: 2014-02-28 18:30:43
Message-ID: E1WJSCp-0004Dp-HE () scm ! kde ! org
[Download RAW message or body]
Git commit 86dc7bc3f52ce61d2372673aa18c42768200e146 by Dan Vrátil.
Committed on 27/02/2014 at 16:39.
Pushed by dvratil into branch 'master'.
Handle database transaction deadlocks
Two concurrent database transactions can cause a database deadlock under
certain conditions. Both MySQL and PostgreSQL can detect the deadlock and
will automatically rollback one of the offending transactions. Very often
we've seen atime update in ItemRetriever to cause a deadlock with a
large transaction updating or inserting items, especially from maildir
resource.
According to documentation of both databases, transaction deadlocks are
nothing dangerous and applications should be able to handle them by trying
to replay the rolled-back transaction again.
For that reason we now store all QSqlQueries executed within a single
transaction into DataStore, so that in case of error we can ask DataStore
to execute them all again within a new transaction. After each transaction
is committed or rolled back, DataStore will clear it's query cache.
Queries executed outside transactions are not recorded. Also when using
SQLite, the queries are not recorded either, because SQLite does not support
concurrent transaction and we serialize them through a global lock in
DataStore, so no deadlock can occur.
This commit does not solve the actual problem that we somewhere generate a
huge and slow transaction that causes the deadlock but makes Akonadi able
to handle it without errors. So far I was not able to find where the big
transaction comes from.
M +87 -4 server/src/storage/datastore.cpp
M +31 -0 server/src/storage/datastore.h
M +48 -2 server/src/storage/querybuilder.cpp
M +2 -0 server/src/storage/querybuilder.h
http://commits.kde.org/akonadi/86dc7bc3f52ce61d2372673aa18c42768200e146
diff --git a/server/src/storage/datastore.cpp b/server/src/storage/datastore.cpp
index 75427db..762f9ec 100644
--- a/server/src/storage/datastore.cpp
+++ b/server/src/storage/datastore.cpp
@@ -76,7 +76,6 @@ DataStore::DataStore()
{
open();
- m_transactionLevel = 0;
NotificationManager::self()->connectNotificationCollector( mNotificationCollector \
);
if ( DbConfig::configuredDatabase()->driverName() == QLatin1String( "QMYSQL" ) ) {
@@ -1079,6 +1078,87 @@ QDateTime DataStore::dateTimeToQDateTime( const QByteArray \
&dateTime )
return QDateTime::fromString( QString::fromLatin1( dateTime ), QLatin1String( \
"yyyy-MM-dd hh:mm:ss" ) ); }
+void DataStore::addQueryToTransaction( const QSqlQuery &query, bool isBatch )
+{
+ DbType::Type dbType = DbType::type( m_database );
+ // This is used for replaying deadlocked transactions, so only record queries
+ // for backends that support concurrent transactions.
+ if ( !inTransaction() || ( dbType != DbType::MySQL && dbType != DbType::PostgreSQL \
) ) { + return;
+ }
+
+ m_transactionQueries.append( qMakePair( query, isBatch ) );
+}
+
+QSqlQuery DataStore::retryLastTransaction()
+{
+ DbType::Type dbType = DbType::type( m_database );
+ if ( !inTransaction() || ( dbType != DbType::MySQL && dbType != DbType::PostgreSQL \
) ) { + return QSqlQuery();
+ }
+
+ // The database has rolled back the actual transaction, so reset the counter
+ // to 0 and start a new one in beginTransaction(). Then restore the level
+ // because this has to be completely transparent to the original caller
+ const int oldTransactionLevel = m_transactionLevel;
+ m_transactionLevel = 0;
+ if ( !beginTransaction() ) {
+ m_transactionLevel = oldTransactionLevel;
+ return QSqlQuery();
+ }
+ m_transactionLevel = oldTransactionLevel;
+
+ QSqlQuery ret;
+ typedef QPair<QSqlQuery, bool> QueryBoolPair;
+ QMutableVectorIterator<QueryBoolPair> iter( m_transactionQueries );
+ while ( iter.hasNext() ) {
+ iter.next();
+ QSqlQuery query = iter.value().first;
+ const bool isBatch = iter.value().second;
+
+ // Make sure the query is ready to be executed again
+ if ( query.isActive() ) {
+ query.finish();
+ }
+
+ bool res = false;
+ if ( isBatch ) {
+ // QSqlQuery::execBatch() does not reset lastError(), so for the sake
+ // of transparency (make it look to the caller like if the query was
+ // successful the first time), we create a copy of the original query,
+ // which has lastError empty.
+ QSqlQuery copiedQuery( m_database );
+ copiedQuery.prepare( query.executedQuery() );
+ const QVariantList values = query.boundValues().values();
+ for (int i = 0; i < values.size(); ++i) {
+ copiedQuery.bindValue(i, values[i]);
+ }
+ query = copiedQuery;
+ res = query.execBatch();
+ } else {
+ res = query.exec();
+ }
+
+ if ( !res ) {
+ // Don't do another deadlock detection here, just give up.
+ akError() << "DATABASE ERROR:";
+ akError() << " Error code:" << query.lastError().number();
+ akError() << " DB error: " << query.lastError().databaseText();
+ akError() << " Error text:" << query.lastError().text();
+ akError() << " Query:" << query.executedQuery();
+
+ // Return the last query, because that's what caller expects to retrieve
+ // from QueryBuilder. It is in error state anyway.
+ return m_transactionQueries.last().first;
+ }
+
+ // Update the query in the list
+ iter.setValue( qMakePair( query, isBatch ) );
+ }
+
+ return m_transactionQueries.last().first;
+}
+
bool DataStore::beginTransaction()
{
if ( !m_dbOpened ) {
@@ -1095,7 +1175,7 @@ bool DataStore::beginTransaction()
}
}
- m_transactionLevel++;
+ ++m_transactionLevel;
return true;
}
@@ -1111,7 +1191,7 @@ bool DataStore::rollbackTransaction()
return false;
}
- m_transactionLevel--;
+ --m_transactionLevel;
if ( m_transactionLevel == 0 ) {
QSqlDriver *driver = m_database.driver();
@@ -1121,8 +1201,9 @@ bool DataStore::rollbackTransaction()
debugLastDbError( "DataStore::rollbackTransaction" );
return false;
}
-
TRANSACTION_MUTEX_UNLOCK;
+
+ m_transactionQueries.clear();
}
return true;
@@ -1149,6 +1230,8 @@ bool DataStore::commitTransaction()
TRANSACTION_MUTEX_UNLOCK;
Q_EMIT transactionCommitted();
}
+
+ m_transactionQueries.clear();
}
m_transactionLevel--;
diff --git a/server/src/storage/datastore.h b/server/src/storage/datastore.h
index e6916fa..51ea3cb 100644
--- a/server/src/storage/datastore.h
+++ b/server/src/storage/datastore.h
@@ -292,6 +292,32 @@ protected:
*/
static QDateTime dateTimeToQDateTime( const QByteArray &dateTime );
+
+ /**
+ * Adds the @p query to current transaction, so that it can be replayed in
+ * case the transaction deadlocks or timeouts.
+ *
+ * When DataStore is not in transaction or SQLite is configured, this method
+ * does nothing.
+ *
+ * All queries will automatically be removed when transaction is committed.
+ *
+ * This method should only be used by QueryBuilder.
+ */
+ void addQueryToTransaction( const QSqlQuery &query, bool isBatch );
+
+ /**
+ * Tries to execute all queries from last transaction again. If any of the
+ * queries fails, the entire transaction is rolled back and fails.
+ *
+ * This method can only be used by QueryBuilder when database rolls back
+ * transaction due to deadlock or timeout.
+ *
+ * @return Returns an invalid query when error occurs, or the last replayed
+ * query on success.
+ */
+ QSqlQuery retryLastTransaction();
+
private Q_SLOTS:
void sendKeepAliveQuery();
@@ -300,10 +326,15 @@ private:
QSqlDatabase m_database;
bool m_dbOpened;
uint m_transactionLevel;
+ QVector<QPair<QSqlQuery,bool /* isBatch */> > m_transactionQueries;
QByteArray mSessionId;
NotificationCollector *mNotificationCollector;
QTimer *m_keepAliveTimer;
static bool s_hasForeignKeyConstraints;
+
+ // Gives QueryBuilder access to addQueryToTransaction() and \
retryLastTransaction() + friend class QueryBuilder;
+
};
} // namespace Server
diff --git a/server/src/storage/querybuilder.cpp \
b/server/src/storage/querybuilder.cpp index d117e11..0abad4a 100644
--- a/server/src/storage/querybuilder.cpp
+++ b/server/src/storage/querybuilder.cpp
@@ -320,6 +320,17 @@ QString QueryBuilder::buildQuery()
return statement;
}
+bool QueryBuilder::retryLastTransaction()
+{
+#ifndef QUERYBUILDER_UNITTEST
+ mQuery = DataStore::self()->retryLastTransaction();
+ return !mQuery.lastError().isValid();
+#else
+ return true;
+#endif
+}
+
+
bool QueryBuilder::exec()
{
const QString statement = buildQuery();
@@ -342,7 +353,9 @@ bool QueryBuilder::exec()
}
//akDebug() << QString::fromLatin1( ":%1" ).arg( i ) << mBindValues[i];
}
+
bool ret;
+
if ( StorageDebugger::instance()->isSQLDebuggingEnabled() ) {
QTime t;
t.start();
@@ -352,7 +365,6 @@ bool QueryBuilder::exec()
ret = mQuery.exec();
}
StorageDebugger::instance()->queryExecuted( mQuery, t.elapsed() );
-
} else {
StorageDebugger::instance()->incSequence();
if ( isBatch ) {
@@ -362,8 +374,42 @@ bool QueryBuilder::exec()
}
}
+ // Add the query to DataStore so that we can replay it in case transaction \
deadlocks. + // The method does nothing when this query is not executed within a \
transaction. + // We don't care whether the query was successful or not. In case of \
error, the caller + // will rollback the transaction anyway, and all cached queries \
will be removed. + DataStore::self()->addQueryToTransaction( mQuery, isBatch );
+
if ( !ret ) {
- akError() << "Error during executing query" << statement << ": " << \
mQuery.lastError().text(); + // Handle transaction deadlocks and timeouts by \
attempting to replay the transaction. + if ( mDatabaseType == DbType::PostgreSQL ) \
{ + const QString dbError = mQuery.lastError().databaseText();
+ if ( dbError.contains( QLatin1String( "40P01" /* deadlock_detected */ ) ) ) {
+ akDebug() << "QueryBuilder::exec(): database reported transaction deadlock, \
retrying transaction"; + akDebug() << mQuery.lastError().text();
+ return retryLastTransaction();
+ }
+ } else if ( mDatabaseType == DbType::MySQL ) {
+ const int error = mQuery.lastError().number();
+ if ( error == 1213 /* ER_LOCK_DEADLOCK */ ) {
+ akDebug() << "QueryBuilder::exec(): database reported transaction deadlock, \
retrying transaction"; + akDebug() << mQuery.lastError().text();
+ return retryLastTransaction();
+ } else if ( error == 1205 /* ER_LOCK_WAIT_TIMEOUT */ ) {
+ akDebug() << "QueryBuilder::exec(): database reported transaction timeout, \
retrying transaction"; + akDebug() << mQuery.lastError().text();
+ return retryLastTransaction();
+ }
+ } else if ( mDatabaseType == DbType::Sqlite ) {
+ // We can't have a transaction deadlock in SQLite, because it does not support
+ // concurrent transactions and DataStore serializes them through a global \
lock. + }
+
+ akError() << "DATABASE ERROR:";
+ akError() << " Error code:" << mQuery.lastError().number();
+ akError() << " DB error: " << mQuery.lastError().databaseText();
+ akError() << " Error text:" << mQuery.lastError().text();
+ akError() << " Query:" << statement;
return false;
}
#else
diff --git a/server/src/storage/querybuilder.h b/server/src/storage/querybuilder.h
index d0f7b19..235a099 100644
--- a/server/src/storage/querybuilder.h
+++ b/server/src/storage/querybuilder.h
@@ -244,6 +244,8 @@ class QueryBuilder
*/
void sqliteAdaptUpdateJoin( Query::Condition &cond );
+ bool retryLastTransaction();
+
private:
QString mTable;
DbType::Type mDatabaseType;
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic