Transactions

BEGIN, COMMIT and ROLLBACK: save many changes together, or none of them.

• Category: Transactions
• Keywords: transaction, TRANSACTION, WORK, BEGIN TRANSACTION, BEGIN WORK, COMMIT WORK, ROLLBACK WORK, START, atomic, all or nothing, rollback, undo, FOR UPDATE, SELECT FOR UPDATE, SKIP LOCKED, NOWAIT, job queue, --per-table-reads, KDB_PER_TABLE_READS, SELECT 1 waits, readers wait, checkpoint, BEGIN without COMMIT, cannot be used inside a transaction, database is busy, table is busy, deadlock, duplicate key while busy, taken key, table lock, --reader-yield, KDB_READER_YIELD, reader yield, --snapshot-reads, KDB_SNAPSHOT_READS, snapshot, snapshot reads, MVCC, writers at the same time, group commit, lock, lock_timeout_ms, lock_timeout, SET lock_timeout, --lock-timeout, KDB_LOCK_TIMEOUT, X-KDB-Lock-Timeout, 55P03, idle_transaction_timeout_ms, idle_in_transaction_session_timeout, --idle-in-transaction-timeout, KDB_IDLE_IN_TRANSACTION_TIMEOUT, session, double entry, ledger, accounting, SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT, ROLLBACK TO, savepoints, 25P02, 3B001, isolation, isolation level, SERIALIZABLE, READ COMMITTED, SET TRANSACTION, BEGIN ISOLATION LEVEL, default_transaction_isolation, transaction_isolation, 40001, serialization_failure, 40P01, lost update, READ ONLY, BEGIN READ ONLY, read-only transaction, SET TRANSACTION READ ONLY, default_transaction_read_only, transaction_read_only, 25006, READ WRITE, LOCK TABLE, LOCK, IN SHARE MODE, ACCESS EXCLUSIVE, lock modes, LOCK TABLE NOWAIT, pg_locks, pg_blocking_pids, log_lock_waits, deadlock_timeout, --log-lock-waits, KDB_LOG_LOCK_WAITS, --deadlock-timeout, KDB_DEADLOCK_TIMEOUT, 25P01, who is blocking, lock waits, txn_spill_mb, --txn-spill-mb, KDB_TXN_SPILL_MB, transaction too big for memory, txn_spills, big transaction
• See also: Stored procedures, Triggers, Cursors, Sessions and cursors over HTTP, kdb sql and kdb shell, Constraints, 15. Transactions and accounting

A transaction is a group of changes that KDB saves together, or not at all. Nobody ever sees half of it.

Moving money is the classic case. A transfer is three changes: take money from one account, give it to the other, and write a ledger row (a row in the list of all transfers). If only the first change is saved, money is lost. Put them in a transaction.

• Start it with BEGIN. Save it with COMMIT. Undo it with ROLLBACK.
• When any statement in it fails, KDB undoes the whole transaction.
• KDB writes the changes to its log (the file where every change is saved first) only at COMMIT, in one write. After a crash or a power cut, a committed transaction is there completely, and an unfinished one is not there at all.

Example: Accounting: a transfer between two accounts, then check the books

Setup:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner STRING NOT NULL,
  opening DECIMAL(2) NOT NULL,
  balance DECIMAL(2) NOT NULL
);
CREATE TABLE ledger (
  id LONG PRIMARY KEY AUTO,
  from_account INT NOT NULL REFERENCES accounts(id),
  to_account INT NOT NULL REFERENCES accounts(id),
  amount DECIMAL(2) NOT NULL,
  note STRING,
  created TIMESTAMP DEFAULT now()
);
INSERT INTO accounts VALUES (1, 'Ram', 1000, 1000), (2, 'Sita', 0, 0), (3, 'Hari', 500, 500);

BEGIN;
UPDATE accounts SET balance = balance - 250.50 WHERE id = 1;
UPDATE accounts SET balance = balance + 250.50 WHERE id = 2;
INSERT INTO ledger (from_account, to_account, amount, note) VALUES (1, 2, 250.50, 'rent');
COMMIT;

-- Check the books: the total never changes, and every balance matches the ledger.
SELECT sum(opening) AS money_at_start, sum(balance) AS money_now FROM accounts;
SELECT a.id, a.owner, a.balance,
       a.opening + coalesce(i.total_in, 0) - coalesce(o.total_out, 0) AS from_ledger,
       a.balance = a.opening + coalesce(i.total_in, 0) - coalesce(o.total_out, 0) AS ok
FROM accounts a
LEFT JOIN (SELECT to_account, sum(amount) AS total_in FROM ledger GROUP BY to_account) i ON i.to_account = a.id
LEFT JOIN (SELECT from_account, sum(amount) AS total_out FROM ledger GROUP BY from_account) o ON o.from_account = a.id
ORDER BY a.id;

Result:

BEGIN

transaction started

UPDATE accounts SET balance = balance - 250.50 WHERE id = 1

1 row(s) updated

UPDATE accounts SET balance = balance + 250.50 WHERE id = 2

1 row(s) updated

INSERT INTO ledger (from_account, to_account, amount, note) VALUES (1, 2, 250.50, 'rent')

1 row(s) inserted

COMMIT

transaction committed: 3 change(s) saved

SELECT sum(opening) AS money_at_start, sum(balance) AS money_now FROM accounts

(1 row)

SELECT a.id, a.owner, a.balance, a.opening + coalesce(i.total_in, 0) - coalesce(o.total_out, 0) AS f

(3 rows)

BEGIN

Syntax:

BEGIN
BEGIN TRANSACTION
BEGIN WORK

Starts a transaction. The statements after it are saved only at COMMIT. BEGIN, BEGIN TRANSACTION and BEGIN WORK are the same.

• Returns: transaction started.
• Notes:
  • Inside the transaction you can use SELECT, INSERT, UPDATE, DELETE, CALL, SHOW ..., EXPLAIN, CHECK TABLE and cursors. Other statements are not allowed.
  • A SELECT inside the transaction sees its own changes. Other sessions do not see them until COMMIT.
  • Only one transaction can be open. A second BEGIN is an error, and the open transaction is rolled back.
  • While the transaction is open, other writers of the same tables wait (see one writer per table). Keep transactions short.
  • BEGIN cannot be used inside a procedure or trigger body: a CALL is already one transaction.
  • START TRANSACTION (the MySQL spelling) is not supported. Write BEGIN.

Example: The SELECT inside sees the change; after ROLLBACK it is gone (sample data: sample)

BEGIN TRANSACTION;
UPDATE securities SET sector = 'Banks' WHERE symbol = 'CITY';
SELECT symbol, sector FROM securities WHERE symbol = 'CITY';
ROLLBACK;
SELECT symbol, sector FROM securities WHERE symbol = 'CITY';

Result:

BEGIN

transaction started

UPDATE securities SET sector = 'Banks' WHERE symbol = 'CITY'

1 row(s) updated

SELECT symbol, sector FROM securities WHERE symbol = 'CITY'

(1 row)

ROLLBACK

transaction rolled back: 1 change(s) undone

SELECT symbol, sector FROM securities WHERE symbol = 'CITY'

(1 row)

Example: BEGIN twice (shows an error)

BEGIN;
BEGIN;

Result:

transaction started

Error: a transaction is already open; COMMIT or ROLLBACK it first (the transaction was rolled back)

COMMIT

Syntax:

COMMIT
COMMIT TRANSACTION
COMMIT WORK

Saves every change of the open transaction, with one write to the log, and ends the transaction.

• Returns: transaction committed: N change(s) saved. N counts the log records (about one per changed row, plus the changes made by triggers and procedures).
• Notes:
  • When COMMIT returns OK, the changes are safe on disk, also after a crash or a power cut.
  • COMMIT without an open transaction is an error.
  • If the disk is full at COMMIT, it returns an error and nothing is saved.
  • In a session (kdb shell, HTTP session) whose transaction was already rolled back by an error, COMMIT fails with COMMIT saved nothing, so your app cannot think it was saved.

Example: App sign-up: the user and the first audit row, together

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY, email STRING NOT NULL, created TIMESTAMP DEFAULT now());
CREATE TABLE audit_log (id LONG PRIMARY KEY AUTO, action STRING, detail STRING);

BEGIN WORK;
INSERT INTO app_users (id, email) VALUES (1, 'ram@example.com');
INSERT INTO audit_log (action, detail) VALUES ('sign-up', 'user 1: ram@example.com');
COMMIT WORK;
SELECT u.id, u.email, a.action FROM app_users u JOIN audit_log a ON a.detail = concat('user ', u.id, ': ', u.email);

