| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571 |
- package pgserver
- import (
- "context"
- "fmt"
- "net"
- "strings"
- "sync"
- "testing"
- "time"
- "github.com/jackc/pgx/v5"
- "github.com/danfragoso/pizzasql-next/pkg/storage"
- "github.com/danfragoso/pizzasql-next/pkg/testkv"
- )
- // connectPGXPair starts one wire server and returns two independent client
- // connections to the same database so row-lock contention can be exercised.
- func connectPGXPair(t *testing.T) (*pgx.Conn, *pgx.Conn) {
- t.Helper()
- conns := connectPGXMany(t, 2)
- return conns[0], conns[1]
- }
- func connectPGXMany(t *testing.T, n int) []*pgx.Conn {
- t.Helper()
- kv := testkv.New(t)
- pool := kv.Pool(8)
- t.Cleanup(func() { pool.Close() })
- dbManager := storage.NewDatabaseManager(pool, &storage.DatabaseManagerConfig{
- DefaultDatabase: "rowlocks",
- AutoCreate: true,
- })
- listener, err := net.Listen("tcp", "127.0.0.1:0")
- if err != nil {
- t.Fatal(err)
- }
- t.Cleanup(func() { listener.Close() })
- var wg sync.WaitGroup
- t.Cleanup(wg.Wait)
- go func() {
- for {
- server, err := listener.Accept()
- if err != nil {
- return
- }
- wg.Add(1)
- go func() {
- defer wg.Done()
- _ = NewConnection(server, dbManager, true).Handle()
- }()
- }
- }()
- dsn := "postgres://tester@" + listener.Addr().String() + "/rowlocks?sslmode=disable"
- connect := func() *pgx.Conn {
- ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
- defer cancel()
- conn, err := pgx.Connect(ctx, dsn)
- if err != nil {
- t.Fatalf("pgx connect: %v", err)
- }
- t.Cleanup(func() { _ = conn.Close(context.Background()) })
- return conn
- }
- conns := make([]*pgx.Conn, 0, n)
- for i := 0; i < n; i++ {
- conns = append(conns, connect())
- }
- return conns
- }
- func TestRowLockSkipLockedClaimsOneRowPerTransaction(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- for conn, statements := range map[*pgx.Conn][]string{
- a: {
- `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT, owner TEXT)`,
- `INSERT INTO jobs VALUES (1, 'pending', NULL), (2, 'pending', NULL), (3, 'pending', NULL)`,
- },
- } {
- for _, sql := range statements {
- if _, err := conn.Exec(ctx, sql); err != nil {
- t.Fatalf("%s: %v", sql, err)
- }
- }
- }
- claim := func(conn *pgx.Conn, owner string) int32 {
- tx, err := conn.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tx.Rollback(ctx)
- var id int32
- if err := tx.QueryRow(ctx, `SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED`).Scan(&id); err != nil {
- t.Fatalf("%s claim: %v", owner, err)
- }
- if _, err := tx.Exec(ctx, `UPDATE jobs SET status = 'taken', owner = $1 WHERE id = $2`, owner, id); err != nil {
- t.Fatalf("%s update: %v", owner, err)
- }
- if err := tx.Commit(ctx); err != nil {
- t.Fatalf("%s commit: %v", owner, err)
- }
- return id
- }
- // The first transaction keeps its lock open while the second claims.
- tx, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tx.Rollback(ctx)
- var first int32
- if err := tx.QueryRow(ctx, `SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED`).Scan(&first); err != nil {
- t.Fatalf("first claim: %v", err)
- }
- if first != 1 {
- t.Fatalf("first claim = %d, want 1", first)
- }
- second := claim(b, "b")
- if second == first {
- t.Fatalf("both transactions claimed row %d", first)
- }
- if _, err := tx.Exec(ctx, `UPDATE jobs SET status = 'taken', owner = 'a' WHERE id = $1`, first); err != nil {
- t.Fatalf("first update: %v", err)
- }
- if err := tx.Commit(ctx); err != nil {
- t.Fatalf("first commit: %v", err)
- }
- rows, err := a.Query(ctx, `SELECT id, status, owner FROM jobs ORDER BY id`)
- if err != nil {
- t.Fatal(err)
- }
- defer rows.Close()
- count := 0
- taken := 0
- for rows.Next() {
- var id int32
- var status string
- var owner *string
- if err := rows.Scan(&id, &status, &owner); err != nil {
- t.Fatal(err)
- }
- if status == "taken" {
- if owner == nil {
- t.Fatalf("row %d is taken without an owner", id)
- }
- taken++
- } else if id != 3 || status != "pending" {
- t.Fatalf("row %d = %s/%v, want pending or taken", id, status, owner)
- }
- count++
- }
- if count != 3 || taken != 2 {
- t.Fatalf("rows/taken = %d/%d, want 3/2", count, taken)
- }
- }
- func TestRowLockNowaitAndWriterConflict(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending'), (2, 'pending')`); err != nil {
- t.Fatal(err)
- }
- tx, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tx.Rollback(ctx)
- if _, err := tx.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`); err != nil {
- t.Fatalf("lock: %v", err)
- }
- _, err = b.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE NOWAIT`)
- if err == nil || !strings.Contains(err.Error(), "55P03") {
- t.Fatalf("NOWAIT error = %v, want SQLSTATE 55P03", err)
- }
- // A writer that does not use a locking clause must not commit over the
- // lock; it gets the optimistic serialization failure.
- _, err = b.Exec(ctx, `UPDATE jobs SET status = 'stolen' WHERE id = 1`)
- if err == nil || !strings.Contains(err.Error(), "40001") {
- t.Fatalf("writer error = %v, want SQLSTATE 40001", err)
- }
- }
- func TestRowLockBlockingWaitsForCommit(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending')`); err != nil {
- t.Fatal(err)
- }
- tx, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- if _, err := tx.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- done := make(chan error, 1)
- go func() {
- var id int32
- err := b.QueryRow(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`).Scan(&id)
- done <- err
- }()
- select {
- case err := <-done:
- t.Fatalf("blocking FOR UPDATE returned before commit: %v", err)
- case <-time.After(150 * time.Millisecond):
- }
- if err := tx.Commit(ctx); err != nil {
- t.Fatal(err)
- }
- select {
- case err := <-done:
- if err != nil {
- t.Fatalf("blocking FOR UPDATE: %v", err)
- }
- case <-time.After(5 * time.Second):
- t.Fatal("blocking FOR UPDATE did not resume")
- }
- }
- func TestRowLockDeadlockDetected(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1), (2)`); err != nil {
- t.Fatal(err)
- }
- ta, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer ta.Rollback(ctx)
- tb, err := b.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tb.Rollback(ctx)
- if _, err := ta.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- if _, err := tb.Exec(ctx, `SELECT id FROM jobs WHERE id = 2 FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- blocked := make(chan error, 1)
- go func() {
- _, err := ta.Exec(ctx, `SELECT id FROM jobs WHERE id = 2 FOR UPDATE`)
- blocked <- err
- }()
- time.Sleep(100 * time.Millisecond)
- _, err = tb.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`)
- if err == nil || !strings.Contains(err.Error(), "40P01") {
- t.Fatalf("deadlock error = %v, want SQLSTATE 40P01", err)
- }
- if err := tb.Rollback(ctx); err != nil {
- t.Fatal(err)
- }
- select {
- case err := <-blocked:
- if err != nil {
- t.Fatalf("waiting transaction after deadlock peer rollback: %v", err)
- }
- case <-time.After(5 * time.Second):
- t.Fatal("waiting transaction did not resume")
- }
- }
- func TestRowLockShareAllowsReadersAndBlocksWriter(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending')`); err != nil {
- t.Fatal(err)
- }
- ta, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer ta.Rollback(ctx)
- tb, err := b.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tb.Rollback(ctx)
- if _, err := ta.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR SHARE`); err != nil {
- t.Fatal(err)
- }
- if _, err := tb.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR SHARE`); err != nil {
- t.Fatalf("second FOR SHARE: %v", err)
- }
- if _, err := tb.Exec(ctx, `UPDATE jobs SET status = 'taken' WHERE id = 1`); err != nil {
- t.Fatalf("staging writer: %v", err)
- }
- err = tb.Commit(ctx)
- if err == nil || !strings.Contains(err.Error(), "40001") {
- t.Fatalf("writer with share lock held error = %v, want 40001", err)
- }
- }
- func TestRowLockAutocommitReleasesImmediately(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending')`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- if _, err := b.Exec(ctx, `UPDATE jobs SET status = 'taken' WHERE id = 1`); err != nil {
- t.Fatalf("autocommit lock was not released: %v", err)
- }
- }
- func TestRowLockQueueWorkersDoNotDoubleClaim(t *testing.T) {
- const workers = 4
- const jobs = 40
- conns := connectPGXMany(t, workers)
- ctx := context.Background()
- if _, err := conns[0].Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT, owner TEXT)`); err != nil {
- t.Fatal(err)
- }
- var insert strings.Builder
- insert.WriteString(`INSERT INTO jobs VALUES `)
- for i := 1; i <= jobs; i++ {
- if i > 1 {
- insert.WriteString(", ")
- }
- fmt.Fprintf(&insert, "(%d, 'pending', NULL)", i)
- }
- if _, err := conns[0].Exec(ctx, insert.String()); err != nil {
- t.Fatal(err)
- }
- var wg sync.WaitGroup
- errs := make(chan error, workers)
- for w, conn := range conns {
- wg.Add(1)
- go func(w int, conn *pgx.Conn) {
- defer wg.Done()
- owner := fmt.Sprintf("worker-%d", w)
- for {
- tx, err := conn.Begin(ctx)
- if err != nil {
- errs <- err
- return
- }
- var id int32
- err = tx.QueryRow(ctx, `SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED`).Scan(&id)
- if err != nil {
- _ = tx.Rollback(ctx)
- if err == pgx.ErrNoRows {
- return
- }
- errs <- err
- return
- }
- if _, err := tx.Exec(ctx, `UPDATE jobs SET status = 'taken', owner = $1 WHERE id = $2`, owner, id); err != nil {
- _ = tx.Rollback(ctx)
- errs <- err
- return
- }
- if err := tx.Commit(ctx); err != nil {
- errs <- err
- return
- }
- }
- }(w, conn)
- }
- wg.Wait()
- close(errs)
- for err := range errs {
- t.Fatalf("worker error: %v", err)
- }
- var taken, pending int
- if err := conns[0].QueryRow(ctx, `SELECT COUNT(*) FROM jobs WHERE status = 'taken'`).Scan(&taken); err != nil {
- t.Fatal(err)
- }
- if err := conns[0].QueryRow(ctx, `SELECT COUNT(*) FROM jobs WHERE status = 'pending'`).Scan(&pending); err != nil {
- t.Fatal(err)
- }
- if taken != jobs || pending != 0 {
- t.Fatalf("taken/pending = %d/%d, want %d/0", taken, pending, jobs)
- }
- var distinctOwners int
- if err := conns[0].QueryRow(ctx, `SELECT COUNT(DISTINCT id) FROM jobs`).Scan(&distinctOwners); err != nil {
- t.Fatal(err)
- }
- if distinctOwners != jobs {
- t.Fatalf("distinct ids = %d, want %d", distinctOwners, jobs)
- }
- }
- func TestRowLockAliasedTableAndEmptySkip(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending'), (2, 'pending')`); err != nil {
- t.Fatal(err)
- }
- tx, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tx.Rollback(ctx)
- var id int32
- if err := tx.QueryRow(ctx, `SELECT j.id FROM jobs AS j WHERE j.id = 1 FOR UPDATE`).Scan(&id); err != nil {
- t.Fatalf("aliased locking select: %v", err)
- }
- if id != 1 {
- t.Fatalf("aliased id = %d", id)
- }
- if err := tx.Commit(ctx); err != nil {
- t.Fatal(err)
- }
- // Hold both rows, then confirm SKIP LOCKED reports no candidate.
- ta, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer ta.Rollback(ctx)
- if _, err := ta.Exec(ctx, `SELECT id FROM jobs FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- tb, err := b.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tb.Rollback(ctx)
- var n int32
- if err := tb.QueryRow(ctx, `SELECT id FROM jobs WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED`).Scan(&n); err != pgx.ErrNoRows {
- t.Fatalf("all-locked SKIP LOCKED = %v, want ErrNoRows", err)
- }
- }
- func TestRowLockRollbackReleases(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending')`); err != nil {
- t.Fatal(err)
- }
- tx, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- if _, err := tx.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- if err := tx.Rollback(ctx); err != nil {
- t.Fatal(err)
- }
- if _, err := b.Exec(ctx, `UPDATE jobs SET status = 'taken' WHERE id = 1`); err != nil {
- t.Fatalf("rollback did not release lock: %v", err)
- }
- }
- func TestRowLockBlocksAutocommitDDL(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending')`); err != nil {
- t.Fatal(err)
- }
- tx, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- defer tx.Rollback(ctx)
- if _, err := tx.Exec(ctx, `SELECT id FROM jobs FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- if _, err := b.Exec(ctx, `ALTER TABLE jobs ADD COLUMN note TEXT`); err == nil || !strings.Contains(err.Error(), "40001") {
- t.Fatalf("DDL with row lock held error = %v, want 40001", err)
- }
- }
- func TestRowLockSyntheticRowIDTable(t *testing.T) {
- a, _ := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE notes (body TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO notes VALUES ('hello')`); err != nil {
- t.Fatal(err)
- }
- var body string
- if err := a.QueryRow(ctx, `SELECT body FROM notes FOR UPDATE`).Scan(&body); err != nil {
- t.Fatalf("locking synthetic-rowid table: %v", err)
- }
- if body != "hello" {
- t.Fatalf("body = %q", body)
- }
- }
- func TestRowLockSkippedPrimaryKeyDoesNotPoisonCommit(t *testing.T) {
- a, b := connectPGXPair(t)
- ctx := context.Background()
- if _, err := a.Exec(ctx, `CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)`); err != nil {
- t.Fatal(err)
- }
- if _, err := a.Exec(ctx, `INSERT INTO jobs VALUES (1, 'pending'), (2, 'pending')`); err != nil {
- t.Fatal(err)
- }
- ta, err := a.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- if _, err := ta.Exec(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE`); err != nil {
- t.Fatal(err)
- }
- tb, err := b.Begin(ctx)
- if err != nil {
- t.Fatal(err)
- }
- var id int32
- if err := tb.QueryRow(ctx, `SELECT id FROM jobs WHERE id = 1 FOR UPDATE SKIP LOCKED`).Scan(&id); err != pgx.ErrNoRows {
- t.Fatalf("skipped primary key = %v, want ErrNoRows", err)
- }
- if _, err := tb.Exec(ctx, `UPDATE jobs SET status = 'b' WHERE id = 2`); err != nil {
- t.Fatal(err)
- }
- if _, err := ta.Exec(ctx, `UPDATE jobs SET status = 'a' WHERE id = 1`); err != nil {
- t.Fatal(err)
- }
- if err := ta.Commit(ctx); err != nil {
- t.Fatal(err)
- }
- if err := tb.Commit(ctx); err != nil {
- t.Fatalf("skipped read poisoned commit: %v", err)
- }
- }
|