Does transaction lock row in postgres or only select?

Question

Consider the following code

func main() {

    ctx := context.Background()
    tx, err := db.BeginTx(ctx, nil)

    row := tx.QueryRow("SELECT timestamp, my_id, my_value FROM my_table ORDER BY timestamp ASC limit 1 ")
    var my_id int
    var my_time time.Time
    var my_value string
    err = row.Scan(&my_time, &my_id, &my_value)
    if err != nil {
        tx.Rollback()
        return
    }
    _, err = tx.ExecContext(ctx, "UPDATE my_table SET status = 'start' WHERE my_id = $1", my_id)
    if err != nil {
        tx.Rollback()
        return
    }

    err = tx.Commit()
    if err != nil {
        log.Fatal(err)
    }

    // do something with my_value that takes a long time
}

Which select the row with the oldest timestamp and set the status to start and do something with the return value, I run this transaction in a multi threaded/server environment, how do I make sure that each thread is getting a unique my_id and that no two thread is processing the same my_id?

I don't think the select statement locks the row that got returned during the first select, so multiple thread can attempt to update the same row.

I could modify the update statement to be

UPDATE my_table SET status = 'start' WHERE my_id = $1 AND status <> `start`

but then I have to reselect another id, is there a way to avoid it?

Answer 1

Use pessimistic locking to put an UPDATE lock on the row when reading:

SELECT ... FOR UPDATE

That will prevent concurrent data modifications.