Result:

BEGIN

transaction started

INSERT INTO app_users (id, email) VALUES (1, 'ram@example.com')

1 row(s) inserted

INSERT INTO audit_log (action, detail) VALUES ('sign-up', 'user 1: ram@example.com')

1 row(s) inserted

COMMIT

transaction committed: 2 change(s) saved

SELECT u.id, u.email, a.action FROM app_users AS u JOIN audit_log AS a ON a.detail = concat('user ',

(1 row)

Example: COMMIT without BEGIN (shows an error)

COMMIT;

Result:

Error: COMMIT without BEGIN: no transaction is open

ROLLBACK

Syntax:

ROLLBACK
ROLLBACK TRANSACTION
ROLLBACK WORK

Undoes every change of the open transaction and ends it. The data is exactly as it was before BEGIN.

• Returns: transaction rolled back: N change(s) undone.
• Notes:
  • Changes made by triggers and by CALLs inside the transaction are undone too.
  • ROLLBACK without an open transaction is an error, except in a session whose transaction was already rolled back by an error: there ROLLBACK ends it and answers transaction rolled back (it was already rolled back: ...).
  • Cursors declared inside the transaction stay open after ROLLBACK (their rows were read at DECLARE).

Example: Forum: undo a post and its counter

Setup:

CREATE TABLE topics (id INT PRIMARY KEY, posts LONG NOT NULL DEFAULT 0);
CREATE TABLE posts (id LONG PRIMARY KEY, topic_id INT NOT NULL, body STRING);
INSERT INTO topics (id) VALUES (1);

BEGIN;
INSERT INTO posts VALUES (1, 1, 'Buy CITY now!!!');
UPDATE topics SET posts = posts + 1 WHERE id = 1;
SELECT id, posts FROM topics;
ROLLBACK WORK;
SELECT id, posts FROM topics;
SELECT count(*) AS posts FROM posts;

Result:

BEGIN

transaction started

INSERT INTO posts VALUES (1, 1, 'Buy CITY now!!!')

1 row(s) inserted

UPDATE topics SET posts = posts + 1 WHERE id = 1

1 row(s) updated

SELECT id, posts FROM topics

(1 row)

ROLLBACK

transaction rolled back: 2 change(s) undone

SELECT id, posts FROM topics

(1 row)

SELECT count(*) AS posts FROM posts

(1 row)

Example: ROLLBACK without BEGIN (shows an error)

ROLLBACK;

Result:

Error: ROLLBACK without BEGIN: no transaction is open

SAVEPOINT

Syntax:

SAVEPOINT name
ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] name
RELEASE [SAVEPOINT] name

A savepoint is a mark inside BEGIN ... COMMIT. ROLLBACK TO undoes only the work after the mark; the work before it stays, and the transaction goes on.

• Returns: savepoint name set, rolled back to savepoint name: N row change(s) undone, savepoint name released.
• Errors: outside BEGIN ... COMMIT: SAVEPOINT can only be used inside BEGIN ... COMMIT (PostgreSQL 25P01). A name that is not there: savepoint 'x' does not exist in this transaction (3B001).
• Notes:
  • Nothing is saved until COMMIT. ROLLBACK (without TO) still undoes the whole transaction.
  • Rows for DEFERRED triggers that a ROLLBACK TO undid make no job.
  • Not inside a procedure or trigger body: the whole CALL is one transaction there.
  • Table locks stay until the transaction ends, also for tables only the undone part used (as in PostgreSQL).

Example: Keep the order, undo the discount

Setup:

CREATE TABLE orders (id INT PRIMARY KEY, symbol SYMBOL, qty LONG, price DECIMAL(2));

BEGIN;
INSERT INTO orders VALUES (1, 'CITY', 10, 510.00);
SAVEPOINT before_discount;
UPDATE orders SET price = price * 0.5 WHERE id = 1;
ROLLBACK TO SAVEPOINT before_discount;
INSERT INTO orders VALUES (2, 'COAST', 5, 385.00);
COMMIT;
SELECT * FROM orders ORDER BY id;

Result:

BEGIN

transaction started

INSERT INTO orders VALUES (1, 'CITY', 10, 510.00)

1 row(s) inserted

SAVEPOINT before_discount

savepoint before_discount set

UPDATE orders SET price = price * 0.5 WHERE id = 1

1 row(s) updated

ROLLBACK TO SAVEPOINT before_discount

rolled back to savepoint before_discount: 1 row change(s) undone

INSERT INTO orders VALUES (2, 'COAST', 5, 385.00)

1 row(s) inserted

COMMIT

transaction committed: 2 change(s) saved

SELECT * FROM orders ORDER BY id

(2 rows)

After an error: ROLLBACK TO SAVEPOINT

In a session (an HTTP session, kdb shell, the PostgreSQL protocol), an error after a savepoint does not throw the transaction away. As in PostgreSQL:

1. The work after the newest savepoint is undone, and the transaction is aborted: every statement is refused with current transaction is aborted ... (25P02), except these.
2. ROLLBACK TO SAVEPOINT name goes back to that mark and makes the transaction usable again.
3. ROLLBACK ends it. COMMIT saves nothing and says so.

Without a savepoint, an error still rolls back the whole transaction (when a statement fails).

HTTP: A duplicate key after a savepoint: go back and go on

Setup:

CREATE TABLE users (id INT PRIMARY KEY, email STRING UNIQUE);
INSERT INTO users VALUES (1, 'ram@example.np');

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; INSERT INTO users VALUES (2, 'sita@example.np'); SAVEPOINT before_hari
###
POST /exec
X-KDB-Session: {{session}}

INSERT INTO users VALUES (3, 'ram@example.np')
###
POST /exec
X-KDB-Session: {{session}}

ROLLBACK TO SAVEPOINT before_hari; INSERT INTO users VALUES (3, 'hari@example.np'); COMMIT
###
POST /exec

SELECT id, email FROM users ORDER BY id

Response:

POST /session

HTTP 200 (application/json)

{"session": "ab37d2c476871d5a558032bd594384f8", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; INSERT INTO users VALUES (2, 'sita@example.np'); SAVEPOINT before_hari",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 13, "execute_us": 5}
    },
    {
      "statement": "INSERT INTO users VALUES (2, 'sita@example.np')",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) inserted",
      "timings": {"parse_us": 13, "execute_us": 48}
    },
    {
      "statement": "SAVEPOINT before_hari",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "savepoint before_hari set",
      "timings": {"parse_us": 13, "execute_us": 6}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 39, "execute_us": 59}
}

POST /exec

HTTP 400 (application/json)

{
  "query": "INSERT INTO users VALUES (3, 'ram@example.np')",
  "results": [],
  "saved": 0,
  "rolled_back": true,
  "error": "column 'email' must be unique: 'ram@example.np' is already used (the transaction is aborted; send ROLLBACK TO SAVEPOINT before_hari to go on from the savepoint, or ROLLBACK to end it)",
  "sqlstate": "23505",
  "table": "users",
  "constraint": "users_email_key",
  "column_name": "email"
}

POST /exec

HTTP 200 (application/json)

{
  "query": "ROLLBACK TO SAVEPOINT before_hari; INSERT INTO users VALUES (3, 'hari@example.np'); COMMIT",
  "results": [
    {
      "statement": "ROLLBACK TO SAVEPOINT before_hari",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "rolled back to savepoint before_hari: 0 row change(s) undone",
      "timings": {"parse_us": 6, "execute_us": 6}
    },
    {
      "statement": "INSERT INTO users VALUES (3, 'hari@example.np')",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) inserted",
      "timings": {"parse_us": 6, "execute_us": 31}
    },
    {
      "statement": "COMMIT",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction committed: 2 change(s) saved",
      "timings": {"parse_us": 6, "execute_us": 8}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 18, "execute_us": 45}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT id, email FROM users ORDER BY id",
  "results": [
    {
      "statement": "SELECT id, email FROM users ORDER BY id",
      "columns": [
        {"name": "id", "type": "INT"},
        {"name": "email", "type": "STRING"}
      ],
      "dataset": [
        [1, "ram@example.np"],
        [2, "sita@example.np"],
        [3, "hari@example.np"]
      ],
      "count": 3,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 38, "execute_us": 100}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"},
    {"name": "email", "type": "STRING"}
  ],
  "dataset": [
    [1, "ram@example.np"],
    [2, "sita@example.np"],
    [3, "hari@example.np"]
  ],
  "count": 3,
  "timings": {"parse_us": 38, "execute_us": 100}
}

What is one transaction by itself

