Proper way to SQL select and update

Question

I'm using github.com/bmizerany/pq against a Postgres database. I need to select all the rows in the "todos" table, and for each row check for condition and update the row accordingly. Pseudo codes:

rows, _ := dbConn.Query("SELECT id, condition, task FROM todos")

for rows.Next() {
    var Id int
    var Condition int
    var Task string
    rows.Scan(&Id, &Condition, &Task)
    if Condition == 0 {
           UpdateTask(Id, Task)
    }
}

The UpdateTask() function will issue a SQL update statement to update the row.

Will issuing SQL update within a SELECT query lock the database? Is this the proper way of doing such update?

score 6 · Accepted Answer · answered Aug 11 '13 at 10:39

First, at minimum you should be doing a SELECT ... FOR UPDATE so you lock the rows against other SELECT ... FOR [SHARE|UPDATE] access. You must do this inside a transaction and hold that transaction until you update the last row and commit.

The rows you SELECT ... FOR UPDATE not locked against normal SELECT; they're still readable to other transactions that aren't using FOR UPDATE or FOR SHARE.

Better still, try to rephrase the whole thing as an UPDATE ... FROM or other set-based operation where you do all the work in a single query. It'll generally perform massively better than a SELECT ... FOR UPDATE followed by a stream of UPDATEs.

Proper way to SQL select and update

1 Answers1

Linked