英文:
Why is pessimistic locking not working for me in psql? The code is not concurrent as I would expect it to be
问题
我正在尝试获取并更新一列，并且需要这个过程是并发的。因此，我通过使用SELECT FOR UPDATE进行行级锁定，然后进行计算，然后进行更新，所有这些都在一个隔离级别为repeatable read的事务中进行。然而，这仍然不像预期的那样并发。这个列只是一个随机列，不是主键或外键。
后来，我将其更改为乐观锁定，这样就可以正常工作了，但我想知道为什么这样做没有起作用。我多次同时运行了下面的代码，但它的行为与我单独运行相同次数的代码时不同。
这是我的带有FOR UPDATE的Get查询，用于锁定行。
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
}
英文:
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
}
答案1
得分: 0
你的代码在不同的事务中执行查询，要么创建一个新的事务（db.BeginTx），要么使用提供的方式，例如：
- rows := tx.Query(你的查询)
- 进行一些计算
- tx.ExecContext
- tx.Commit
（参考 https://go.dev/doc/database/execute-transactions）
英文:
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 )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论