inconsistent transaction state

[rtxu]

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.

[mattn]

Could you please show me the small code to reproduce?

[rtxu]

Here's my original code snippet:
https://gist.github.com/xrtgavin/5a18126c383cfc29ccb4/5446f1877facc57e889f8bb036794c9baf3356b7#file-gistfile1-go

REMIND：When commit transaction periodically, /usr/bin/sqlite3 will do a big query and the commit will fail.

Here's modified code snippet:
https://gist.github.com/xrtgavin/5a18126c383cfc29ccb4/3786e63e4783cd23126b3b2915563424882d4a9e#file-gistfile1-go

Here's the Close-Then-ReOpen workaround I'm not satisfied：
https://gist.github.com/xrtgavin/5a18126c383cfc29ccb4#file-gistfile1-go

[rtxu]

@mattn What can I do for you?

[mattn]

I'm thinking either of program should be given a BUSY or LOCKED at commit time.

[rtxu]

How the bug progress?

[mattn]

Could you please try _busy_timeout?

https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go#L274

Set bigger value like _busy_timeout=XXXXX.

[rtxu]

When timeout, could I fail the current transaction and begin another transaction without reopen db?

[mattn]

Do you know where the timeout occured? Commit? Exec?

[rtxu]

Almost commit, I guess, because it's the most time-consuming operation, isn't it ?

And _busy_timeout's unit ? ms or us ?

[mattn]

And _busy_timeout's unit ? ms or us ?

ms
see https://www.sqlite.org/c3ref/busy_timeout.html

Almost commit, I guess, because it's the most time-consuming operation, isn't it ?

I must check which is occured timeout.

[mattn]

Could you please minimum test case that can reproduce?

[rtxu]

No matter what I set timeout is, I have to reopen db when transaction fail, right ?

[mattn]

I want to check whether this behavior should be fixed or not.

[xeodou]

hi @xrtgavin Here is what i solve the database is locked issue

func (s *Meyovoter) InsertFormVoterList(mid int64, ids []int64) error {
    tx, err := s.db.Begin()
    if err != nil {
        return err
    }
    str := fmt.Sprintf("INSERT INTO %s ( `meyo_id`, `voter_id`) VALUES (?, ?);", meyo_voter_table)

    stmt, err := tx.Prepare(str)
    if err != nil {
        return err
    }
    defer stmt.Close()
    for _, id := range ids {
        _, err = stmt.Exec(mid, id)
        if err != nil {
            tx.Rollback() // This line is the key.
            return err
        }
    }
    if err := tx.Commit(); err != nil {
        return tx.Rollback()
    }
    return nil
}

[rtxu]

@xeodou Rollback() does not make sense to the problem.

See my problem again.

[xeodou]

oh, Sorry. @xrtgavin

[ea-at-diladele-com]

Hi guys,
Any progress on this?
It seems I have bumped into the same issue. Is db reopening the only workaround?
Thank you!

[rtxu]

@ea-at-diladele-com AFAIK，no any process

[sqweek]

@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:

1. Change go-sqlite3's Commit implementation to always clean up the transaction so it matches the semantics expected by database/sql
2. 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...

[rtxu]

@sqweek Appreciate your insight about the issue. Thank you.

[sqweek]

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:

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