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.