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?