Compare · 023

Transactions: CockroachDB and DuckDB

Every file of the CockroachDB and DuckDB versions side by side, with the lines that differ marked. 4 parameter or column types differ in Go.

Compare CockroachDB withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
CockroachDB
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: bank
4 - name: bank
5 dialect: cockroachdb
5 dialect: duckdb
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
DuckDB
1CREATE SEQUENCE account_ids;
2
1CREATE TABLE accounts (
3CREATE TABLE accounts (
2 id INT8 NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
4 id BIGINT PRIMARY KEY DEFAULT nextval('account_ids'),
3 owner STRING NOT NULL,
5 owner VARCHAR NOT NULL,
4 balance INTEGER NOT NULL CHECK (balance >= 0)
6 balance INTEGER NOT NULL CHECK (balance >= 0)
5);
7);
db/migrations/0001_accounts.down.sqldiffers
CockroachDB
DuckDB
1DROP TABLE accounts;
1DROP TABLE accounts;
2DROP SEQUENCE account_ids;

Queries

OpenAccountidentical
CockroachDB · db/queries/accounts.sql
DuckDB · 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 ($1, $2) RETURNING id;
Depositdiffers
CockroachDB · db/queries/accounts.sql
DuckDB · 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
DuckDB · 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
DuckDB · not in this version
1-- sgl.query(GetBalance, one)
2SELECT balance FROM accounts WHERE id = $1;
SetBalanceonly CockroachDB
CockroachDB · db/queries/accounts.sql
DuckDB · not in this version
1-- sgl.query(SetBalance, exec)
2UPDATE accounts SET balance = $1 WHERE id = $2;
ListAccountsidentical
CockroachDB · db/queries/accounts.sql
DuckDB · 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

OpenAccountdiffers
CockroachDB
DuckDB
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 int32
6}
6}
7
7
8type OpenAccountRow struct {
8type OpenAccountRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10}
10}
Depositdiffers
CockroachDB
DuckDB
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 int32
5 ID int64
5 ID int64
6}
6}
Withdrawdiffers
CockroachDB
DuckDB
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 int32
5 ID int64
5 ID int64
6}
6}
GetBalanceonly CockroachDB
CockroachDB
DuckDB · 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
DuckDB · 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}
ListAccountsdiffers
CockroachDB
DuckDB
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 int32 `json:"balance"`
7}
7}

The program

example.godiffers
CockroachDB
DuckDB
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/duckdb/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 (int32) 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 int64, amount int32) 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}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldCockroachDBDuckDB
OpenAccountparameter balanceint64int32
Depositparameter amountint64int32
Withdrawparameter amountint64int32
ListAccountscolumn balanceint64int32