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