# 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:

```python
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()`](connecting.md#a-rollback-undoes-it)
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:

```python
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:

```python
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
```

```pycon
>>> 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()`](../production/untrusted-statements.md)
is built on the same mechanism.

## Autocommit

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

```python
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](../data/notifications.md), 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()`](running-statements.md#streaming-a-large-result) 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:

```python
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](../production/retries.md) 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:

```pycon
>>> 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()`](../production/untrusted-statements.md), 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:

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

```pycon
>>> 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](../production/retries.md#losing-a-connection-while-committing).

## Two-phase commit

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

```{code-block} python
:class: no-run

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:

```text
psycopg.NotSupportedError: prepared transactions are disabled
```