Feature: BEGIN immediate/exclusive per transaction

[zombiezen]

Right now, the _txlock connection parameter determines which BEGIN statement is used to start a transaction. I'd like to be able to pick this at a per-transaction level. I don't think there's a way of exposing this functionality to database/sql, but it would be good to add an option there eventually. I'm imagining adding:

func (c *SQLiteConn) BeginWithType(t TransactionType) (driver.Tx, error)

type TransactionType int

const (
  Deferred TransactionType = iota
  Immediate
  Exclusive
)

Relevant SO post: https://stackoverflow.com/questions/42657639/begin-immediate-in-sqlx-or-database-sql-golang

[mattn]

database/sql has IsolationLevel below.

const (
	LevelDefault IsolationLevel = iota
	LevelReadUncommitted
	LevelReadCommitted
	LevelWriteCommitted
	LevelRepeatableRead
	LevelSnapshot
	LevelSerializable
	LevelLinearizable
)

It should map those values into the SQLite's transaction types.

[zombiezen]

I don't think it works in this case. SQLite's docs assert:

Except in the case of shared cache database connections with PRAGMA read_uncommitted turned on, all transactions in SQLite show "serializable" isolation.

[mattn]

go-sqlite3 is driver for database/sql. So you can't see the BeginWithType from database/sql.

[zombiezen]

That's actually something I'm curious about: why does this package export the types used to implement the driver? Can they be used directly if an application wants to have SQLite3-specific code?

[mattn]

I don't make sure but It's possible. However, interfaces are related on database/sql or database/sq/driver.
For example. SQLiteConn#Exec should be passed arguments []driver.Value. So programmer should consider what the value should be passed, what value will be returned.

[yonderblue]

Well there is https://golang.org/pkg/database/sql/#TxOptions now. But as mentioned its not "isolation level" that is requested but either BEGIN IMMEDIATE/DEFFERED etc. All sqlite transactions are serializable level as far as I understand.
Best I can see to make this possible is for applications have a few different initialized sql drivers at the pkg level that have their own connection hooks and maybe have a field on *sqlite3.SQLiteConn to set?
I would say just change _txlock in the connection hook, but it has to already be given to the sql.Open.

[win-t]

Hi, it's been a year now,

I just wondering if

db.Exec("BEGIN EXCLUSIVE TRANSACTION;")
// do something
db.Exec("COMMIT;")

will works?, I don't know how to test it my self, that is why I asked it here

[zombiezen]

Nope. It's blocked on the upstream database/sql issue.

[otoolep]

Nope. It's blocked on the upstream database/sql issue.

Huh? Why wouldn't it work? Those command just get sent straight to SQLite, and the transaction is started.

[win-t]

I have an idea, but clearly not a good one.
What if at

go-sqlite3/sqlite3.go

Line 772 in 52b8080

if _, err := c.exec(ctx, c.txlock, nil); err != nil {

instead of exec c.txlock, we can defer the execution of c.txlock to the first statement of the Tx, if that statement contains "BEGIN", we can prefer that one instead of c.txlock, so the user will write something like this

tx, _ := db.Begin()
tx.Exec("BEGIN EXCLUSIVE TRANSACTION;")
// do something
tx.Commit()

what do you think?

or maybe better with comment statement

tx, _ := db.Begin()
tx.Exec("/* BEGIN EXCLUSIVE TRANSACTION */")
// do something
tx.Commit()

I'm still looking into source code, I'm not sure if this is possible

[win-t]

I just found that std database/sql (go1.10.2) has:

// Conn returns a single connection by either opening a new connection
// or returning an existing connection from the connection pool. Conn will
// block until either a connection is returned or ctx is canceled.
// Queries run on the same Conn will be run in the same database session.
//
// Every Conn must be returned to the database pool after use by
// calling Conn.Close.
func (db *DB) Conn(ctx context.Context) (*Conn, error)

Code like this should work, right?

bctx := context.Background()
conn, _ := db.Conn(bctx)
defer conn.Close()
conn.ExecContext(bctx, "BEGIN EXCLUSIVE TRANSACTION;")
// do something
conn.ExecContext(bctx, "COMMIT;")

so, I think there is no need to change any code.
ping @zombiezen @gjrtimmer

[zombiezen]

@win-t Your final suggestion looks like it would work, yes. It's probably fine to close out this issue, then.

The previous suggestions either don't have the guarantee that the statements are being sent to the same connection, or the transaction would have already started.

[gjrtimmer]

@zombiezen is this something we should add to either a wiki page or to the README is so could you send a PR for documentation update ? Wpuld be nice not to loose this kind of information in a issue which will be closed over time.

[niemeyer]

I've just run through this need as well. The most elegant solution would be to have another field in sql.TxOptions named Locking or something similar. The feature shouldn't abuse the Isolation field as that's orthogonal to the locking mode and may be required together.

With that said, there's a simple workaround:

func beginImmediate(db *sql.DB) (*sql.Tx, error) {
        tx, err := db.Begin()
        if err == nil {
                _, err = tx.Exec("ROLLBACK; BEGIN IMMEDIATE")
        }
        return tx, err
}