Transactions

Transactions are psycopg’s, unchanged. A Cypher statement and a SQL statement are both ordinary statements to PostgreSQL, so they share a transaction like any two statements, and a graph write commits or rolls back together with the relational writes beside it.

The default: a transaction opens by itself

A new connection is not in autocommit. The first statement opens a transaction, and it stays open until you end it:

conn.execute_query("CREATE (:movie {title: 'Tenet', year: 2020})")
conn.execute("INSERT INTO rating VALUES ('Tenet', 'critics', 7.3)")
conn.commit()          # both, or with rollback() neither

Nothing is visible to another session before commit(). Closing the connection, or leaving a with agensgraph.connect(...) block because of an exception, rolls back.

Because selecting a graph is a statement too, a rollback undoes conn.graph() if it ran in the same transaction. Commit after selecting it.

Transaction blocks

conn.transaction() makes a block a transaction of its own. It commits when the block ends, and rolls back if the block raises:

with conn.transaction():
    conn.execute_query("CREATE (:movie {title: 'Oppenheimer', year: 2023})")
    conn.execute_query(
        "MATCH (p:person {name: 'Christopher Nolan'}), (m:movie {title: 'Oppenheimer'})"
        " CREATE (p)-[:directed]->(m)"
    )

A block inside another block is a savepoint. When the inner block raises, only its own work is undone, and the outer block carries on:

with conn.transaction():
    conn.execute_query("CREATE (:probe {n: 1})")
    try:
        with conn.transaction():
            conn.execute_query("CREATE (:probe {n: 2})")
            raise RuntimeError("changed my mind")
    except RuntimeError:
        pass

>>> conn.execute_query("MATCH (n:probe) RETURN n.n").records
[(1,)]

transaction(force_rollback=True) runs a block and then undoes it whatever happens, which is useful in tests. The driver’s own read_only_transaction() is built on the same mechanism.

Autocommit

With autocommit=True every statement commits as soon as it finishes:

auto = agensgraph.connect("host=localhost dbname=docs user=agens", autocommit=True)

Some statements cannot run inside a transaction at all, such as CREATE DATABASE and VACUUM; they need autocommit. A long-lived connection that mostly reads, such as one that waits for notifications, is simpler in autocommit too, because nothing it does holds a transaction open.

conn.transaction() still works in autocommit and opens a real transaction for the block. That is the way to group several statements on such a connection, and the way to give stream() the transaction it needs.

Change the mode with conn.autocommit = True or conn.set_autocommit(True), between transactions.

Isolation

The isolation level is psycopg’s setting, applied to each transaction the connection opens:

import psycopg

conn.commit()          # settings like this one change between transactions
conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE

psycopg refuses to change it, or read_only or autocommit, while a transaction is open.

Concurrent writes to the same elements under REPEATABLE READ or SERIALIZABLE are refused with SQLSTATE 40001, a serialization failure. That is the server’s ordinary answer to a conflict, and it is safe to run the transaction again: Retries covers how to decide and how long to wait.

Read-only transactions

conn.read_only = True makes every transaction the connection opens read-only. The server then refuses every write, whatever language it is written in:

>>> conn.read_only = True
>>> conn.execute_query("CREATE (:movie {title: 'Not today'})")
Traceback (most recent call last):
  ...
agensgraph.errors.ReadOnlyGraphWrite: cannot write to a graph in a read-only transaction
>>> conn.rollback()
>>> conn.read_only = False

The server reports a refused graph write with a code it shares with every other refused write and a message that varies between releases, so the driver raises a class of its own for it, ReadOnlyGraphWrite. It is a subclass of psycopg’s ReadOnlySqlTransaction, so code catching the psycopg class keeps working. A refused SQL write, such as an INSERT, is psycopg’s ReadOnlySqlTransaction itself.

To run one statement you did not write, such as a query from a language model, use read_only_transaction(), which adds the checks a read-only transaction alone does not make.

Transaction ids

conn.transaction_id() returns the id of the open transaction, as an int, once it has one. A transaction gets an id when it first writes, so a read-only one has none:

with conn.transaction():
    before = conn.transaction_id()
    conn.execute_query("CREATE (:probe {n: 3})")
    after = conn.transaction_id()

>>> before is None, isinstance(after, int)
(True, True)

transaction_id(assign=True) gives the transaction an id at once. Keeping the id is what makes it possible to find out, from another connection, whether a commit whose answer never arrived took effect; see Losing a connection while committing.

Two-phase commit

psycopg’s two-phase commit methods work unchanged, including for a graph write:

conn.graph("moviekb")
conn.commit()                                       # start with no transaction open

conn.tpc_begin(conn.xid(0, "order-7", "graph"))
conn.execute_query("CREATE (:movie {title: 'Prepared'})")
conn.tpc_prepare()                                  # durable, but not yet visible
conn.tpc_commit()                                   # or tpc_rollback()

tpc_begin() must be called with no transaction open, so commit after selecting the graph. Between tpc_prepare() and the commit, the transaction survives the connection: another connection lists it with tpc_recover() and can finish it with tpc_commit(xid).

The server allows no prepared transactions unless max_prepared_transactions is above zero, and the default is zero. On such a server tpc_prepare() fails:

psycopg.NotSupportedError: prepared transactions are disabled