Syntax:

INSERT ... / UPDATE ... / DELETE ...    -- one statement: all its rows, or none
CALL procedure(...)                     -- the whole procedure
a statement that fires triggers         -- the statement and all its triggers

You do not always need BEGIN. These are each one transaction by themselves:

• Notes:
  • Several statements without BEGIN are separate transactions. When the third one fails, the first two stay saved.
  • Inside BEGIN ... COMMIT, all of these become part of the bigger transaction.
  • At most 10 million changed rows in one transaction (DatabaseOptions::max_transaction_rows). Until COMMIT, KDB keeps the old version of every changed row in memory.
  • A long transaction's log records go to a temporary file once they pass 64 MB (see big transactions).

Command line: One INSERT with a bad row writes no row (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);
INSERT INTO brokers VALUES (1, 'Keystone Securities');

kdb sql market "INSERT INTO brokers VALUES (34, 'Vision Securities'), (45, 'Imperial Securities'), (1, 'Keystone again')"
kdb sql market "SELECT id, name FROM brokers ORDER BY id"

Output:

> kdb sql market "INSERT INTO brokers VALUES (34, 'Vision Securities'), (45, 'Imperial Securities'), (1, 'Keystone again')"

error: row 3: a row with id='1' already exists in 'brokers'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it

exit code 1

> kdb sql market "SELECT id, name FROM brokers ORDER BY id"

id | name               
---+--------------------
 1 | Keystone Securities
(1 row, 0.3 ms)

Command line: Without BEGIN, the statements before the error stay saved (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);
INSERT INTO brokers VALUES (1, 'Keystone Securities');

kdb sql market "INSERT INTO brokers VALUES (34, 'Vision Securities'); INSERT INTO brokers VALUES (1, 'Keystone again'); INSERT INTO brokers VALUES (45, 'Imperial Securities')"
kdb sql market "SELECT id, name FROM brokers ORDER BY id"

Output:

> kdb sql market "INSERT INTO brokers VALUES (34, 'Vision Securities'); INSERT INTO brokers VALUES (1, 'Keystone again'); INSERT INTO brokers VALUES (45, 'Imperial Securities')"

1 row(s) inserted
error: a row with id='1' already exists in 'brokers'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it

exit code 1

> kdb sql market "SELECT id, name FROM brokers ORDER BY id"

id | name               
---+--------------------
 1 | Keystone Securities
34 | Vision Securities  
(2 rows, 0.3 ms)

When a statement fails

Syntax:

BEGIN; statement; failing statement;   -- the whole transaction is rolled back here

When a statement inside BEGIN ... COMMIT fails (a duplicate key, REFERENCES, a CHECK, RAISE in a procedure or trigger, a typing error in a column name, ...), KDB rolls back the whole transaction at once.

• Returns: the error of the statement, with (the transaction was rolled back) at the end.
• Notes:
  • In one text (kdb sql, POST /exec, the web console), the statements after the error do not run.
  • In a session (kdb shell, an HTTP session, the PostgreSQL protocol) the session then accepts only ROLLBACK (or COMMIT, which fails). So your next statements cannot run outside the transaction by mistake.
  • With a savepoint set, the error undoes only the work after it, and ROLLBACK TO SAVEPOINT lets the transaction go on.
  • A syntax error is found before anything runs: then no statement of the text runs at all.

