CODEWITHSUNDEEP
sqlpostgresqlgo
Kaedys
Kaedys

Reputation: 10158

Golang Concurrent SQL Transactions

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?

Upvotes: 5

Views: 10290

Answers (1)

Kaedys
Kaedys

Reputation: 10158

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.

Upvotes: 4

Related Questions