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 UPDATE
and 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
}
CodePudding user response:
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 )