Important: No Error being thrown while trying to insert when select is active

[ppone]

SQLITE VERSION: 3.7.13
GO VERSION: 1.3.1
Latest go-sqlite3 as of 9/5/14

No error is being thrown while trying to insert when select is active. Should be getting some type of database is locked error. This issue is highly related to #39. The only difference is here we use transactions.

Here is some sample code. Every time code is run, there should be addition of 1 new record. However we are seeing a maximum of 2 rows, with the second row continuously being replaced with the newer record.

We can correct the code either by

changing

rows, err := db.Query("SELECT name FROM sqlite_master WHERE type='table' AND name='TEMP';")

to

rows, err := tx.Query("SELECT name FROM sqlite_master WHERE type='table' AND name='TEMP';")

Or applying the fix to #39 which is to change

db, err := sql.Open("sqlite3", "locked.sqlite")

to

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

Or we can manually close the row. However some type of error should be displayed.

package main

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
    "log"
)

func main() {

    db, err := sql.Open("sqlite3", "locked.sqlite")
    if err != nil {
        log.Fatalln("could not open database:", err)
    }
    defer db.Close()

    tx, err := db.Begin()
    if err != nil {
        log.Fatalln(err)

    }

    rows, err := db.Query("SELECT name FROM sqlite_master WHERE type='table' AND name='TEMP';")
    if err != nil {
        log.Fatalln(err)

    }

    defer rows.Close()

    if !rows.Next() {
        _, err = db.Exec("CREATE TABLE TEMP (ID INTEGER NOT NULL PRIMARY KEY, DATA1 INTEGER, DATA2 INTEGER, TIME DEFAULT CURRENT_TIMESTAMP);")
        if err != nil {
            log.Fatalln("could not create table:", err)
        }
    }

    defer tx.Commit()

    _, err = tx.Exec("INSERT INTO TEMP (DATA1, DATA2) VALUES (?, ?);", 10, 100)

    if err != nil {
        log.Fatalln("could not insert into table:", err)
    }

    rows, err = tx.Query("SELECT ID, DATA1, DATA2, TIME FROM TEMP;")

    if err != nil {
        log.Fatalln("could not select data:", err)
    }

    for rows.Next() {

        var id, data1, data2 int
        var time string

        err = rows.Scan(&id, &data1, &data2, &time)
        if err != nil {
            log.Fatalln("could not scan row:", err)
        }

        log.Println("reading id = ", id, ", data1 = ", data1, ", data2 = ", data2, ", time = ", time)

    }

}

[mattn]

Yes, see docs.

http://www.sqlite.org/lockingv3.html

[gwenn]

No error is displayed because you are ignoring the error returned by the Commit.
Here is a quick fix:
defer func() {
if err := tx.Commit(); err != nil {
log.Fatalln("could not commit:", err)
}
}()

[ppone]

@gwenn Yes that works. I followed a sample program in go-sqlite3. Should have known to catch the commit error.

I would update the examples in go-sqlite3 to show it for errors when doing a Commit().

Thanks

[gwenn]

You're welcome.
I think that the following bradfitz's statement:
https://groups.google.com/forum/#!topic/golang-nuts/FBgjdhHApuk
"Never defer a file Close when the file was opened for writing."
applies to Tx.Commit.
"Never defer a transaction Commit (without checking the error)."