laskoviymishka commented on code in PR #2589:
URL: https://github.com/apache/iceberg-go/pull/2589#discussion_r4576051028
##########
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:
Could the table name come from the bun model rather than a literal? It
matches the `bun:"table:iceberg_tables"` tag today
(https://github.com/apache/iceberg-go/blob/2c84b07a96ef829436b12bc1b5b7717049d769f1/catalog/sql/sql.go#L278-L279),
but if the model is ever renamed this statement keeps pointing at the old
table, and that error would read as a lock failure.
##########
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:
Should we keep the catalog sentinels on a read-only database, or at least
document that they change? On base, creating an existing identifier returned
`ErrTableAlreadyExists` from `checkIdentifierAvailable` before any write
(https://github.com/apache/iceberg-go/blob/8d13e317c79f36f38f52a16b2f81f1d966f1fc27/catalog/sql/sql.go#L845-L856).
Now the lock statement fails first, with `failed to acquire sqlite write lock:
attempt to write a readonly database`. A probe against modernc v1.57.0 shows
that error, and `no such table: iceberg_tables` on an uninitialized DB, both
landing in this branch.
##########
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:
Should this case match only `database is locked`? With mattn and a
`cache=shared` DSN the lock statement gets SQLITE_LOCKED (`database table is
locked`) at once, with no busy wait, but mattn's 5000 ms default puts it here
and it's never retried. In a repro where a second writer commits after 100 ms,
HEAD fails after 38µs with `retryable=false`. Base retried the same error
(https://github.com/apache/iceberg-go/blob/8d13e317c79f36f38f52a16b2f81f1d966f1fc27/catalog/sql/sql.go#L379-L386)
and succeeded at 151 ms. Sending `database table is locked` to `default` keeps
it retryable. modernc isn't affected because it waits via unlock_notify.
##########
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:
Is it worth handling a cancellation without a deadline too, for example by
interrupting the connection once ctx is done, or do we keep this as documented?
With busy_timeout at 2s and a cancel at 200 ms, the write returns `context
canceled` only after about 2.0 s on both drivers. On mattn's 5000 ms default
that becomes a 5 s wait after the caller gave up.
##########
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:
Could the PRAGMA read wait until we know we need it? Without a deadline the
value only feeds the `busyTimeout > 0` case in `lockSQLiteForWrite`, so it
could be read there on a lock error, and most write transactions would skip the
extra statement
(https://github.com/apache/iceberg-go/blob/2c84b07a96ef829436b12bc1b5b7717049d769f1/catalog/sql/sql.go#L388-L397).
--
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]