Retries#

Some failures are worth trying again and most are not. The driver classifies a failure and decides whether, and how long, to wait before another attempt. It does not run the loop for you: only you know whether the work is safe to repeat, and whether it had already written something.

Conflicts are expected#

Two transactions that write the same element at once cannot both succeed. Under REPEATABLE READ or SERIALIZABLE, and for some concurrent graph writes under any level, the loser fails with SQLSTATE 40001:

>>> import psycopg
>>> dsn = "host=localhost dbname=docs user=agens"
>>> first, second = agensgraph.connect(dsn), agensgraph.connect(dsn)
>>> for c in (first, second):
...     c.graph("moviekb"); c.commit()
...     c.isolation_level = psycopg.IsolationLevel.REPEATABLE_READ
>>> first.execute_query("MATCH (m:movie {title: 'Inception'}) RETURN m.year").records
[(2010,)]
>>> second.execute_query("MATCH (m:movie {title: 'Inception'}) SET m.seen = 1")
Result(records=[], ...)
>>> second.commit()
>>> try:
...     first.execute_query("MATCH (m:movie {title: 'Inception'}) SET m.seen = 2")
... except psycopg.Error as exc:
...     conflict = exc
>>> conflict.sqlstate, str(conflict)
('40001', 'could not serialize access due to concurrent update')
>>> first.rollback()

That is the server working as designed, not a fault, and running the whole transaction again is the correct response. The server has no SQLSTATE of its own for graph conflicts, so a graph write conflict is this same 40001.

Classifying a failure#

retryability(exc) answers with one of six categories, each a different recovery:

>>> from agensgraph import retryability
>>> retryability(conflict)
<Retryability.SAFE: 'safe'>

`Retryability`	What to do	For
`SAFE`	roll back and run it again, on the same connection	`40001` serialization failure, `40P01` deadlock
`RECONNECT`	take a new connection, then run it again	class `08` connection failures, `57P01`, `57P02`, `57P03`, `57P05`, `25P03`, a lost connection
`BACKPRESSURE`	run it again, but wait longer	`53200`, `53300` too many connections, `55P03` lock not available, `55006`, a pool with no connection to give
`RESET_STATE`	keep the connection, clear the state that went stale	`26000` prepared statement gone; `StaleLabelCache`: call `refresh_labels()`
`UNKNOWN`	find out what happened before doing anything	`40003`, `08007`, a connection lost after writing
`FATAL`	do not retry: it will fail the same way	everything else, including syntax, data and constraint errors, `57014`

The categories are richer than a yes or no because the recoveries differ, and getting one wrong is worse than not retrying: 26000 looks as though reconnecting fixes it, because a new connection has no stale statements, but reconnecting hides the cause and it comes back. is_retryable(exc) is the yes or no, for code that only needs that, and each category has needs_new_connection, keeps_connection, clears_prepared_statements and wants_longer_delay.

Two arguments change the answer, and both matter:

wrote=True says the transaction had already written when it failed. A lost connection is then UNKNOWN rather than RECONNECT, because the write may have committed; see below. A conflict stays SAFE, because a conflict means nothing committed.
merging=True says the statement was a MERGE. Two writers merging the same new element race, and the loser fails with 23505 (unique violation) or 42P07 (duplicate label); for a merge that is the same as a conflict, and becomes SAFE.

57014, a cancelled statement, is FATAL whatever cancelled it, your own deadline included: whether work that ran out of time should run again is your decision, not the driver’s.

A retry loop#

RetryPolicy makes the decisions: whether to retry, and how long to wait first. You keep the loop. With a pool:

pool = agensgraph.ConnectionPool("host=localhost dbname=docs user=agens", graph="moviekb",
                                 kwargs={"autocommit": True})
pool.open(wait=True)

import time
from agensgraph import Deadline, RetryPolicy

