Why is pessimistic locking not working for me in psql? The code is not concurrent as I would expect it to be

Question

I am trying to get and update a column and need this process to be concurrent. Therefore, I do a row level lock via SELECT FOR UPDATEand then do my calculations and then do my update, all within a transaction with isolation level repeatable read.However, this is still not concurrent as expected. This column is just a random column and not the primary or a foreign key.

I changed this to optimistic locking after and that worked but I am trying to understand why this did not work. I ran this code below concurrently multiple times and it did not behave the same way as it would have if I individually ran it the same number of times.

_, err = s.xStore.ManageTransaction(func(ctx context.Context, tx *sqlx.Tx) (interface{}, error) {
                _, err := tx.Exec("set transaction isolation level repeatable read")
                if err != nil {
                    return nil, err
                }
                c, err = s.xStore.GetForUpdate(x) 
                //Some calculations
                _ = s.xStore.Update(c)
                return nil, nil
            })
            return
    }()
}

Here is my Get query with a FOR UPDATE to lock the row.

func (s *xStore) GetForUpdate(id string) (*model.X, error) {
    query := `
        SELECT * FROM things where id = $1 FOR UPDATE`

    _, err := s.db.Exec(query, id)
    if err != nil {
        return nil, err
    }

    var x model.X
    err := s.db.Get(&x, query, id)
    err = s.db.Get(&x, query, id)
    if err != nil {
        return nil, err
    }
    return &x, nil
}

Answer 1

Your code is executing queries in different transactions , create new one (db.BeginTx) or use provided someway like :

• rows := tx.Query( your query )
• do some calc
• tx.ExecContext
• tx.Commit

(Referring to https://go.dev/doc/database/execute-transactions )