go-sqlite3: inconsistent transaction state

First of all，I open the db file in the following way.

db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared&mode=rwc")

My program commit a db transaction periodically. At the same time, another program(like /usr/bin/sqlite3) do a big query on the same db. Then error reported is:

2015/03/16 20:05:49 "database is locked": Commit
# Error from the successive transation
2015/03/16 20:05:49 "cannot start a transaction within a transaction": Begin transaction

IMO, a fail-commited transaction means a rollbacked transaction. Am I wrong ? I do the rollback manually. And error reported became:

2015/03/16 20:06:51 "database is locked": Commit
2015/03/16 20:06:51 "sql: Transaction has already been committed or rolled back": Rollback
2015/03/16 20:06:51 "cannot start a transaction within a transaction": Begin transaction

Now, I‘m confusing. What should I do to begin a new transaction? The old transaction is rolled back, OR NOT?

Do not inform me: Close-Then-ReOpen the db. It’s just a workaround.

func Open
func Open(driverName, dataSourceName string) (*DB, error)
Open opens a database specified by its database driver name and a driver-specific data source name, usually consisting of at least a database name and connection information.

Most users will open a database via a driver-specific connection helper function that returns a *DB. No database drivers are included in the Go standard library. See http://golang.org/s/sqldrivers for a list of third-party drivers.

Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

Finally I need help and also want to help.

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 21 (9 by maintainers)

Commits related to this issue

Fix inconsistent tx state with database/sql. The semantics of sql.Tx.Commit impose that the transaction is finished and cleaned up by the time the driver's Commit function returns. However sqlite3 le... — committed to sqweek/go-sqlite3 by sqweek 8 years ago

Most upvoted comments

I think option 1 is the right solution; it seems like the only way to provide consistent behaviour via the database/sql.Tx API. I’ve opened a pull request to that effect.

Users who really really want to keep retrying COMMIT upon SQLITE_BUSY can either:

Set the busy timeout to something ridiculously large, or
Manually manage their transactions (db.Exec("BEGIN"), db.Exec("COMMIT"), db.Exec("ROLLBACK"))

sqweek on Apr 18, 2016

@xrtgavin wrote:

IMO, a fail-commited transaction means a rollbacked transaction. Am I wrong ?

You are wrong from sqlite’s perspective. See https://www.sqlite.org/lang_transaction.html:

An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.

However you are correct from database/sql’s perspective - it closes the transaction whether COMMIT succeeds or fails.

This is the source of the inconsistency; as far as the database package is concerned the transaction is done, so any further attempts to commit/rollback are met with the error “sql: Transaction has already been committed or rolled back” without consulting the sqlite3 driver.

There’s two ways to proceed:

Change go-sqlite3’s Commit implementation to always clean up the transaction so it matches the semantics expected by database/sql
Change nothing and leave the user to explicitly clean up the transaction by calling db.Exec("COMMIT") or db.Exec("ROLLBACK") (note db is what was returned from sql.Open, not the transaction).

Option 2 is more flexible in that it allows the user to decide whether they want to retry the COMMIT at a later time, but it also kind of defeats the purpose of using the database/sql API since they have to know (or check) that the sqlite3 driver is in use…

sqweek on Apr 16, 2016