Compare · 023

Transactions: CockroachDB and SQLite

Every file of the CockroachDB and SQLite versions side by side, with the lines that differ marked.

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

CockroachDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: bank
4  - name: bank
5    dialect: cockroachdb
5    dialect: sqlite
6    migrations: db/migrations
6    migrations: db/migrations
7    queries: db/queries
7    queries: db/queries
8    go:
8    go:
9      package: store
9      package: store
10      out: internal/store
10      out: internal/store

Migrations

db/migrations/0001_accounts.up.sqldiffers

CockroachDB
SQLite
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2    id      INT8    NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
2    id      INTEGER PRIMARY KEY,
3    owner   STRING  NOT NULL,
3    owner   TEXT    NOT NULL,
4    balance INTEGER NOT NULL CHECK (balance >= 0)
4    balance INTEGER NOT NULL CHECK (balance >= 0)
5);
5);

db/migrations/0001_accounts.down.sqlidentical

CockroachDB
SQLite
1DROP TABLE accounts;
1DROP TABLE accounts;

Queries

OpenAccountdiffers

CockroachDB · db/queries/accounts.sql
SQLite · db/queries/accounts.sql
1-- sgl.query(OpenAccount, one)
1-- sgl.query(OpenAccount, one)
2INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;
2INSERT INTO accounts (owner, balance) VALUES (?, ?) RETURNING id;

Depositdiffers

CockroachDB · db/queries/accounts.sql
SQLite · db/queries/accounts.sql
1-- sgl.query(Deposit, exec)
1-- sgl.query(Deposit, exec)
2-- $1 as sgl.param(amount)
2UPDATE accounts SET balance = balance + :amount WHERE id = :id;
3UPDATE accounts SET balance = balance + $1 WHERE id = $2;

Withdrawdiffers

CockroachDB · db/queries/accounts.sql
SQLite · db/queries/accounts.sql
1-- sgl.query(Withdraw, exec)
1-- sgl.query(Withdraw, exec)
2-- $1 as sgl.param(amount)
2UPDATE accounts SET balance = balance - :amount WHERE id = :id;
3UPDATE accounts SET balance = balance - $1 WHERE id = $2;

GetBalanceonly CockroachDB

CockroachDB · db/queries/accounts.sql
SQLite · not in this version
1-- sgl.query(GetBalance, one)
2SELECT balance FROM accounts WHERE id = $1;

SetBalanceonly CockroachDB

CockroachDB · db/queries/accounts.sql
SQLite · not in this version
1-- sgl.query(SetBalance, exec)
2UPDATE accounts SET balance = $1 WHERE id = $2;

ListAccountsidentical

CockroachDB · db/queries/accounts.sql
SQLite · db/queries/accounts.sql
1-- sgl.query(ListAccounts, many)
1-- sgl.query(ListAccounts, many)
2SELECT id, owner, balance FROM accounts ORDER BY id;
2SELECT id, owner, balance FROM accounts ORDER BY id;

What sgl generates

OpenAccountidentical

CockroachDB
SQLite
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
2
2
3type OpenAccountParams struct {
3type OpenAccountParams struct {
4	Owner   string
4	Owner   string
5	Balance int64
5	Balance int64
6}
6}
7
7
8type OpenAccountRow struct {
8type OpenAccountRow struct {
9	ID int64 `json:"id"`
9	ID int64 `json:"id"`
10}
10}

Depositidentical

CockroachDB
SQLite
1func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error
1func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error
2
2
3type DepositParams struct {
3type DepositParams struct {
4	Amount int64
4	Amount int64
5	ID     int64
5	ID     int64
6}
6}

Withdrawidentical

CockroachDB
SQLite
1func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error
1func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error
2
2
3type WithdrawParams struct {
3type WithdrawParams struct {
4	Amount int64
4	Amount int64
5	ID     int64
5	ID     int64
6}
6}

GetBalanceonly CockroachDB

CockroachDB
SQLite · not in this version
1func (queries *Queries) GetBalance(ctx context.Context, id int64) (GetBalanceRow, error)
2
3type GetBalanceRow struct {
4	Balance int64 `json:"balance"`
5}