policy = RetryPolicy(attempts=3)        # the first try counts: one try and up to two retries
budget = Deadline(10.0)
previous = []
number = 1

while True:
    try:
        with pool.connection(deadline=budget) as work:
            result = work.execute_query(
                "MATCH (m:movie {title: %s}) SET m.views = coalesce(m.views, 0) + 1",
                ("Inception",),
            )
        policy.succeeded()
        break
    except Exception as exc:
        attempt = policy.decide(exc, number=number, wrote=True, remaining=budget.remaining())
        if not attempt.retry:
            if number > 1:
                raise policy.exhausted(exc, attempts=number, previous=previous)
            raise
        previous.append(exc)
        time.sleep(attempt.delay)
        number += 1

decide() returns an Attempt with retry, delay, recovery and a reason in words. It says no when the failure is not retryable, when this was the last attempt, when the process-wide allowance below is spent, or when what is left of remaining would not cover the wait and another attempt. Pass remaining, or a budget that has run out is retried anyway.

exhausted() marks the exception with how it got there and returns it: attempts, the earlier failures as previous_errors, and (reached max retries: N) added to its message, so a log says the work was retried rather than tried once:

psycopg.errors.SerializationFailure: could not serialize access due to concurrent update (reached max retries: 3)

In awaiting code the loop is the same, with await asyncio.sleep(attempt.delay).

How long it waits#

The delay is full jitter: a random wait between zero and a ceiling that doubles with each attempt, from base (0.05 seconds) up to cap (2 seconds). The cap is applied to the ceiling before the random draw, so it is a real bound; a BACKPRESSURE retry starts from twice the base, waiting longer without passing the cap. The randomness is what stops many clients that failed together from retrying together.

One allowance for the whole process#

A count of attempts is not a budget. Four layers each willing to try three times make eighty-one attempts out of one request, aimed at a server that is already struggling. So every RetryPolicy draws on one shared allowance, a TokenBucket of 100 tokens per process. A retry spends 14 tokens, or 5 for BACKPRESSURE, and retrying stops while the bucket is at half or below:

>>> from agensgraph import Retryability, TokenBucket
>>> bucket = TokenBucket(100)
>>> for _ in range(4):
...     bucket.spend(Retryability.SAFE)
>>> round(bucket.tokens()), bucket.allows_retry
(44, False)

Time refills it: an empty bucket fills in two minutes (refill= changes that), so retrying resumes on the timescale of a failover rather than of a request. policy.succeeded() pays a token back, which recovers it sooner; call it on every success.

The allowance is shared deliberately, and it throttles conflicts too. A service that expects many conflicts on one hot path can give that path its own: RetryPolicy(bucket=TokenBucket(...)).

Losing a connection while committing#

One failure cannot be retried blind: a COMMIT whose answer never arrived. The write may have committed or not, and doing it again could apply it twice. The way out is to keep the transaction’s id, and ask a different connection what became of it:

with conn.transaction():
    xid = conn.transaction_id(assign=True)     # keep this
    conn.execute_query("MATCH (m:movie {title: 'Inception'}) SET m.checked = true")

>>> with agensgraph.connect("host=localhost dbname=docs user=agens", autocommit=True) as other:
...     other.resolve_commit(xid)
<CommitOutcome.COMMITTED: 'committed'>

`CommitOutcome`	Means	Do
`COMMITTED`	it landed	do not retry
`ABORTED`	it did not	retry, as for a conflict
`IN_PROGRESS`	the server has not finished with it	wait, and ask again
`UNKNOWN`	the server no longer knows	report it; it is not the same as aborted

outcome.safe_to_retry is true only for ABORTED, and outcome.is_settled for COMMITTED and ABORTED.

transaction_id(assign=True) gives the transaction an id at once, which costs one small statement; a transaction that writes gets one anyway. Call it inside the transaction you want to resolve. In the retry loop above, a lost connection after a write is UNKNOWN because of wrote=True, and that is the point at which to ask.