feat(DB): Move retry logic to the QueryBuilderartonge/feat/add_retry_support_to_query_builder

Signed-off-by: Louis Chemineau <louis@chmn.me>

3 files changed, 42 insertions, 24 deletions

diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php
index c2818911ccf..bae038aab1b 100644
--- a/lib/private/DB/QueryBuilder/QueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/QueryBuilder.php
@@ -30,6 +30,7 @@
  */
 namespace OC\DB\QueryBuilder;
 
+use Doctrine\DBAL\Exception\RetryableException;
 use Doctrine\DBAL\Platforms\MySQLPlatform;
 use Doctrine\DBAL\Platforms\OraclePlatform;
 use Doctrine\DBAL\Platforms\PostgreSQL94Platform;
@@ -76,6 +77,8 @@ class QueryBuilder implements IQueryBuilder {
 	/** @var string */
 	protected $lastInsertedTable;
 
+	private int $retryLimit = 0;
+
 	/**
 	 * Initializes a new QueryBuilder.
 	 *
@@ -277,13 +280,36 @@ class QueryBuilder implements IQueryBuilder {
 			]);
 		}
 
-		$result = $this->queryBuilder->execute();
+		for ($i = 0; $i <= $this->retryLimit; $i++) {
+			try {
+				$result = $this->queryBuilder->execute();
+				break;
+			} catch (RetryableException $e) {
+				// Throw if we reached retryLimit.
+				if ($i === $this->retryLimit - 1) {
+					throw $e;
+				}
+
+				$this->logger->warning("Retrying query after a RetryableException", ['exception' => $e]);
+
+				// Incrementally sleep a bit to give some time to the other transaction to finish.
+				usleep(100 * 1000 * ($i + 1));
+			}
+		}
+
 		if (is_int($result)) {
 			return $result;
 		}
 		return new ResultAdapter($result);
 	}
 
+	/**
+	 * @param int $retryLimit - Retry the query up to $retryLimit times in case of a RetryableException. Initially equal to 4.
+	 */
+	public function setRetryLimit(int $retryLimit): void {
+		$this->retryLimit = $retryLimit;
+	}
+
 	public function executeQuery(): IResult {
 		if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
 			throw new \RuntimeException('Invalid query type, expected SELECT query');
diff --git a/lib/private/Files/Cache/Cache.php b/lib/private/Files/Cache/Cache.php
index 8f0f962a3b7..594e71a73b1 100644
--- a/lib/private/Files/Cache/Cache.php
+++ b/lib/private/Files/Cache/Cache.php
@@ -40,7 +40,6 @@
 
 namespace OC\Files\Cache;
 
-use Doctrine\DBAL\Exception\RetryableException;
 use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
 use OC\Files\Search\SearchComparison;
 use OC\Files\Search\SearchQuery;
@@ -715,28 +714,14 @@ class Cache implements ICache {
 					$query->set('encrypted', $query->createNamedParameter(0, IQueryBuilder::PARAM_INT));
 				}
 
-				// Retry transaction in case of RetryableException like deadlocks.
-				// Retry up to 4 times because we should receive up to 4 concurrent requests from the frontend
-				$retryLimit = 4;
-				for ($i = 1; $i <= $retryLimit; $i++) {
-					try {
-						$this->connection->beginTransaction();
-						$query->executeStatement();
-						break;
-					} catch (\OC\DatabaseException $e) {
-						$this->connection->rollBack();
-						throw $e;
-					} catch (RetryableException $e) {
-						// Simply throw if we already retried 4 times.
-						if ($i === $retryLimit) {
-							throw $e;
-						}
-
-						$this->connection->rollBack();
-
-						// Sleep a bit to give some time to the other transaction to finish.
-						usleep(100 * 1000 * $i);
-					}
+				$query->setRetryLimit(4);
+
+				try {
+					$this->connection->beginTransaction();
+					$query->executeStatement();
+				} catch (\OC\DatabaseException $e) {
+					$this->connection->rollBack();
+					throw $e;
 				}
 			} else {
 				$this->connection->beginTransaction();
diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php
index dba5f390d6e..8aef47ddbbf 100644
--- a/lib/public/DB/QueryBuilder/IQueryBuilder.php
+++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php
@@ -185,6 +185,13 @@ interface IQueryBuilder {
 	public function executeQuery(): IResult;
 
 	/**
+	 * @since 30.0.0
+	 *
+	 * @param int $retryLimit - Retry the query up to $retryLimit times in case of a RetryableException. Won't retry by default.
+	 */
+	public function setRetryLimit(int $retryLimit): void;
+
+	/**
 	 * Execute insert, update and delete statements
 	 *
 	 * @return int the number of affected rows