Example: A transfer to an account that does not exist (shows an error)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
CREATE TABLE ledger (id LONG PRIMARY KEY AUTO, from_account INT NOT NULL REFERENCES accounts(id), to_account INT NOT NULL REFERENCES accounts(id), amount DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 99;
INSERT INTO ledger (from_account, to_account, amount) VALUES (1, 99, 100);
COMMIT;

Result:

BEGIN

transaction started

UPDATE accounts SET balance = balance - 100 WHERE id = 1

1 row(s) updated

UPDATE accounts SET balance = balance + 100 WHERE id = 99

0 row(s) updated

Error: column 'to_account' = '99' has no matching row in accounts(id) (the transaction was rolled back)

Command line: Ram still has 1000.00 after that (shows an error)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
CREATE TABLE ledger (id LONG PRIMARY KEY AUTO, from_account INT NOT NULL REFERENCES accounts(id), to_account INT NOT NULL REFERENCES accounts(id), amount DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

kdb sql market "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; INSERT INTO ledger (from_account, to_account, amount) VALUES (1, 99, 100); COMMIT"
kdb sql market "SELECT id, owner, balance FROM accounts ORDER BY id"

Output:

> kdb sql market "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; INSERT INTO ledger (from_account, to_account, amount) VALUES (1, 99, 100); COMMIT"

transaction started

1 row(s) updated
error: column 'to_account' = '99' has no matching row in accounts(id) (the transaction was rolled back)

exit code 1

> kdb sql market "SELECT id, owner, balance FROM accounts ORDER BY id"

id | owner | balance
---+-------+--------
 1 | Ram   | 1000.00
 2 | Sita  |    0.00
(2 rows, 0.4 ms)

A text that ends without COMMIT

Syntax:

BEGIN; statement; ...   <end of the text>   -- rolled back

kdb sql, POST /exec without a session, and the web console run one text at a time. A transaction cannot stay open after its text ends: when the text ends without COMMIT or ROLLBACK, KDB rolls it back and returns an error.

• Returns: the error BEGIN without COMMIT: the transaction was rolled back (end it with COMMIT to save the changes), after the results of the statements that ran.
• Notes: in kdb shell, in an HTTP session and over the PostgreSQL protocol, the transaction stays open until you send COMMIT or ROLLBACK (or leave). Leaving the shell or closing the session rolls it back.

Example: Forgot the COMMIT (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);

BEGIN;
INSERT INTO brokers VALUES (58, 'Northgate Securities');
SELECT count(*) AS brokers FROM brokers;

Result:

BEGIN

transaction started

INSERT INTO brokers VALUES (58, 'Northgate Securities')

1 row(s) inserted

SELECT count(*) AS brokers FROM brokers

(1 row)

Error: BEGIN without COMMIT: the transaction was rolled back (end it with COMMIT to save the changes)

Statements not allowed inside a transaction

Syntax:

CREATE ...  DROP ...  ALTER ...  RENAME ...  BACKUP DATABASE   -- before BEGIN or after COMMIT

Changes of the database structure cannot be undone like rows, so they are refused inside BEGIN ... COMMIT: CREATE / DROP / ALTER of tables, views, indexes, functions, procedures and triggers, RENAME TABLE, and BACKUP DATABASE. Run them before BEGIN or after COMMIT.

• Returns: the error CREATE TABLE cannot be used inside a transaction; COMMIT or ROLLBACK first. The open transaction is rolled back.
• Notes: the same statements are also not allowed inside a procedure or trigger body.

Example: CREATE TABLE inside a transaction (shows an error)

BEGIN;
CREATE TABLE archive (id INT PRIMARY KEY);

Result:

transaction started

Error: CREATE TABLE cannot be used inside a transaction; COMMIT or ROLLBACK first (the transaction was rolled back)

Example: CREATE PROCEDURE inside a transaction (shows an error)

BEGIN;
CREATE PROCEDURE hello() AS BEGIN PRINT 'hello'; END;

Result:

transaction started

Error: CREATE PROCEDURE cannot be used inside a transaction; COMMIT or ROLLBACK first (the transaction was rolled back)

SELECT ... FOR UPDATE

Syntax:

SELECT columns FROM table WHERE ... FOR UPDATE

A SELECT that says: "I read these rows because I will change them". Write it at the start of a transaction, before the UPDATEs.

• Returns: the rows, like a normal SELECT.

• Notes:

  • On a table with a primary key, FOR UPDATE locks the rows it returns, until your transaction ends (COMMIT, ROLLBACK, the session closing, or the idle limit). The rest of the table stays free: other sessions read it, change other rows, and lock other rows with their own FOR UPDATE.

  • Another transaction that wants one of your locked rows:

    It runs	What happens
    SELECT ... FOR UPDATE	waits until your transaction ends (up to the lock timeout)
    SELECT ... FOR UPDATE NOWAIT	fails at once: could not obtain lock on row in relation "jobs" (HTTP 503, PostgreSQL 55P03)
    SELECT ... FOR UPDATE SKIP LOCKED	leaves your rows out and returns the next free ones; LIMIT and OFFSET count after the skipping
    UPDATE or DELETE of the row	waits until your transaction ends (up to the lock timeout)
    a plain SELECT	reads it at once: row locks never stop a reader

  • So a job queue works with many workers: each one runs BEGIN; SELECT ... ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED, does the job, then UPDATE ...; COMMIT, and no two workers get the same job.

  • Two transactions that each wait for a row the other one locked get deadlock: ... at once (PostgreSQL 40P01) instead of both waiting. Roll back and try again.

  • A row your transaction changed (UPDATE, DELETE, INSERT) is still locked with its whole table until COMMIT, as every change is. Meanwhile:

    Another session runs	What happens
    SELECT ... FOR UPDATE SKIP LOCKED inside its own BEGIN	waits a moment (50 ms) and then returns no rows
    SELECT ... FOR UPDATE SKIP LOCKED without BEGIN	skips the rows you locked and the rows you changed, and returns the next free ones at once, as in PostgreSQL. It keeps no lock on them. Rows you added are not seen until COMMIT.
    SELECT ... FOR UPDATE NOWAIT	fails at once with table 'x' is busy

    So in a job worker, do the work first and send the UPDATE just before COMMIT: then the jobs table is held only for a few milliseconds. A worker that marks its job running early and then works on it makes the queue look empty to the others until it commits.

  • kdb serve --skip-locked-beside on (off by default) makes SKIP LOCKED inside BEGIN return the free rows too, and lock them. The catch, until a change stops holding the whole table: the row you got is not free for your UPDATE. That UPDATE waits for the other transaction's COMMIT, and fails with 40001 after the lock timeout (5 seconds unless set) when that transaction stays open longer. If the other transaction then changes that row too, one of the two gets deadlock: ... at once. Turn it on only when your workers commit quickly and retry 40001 and deadlock.

  • With either setting, SKIP LOCKED returns no rows while another transaction holds the table in a way that names no rows: a FOR UPDATE that locks tables (a join, a subquery), a change of more than 4,096 rows of the table, a DELETE of every row or a bulk load. It also returns no rows when the table is held by a statement that is running right now for more than 50 ms.

  • On a table without a primary key there is no key to lock a row by: FOR UPDATE locks the whole table until your transaction ends, and SKIP LOCKED returns no rows while another transaction holds it.

  • Without BEGIN (no transaction), FOR UPDATE locks the rows only while the statement runs.

  • FOR UPDATE goes at the very end of the SELECT, after ORDER BY and LIMIT. Row locks are taken for a SELECT from one table; with a join, GROUP BY, DISTINCT or a view it locks the tables, as before.

  • FOR SHARE, FOR NO KEY UPDATE and FOR KEY SHARE take the same lock as FOR UPDATE, with NOWAIT and SKIP LOCKED too. In PostgreSQL two sessions may both hold FOR SHARE on one row; in KDB the second one waits (or NOWAIT fails, or SKIP LOCKED skips the row). That is stricter, never looser: nothing gets through that PostgreSQL would stop. It is only slower when many readers share rows.

Example: Read the balances to be changed, then move the money

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

BEGIN;
SELECT id, balance FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 250.50 WHERE id = 1;
UPDATE accounts SET balance = balance + 250.50 WHERE id = 2;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

Result:

BEGIN

transaction started

SELECT id, balance FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE

(2 rows)

UPDATE accounts SET balance = balance - 250.50 WHERE id = 1

1 row(s) updated

UPDATE accounts SET balance = balance + 250.50 WHERE id = 2

1 row(s) updated

COMMIT

transaction committed: 2 change(s) saved

SELECT id, owner, balance FROM accounts ORDER BY id

(2 rows)

HTTP: SKIP LOCKED: another session holds job 1, so this worker gets job 2

Setup:

CREATE TABLE jobs (id INT PRIMARY KEY, state STRING);
INSERT INTO jobs VALUES (1, 'new'), (2, 'new'), (3, 'new');

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; SELECT id FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED
###
POST /exec

SELECT id, state FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED

Response:

POST /session

HTTP 200 (application/json)

{"session": "aeed33335b556ca67e8dfc88daeadbaa", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; SELECT id FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 23, "execute_us": 6}
    },
    {
      "statement": "SELECT id FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED",
      "columns": [
        {"name": "id", "type": "INT"}
      ],
      "dataset": [
        [1]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 23, "execute_us": 151}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"}
  ],
  "dataset": [
    [1]
  ],
  "count": 1,
  "timings": {"parse_us": 46, "execute_us": 157}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT id, state FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED",
  "results": [
    {
      "statement": "SELECT id, state FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED",
      "columns": [
        {"name": "id", "type": "INT"},
        {"name": "state", "type": "STRING"}
      ],
      "dataset": [
        [2, "new"]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 66, "execute_us": 84}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"},
    {"name": "state", "type": "STRING"}
  ],
  "dataset": [
    [2, "new"]
  ],
  "count": 1,
  "timings": {"parse_us": 66, "execute_us": 84}
}

HTTP: NOWAIT: a locked row is an error at once

Setup:

CREATE TABLE jobs (id INT PRIMARY KEY, state STRING);
INSERT INTO jobs VALUES (1, 'new'), (2, 'new');

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; SELECT id FROM jobs WHERE id = 1 FOR UPDATE
###
POST /exec

SELECT id FROM jobs WHERE id = 1 FOR UPDATE NOWAIT

Response:

POST /session

HTTP 200 (application/json)

{"session": "91ede6e2c499432dfd6ae7ad40f6f98d", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; SELECT id FROM jobs WHERE id = 1 FOR UPDATE",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 16, "execute_us": 4}
    },
    {
      "statement": "SELECT id FROM jobs WHERE id = 1 FOR UPDATE",
      "columns": [
        {"name": "id", "type": "INT"}
      ],
      "dataset": [
        [1]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 16, "execute_us": 64}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"}
  ],
  "dataset": [
    [1]
  ],
  "count": 1,
  "timings": {"parse_us": 32, "execute_us": 68}
}

POST /exec

HTTP 503 (application/json)

{
  "query": "SELECT id FROM jobs WHERE id = 1 FOR UPDATE NOWAIT",
  "results": [],
  "saved": 0,
  "rolled_back": false,
  "error": "could not obtain lock on row in relation \"jobs\": another transaction holds it (SELECT ... FOR UPDATE), and FOR UPDATE NOWAIT does not wait",
  "sqlstate": "55P03"
}

HTTP: NOWAIT on a row nobody locked: the row

Setup:

CREATE TABLE jobs (id INT PRIMARY KEY, state STRING);
INSERT INTO jobs VALUES (1, 'new'), (2, 'new');

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; SELECT id FROM jobs WHERE id = 1 FOR UPDATE
###
POST /exec

SELECT id FROM jobs WHERE id = 2 FOR UPDATE NOWAIT

Response:

POST /session

HTTP 200 (application/json)

{"session": "12fda51f843e6372cb7f667626ffcc65", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; SELECT id FROM jobs WHERE id = 1 FOR UPDATE",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 21, "execute_us": 4}
    },
    {
      "statement": "SELECT id FROM jobs WHERE id = 1 FOR UPDATE",
      "columns": [
        {"name": "id", "type": "INT"}
      ],
      "dataset": [
        [1]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 21, "execute_us": 71}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"}
  ],
  "dataset": [
    [1]
  ],
  "count": 1,
  "timings": {"parse_us": 42, "execute_us": 75}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT id FROM jobs WHERE id = 2 FOR UPDATE NOWAIT",
  "results": [
    {
      "statement": "SELECT id FROM jobs WHERE id = 2 FOR UPDATE NOWAIT",
      "columns": [
        {"name": "id", "type": "INT"}
      ],
      "dataset": [
        [2]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 25, "execute_us": 61}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"}
  ],
  "dataset": [
    [2]
  ],
  "count": 1,
  "timings": {"parse_us": 25, "execute_us": 61}
}

Transactions in kdb shell

Syntax:

kdb shell database
kdb> BEGIN;
kdb> ...;
kdb> COMMIT;

In kdb shell, one transaction can go over many lines: the shell keeps one session for the whole time.

• Notes:
  • If you leave the shell (.exit) with a transaction still open, it is rolled back.
  • After an error inside the transaction, the shell accepts only ROLLBACK (see when a statement fails).
  • The shell knows that CREATE PROCEDURE ... BEGIN ... END; is one statement, so BEGIN inside a procedure does not start a transaction.

Command line: A transfer typed line by line

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

kdb shell market
> BEGIN;
> UPDATE accounts SET balance = balance - 250.50 WHERE id = 1;
> UPDATE accounts SET balance = balance + 250.50 WHERE id = 2;
> SELECT id, owner, balance FROM accounts ORDER BY id;
> COMMIT;
> .exit

Output:

