Transactions & COPY

SQL Reference: Transactions

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

Transaction Control

SQL

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

▶ Run

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

SQL

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

▶ Run

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, taken when the transaction starts (PostgreSQL takes it at the transaction’s first query)
SERIALIZABLE	Not implemented — downgraded to REPEATABLE READ

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

SQL

-- 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 Difference: some write conflicts fail at COMMIT, or not at all

Under REPEATABLE READ, DB9 handles a conflict with a transaction that committed after your snapshot differently from PostgreSQL. Where PostgreSQL fails the statement with 40001, DB9 usually lets the statement succeed and fails the COMMIT with 40001 could not serialize access due to concurrent update. A few cases raise no error at all, or fail at COMMIT where PostgreSQL commits:

Statement, after another transaction committed a change	PostgreSQL	DB9
UPDATE of a row it updated	40001 at the statement	Succeeds, computed from the snapshot’s version of the row; COMMIT fails with 40001
DELETE of a row it updated	40001 at the statement	Succeeds against the latest version (RETURNING shows it); COMMIT fails with 40001
SELECT ... FOR UPDATE of a row it updated	40001 at the statement	Returns the snapshot’s version; COMMIT fails with 40001
UPDATE, DELETE or SELECT ... FOR UPDATE of a row it deleted	40001 at the statement	Matches no row; COMMIT fails with 40001
INSERT ... ON CONFLICT DO UPDATE on a row it updated	40001 at the statement	Succeeds against the latest version (RETURNING shows it); COMMIT fails with 40001
INSERT ... ON CONFLICT DO UPDATE on a row it deleted	Inserts the row	Inserts the row; COMMIT fails with 40001
INSERT ... ON CONFLICT DO NOTHING on a row it updated	40001 at the statement	Does nothing; COMMIT succeeds
INSERT ... ON CONFLICT DO NOTHING on a row it deleted	Inserts the row	Does nothing; COMMIT succeeds
Plain INSERT of the key of a row it deleted	Inserts the row	Inserts the row; COMMIT fails with 40001
INSERT ... ON CONFLICT DO UPDATE of a key it inserted	40001 at the statement	Succeeds against the other transaction’s row (RETURNING shows it); COMMIT fails with 40001

SQL

-- Session A                                        -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT v FROM t WHERE id = 1;  -- 100
                                                    UPDATE t SET v = v + 1 WHERE id = 1;  -- commits
UPDATE t SET v = v + 1000 WHERE id = 1 RETURNING v;  -- 1100, computed from the snapshot
COMMIT;
-- ERROR: could not serialize access due to concurrent update
-- SQLSTATE: 40001

A failed COMMIT rolls the transaction back, so no update is lost; the row above keeps 101. Retry the whole transaction when COMMIT returns 40001, not only when a statement does, and do not act on values read inside the transaction until COMMIT succeeds. Under READ COMMITTED, an UPDATE, SELECT ... FOR UPDATE or INSERT ... ON CONFLICT DO UPDATE waits for the other transaction’s row lock and then works on the latest version, as in PostgreSQL. INSERT ... ON CONFLICT DO NOTHING does not wait, at that level or in autocommit mode: against another transaction’s uncommitted UPDATE or DELETE of the conflicting row it returns at once and inserts nothing, so if that DELETE commits, the key ends up with no row at all. PostgreSQL waits for the other transaction and, once a DELETE commits, inserts the row.

DB9 Difference: SERIALIZABLE is downgraded, not rejected

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:

SQL

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ
SHOW transaction_isolation;  -- repeatable read — the level actually in effect

SHOW transaction_isolation reports the level DB9 applied rather than the one requested, so reading it back confirms the downgrade at runtime. Where you relied on serializability, take SELECT ... FOR UPDATE locks on rows that the conflicting transactions also update (the table needs a primary key, see Row Locks), or add a unique constraint that makes the conflicting write fail. At the downgraded level the transaction that loses a SELECT ... FOR UPDATE race fails at COMMIT with 40001 rather than at the statement (see above), so retry on that error at COMMIT as well as at the statement. A unique-constraint conflict fails the INSERT with 23505, as in PostgreSQL. A row that is only locked and never changed raises no conflict at this level, in PostgreSQL as well: the waiting transaction gets the lock, continues on its old snapshot and commits.

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:

SQL

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.

Row Locks

SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE, with or without NOWAIT or SKIP LOCKED, need a primary key on the table they lock. On a table without one they fail as soon as the query returns a row, where PostgreSQL locks the rows. A query that matches nothing returns no rows without an error, so test against data:

SQL

CREATE TABLE notes (id INT, body TEXT);   -- no primary key
INSERT INTO notes VALUES (1, 'draft');
SELECT * FROM notes FOR UPDATE;
-- ERROR: FOR UPDATE/SHARE requires primary key
-- SQLSTATE: 0A000

A RANGE-partitioned table is the exception: locking through the parent works with or without a primary key, and another session’s NOWAIT and SKIP LOCKED see those locks. A partition without a primary key, queried directly, fails as above.

Sequence Behavior

Sequence gaps are expected

Sequence advances (nextval) are non-transactional — they survive transaction rollbacks, matching PostgreSQL behavior. If a transaction rolls back after calling nextval, the sequence value is permanently consumed. Do not rely on sequences for gap-free numbering.

A sequence with CACHE greater than 1 also hands each session its own block of values, as in PostgreSQL: with CACHE 10, two sessions drawing at the same time get 1, 2, 3 and 11, 12, 13, and the values a session reserved but did not use are skipped once it disconnects. Each HTTP SQL API request is a new session, so every request that calls nextval on such a sequence starts a new block. With the default CACHE 1, values follow call order across sessions unless the database turns on db9.sequence_windows.

Failed Transaction State

When a statement fails inside a transaction block, the transaction enters a failed state, and every later statement returns 25P02 (current transaction is aborted, commands ignored until end of transaction block). Only ROLLBACK (or ABORT), ROLLBACK TO SAVEPOINT, COMMIT and END are accepted. COMMIT and END roll the transaction back and report ROLLBACK. ROLLBACK TO SAVEPOINT undoes the work done after the savepoint and makes the transaction usable again. This matches PostgreSQL behavior, including after a serialization failure (40001) or deadlock (40P01) raised by a statement.

---

SQL Reference: COPY

Bulk data import and export.

SQL

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

-- Import from Parquet (requires CREATE EXTENSION parquet)
COPY table_name FROM 'fs9:///data/file.parquet' WITH (FORMAT parquet);

-- Export a table (PostgreSQL wire protocol only)
COPY table_name TO STDOUT WITH (FORMAT csv, HEADER);

Text, CSV, and Parquet formats are supported for COPY FROM; FORMAT binary is rejected with 0A000. A Parquet source must be an http://, https:// or fs9:// URL — a bare path such as '/data/file.parquet' fails with unsupported Parquet URL scheme. See Parquet Import.

COPY ... TO writes only to STDOUT, only in the table form (COPY table [(columns)] TO STDOUT), and only over the PostgreSQL wire protocol: COPY (SELECT ...) TO STDOUT is rejected, a file target fails, and the HTTP SQL API cannot carry the stream. See Migrate from PostgreSQL.