SetBalanceonly CockroachDB

CockroachDB
SQLite · not in this version
1func (queries *Queries) SetBalance(ctx context.Context, params SetBalanceParams) error
2
3type SetBalanceParams struct {
4	Balance int64
5	ID      int64
6}

ListAccountsidentical

CockroachDB
SQLite
1func (queries *Queries) ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
1func (queries *Queries) ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
2
2
3type ListAccountsRow struct {
3type ListAccountsRow struct {
4	ID      int64  `json:"id"`
4	ID      int64  `json:"id"`
5	Owner   string `json:"owner"`
5	Owner   string `json:"owner"`
6	Balance int64  `json:"balance"`
6	Balance int64  `json:"balance"`
7}
7}

The program

example.godiffers

CockroachDB
SQLite
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"errors"
6	"errors"
7	"fmt"
7	"fmt"
8	"io"
8	"io"
9
9
10	"seagull.sh/seagull/examples/cockroachdb/023_transactions/internal/store"
10	"seagull.sh/seagull/examples/sqlite/023_transactions/internal/store"
11)
12
13const (
14	// serialisationFailure is the SQLSTATE CockroachDB reports when it aborts a transaction
15	// that conflicted with another, so that the client can run it again.
16	serialisationFailure = "40001"
17
18	// maxAttempts bounds how many times retrySerialisable runs a transaction.
19	maxAttempts = 3
20)
11)
21
12
22// sqlStateError is the method a driver's error offers to report its SQLSTATE. pgx's
23// *pgconn.PgError has it, so code can read the state without importing pgx.
24type sqlStateError interface {
25	error
26
27	// SQLState returns the five-character SQLSTATE code.
28	SQLState() string
29}
30
31// run opens two accounts in a transaction it manages itself, then makes two transfers
13// run opens two accounts in a transaction it manages itself, then makes two transfers
32// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
14// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
33// Last, it pays interest with a read-then-write transaction that another client's deposit
34// interrupts, which CockroachDB aborts and retrySerialisable runs again.
35//
15//
36// Takes database (*sql.DB) which is a migrated database.
16// Takes database (*sql.DB) which is a migrated database.
37// Takes output (io.Writer) which receives each step and the balances.
17// Takes output (io.Writer) which receives each step and the balances.
38//
18//
39// Returns error when a query fails other than as the example expects.
19// Returns error when a query fails other than as the example expects.
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
20func run(ctx context.Context, database *sql.DB, output io.Writer) error {
41	queries := store.New(database)
21	queries := store.New(database)
42
22
43	pip, gull, err := openAccounts(ctx, database, queries)
23	pip, gull, err := openAccounts(ctx, database, queries)
44	if err != nil {
24	if err != nil {
45		return fmt.Errorf("opening accounts: %w", err)
25		return fmt.Errorf("opening accounts: %w", err)
46	}
26	}
47	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
27	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
48	if err := printBalances(ctx, queries, output); err != nil {
28	if err := printBalances(ctx, queries, output); err != nil {
49		return err
29		return err
50	}
30	}
51
31
52	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
32	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
53		return fmt.Errorf("first transfer: %w", err)
33		return fmt.Errorf("first transfer: %w", err)
54	}
34	}
55	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
35	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
56	if err := printBalances(ctx, queries, output); err != nil {
36	if err := printBalances(ctx, queries, output); err != nil {
57		return err
37		return err
58	}
38	}
59
39
60	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
40	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
61	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
41	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
62	// undone with it.
42	// undone with it.
63	err = transfer(ctx, database, queries, gull, pip, 9000)
43	err = transfer(ctx, database, queries, gull, pip, 9000)
64	if err == nil {
44	if err == nil {
65		return errors.New("an overdrawing transfer committed")
45		return errors.New("an overdrawing transfer committed")
66	}
46	}
67	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
47	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
68	if err := printBalances(ctx, queries, output); err != nil {
69		return err
70	}
71
72	aborted, err := payInterest(ctx, database, queries, gull)
73	if err != nil {
74		return fmt.Errorf("paying interest: %w", err)
75	}
76	fmt.Fprintln(output, "paid Gull 10% interest while a deposit of 100 landed:")
77	// A 40001 message names timestamps and transaction IDs that change on every run, so only
78	// its SQLSTATE is printed.
79	for index, abort := range aborted {
80		fmt.Fprintf(output, "  attempt %d: aborted with SQLSTATE %s, retried\n", index+1, sqlState(abort))
81	}
82	fmt.Fprintf(output, "  attempt %d: committed\n", len(aborted)+1)
83	return printBalances(ctx, queries, output)
48	return printBalances(ctx, queries, output)
84}
49}
85
50
86// openAccounts opens both accounts in one transaction that it begins and commits itself.
51// openAccounts opens both accounts in one transaction that it begins and commits itself.
87// WithTx returns a copy of queries bound to that transaction, which suits a transaction
52// WithTx returns a copy of queries bound to that transaction, which suits a transaction
88// begun with options or shared with code that does not use Seagull.
53// begun with options or shared with code that does not use Seagull.
89//
54//
90// Takes database (*sql.DB) which begins the transaction.
55// Takes database (*sql.DB) which begins the transaction.
91// Takes queries (*store.Queries) which are bound to it.
56// Takes queries (*store.Queries) which are bound to it.
92//
57//
93// Returns pipID (int64) which is Pip's account ID.
58// Returns pipID (int64) which is Pip's account ID.
94// Returns gullID (int64) which is Gull's account ID.
59// Returns gullID (int64) which is Gull's account ID.
95// Returns err (error) when an account cannot be opened or the commit fails.
60// Returns err (error) when an account cannot be opened or the commit fails.
96func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
61func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
97	transaction, err := database.BeginTx(ctx, nil)
62	transaction, err := database.BeginTx(ctx, nil)
98	if err != nil {
63	if err != nil {
99		return 0, 0, err
64		return 0, 0, err
100	}
65	}
101	// Rollback after a successful Commit does nothing, so deferring it covers every early
66	// Rollback after a successful Commit does nothing, so deferring it covers every early
102	// return.
67	// return.
103	defer func() { _ = transaction.Rollback() }()
68	defer func() { _ = transaction.Rollback() }()
104
69
105	inTransaction := queries.WithTx(transaction)
70	inTransaction := queries.WithTx(transaction)
106	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
71	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
107	if err != nil {
72	if err != nil {
108		return 0, 0, err
73		return 0, 0, err
109	}
74	}
110	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
75	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
111	if err != nil {
76	if err != nil {
112		return 0, 0, err
77		return 0, 0, err
113	}
78	}
114	return pip.ID, gull.ID, transaction.Commit()
79	return pip.ID, gull.ID, transaction.Commit()
115}
80}
116
81
117// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
82// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
118// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
83// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
119// returns an error.
84// returns an error.
120//
85//
121// Takes database (*sql.DB) which begins the transaction.
86// Takes database (*sql.DB) which begins the transaction.
122// Takes queries (*store.Queries) which run the transfer.
87// Takes queries (*store.Queries) which run the transfer.
123// Takes from (int64) which is the account to debit.
88// Takes from (int64) which is the account to debit.
124// Takes to (int64) which is the account to credit.
89// Takes to (int64) which is the account to credit.
125// Takes amount (int64) which is the sum to move, in pence.
90// Takes amount (int64) which is the sum to move, in pence.
126//
91//
127// Returns error when either update fails; the transaction is then rolled back.
92// Returns error when either update fails; the transaction is then rolled back.
128func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
93func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
129	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
94	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
130		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
95		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
131			return err
96			return err
132		}
97		}
133		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
98		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
134	})
99	})
135}
100}
136
101
137// payInterest adds a tenth to an account's balance by reading the balance into Go and
138// writing back the result, under retrySerialisable.
139//
140// To show a conflict on demand, the first attempt deposits 100 into the same account on
141// the pool between its read and its write, standing in for another client. That deposit
142// commits first, so the balance this transaction read is stale. CockroachDB uses
143// SERIALIZABLE isolation by default, so instead of letting the write overwrite the
144// deposit it fails the write with SQLSTATE 40001, "restart transaction", and RunInTx
145// rolls back. The second attempt reads the balance with the deposit in it, and commits.
146//
147// Takes database (*sql.DB) which begins each attempt's transaction.
148// Takes queries (*store.Queries) which run the reads and writes.
149// Takes account (int64) which is the account to pay.
150//
151// Returns []error which holds the error of each attempt CockroachDB aborted.
152// Returns error when an attempt fails other than with 40001, or every attempt does.
153func payInterest(ctx context.Context, database *sql.DB, queries *store.Queries, account int64) ([]error, error) {
154	interrupted := false
155	return retrySerialisable(ctx, database, queries, func(inTransaction *store.Queries) error {
156		current, err := inTransaction.GetBalance(ctx, account)
157		if err != nil {
158			return err
159		}
160		if !interrupted {
161			interrupted = true
162			if err := queries.Deposit(ctx, store.DepositParams{Amount: 100, ID: account}); err != nil {
163				return fmt.Errorf("depositing from another client: %w", err)
164			}
165		}
166		return inTransaction.SetBalance(ctx, store.SetBalanceParams{Balance: current.Balance + current.Balance/10, ID: account})
167	})
168}
169
170// retrySerialisable runs work with RunInTx, and runs it again in a new transaction each
171// time CockroachDB aborts the transaction with SQLSTATE 40001, up to maxAttempts.
172//
173// The generated RunInTx commits or rolls back once and returns the error, so on
174// CockroachDB a caller retries itself. work must be safe to run more than once: each
175// attempt starts again from its first read, and anything work does outside the
176// transaction happens once per attempt. Under heavy contention, wait a short, jittered
177// while between attempts as well.
178//
179// Takes database (*sql.DB) which begins each transaction.
180// Takes queries (*store.Queries) which RunInTx binds to it.
181// Takes work (func(*store.Queries) error) which is the transaction's work.
182//
183// Returns []error which holds the error of each attempt CockroachDB aborted, so the
184// caller can log them.
185// Returns error which is nil once an attempt commits, work's own error, or the 40001 of
186// the last attempt.
187func retrySerialisable(ctx context.Context, database *sql.DB, queries *store.Queries, work func(*store.Queries) error) ([]error, error) {
188	var aborted []error
189	for range maxAttempts {
190		err := queries.RunInTx(ctx, database, work)
191		if sqlState(err) != serialisationFailure {
192			return aborted, err
193		}
194		aborted = append(aborted, err)
195	}
196	return aborted, fmt.Errorf("still conflicting after %d attempts: %w", maxAttempts, aborted[len(aborted)-1])
197}
198
199// sqlState returns the SQLSTATE that err, or an error it wraps, carries.
200//
201// Takes err (error) which is the error to inspect, or nil.
202//
203// Returns string which is the five-character code, or empty when err has none.
204func sqlState(err error) string {
205	stateError, hasState := errors.AsType[sqlStateError](err)
206	if !hasState {
207		return ""
208	}
209	return stateError.SQLState()
210}
211
212// printBalances writes every account's balance on one line.
102// printBalances writes every account's balance on one line.
213//
103//
214// Takes queries (*store.Queries) which read the accounts.
104// Takes queries (*store.Queries) which read the accounts.
215// Takes output (io.Writer) which receives the line.
105// Takes output (io.Writer) which receives the line.
216//
106//
217// Returns error when the accounts cannot be listed.
107// Returns error when the accounts cannot be listed.
218func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
108func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
219	accounts, err := queries.ListAccounts(ctx)
109	accounts, err := queries.ListAccounts(ctx)
220	if err != nil {
110	if err != nil {
221		return fmt.Errorf("listing accounts: %w", err)
111		return fmt.Errorf("listing accounts: %w", err)
222	}
112	}
223	fmt.Fprint(output, "  balances:")
113	fmt.Fprint(output, "  balances:")
224	for _, account := range accounts {
114	for _, account := range accounts {
225		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
115		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
226	}
116	}
227	fmt.Fprintln(output)
117	fmt.Fprintln(output)
228	return nil
118	return nil
229}
119}