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)
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:
db.Exec("BEGIN")
,db.Exec("COMMIT")
,db.Exec("ROLLBACK")
)@xrtgavin wrote:
You are wrong from sqlite’s perspective. See https://www.sqlite.org/lang_transaction.html:
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:
Commit
implementation to always clean up the transaction so it matches the semantics expected bydatabase/sql
db.Exec("COMMIT")
ordb.Exec("ROLLBACK")
(notedb
is what was returned fromsql.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…