KDB 0.2.0-dev shell on market
End statements with ';'. Type .help for commands, .exit to quit.
kdb> transaction started
kdb> 1 row(s) updated
kdb> 1 row(s) updated
kdb> id | owner | balance
---+-------+--------
 1 | Ram   |  749.50
 2 | Sita  |  250.50
(2 rows, 0.2 ms)
kdb> transaction committed: 2 change(s) saved
kdb>

In the next example, the INSERT fails (id 1 is already used). The shell then refuses the next UPDATE, so it cannot take 10 from Ram outside the transaction. ROLLBACK ends the failed transaction. The shell writes error lines to a separate output (stderr), so on this page they are shown at the end. In a real window, each error comes right after its line.

Command line: After an error, only ROLLBACK is accepted

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

kdb shell market
> BEGIN;
> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
> INSERT INTO accounts VALUES (1, 'Ram again', 0);
> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
> ROLLBACK;
> SELECT id, owner, balance FROM accounts ORDER BY id;
> .exit

Output:

KDB 0.2.0-dev shell on market
End statements with ';'. Type .help for commands, .exit to quit.
kdb> transaction started
kdb> 1 row(s) updated
kdb> kdb> kdb> transaction rolled back (it was already rolled back: a statement in it failed)
kdb> id | owner | balance
---+-------+--------
 1 | Ram   | 1000.00
 2 | Sita  |    0.00
(2 rows, 0.2 ms)
kdb> 
error: a row with id='1' already exists in 'accounts'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it (the transaction was rolled back; send ROLLBACK to end it)
error: the transaction of this session was rolled back (a statement in it failed); nothing of it was saved. Send ROLLBACK to end it, then start again

Transactions over HTTP

Syntax:

POST /session                               -> {"session": "<id>", ...}
POST /exec   X-KDB-Session: <id>   BEGIN; ...
POST /exec   X-KDB-Session: <id>   ...; COMMIT
DELETE /session/<id>

POST /exec without a session runs one text: a transaction must end in the same request. To keep a transaction open over many requests, open a session with POST /session, and send its id in the header X-KDB-Session with every /exec. See HTTP sessions and cursors.

• Notes:
  • A request without the session header does not see the open transaction's changes. It sees the data as it was before BEGIN.
  • A transaction with no statement for 30 seconds is rolled back. Change it with SET idle_in_transaction_session_timeout or for the server (see idle transactions). A session without a request for 10 minutes is closed.
  • DELETE /session/<id> rolls back an open transaction.
  • Over the PostgreSQL protocol, every connection is a session: use BEGIN and COMMIT as usual.

HTTP: A transfer over two requests; another request does not see it until COMMIT

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1
###
POST /exec

SELECT id, balance FROM accounts ORDER BY id
###
POST /exec
X-KDB-Session: {{session}}

UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT
###
POST /exec

SELECT id, balance FROM accounts ORDER BY id
###
DELETE /session/{{session}}

Response:

POST /session

HTTP 200 (application/json)

{"session": "c6c2e2f850f77619986b3b53723d9d39", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 24, "execute_us": 9}
    },
    {
      "statement": "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 24, "execute_us": 74}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 48, "execute_us": 83}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT id, balance FROM accounts ORDER BY id",
  "results": [
    {
      "statement": "SELECT id, balance FROM accounts ORDER BY id",
      "columns": [
        {"name": "id", "type": "INT"},
        {"name": "balance", "type": "DECIMAL(2)"}
      ],
      "dataset": [
        [1, "1000.00"],
        [2, "0.00"]
      ],
      "count": 2,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 27, "execute_us": 63}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"},
    {"name": "balance", "type": "DECIMAL(2)"}
  ],
  "dataset": [
    [1, "1000.00"],
    [2, "0.00"]
  ],
  "count": 2,
  "timings": {"parse_us": 27, "execute_us": 63}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT",
  "results": [
    {
      "statement": "UPDATE accounts SET balance = balance + 100 WHERE id = 2",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 18, "execute_us": 56}
    },
    {
      "statement": "COMMIT",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction committed: 2 change(s) saved",
      "timings": {"parse_us": 18, "execute_us": 13}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 36, "execute_us": 69}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT id, balance FROM accounts ORDER BY id",
  "results": [
    {
      "statement": "SELECT id, balance FROM accounts ORDER BY id",
      "columns": [
        {"name": "id", "type": "INT"},
        {"name": "balance", "type": "DECIMAL(2)"}
      ],
      "dataset": [
        [1, "900.00"],
        [2, "100.00"]
      ],
      "count": 2,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 2, "execute_us": 91}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"},
    {"name": "balance", "type": "DECIMAL(2)"}
  ],
  "dataset": [
    [1, "900.00"],
    [2, "100.00"]
  ],
  "count": 2,
  "timings": {"parse_us": 2, "execute_us": 91}
}

DELETE /session/{{session}}

HTTP 200 (application/json)

{"session": "c6c2e2f850f77619986b3b53723d9d39", "closed": true}

One writer per table

Syntax:

session A: BEGIN; UPDATE accounts SET ... WHERE id = 1;  -- A locks row 1 of accounts (row-level locks)
session B: INSERT INTO ticks ...;                         -- B goes at once: another table
session C: UPDATE accounts SET ... WHERE id = 2;          -- C goes at once: another row
session C: UPDATE accounts SET ... WHERE id = 1;          -- C waits for A's row (5 s unless set), then "a row of table 'accounts' is locked"
session C: SELECT ... FROM accounts;                      -- C does not wait for A's open transaction: it reads the data without A's changes
session A: COMMIT;                                        -- row 1 is free; the next writer goes

A reader waits only while a writer's statement runs (see reader yield for long triggers and CALLs). Between A's requests nothing runs, so C reads at once.

A writer locks every table it uses until its statement ends. Inside BEGIN ... COMMIT it keeps them until the transaction ends - except where it only updated rows by their key: there it holds just those rows (row-level locks, in the table below). Writers of different tables run at the same time. Writers of the same table take turns, so two transfers can never mix, and every transaction sees the data exactly as the one before it left it (this is called serializable).

• Notes: a transfer takes about a millisecond, so waiting is short. Never keep a transaction open while you wait for a person (a click, a form, an SMS code). Read first, ask the person, then do BEGIN ... COMMIT quickly.
• Use the tables in the same order in every transaction (for example accounts, then ledger), or send the whole transaction in one request. Then deadlocks cannot happen.

HTTP: A writer of another table goes at once

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
CREATE TABLE ticks (symbol SYMBOL, price DOUBLE);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1
###
POST /exec

INSERT INTO ticks VALUES ('CITY', 505.5)

Response:

POST /session

HTTP 200 (application/json)

{"session": "f7e759966764a764063c635d46f4e31d", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 14, "execute_us": 6}
    },
    {
      "statement": "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 14, "execute_us": 73}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 28, "execute_us": 79}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "INSERT INTO ticks VALUES ('CITY', 505.5)",
  "results": [
    {
      "statement": "INSERT INTO ticks VALUES ('CITY', 505.5)",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) inserted",
      "timings": {"parse_us": 32, "execute_us": 19}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 32, "execute_us": 19}
}

HTTP: Another row of the same table goes at once (row-level locks)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1
###
POST /exec

UPDATE accounts SET balance = 0 WHERE id = 2

Response:

POST /session

HTTP 200 (application/json)

{"session": "b53794a14362801a49dbfdae615461bd", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 12, "execute_us": 4}
    },
    {
      "statement": "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 12, "execute_us": 42}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 24, "execute_us": 46}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "UPDATE accounts SET balance = 0 WHERE id = 2",
  "results": [
    {
      "statement": "UPDATE accounts SET balance = 0 WHERE id = 2",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 23, "execute_us": 34}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 23, "execute_us": 34}
}

HTTP: The same row waits 5 seconds, then gets 'a row of table ... is locked'

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1
###
POST /exec

UPDATE accounts SET balance = 0 WHERE id = 1

Response:

POST /session

HTTP 200 (application/json)

{"session": "2fcad7bfc65a0a969b174771e5c08a0e", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 11, "execute_us": 3}
    },
    {
      "statement": "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 11, "execute_us": 39}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 22, "execute_us": 42}
}

POST /exec

HTTP 503 (application/json)

{
  "query": "UPDATE accounts SET balance = 0 WHERE id = 1",
  "results": [],
  "saved": 0,
  "rolled_back": false,
  "error": "a row of table 'accounts' is locked: another transaction changed it or took it with SELECT ... FOR UPDATE, and is still open; waited 5013 ms (lock_timeout: SET lock_timeout, or kdb serve --lock-timeout). Try again, or keep transactions short",
  "sqlstate": "55P03"
}

