Transactions & COPY

SQL Reference: Transactions

Transaction control, savepoints, isolation semantics, and autocommit behavior.

Transaction Control

BEGIN;
-- ... your SQL statements ...
COMMIT;     -- persist all changes atomically
ROLLBACK;   -- discard all changes

Autocommit

Single statements outside an explicit BEGIN block run in implicit autocommit mode. The system begins a transaction, executes the statement, and commits (or rolls back on error). Retryable TiKV errors trigger automatic retry with backoff.

Savepoints

BEGIN;
INSERT INTO users (name) VALUES ('Alice');

SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('Bob');
ROLLBACK TO SAVEPOINT sp1;  -- Bob's insert is undone

RELEASE SAVEPOINT sp1;     -- destroys savepoint, merges state
COMMIT;  -- only Alice is committed

Savepoint behavior matches PostgreSQL: duplicate names are allowed (most recent is targeted), RELEASE destroys the named savepoint and all later ones, ROLLBACK TO re-establishes the savepoint for reuse.

Isolation Level

Transactions are pessimistic, and the default isolation level is READ COMMITTED.

Level	Behavior
`READ COMMITTED` (default)	Statement-level snapshots, as in PostgreSQL — each statement sees rows committed before it started
`READ UNCOMMITTED`	Behaves as `READ COMMITTED`, as in PostgreSQL
`REPEATABLE READ`	One snapshot held for the transaction’s lifetime
`SERIALIZABLE`	Not implemented — downgraded to `REPEATABLE READ`

Under READ COMMITTED, a statement later in the transaction sees another session’s committed writes:

-- Session A                          -- Session B
BEGIN;                                 -- default: READ COMMITTED
SELECT count(*) FROM t;  -- 1
                                       INSERT INTO t VALUES (2);  -- commits
SELECT count(*) FROM t;  -- 2
COMMIT;

Under REPEATABLE READ the same sequence returns 1 both times.

DB9 does not implement SERIALIZABLE. TiKV provides snapshot isolation, not PostgreSQL’s serializable snapshot isolation (SSI), so write skew is not detected.

On the PostgreSQL wire protocol the request is accepted with a warning rather than refused:

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ
SHOW transaction_isolation;  -- reports 'serializable', but the transaction runs at REPEATABLE READ

Because SHOW transaction_isolation echoes back serializable, the session cannot confirm the level actually in effect — the warning is the only signal. Where you relied on serializability, take explicit row locks with SELECT ... FOR UPDATE or add a unique constraint that makes the conflicting write fail.

Over the HTTP SQL API the same request is rejected instead: ERROR: SERIALIZABLE isolation level is not supported. Use REPEATABLE READ or READ COMMITTED instead.

Read-Only Transactions

BEGIN READ ONLY, SET TRANSACTION READ ONLY, and SET default_transaction_read_only = on all enable read-only transaction enforcement. DML and DDL writes are rejected with SQLSTATE 25006:

BEGIN READ ONLY;
INSERT INTO t VALUES (1);
-- ERROR: cannot execute INSERT in a read-only transaction
-- SQLSTATE: 25006
ROLLBACK;

For credential-level write restrictions that apply across sessions, issue a token with a read-only scope (db9 token create --scope mydb:ro) — see CLI reference.

Sequence Behavior

Failed Transaction State

When an error occurs in a transaction, it enters a failed state. Only ROLLBACK, COMMIT, and END are accepted. All other statements return an error. This matches PostgreSQL behavior.

SQL Reference: COPY

Bulk data import and export.

-- Import from file (via client)
COPY table_name FROM STDIN;

-- Import from Parquet (with parquet extension)
COPY table_name FROM '/data/file.parquet' FORMAT parquet;

Text, CSV, and Parquet formats are supported for COPY FROM.