slachiewicz commented on code in PR #2589:
URL: https://github.com/apache/iceberg-go/pull/2589#discussion_r4578560628
##########
catalog/sql/sql.go:
##########
@@ -305,18 +306,146 @@ func withReadTx[R any](ctx context.Context, db *bun.DB,
fn func(context.Context,
}
func withWriteTx(ctx context.Context, db *bun.DB, fn func(context.Context,
bun.Tx) error) error {
- return db.RunInTx(ctx, &sql.TxOptions{Isolation: sql.LevelDefault},
func(ctx context.Context, tx bun.Tx) error {
+ return runWriteTx(ctx, db, &sql.TxOptions{Isolation: sql.LevelDefault},
fn)
+}
+
+// runWriteTx runs fn in a write transaction. On SQLite it runs on a dedicated
pooled
+// connection whose busy_timeout it caps to ctx's deadline for the transaction
and then
+// restores, and it takes the write lock before fn reads anything.
+func runWriteTx(ctx context.Context, db *bun.DB, opts *sql.TxOptions, fn
func(context.Context, bun.Tx) error) error {
+ if db.Dialect().Name() != dialect.SQLite {
+ return db.RunInTx(ctx, opts, fn)
+ }
+
+ conn, err := db.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer conn.Close()
+
+ busyTimeout, capped, restore, err := capSQLiteBusyTimeout(ctx, conn)
+ if err != nil {
+ return err
+ }
+ defer restore()
+
+ return conn.RunInTx(ctx, opts, func(ctx context.Context, tx bun.Tx)
error {
+ if err := lockSQLiteForWrite(ctx, tx, busyTimeout, capped); err
!= nil {
+ return err
+ }
+
return fn(ctx, tx)
})
}
+// sqliteWriteLockQuery writes no rows; issuing it takes SQLite's write lock.
+const sqliteWriteLockQuery = "UPDATE iceberg_tables SET catalog_name =
catalog_name WHERE 0"
Review Comment:
Done in a76b739: the statement is built with
`tx.NewUpdate().Model((*sqlIcebergTable)(nil))`.
##########
catalog/sql/sql.go:
##########
@@ -305,18 +306,146 @@ func withReadTx[R any](ctx context.Context, db *bun.DB,
fn func(context.Context,
}
func withWriteTx(ctx context.Context, db *bun.DB, fn func(context.Context,
bun.Tx) error) error {
- return db.RunInTx(ctx, &sql.TxOptions{Isolation: sql.LevelDefault},
func(ctx context.Context, tx bun.Tx) error {
+ return runWriteTx(ctx, db, &sql.TxOptions{Isolation: sql.LevelDefault},
fn)
+}
+
+// runWriteTx runs fn in a write transaction. On SQLite it runs on a dedicated
pooled
+// connection whose busy_timeout it caps to ctx's deadline for the transaction
and then
+// restores, and it takes the write lock before fn reads anything.
+func runWriteTx(ctx context.Context, db *bun.DB, opts *sql.TxOptions, fn
func(context.Context, bun.Tx) error) error {
+ if db.Dialect().Name() != dialect.SQLite {
+ return db.RunInTx(ctx, opts, fn)
+ }
+
+ conn, err := db.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer conn.Close()
+
+ busyTimeout, capped, restore, err := capSQLiteBusyTimeout(ctx, conn)
+ if err != nil {
+ return err
+ }
+ defer restore()
+
+ return conn.RunInTx(ctx, opts, func(ctx context.Context, tx bun.Tx)
error {
+ if err := lockSQLiteForWrite(ctx, tx, busyTimeout, capped); err
!= nil {
+ return err
+ }
+
return fn(ctx, tx)
})
}
+// sqliteWriteLockQuery writes no rows; issuing it takes SQLite's write lock.
+const sqliteWriteLockQuery = "UPDATE iceberg_tables SET catalog_name =
catalog_name WHERE 0"
+
+// lockSQLiteForWrite takes SQLite's write lock as the transaction's first
statement.
+//
+// database/sql begins SQLite transactions as DEFERRED, so a write transaction
that
+// reads first holds a read snapshot when its first write asks for the write
lock.
+// SQLite does not consult the busy handler for that upgrade: it returns
SQLITE_BUSY
+// at once, whatever busy_timeout the connection sets. A write that matches no
rows,
+// issued before any read, requests the lock from no transaction, which is the
one case
+// where SQLite waits on the busy handler. Writers then queue on busy_timeout
instead of
+// failing fast, and the reads that follow see the latest committed state.
+//
+// The statement takes the lock even when the transaction goes on to return a
sentinel
+// without writing, such as an identifier that already exists, so those paths
wait for
+// a concurrent writer too.
+//
+// capped reports that busy_timeout was lowered to ctx's deadline, so a lock
timeout
+// means the deadline ended the wait even if ctx has not yet noticed:
busy_timeout is
+// whole milliseconds and can run out just before the deadline does.
+func lockSQLiteForWrite(ctx context.Context, tx bun.Tx, busyTimeout int64,
capped bool) error {
+ _, err := tx.ExecContext(ctx, sqliteWriteLockQuery)
+ switch {
+ case err == nil:
+ return nil
+ case ctx.Err() != nil, capped && isSQLiteLockError(err):
+ // mattn reports a lock timeout here even when ctx's deadline
ended the
+ // wait; keep the context error so callers can match it.
+ cause := context.Cause(ctx)
+ if cause == nil {
+ cause = context.DeadlineExceeded
+ }
+
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
errors.Join(cause, err))
+ case busyTimeout > 0 && isSQLiteLockError(err):
Review Comment:
Fixed in a76b739: only `database is locked` counts as a wait SQLite has
done, so the table lock goes to the retry budget again, and it is no longer
reported as a deadline under a capped timeout.
`TestWriteRetriesSharedCacheTableLock` reproduces it on mattn with
`cache=shared`; it succeeds after about 160 ms.
##########
catalog/sql/sql.go:
##########
@@ -305,18 +306,146 @@ func withReadTx[R any](ctx context.Context, db *bun.DB,
fn func(context.Context,
}
func withWriteTx(ctx context.Context, db *bun.DB, fn func(context.Context,
bun.Tx) error) error {
- return db.RunInTx(ctx, &sql.TxOptions{Isolation: sql.LevelDefault},
func(ctx context.Context, tx bun.Tx) error {
+ return runWriteTx(ctx, db, &sql.TxOptions{Isolation: sql.LevelDefault},
fn)
+}
+
+// runWriteTx runs fn in a write transaction. On SQLite it runs on a dedicated
pooled
+// connection whose busy_timeout it caps to ctx's deadline for the transaction
and then
+// restores, and it takes the write lock before fn reads anything.
+func runWriteTx(ctx context.Context, db *bun.DB, opts *sql.TxOptions, fn
func(context.Context, bun.Tx) error) error {
+ if db.Dialect().Name() != dialect.SQLite {
+ return db.RunInTx(ctx, opts, fn)
+ }
+
+ conn, err := db.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer conn.Close()
+
+ busyTimeout, capped, restore, err := capSQLiteBusyTimeout(ctx, conn)
+ if err != nil {
+ return err
+ }
+ defer restore()
+
+ return conn.RunInTx(ctx, opts, func(ctx context.Context, tx bun.Tx)
error {
+ if err := lockSQLiteForWrite(ctx, tx, busyTimeout, capped); err
!= nil {
+ return err
+ }
+
return fn(ctx, tx)
})
}
+// sqliteWriteLockQuery writes no rows; issuing it takes SQLite's write lock.
+const sqliteWriteLockQuery = "UPDATE iceberg_tables SET catalog_name =
catalog_name WHERE 0"
+
+// lockSQLiteForWrite takes SQLite's write lock as the transaction's first
statement.
+//
+// database/sql begins SQLite transactions as DEFERRED, so a write transaction
that
+// reads first holds a read snapshot when its first write asks for the write
lock.
+// SQLite does not consult the busy handler for that upgrade: it returns
SQLITE_BUSY
+// at once, whatever busy_timeout the connection sets. A write that matches no
rows,
+// issued before any read, requests the lock from no transaction, which is the
one case
+// where SQLite waits on the busy handler. Writers then queue on busy_timeout
instead of
+// failing fast, and the reads that follow see the latest committed state.
+//
+// The statement takes the lock even when the transaction goes on to return a
sentinel
+// without writing, such as an identifier that already exists, so those paths
wait for
+// a concurrent writer too.
+//
+// capped reports that busy_timeout was lowered to ctx's deadline, so a lock
timeout
+// means the deadline ended the wait even if ctx has not yet noticed:
busy_timeout is
+// whole milliseconds and can run out just before the deadline does.
+func lockSQLiteForWrite(ctx context.Context, tx bun.Tx, busyTimeout int64,
capped bool) error {
+ _, err := tx.ExecContext(ctx, sqliteWriteLockQuery)
+ switch {
+ case err == nil:
+ return nil
+ case ctx.Err() != nil, capped && isSQLiteLockError(err):
+ // mattn reports a lock timeout here even when ctx's deadline
ended the
+ // wait; keep the context error so callers can match it.
+ cause := context.Cause(ctx)
+ if cause == nil {
+ cause = context.DeadlineExceeded
+ }
+
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
errors.Join(cause, err))
+ case busyTimeout > 0 && isSQLiteLockError(err):
+ return &sqliteLockWaitError{err: err}
+ default:
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
err)
Review Comment:
Kept the sentinels, in a76b739: on `attempt to write a readonly database`
the transaction carries on without the lock, so the reads return what they did
on `main` and a real write fails with the driver's error.
`TestReadOnlyDatabaseKeepsCatalogSentinels` covers it. A missing
`iceberg_tables` still returns `failed to acquire sqlite write lock: no such
table`; the description says so.
##########
catalog/sql/sql.go:
##########
@@ -305,18 +306,146 @@ func withReadTx[R any](ctx context.Context, db *bun.DB,
fn func(context.Context,
}
func withWriteTx(ctx context.Context, db *bun.DB, fn func(context.Context,
bun.Tx) error) error {
- return db.RunInTx(ctx, &sql.TxOptions{Isolation: sql.LevelDefault},
func(ctx context.Context, tx bun.Tx) error {
+ return runWriteTx(ctx, db, &sql.TxOptions{Isolation: sql.LevelDefault},
fn)
+}
+
+// runWriteTx runs fn in a write transaction. On SQLite it runs on a dedicated
pooled
+// connection whose busy_timeout it caps to ctx's deadline for the transaction
and then
+// restores, and it takes the write lock before fn reads anything.
+func runWriteTx(ctx context.Context, db *bun.DB, opts *sql.TxOptions, fn
func(context.Context, bun.Tx) error) error {
+ if db.Dialect().Name() != dialect.SQLite {
+ return db.RunInTx(ctx, opts, fn)
+ }
+
+ conn, err := db.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer conn.Close()
+
+ busyTimeout, capped, restore, err := capSQLiteBusyTimeout(ctx, conn)
+ if err != nil {
+ return err
+ }
+ defer restore()
+
+ return conn.RunInTx(ctx, opts, func(ctx context.Context, tx bun.Tx)
error {
+ if err := lockSQLiteForWrite(ctx, tx, busyTimeout, capped); err
!= nil {
+ return err
+ }
+
return fn(ctx, tx)
})
}
+// sqliteWriteLockQuery writes no rows; issuing it takes SQLite's write lock.
+const sqliteWriteLockQuery = "UPDATE iceberg_tables SET catalog_name =
catalog_name WHERE 0"
+
+// lockSQLiteForWrite takes SQLite's write lock as the transaction's first
statement.
+//
+// database/sql begins SQLite transactions as DEFERRED, so a write transaction
that
+// reads first holds a read snapshot when its first write asks for the write
lock.
+// SQLite does not consult the busy handler for that upgrade: it returns
SQLITE_BUSY
+// at once, whatever busy_timeout the connection sets. A write that matches no
rows,
+// issued before any read, requests the lock from no transaction, which is the
one case
+// where SQLite waits on the busy handler. Writers then queue on busy_timeout
instead of
+// failing fast, and the reads that follow see the latest committed state.
+//
+// The statement takes the lock even when the transaction goes on to return a
sentinel
+// without writing, such as an identifier that already exists, so those paths
wait for
+// a concurrent writer too.
+//
+// capped reports that busy_timeout was lowered to ctx's deadline, so a lock
timeout
+// means the deadline ended the wait even if ctx has not yet noticed:
busy_timeout is
+// whole milliseconds and can run out just before the deadline does.
+func lockSQLiteForWrite(ctx context.Context, tx bun.Tx, busyTimeout int64,
capped bool) error {
+ _, err := tx.ExecContext(ctx, sqliteWriteLockQuery)
+ switch {
+ case err == nil:
+ return nil
+ case ctx.Err() != nil, capped && isSQLiteLockError(err):
+ // mattn reports a lock timeout here even when ctx's deadline
ended the
+ // wait; keep the context error so callers can match it.
+ cause := context.Cause(ctx)
+ if cause == nil {
+ cause = context.DeadlineExceeded
+ }
+
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
errors.Join(cause, err))
+ case busyTimeout > 0 && isSQLiteLockError(err):
+ return &sqliteLockWaitError{err: err}
+ default:
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
err)
+ }
+}
+
+// capSQLiteBusyTimeout reads conn's busy_timeout and, when ctx's deadline
comes
+// sooner, lowers it to the time remaining. Neither driver interrupts the busy
handler
+// when ctx ends, so without the cap a deadline would wait out the full
busy_timeout.
+// A cancellation without a deadline still does. restore puts the connection's
own
Review Comment:
I'd keep this as documented. Neither driver interrupts the busy handler, so
doing it here means running the wait in short `busy_timeout` slices and polling
the context, which adds pragma statements to every write with a cancellable
context. Happy to do that in a follow-up if you think the 5 s worst case on
mattn is worth it.
##########
catalog/sql/sql.go:
##########
@@ -305,18 +306,146 @@ func withReadTx[R any](ctx context.Context, db *bun.DB,
fn func(context.Context,
}
func withWriteTx(ctx context.Context, db *bun.DB, fn func(context.Context,
bun.Tx) error) error {
- return db.RunInTx(ctx, &sql.TxOptions{Isolation: sql.LevelDefault},
func(ctx context.Context, tx bun.Tx) error {
+ return runWriteTx(ctx, db, &sql.TxOptions{Isolation: sql.LevelDefault},
fn)
+}
+
+// runWriteTx runs fn in a write transaction. On SQLite it runs on a dedicated
pooled
+// connection whose busy_timeout it caps to ctx's deadline for the transaction
and then
+// restores, and it takes the write lock before fn reads anything.
+func runWriteTx(ctx context.Context, db *bun.DB, opts *sql.TxOptions, fn
func(context.Context, bun.Tx) error) error {
+ if db.Dialect().Name() != dialect.SQLite {
+ return db.RunInTx(ctx, opts, fn)
+ }
+
+ conn, err := db.Conn(ctx)
+ if err != nil {
+ return err
+ }
+ defer conn.Close()
+
+ busyTimeout, capped, restore, err := capSQLiteBusyTimeout(ctx, conn)
+ if err != nil {
+ return err
+ }
+ defer restore()
+
+ return conn.RunInTx(ctx, opts, func(ctx context.Context, tx bun.Tx)
error {
+ if err := lockSQLiteForWrite(ctx, tx, busyTimeout, capped); err
!= nil {
+ return err
+ }
+
return fn(ctx, tx)
})
}
+// sqliteWriteLockQuery writes no rows; issuing it takes SQLite's write lock.
+const sqliteWriteLockQuery = "UPDATE iceberg_tables SET catalog_name =
catalog_name WHERE 0"
+
+// lockSQLiteForWrite takes SQLite's write lock as the transaction's first
statement.
+//
+// database/sql begins SQLite transactions as DEFERRED, so a write transaction
that
+// reads first holds a read snapshot when its first write asks for the write
lock.
+// SQLite does not consult the busy handler for that upgrade: it returns
SQLITE_BUSY
+// at once, whatever busy_timeout the connection sets. A write that matches no
rows,
+// issued before any read, requests the lock from no transaction, which is the
one case
+// where SQLite waits on the busy handler. Writers then queue on busy_timeout
instead of
+// failing fast, and the reads that follow see the latest committed state.
+//
+// The statement takes the lock even when the transaction goes on to return a
sentinel
+// without writing, such as an identifier that already exists, so those paths
wait for
+// a concurrent writer too.
+//
+// capped reports that busy_timeout was lowered to ctx's deadline, so a lock
timeout
+// means the deadline ended the wait even if ctx has not yet noticed:
busy_timeout is
+// whole milliseconds and can run out just before the deadline does.
+func lockSQLiteForWrite(ctx context.Context, tx bun.Tx, busyTimeout int64,
capped bool) error {
+ _, err := tx.ExecContext(ctx, sqliteWriteLockQuery)
+ switch {
+ case err == nil:
+ return nil
+ case ctx.Err() != nil, capped && isSQLiteLockError(err):
+ // mattn reports a lock timeout here even when ctx's deadline
ended the
+ // wait; keep the context error so callers can match it.
+ cause := context.Cause(ctx)
+ if cause == nil {
+ cause = context.DeadlineExceeded
+ }
+
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
errors.Join(cause, err))
+ case busyTimeout > 0 && isSQLiteLockError(err):
+ return &sqliteLockWaitError{err: err}
+ default:
+ return fmt.Errorf("failed to acquire sqlite write lock: %w",
err)
+ }
+}
+
+// capSQLiteBusyTimeout reads conn's busy_timeout and, when ctx's deadline
comes
+// sooner, lowers it to the time remaining. Neither driver interrupts the busy
handler
+// when ctx ends, so without the cap a deadline would wait out the full
busy_timeout.
+// A cancellation without a deadline still does. restore puts the connection's
own
+// busy_timeout back, or discards the connection if it cannot, since the
pragma would
+// otherwise outlive this call in the pool.
+func capSQLiteBusyTimeout(ctx context.Context, conn bun.Conn) (int64, bool,
func(), error) {
+ noRestore := func() {}
+ var busyTimeout int64
+ if err := conn.QueryRowContext(ctx, "PRAGMA
busy_timeout").Scan(&busyTimeout); err != nil {
Review Comment:
Done in a76b739: `busy_timeout` is read up front only when the context has a
deadline to cap it to, and otherwise only after the lock statement reports
`SQLITE_BUSY`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]