英文:
Golang Concurrent SQL Transactions
问题
在并发和SQL事务方面遇到了问题。以下是我翻译的代码（为了清晰起见，删除了错误检查等）：
dao, _ := sql.Open("postgres", args)
tx1 := dao.Begin()
res, _ := tx1.Exec("UPDATE <...>", args...)
// 错误检查
tx2 := dao.Begin()
res, _ = tx2.Exec("UPDATE <same>", args...)
_ = tx1.Commit()
_ = tx2.Commit()
这段代码在一个单元测试中运行。我的想法是强制发生并发失败，因为两个Exec尝试更新同一行，以确保正确的冲突错误响应被给出。然而，第二个Exec会永久地阻塞。
据我所知，只有在数据库连接用尽时才会发生这种类型的阻塞，但是事务应该在它们自己的（独占的）连接中运行，并且我没有在任何地方设置最大连接数（我也尝试将其设置为100之类的值，没有效果）。
奇怪的是，如果我将tx2.Exec()和tx2.Commit()分离到一个单独的goroutine中，并使用一个同步通道来阻塞主线程，直到tx2运行完Exec()，同样的情况发生，tx2.Exec()会无限期地阻塞。如果我使用time.Sleep()而不是同步通道，tx2.Exec()会阻塞，直到sleep完成并且tx1.Commit()被执行，然后会按预期产生错误（pq: could not serialize access due to concurrent update）。
我是否对golang的SQL包或postgres驱动程序处理连接池的方式有所遗漏？为什么第二个事务的Exec会阻塞，直到第一个事务提交？事务的目的不是让两个事务可以同时运行，并且先提交（或开始）的事务获胜吗？
英文:
Running into an issues with concurrency and SQL transactions. I have the (stubbed) code below (error checks and such removed for clarity):
dao, _ := sql.Open("postgres", args)
tx1 := dao.Begin()
res, _ := tx1.Exec("UPDATE <...>", args...)
// error check
tx2 := dao.Begin()
res, _ = tx2.Exec("UPDATE <same>", args...)
_ = tx1.Commit()
_ = tx2.Commit()
This occurs within a unit test. The idea is to force a concurrency failure, since the two Execs are trying to update the same row, to make sure the proper conflict error response is given. However, the second Exec blocks permanently.
As far as I can tell, this type of blocking is only supposed to occur if the DB is out of database connections, but transactions are all supposed to run in their own (exclusive) connections, and I'm not setting a max connections anywhere (I've also tried setting it to something like 100, no effect).
Here's the strange part. If I separate out the tx2 Exec() and Commit() into a separate goroutine with a synchronizing channel to block the main thread from running tx1.Commit() until tx2 has run it's Exec(), the same thing happens, block indefinitely on the tx2.Exec(). If I use a time.Sleep() instead of a sync channel, the tx2.Exec blocks until the sleep finishes and tx1.Commit() is run, then completes with the expected error (pq: could not serialize access due to concurrent update)
Am I missing something about how golang's SQL package, or the postgres driver, handles connection pools? Why is the second transaction Exec blocking until the first one is committed? Isn't the point of transactions that the two can run simultaneously, and whichever commits (or is it starts?) first wins?
答案1
得分: 4
进一步研究后，看起来这实际上不是Golang、SQL或PQ包的问题。这是PostgreSQL内在的（并且是有意设计的）行为：
UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行方面与SELECT相同：它们只会找到在命令开始时间之前提交的目标行。然而，在找到目标行时，该目标行可能已经被另一个并发事务更新（或删除或锁定）。在这种情况下，欲更新的事务将等待第一个更新事务提交或回滚（如果它仍在进行中）。
因此，阻塞发生在Postgres中，而不是Go中。可以通过在单独的终端中使用psql运行并发事务来确认这一点，这些事务对同一条记录进行Update（或Insert或Delete等）操作。第二个Update/Insert/Delete将会阻塞，直到第一个调用它的事务调用COMMIT或ROLLBACK。
英文:
After further research, it looks like this isn't actually an issue with Golang, or the SQL or PQ packages. This is an inherent (and by design) behavior within PostgreSQL:
>UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress).
http://www.postgresql.org/docs/9.1/static/transaction-iso.html
So the block is occurring in Postgres, not in Go. This can be confirmed by running concurrent transactions that Update (or Insert or Delete, et al) the same record in separate terminals using psql. The second Update/Insert/Delete will block until the transaction in which the first one was called either calls COMMIT or ROLLBACK.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论