Why does CockroachDB keep telling me to "restart transaction"?

Your app moved from PostgreSQL to CockroachDB, and under load a new error starts showing up:

ERROR: restart transaction: TransactionRetryWithProtoRefreshError: ... RETRY_SERIALIZABLE
SQLSTATE: 40001

It never happened on Postgres, and retrying by hand works. Is something broken? No. This is how CockroachDB keeps serializable transactions correct across a distributed cluster, and your application is expected to handle it.

Why it happens

CockroachDB runs transactions at SERIALIZABLE isolation by default: the result must be as if transactions ran one after another. When two concurrent transactions conflict (one reads a key the other then writes, or both write the same keys), one of them may have to move its commit timestamp forward. If what it already read might have changed in the meantime, it can't commit safely and has to start over.

CockroachDB retries automatically when it can: single-statement (implicit) transactions whose results haven't been sent to the client yet (results are buffered up to 16 KiB by default). It can't retry for you when:

• the transaction is an explicit BEGIN … COMMIT with several statements, because your application logic ran between them;
• results have already been streamed back to the client.

Then it returns SQLSTATE 40001 with "restart transaction" in the message. Every retry error uses this code, whatever the detail (RETRY_SERIALIZABLE, RETRY_WRITE_TOO_OLD, ABORT_REASON_…).

PostgreSQL behaves the same way at SERIALIZABLE; it just defaults to READ COMMITTED, so most Postgres apps never see it.

Fix 1: retry the whole transaction in the app

Treat 40001 as "run it again", with a cap and backoff:

import time, psycopg

def run_tx(conn, fn, max_retries=5):
    for attempt in range(max_retries):
        try:
             conn.transaction():
                 fn(conn)
         psycopg.errors.SerializationFailure:
            time.sleep(( ** attempt) * )
     RuntimeError()