如何在遍历行时修改行

Question

I have a loop iterating through a table that looks similar to this (error handling simplified for increased readability):

for rows.Next() {
    var (
        id          int
        field2      int
        field1      int
    )
    err = rows.Scan(&id, &field1, &field2)
    chk(err)
    field1 += someFunc()
    field2 += someOtherFunc()
    err = db.Exec(`UPDATE table SET field1 = ?, field2 = ? WHERE id = ?`, field1, field2, id)
    chk(err)
}

As you can see, I want to scan the fields of each row, modify them in some way and them update the database. The values returned by someFunc and someOtherFunc are different for each row and impossible to determine from the values of its fields alone.

This piece of code doesn't work, because the database is locked until rows are closed (I'm using mattn's go-sqlite3 driver). Additionally, it's not efficient, because the database needs to perform a query each time a row is updated. I know that I could use db.Prepare and then perform all of the queries once I'm done iterating, but that would consume unnecessary amounts of memory and would not alleviate the efficiency problem. I've done some reading and it seems like cursors provide the functionality I'm looking for (I'm not an SQL expert) and the flavor of SQL I'm using (SQLite3) supports them, but database/sql does not seem to.

Is there any natural way in database/sql to update rows while iterating through them?

Answer 1

You should execute your SELECTs & UPDATEs against a transaction, not a handle. I've stumbled with it in one of my projects, and now have a code like this:

tx, err := dbh.Begin()
...
rows1, err := tx.Query("SELECT id, data FROM cache")
...
for rows1.Next() {
...
   tx.Exec("DELETE FROM cache WHERE id = ?", id)
...
}
tx.Commit()

And no more errors about a locked database.