HTTP: A transaction that inserted keeps the whole table: another writer waits, then gets 'table ... is busy'

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; INSERT INTO accounts VALUES (3, 'Hari', 0)
###
POST /exec

UPDATE accounts SET balance = 0 WHERE id = 2

Response:

POST /session

HTTP 200 (application/json)

{"session": "ccf8379d5ccea6c1117951a641db70db", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; INSERT INTO accounts VALUES (3, 'Hari', 0)",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 9, "execute_us": 5}
    },
    {
      "statement": "INSERT INTO accounts VALUES (3, 'Hari', 0)",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) inserted",
      "timings": {"parse_us": 9, "execute_us": 47}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 18, "execute_us": 52}
}

POST /exec

HTTP 503 (application/json)

{
  "query": "UPDATE accounts SET balance = 0 WHERE id = 2",
  "results": [],
  "saved": 0,
  "rolled_back": false,
  "error": "table 'accounts' is busy: another transaction has held it for 5027 ms; waited 5003 ms (lock_timeout: SET lock_timeout, or kdb serve --lock-timeout). Try again, or keep transactions short",
  "sqlstate": "55P03"
}

HTTP: An INSERT of a key that is taken gets 23505 at once, even while the table is busy

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1
###
POST /exec

INSERT INTO accounts VALUES (2, 'Hari', 50)

Response:

POST /session

HTTP 200 (application/json)

{"session": "3663ba76f5b8fcea203f7cb874c34c46", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 24, "execute_us": 5}
    },
    {
      "statement": "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 24, "execute_us": 56}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 48, "execute_us": 61}
}

POST /exec

HTTP 400 (application/json)

{
  "query": "INSERT INTO accounts VALUES (2, 'Hari', 50)",
  "results": [],
  "saved": 0,
  "rolled_back": false,
  "error": "a row with id='2' already exists in 'accounts'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it",
  "sqlstate": "23505",
  "table": "accounts",
  "constraint": "accounts_pkey",
  "column_name": "id"
}

HTTP: CREATE TABLE IF NOT EXISTS of a table that is there goes at once, even while a transaction is open

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1
###
POST /exec

CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
ALTER TABLE accounts ADD COLUMN IF NOT EXISTS owner STRING

Response:

POST /session

HTTP 200 (application/json)

{"session": "938dc0eedc33ef8407f39dac500ead73", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 17, "execute_us": 4}
    },
    {
      "statement": "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 17, "execute_us": 47}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 34, "execute_us": 51}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);\nALTER TABLE accounts ADD COLUMN IF NOT EXISTS owner STRING",
  "results": [
    {
      "statement": "CREATE TABLE IF NOT EXISTS accounts (\n  id INT PRIMARY KEY,\n  owner STRING NOT NULL,\n  balance DECIMAL(2) NOT NULL\n)",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "table 'accounts' already exists",
      "timings": {"parse_us": 17, "execute_us": 1}
    },
    {
      "statement": "ALTER TABLE accounts ADD COLUMN IF NOT EXISTS owner STRING",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "table 'accounts' not changed (already as asked)",
      "timings": {"parse_us": 17, "execute_us": 0}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 34, "execute_us": 1}
}

Isolation: what a transaction sees

Syntax:

BEGIN [ISOLATION LEVEL READ COMMITTED | SERIALIZABLE]
SET TRANSACTION ISOLATION LEVEL READ COMMITTED        -- this transaction only (inside BEGIN)
SET default_transaction_isolation = 'read committed'  -- the session's transactions from now on
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
SHOW transaction_isolation

KDB's transactions are serializable unless you ask for READ COMMITTED: every table a transaction reads or writes is locked until COMMIT or ROLLBACK. PostgreSQL's default is READ COMMITTED.

• What a refusal is: a transaction never sees half of another one. Instead, a writer that waits too long for a table is refused:

• Notes:
  • REPEATABLE READ is taken as SERIALIZABLE, READ UNCOMMITTED as READ COMMITTED (as PostgreSQL does). A database may always give more than asked.
  • READ COMMITTED leaves only a SELECT sent on its own outside the locks. A SELECT in the same request as BEGIN or a write, SELECT ... FOR UPDATE, and a read that joins a table the transaction holds, lock their tables as before.
  • SET TRANSACTION outside BEGIN ... COMMIT changes nothing (PostgreSQL gives a warning, 25P01).
  • Readers outside any transaction never lock: they see the last committed rows (one writer per table).

HTTP: READ COMMITTED: another session writes the table this transaction read

Setup:

CREATE TABLE counter (k INT PRIMARY KEY, v LONG);
INSERT INTO counter VALUES (1, 10);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN ISOLATION LEVEL READ COMMITTED
###
POST /exec
X-KDB-Session: {{session}}

SELECT v FROM counter WHERE k = 1
###
POST /exec

UPDATE counter SET v = 11 WHERE k = 1
###
POST /exec
X-KDB-Session: {{session}}

SELECT v FROM counter WHERE k = 1
###
POST /exec
X-KDB-Session: {{session}}

COMMIT

Response:

POST /session

HTTP 200 (application/json)

{"session": "29d98ffec3c666650a313a55dbe88401", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN ISOLATION LEVEL READ COMMITTED",
  "results": [
    {
      "statement": "BEGIN ISOLATION LEVEL READ COMMITTED",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 8, "execute_us": 10}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 8, "execute_us": 10}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT v FROM counter WHERE k = 1",
  "results": [
    {
      "statement": "SELECT v FROM counter WHERE k = 1",
      "columns": [
        {"name": "v", "type": "LONG"}
      ],
      "dataset": [
        [10]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 40, "execute_us": 64}
    }
  ],
  "columns": [
    {"name": "v", "type": "LONG"}
  ],
  "dataset": [
    [10]
  ],
  "count": 1,
  "timings": {"parse_us": 40, "execute_us": 64}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "UPDATE counter SET v = 11 WHERE k = 1",
  "results": [
    {
      "statement": "UPDATE counter SET v = 11 WHERE k = 1",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 1,
      "message": "1 row(s) updated",
      "timings": {"parse_us": 19, "execute_us": 50}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 19, "execute_us": 50}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT v FROM counter WHERE k = 1",
  "results": [
    {
      "statement": "SELECT v FROM counter WHERE k = 1",
      "columns": [
        {"name": "v", "type": "LONG"}
      ],
      "dataset": [
        [11]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 4, "execute_us": 112}
    }
  ],
  "columns": [
    {"name": "v", "type": "LONG"}
  ],
  "dataset": [
    [11]
  ],
  "count": 1,
  "timings": {"parse_us": 4, "execute_us": 112}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "COMMIT",
  "results": [
    {
      "statement": "COMMIT",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction committed: 0 change(s) saved",
      "timings": {"parse_us": 11, "execute_us": 10}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 11, "execute_us": 10}
}

Read-only transactions

Syntax:

BEGIN READ ONLY                               -- also START TRANSACTION READ ONLY (PostgreSQL port)
BEGIN ISOLATION LEVEL READ COMMITTED, READ ONLY
SET TRANSACTION READ ONLY | READ WRITE        -- this transaction only (inside BEGIN)
SET default_transaction_read_only = on | off  -- the session's transactions from now on
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY
SHOW transaction_read_only

A read-only transaction refuses every change, as in PostgreSQL. Use it for reports and checks that must never write by mistake.

• The error: cannot execute INSERT in a read-only transaction, SQLSTATE 25006 (HTTP 403). Inside BEGIN ... COMMIT the transaction is aborted, as in PostgreSQL: send ROLLBACK. Nothing is written.
• Notes:
  • With default_transaction_read_only = on, a statement sent on its own is read-only too. BEGIN READ WRITE (or SET TRANSACTION READ WRITE first in the block) writes once, for that transaction.
  • SET TRANSACTION READ WRITE after the transaction ran a query is refused (25001), as in PostgreSQL.
  • Over HTTP, BEGIN READ ONLY works in one request. SET default_transaction_read_only and SET TRANSACTION need an HTTP session (POST /session): without one there is nowhere to keep them, so they are refused.
  • One session's setting never changes another session.
  • To make a whole database or server read-only for everyone, use ALTER DATABASE ... SET READ ONLY or kdb serve --read-only instead.

HTTP: A report session that cannot write by mistake (shows an error)

Setup:

CREATE TABLE report_src (k INT PRIMARY KEY, v LONG);
INSERT INTO report_src VALUES (1, 10);

POST /exec

BEGIN READ ONLY; SELECT v FROM report_src; COMMIT
###
POST /exec

BEGIN READ ONLY; UPDATE report_src SET v = 0 WHERE k = 1

Response:

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN READ ONLY; SELECT v FROM report_src; COMMIT",
  "results": [
    {
      "statement": "BEGIN READ ONLY",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 7, "execute_us": 3}
    },
    {
      "statement": "SELECT v FROM report_src",
      "columns": [
        {"name": "v", "type": "LONG"}
      ],
      "dataset": [
        [10]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 7, "execute_us": 54}
    },
    {
      "statement": "COMMIT",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction committed: 0 change(s) saved",
      "timings": {"parse_us": 7, "execute_us": 2}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 21, "execute_us": 59}
}

POST /exec

HTTP 403 (application/json)

{
  "query": "BEGIN READ ONLY; UPDATE report_src SET v = 0 WHERE k = 1",
  "results": [
    {
      "statement": "BEGIN READ ONLY",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 14, "execute_us": 4}
    }
  ],
  "saved": 0,
  "rolled_back": true,
  "error": "cannot execute UPDATE in a read-only transaction (the transaction was rolled back)",
  "sqlstate": "25006"
}

How long a writer waits: lock_timeout

Syntax:

kdb serve --lock-timeout 20s              the server (default 5s; 0 = no limit)
KDB_LOCK_TIMEOUT=20s                      the same, for Docker
"lock_timeout_ms": 20000                  the same, in kdb.conf
SET lock_timeout = '20s' | 20000 | 0 | DEFAULT     one session (HTTP session or PostgreSQL connection)
X-KDB-Lock-Timeout: 20s                   one HTTP request

Many small writers of one table (counters, logins) that should queue instead of failing: raise the lock timeout for them, and keep each transaction short.

LOCK TABLE, pg_locks and lock waits

Syntax:

LOCK [TABLE] name [, name ...] [IN mode MODE] [NOWAIT]      only inside BEGIN ... COMMIT
mode: ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE (the default)
SELECT relation::regclass, mode, granted, pid FROM pg_locks        who holds and who waits
SELECT pg_blocking_pids(pid)                                       who a waiting pid waits for
SET log_lock_waits = on;  SET deadlock_timeout = '1s'              a log line for a long wait

LOCK TABLE takes a table lock in one of PostgreSQL's eight modes and keeps it until the transaction ends (COMMIT or ROLLBACK). The modes keep each other out as in PostgreSQL:

pg_locks lists the table locks of the database: one row per table and mode, granted true for a held lock and false for one waited for (then waitstart says since when). pid is the same number pg_stat_activity and pg_backend_pid() give for that connection or HTTP session. A lock of every table (a schema change, BACKUP) shows as AccessExclusiveLock on each table. Over the PostgreSQL port relation::regclass gives the table's name; over HTTP join pg_class on oid.

pg_locks, pg_stat_activity and pg_stat_statements read no table, so a transaction can look at them (BEGIN; LOCK TABLE t; SELECT * FROM pg_locks) without taking any other table and without waiting for anyone.

pg_blocking_pids(pid) gives the pids a waiting pid waits for: those holding a lock in its way, and those that came first and wait for the same table. {} when it does not wait.

log_lock_waits (off by default) writes one line to the server log when a statement has waited for a table lock longer than deadlock_timeout (1 second by default), naming it, the lock, who holds it and who waits: kdb: LOG: process 12 still waiting for RowExclusiveLock on relation 16448 ('t') of database 'main' after 1003 ms; process holding the lock: 34; wait queue: 12. Set it for the server with kdb serve --log-lock-waits on --deadlock-timeout 1s (KDB_LOG_LOCK_WAITS, KDB_DEADLOCK_TIMEOUT), or for one session with SET. KDB finds a deadlock at once whatever deadlock_timeout is: it only says when the line is written.

HTTP: SHARE mode: another transaction reads the table, its UPDATE would wait; pg_locks shows the lock

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; LOCK TABLE accounts IN SHARE MODE
###
POST /exec

SELECT owner, balance FROM accounts ORDER BY id;
SELECT c.relname, l.mode, l.granted FROM pg_locks l JOIN pg_class c ON c.oid = l.relation

Response:

POST /session

HTTP 200 (application/json)

{"session": "18667f6568b87caad682354243a60541", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; LOCK TABLE accounts IN SHARE MODE",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 7, "execute_us": 4}
    },
    {
      "statement": "LOCK TABLE accounts IN SHARE MODE",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "LOCK TABLE",
      "timings": {"parse_us": 7, "execute_us": 3}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 14, "execute_us": 7}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "SELECT owner, balance FROM accounts ORDER BY id;\nSELECT c.relname, l.mode, l.granted FROM pg_locks l JOIN pg_class c ON c.oid = l.relation",
  "results": [
    {
      "statement": "SELECT owner, balance FROM accounts ORDER BY id",
      "columns": [
        {"name": "owner", "type": "STRING"},
        {"name": "balance", "type": "DECIMAL(2)"}
      ],
      "dataset": [
        ["Ram", "1000.00"],
        ["Sita", "0.00"]
      ],
      "count": 2,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 18, "execute_us": 64}
    },
    {
      "statement": "SELECT c.relname, l.mode, l.granted FROM pg_locks AS l JOIN pg_class AS c ON c.oid = l.relation",
      "columns": [
        {"name": "relname", "type": "STRING"},
        {"name": "mode", "type": "STRING"},
        {"name": "granted", "type": "BOOL"}
      ],
      "dataset": [
        ["accounts", "ShareLock", true]
      ],
      "count": 1,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 18, "execute_us": 131}
    }
  ],
  "columns": [
    {"name": "relname", "type": "STRING"},
    {"name": "mode", "type": "STRING"},
    {"name": "granted", "type": "BOOL"}
  ],
  "dataset": [
    ["accounts", "ShareLock", true]
  ],
  "count": 1,
  "timings": {"parse_us": 36, "execute_us": 195}
}

