Compare · 023

Transactions: SQLite and CockroachDB

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

Compare SQLite withPostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

SQLite
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: bank
4  - name: bank
5    dialect: sqlite
5    dialect: cockroachdb
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

SQLite
CockroachDB
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2    id      INTEGER PRIMARY KEY,
2    id      INT8    NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
3    owner   TEXT    NOT NULL,
3    owner   STRING  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

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

Queries

OpenAccountdiffers

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

Depositdiffers

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

Withdrawdiffers

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

ListAccountsidentical

SQLite · db/queries/accounts.sql
CockroachDB · 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;

GetBalanceonly CockroachDB

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

SetBalanceonly CockroachDB

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

What sgl generates

OpenAccountidentical

SQLite
CockroachDB
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

SQLite
CockroachDB
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

SQLite
CockroachDB
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}

ListAccountsidentical

SQLite
CockroachDB
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}

GetBalanceonly CockroachDB

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

SetBalanceonly CockroachDB

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

The program

example.godiffers

SQLite
CockroachDB
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/sqlite/023_transactions/internal/store"
10	"seagull.sh/seagull/examples/cockroachdb/023_transactions/internal/store"
11)
11)
12
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)
21
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
13// run opens two accounts in a transaction it manages itself, then makes two transfers
31// run opens two accounts in a transaction it manages itself, then makes two transfers
14// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
32// 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.
15//
35//
16// Takes database (*sql.DB) which is a migrated database.
36// Takes database (*sql.DB) which is a migrated database.
17// Takes output (io.Writer) which receives each step and the balances.
37// Takes output (io.Writer) which receives each step and the balances.
18//
38//
19// Returns error when a query fails other than as the example expects.
39// Returns error when a query fails other than as the example expects.
20func run(ctx context.Context, database *sql.DB, output io.Writer) error {
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
21	queries := store.New(database)
41	queries := store.New(database)
22
42
23	pip, gull, err := openAccounts(ctx, database, queries)
43	pip, gull, err := openAccounts(ctx, database, queries)
24	if err != nil {
44	if err != nil {
25		return fmt.Errorf("opening accounts: %w", err)
45		return fmt.Errorf("opening accounts: %w", err)
26	}
46	}
27	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
47	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
28	if err := printBalances(ctx, queries, output); err != nil {
48	if err := printBalances(ctx, queries, output); err != nil {
29		return err
49		return err
30	}
50	}
31
51
32	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
52	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
33		return fmt.Errorf("first transfer: %w", err)
53		return fmt.Errorf("first transfer: %w", err)
34	}
54	}
35	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
55	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
36	if err := printBalances(ctx, queries, output); err != nil {
56	if err := printBalances(ctx, queries, output); err != nil {
37		return err
57		return err
38	}
58	}
39
59
40	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
60	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
41	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
61	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
42	// undone with it.
62	// undone with it.
43	err = transfer(ctx, database, queries, gull, pip, 9000)
63	err = transfer(ctx, database, queries, gull, pip, 9000)
44	if err == nil {
64	if err == nil {
45		return errors.New("an overdrawing transfer committed")
65		return errors.New("an overdrawing transfer committed")
46	}
66	}
47	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
67	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)
48	return printBalances(ctx, queries, output)
83	return printBalances(ctx, queries, output)
49}
84}
50
85
51// openAccounts opens both accounts in one transaction that it begins and commits itself.
86// openAccounts opens both accounts in one transaction that it begins and commits itself.
52// WithTx returns a copy of queries bound to that transaction, which suits a transaction
87// WithTx returns a copy of queries bound to that transaction, which suits a transaction
53// begun with options or shared with code that does not use Seagull.
88// begun with options or shared with code that does not use Seagull.
54//
89//
55// Takes database (*sql.DB) which begins the transaction.
90// Takes database (*sql.DB) which begins the transaction.
56// Takes queries (*store.Queries) which are bound to it.
91// Takes queries (*store.Queries) which are bound to it.
57//
92//
58// Returns pipID (int64) which is Pip's account ID.
93// Returns pipID (int64) which is Pip's account ID.
59// Returns gullID (int64) which is Gull's account ID.
94// Returns gullID (int64) which is Gull's account ID.
60// Returns err (error) when an account cannot be opened or the commit fails.
95// Returns err (error) when an account cannot be opened or the commit fails.
61func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
96func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
62	transaction, err := database.BeginTx(ctx, nil)
97	transaction, err := database.BeginTx(ctx, nil)
63	if err != nil {
98	if err != nil {
64		return 0, 0, err
99		return 0, 0, err
65	}
100	}
66	// Rollback after a successful Commit does nothing, so deferring it covers every early
101	// Rollback after a successful Commit does nothing, so deferring it covers every early
67	// return.
102	// return.
68	defer func() { _ = transaction.Rollback() }()
103	defer func() { _ = transaction.Rollback() }()
69
104
70	inTransaction := queries.WithTx(transaction)
105	inTransaction := queries.WithTx(transaction)
71	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
106	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
72	if err != nil {
107	if err != nil {
73		return 0, 0, err
108		return 0, 0, err
74	}
109	}
75	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
110	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
76	if err != nil {
111	if err != nil {
77		return 0, 0, err
112		return 0, 0, err
78	}
113	}
79	return pip.ID, gull.ID, transaction.Commit()
114	return pip.ID, gull.ID, transaction.Commit()
80}
115}
81
116
82// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
117// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
83// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
118// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
84// returns an error.
119// returns an error.
85//
120//
86// Takes database (*sql.DB) which begins the transaction.
121// Takes database (*sql.DB) which begins the transaction.
87// Takes queries (*store.Queries) which run the transfer.
122// Takes queries (*store.Queries) which run the transfer.
88// Takes from (int64) which is the account to debit.
123// Takes from (int64) which is the account to debit.
89// Takes to (int64) which is the account to credit.
124// Takes to (int64) which is the account to credit.
90// Takes amount (int64) which is the sum to move, in pence.
125// Takes amount (int64) which is the sum to move, in pence.
91//
126//
92// Returns error when either update fails; the transaction is then rolled back.
127// Returns error when either update fails; the transaction is then rolled back.
93func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
128func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
94	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
129	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
95		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
130		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
96			return err
131			return err
97		}
132		}
98		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
133		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
99	})
134	})
100}
135}
101
136
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
102// printBalances writes every account's balance on one line.
212// printBalances writes every account's balance on one line.
103//
213//
104// Takes queries (*store.Queries) which read the accounts.
214// Takes queries (*store.Queries) which read the accounts.
105// Takes output (io.Writer) which receives the line.
215// Takes output (io.Writer) which receives the line.
106//
216//
107// Returns error when the accounts cannot be listed.
217// Returns error when the accounts cannot be listed.
108func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
218func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
109	accounts, err := queries.ListAccounts(ctx)
219	accounts, err := queries.ListAccounts(ctx)
110	if err != nil {
220	if err != nil {
111		return fmt.Errorf("listing accounts: %w", err)
221		return fmt.Errorf("listing accounts: %w", err)
112	}
222	}
113	fmt.Fprint(output, "  balances:")
223	fmt.Fprint(output, "  balances:")
114	for _, account := range accounts {
224	for _, account := range accounts {
115		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
225		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
116	}
226	}
117	fmt.Fprintln(output)
227	fmt.Fprintln(output)
118	return nil
228	return nil
119}
229}