Retrying transactions

On a synchronously replicated cluster a transaction can fail at COMMIT even though every statement succeeded. Applications must retry such transactions.

Which errors to retry#

Error	Meaning	Action
`1213` deadlock	a conflicting transaction won certification	retry the whole transaction
`1205` lock wait timeout	a row stayed locked too long	retry, then investigate long transactions
`1047` not ready	the server is rejoining the cluster	reconnect and retry
`2006`, `2013` connection lost	failover or maintenance; while connecting, also the connection rate limit	reconnect and retry, backing off at least a second when it happens at connect time

The pattern#

Retry the whole transaction, from BEGIN, a bounded number of times with a short, randomised pause.

python
import random, time
import mariadb

RETRYABLE = {1213, 1205, 1047, 2006, 2013}

def run_in_transaction(pool, work, attempts=5):
    for attempt in range(attempts):
        conn = pool.get_connection()
        try:
            conn.begin()
            result = work(conn)
            conn.commit()
            return result
        except mariadb.Error as exc:
            conn.rollback()
            if exc.errno not in RETRYABLE or attempt == attempts - 1:
                raise
            time.sleep(0.05 * 2 ** attempt * random.random())
        finally:
            conn.close()

Make the work repeatable#

A retried transaction runs twice from the application's point of view. Keep side effects — sending mail, calling another service — outside the transaction, or make them idempotent.

Reduce conflicts#

Keep transactions short, touch rows in a consistent order, and avoid hot counter rows that every request updates.