HTTP: NOWAIT on a table another transaction holds: 55P03 at once

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

POST /session

###
POST /exec
X-KDB-Session: {{session}}

BEGIN; LOCK TABLE accounts IN SHARE MODE
###
POST /exec

BEGIN; LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE NOWAIT

Response:

POST /session

HTTP 200 (application/json)

{"session": "4715e1b78204942da6d204e9b1644327", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "BEGIN; LOCK TABLE accounts IN SHARE MODE",
  "results": [
    {
      "statement": "BEGIN",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "transaction started",
      "timings": {"parse_us": 8, "execute_us": 4}
    },
    {
      "statement": "LOCK TABLE accounts IN SHARE MODE",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "LOCK TABLE",
      "timings": {"parse_us": 8, "execute_us": 1}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 16, "execute_us": 5}
}

POST /exec

HTTP 503 (application/json)

{
  "query": "BEGIN; LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE NOWAIT",
  "results": [],
  "saved": 0,
  "rolled_back": false,
  "error": "could not obtain lock on relation \"accounts\" (none of the 2 statements of this request was run)",
  "sqlstate": "55P03"
}

Idle transactions: idle_in_transaction_session_timeout

Syntax:

kdb serve --idle-in-transaction-timeout 5min    the server (default 30s; 0 = never)
KDB_IDLE_IN_TRANSACTION_TIMEOUT=5min            the same, for Docker
"idle_in_transaction_timeout_ms": 300000        the same, in kdb.conf
SET idle_in_transaction_session_timeout = '5min' | 300000 | 0 | DEFAULT   one session

An open BEGIN ... COMMIT that sends no statement for this long is rolled back. This happens when another writer needs its tables, or at its own next statement. Its session then gets an error until it sends ROLLBACK. So a client that died in the middle of a transaction cannot keep its tables locked for ever.

Readers during a long writer

Syntax:

kdb serve --per-table-reads on|off     (default on)
KDB_PER_TABLE_READS=off                (the same, for Docker; a flag wins)

A reader waits only for writers that are running right now on a table it reads.

• Notes:
  • Readers still see only committed rows: a table a running writer holds is never read until it is done.
  • A checkpoint that waits for a long writer does not hold readers back meanwhile.
  • off goes back to the old rule: every reader waits for every running writer and for the whole checkpoint.

Readers of the writer's tables: reader yield

Syntax:

kdb serve --reader-yield on|off        (default on)
KDB_READER_YIELD=off                   (the same, for Docker; a flag wins)

A writer's request can run for many seconds: a CALL that builds a day of candles, an immediate trigger, a request with many statements. Readers never see its changes before it commits. Readers of other tables never wait for it (above). For readers of its tables, this switch decides whether they wait meanwhile. The start-up lines say which one is in use (readers ...).

• Notes:
  • The pause comes only between statements. One long statement (a big INSERT ... SELECT, a big UPDATE) still makes readers wait until it ends.
  • Readers see a committed state, never half of one: a commit that fills three tables is seen whole or not at all.
  • Writers of other tables do not need this switch: they never wait for each other.
  • The switch is for the whole server, not per database.
  • A deferred trigger job pauses the same way. See deferred triggers.

Readers of the writer's tables: snapshot reads

Syntax:

kdb serve --snapshot-reads on|off      (default off)
KDB_SNAPSHOT_READS=on                  (the same, for Docker; a flag wins)

With snapshot reads on, a reader of a table that a writer is changing does not wait, not even for one long statement. It reads a snapshot of the table: the rows committed when the read started.

• Notes:
  • A writer that is about to finish (within 20 ms, a short write and its disk sync) is still waited for: a snapshot for every read of a table written many times a second would slow its writer down. Once a snapshot is taken, readers during the same long writer use it at once.
  • A snapshot never holds uncommitted rows, and never half of a commit: all the tables of one transaction or none of them.
  • A snapshot waits until the rows it holds are on disk, as the writer that made them does.
  • Tables with an index get snapshots too, while a writer is busy on them, at any size (since 2026-10-02). A read of the snapshot still uses a plain or expression index (CREATE INDEX): it checks each row the index finds against the snapshot, and also finds the rows the writer changed or deleted since. So a lookup stays a lookup, never sees an uncommitted row, and EXPLAIN shows the index as usual.
  • A full-text or JSON index is not used on a snapshot, and neither is an index that is not built in memory yet (after a restart, before its first use): such a read scans the snapshot instead. A table like that with more than 1,000,000 rows is not scanned: its readers wait for the writer, as before. --snapshot-index-rows N (KDB_SNAPSHOT_INDEX_ROWS) moves that line; 0 means indexed tables never get snapshots.
  • An open transaction that sits idle (BEGIN, a change, then nothing) gives readers of an indexed table no snapshot: they take it out of their way and use the index on the table itself, as without snapshot reads.
  • Materialized views are still read as before (the reader waits).
  • One statement that writes a table applies its rows in one step at the end. A reader of that same table waits for that step (up to about a second per million rows), never for the whole statement.
  • Readers that use a snapshot, snapshots taken and snapshots reused show in GET /api/stats (snapshot_reads, snapshots_taken, snapshots_reused).
  • GET /api/stats itself reads snapshots too (since 2026-10-02): the row counts of a table a writer is changing are its committed rows, and it answers at once instead of saying "busy": true until the writer is done.
  • Layers (since 2026-10-02). The unsaved rows of a partition (changed since the last checkpoint) are shared between the table and its snapshots. A writer that changes such a partition freezes them as a layer and writes on a new one, so its cost is the rows it writes, not the rows the partition holds. Reads merge the layers. When no snapshot holds a layer any more, the next write to the partition folds it back; a checkpoint folds them all. A partition keeps at most 6 layers: past that, the two smallest neighbours become one.
  • GET /api/stats counts them since the server started:

A snapshot_delta_layers that stays high while nobody reads means a partition was not written since its last snapshot ended: the next write or checkpoint frees it.

Several writers of one table

Syntax:

kdb serve --table-writers one|many     (default one)
KDB_TABLE_WRITERS=many                 (the same, for Docker; a flag wins)

With many, several users change one table at the same time, as long as each of their requests touches the table only by single rows and their primary key:

The table must be a plain table with a one-column PRIMARY KEY, no foreign key either way, no other UNIQUE column or index, no AUTO column, no FOR EACH ROW trigger and no materialized view built from it (statement triggers are fine). Other tables of the same request are not affected: each is held shared or whole on its own.

• What you get: eight users running a bank-transfer CALL no longer take turns on the accounts table; their statements overlap and more of their commits share one disk sync.
• The same row: a second writer of a row waits for the first one's end (the lock timeout applies), then reads the row as that writer left it, committed or rolled back: balance = balance - 10 twice gives the same balance as one after the other, as PostgreSQL's row locks do. Two inserts of one key give one row and one already exists (23505). A read of a row another writer is changing waits for it: a request never sees a change that is not committed.
• Deadlock: two writers that wait for each other's rows get deadlock: ... (40P01) at once, like two transactions that wait for each other's tables. Run the request again.
• Readers: a SELECT of the table waits for all of its writers, or reads a snapshot with snapshot reads on; it never sees half a statement.
• The escape hatch: a request that turns out to need more of the table than its statements showed (rare) takes the whole table for itself, waiting for the other writers to end; GET /api/stats counts it in shared_writer_upgrades, next to shared_writers (requests that held a table shared) and shared_row_waits.

Big transactions: log records on disk

Syntax:

kdb serve --txn-spill-mb 64            (default 64; 0 = never)
KDB_TXN_SPILL_MB=64                    (the same, for Docker; a flag wins)

A transaction writes its changes to the log only at COMMIT. Until then KDB keeps the log records (the changes, ready to be written). A long CALL, a deferred trigger job or a big BEGIN ... COMMIT can make many of them.

Since 2026-10-02, once they pass 64 MB, KDB moves them to a temporary file in the database's _tmp folder. Only where each record starts stays in memory.

• Notes:
  • With a memory limit (--memory-limit), the line is at most 1/16 of it. Under a 512 MB limit that is 32 MB.
  • Only the log records go to disk. The changed rows themselves stay in memory until COMMIT, so a transaction still stops at a quarter of the memory limit ("transaction too big for memory") when its rows alone need that much.
  • Example: one CALL that inserts 2 million rows under a 512 MB memory limit was stopped at 1.5 million rows. With its 64 MB of log records on disk it finishes, and the heap stays near 230 MB.
  • A commit of spilled records always waits for the disk, even with SET synchronous_commit = off.
  • GET /api/stats counts them per database: txn_spills (transactions that used a file) and txn_spilled_bytes.
  • 0 keeps every record in memory, as before.

Example: The line where log records go to disk

SELECT name, setting, unit, source FROM pg_settings WHERE name = 'txn_spill_mb';

Result:

(1 row)

Accounting with CHECK constraints

Syntax:

balance DECIMAL(2) NOT NULL CHECK (balance >= 0)
BEGIN; UPDATE ...; UPDATE ...; INSERT INTO ledger ...; COMMIT;

A CHECK constraint (a rule that every row must follow, see constraints) makes the database itself refuse a balance below zero. Together with a transaction, a transfer that would break the rule saves nothing.

Checklist for money:

1. Money columns are DECIMAL(2) NOT NULL (exact, never DOUBLE), with CHECK rules for what must never happen.
2. Keep a ledger. Never update or delete its rows; correct a mistake with a new transfer back.
3. Every transfer: BEGIN, UPDATE ... SET balance = balance - amount, UPDATE ... + amount, INSERT INTO ledger, COMMIT. Let the database do the math; do not write a balance that your app computed.
4. On any error: ROLLBACK. On table ... is busy, database is busy or deadlock: run the whole transaction again.
5. Check the books every night (the total, and every balance against the ledger), like in the first example of this page.

Example: A transfer of more than the balance saves nothing (shows an error)

Setup:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner STRING NOT NULL,
  balance DECIMAL(2) NOT NULL DEFAULT 0 CHECK (balance >= 0)
);
CREATE TABLE ledger (
  id LONG PRIMARY KEY AUTO,
  from_account INT NOT NULL REFERENCES accounts(id),
  to_account INT NOT NULL REFERENCES accounts(id),
  amount DECIMAL(2) NOT NULL,
  CONSTRAINT positive_amount CHECK (amount > 0)
);
INSERT INTO accounts VALUES (1, 'Ram', 749.50), (2, 'Sita', 250.50);

BEGIN;
UPDATE accounts SET balance = balance + 900 WHERE id = 2;
UPDATE accounts SET balance = balance - 900 WHERE id = 1;
INSERT INTO ledger (from_account, to_account, amount) VALUES (1, 2, 900);
COMMIT;

Result:

BEGIN

transaction started

UPDATE accounts SET balance = balance + 900 WHERE id = 2

1 row(s) updated

Error: CHECK constraint 'accounts_balance_check' of table 'accounts' failed: balance >= 0 is false for the row id='1', balance='-150.50' (the transaction was rolled back)

Without CHECK constraints (or in an older KDB), a BEFORE UPDATE trigger can refuse the same change: see triggers.