Dead Lock in golang database/sql

Question

I've noticed a strange behavior in Go's database/sql package, using the PostgreSQL driver from lib/pq. Basically if I use a database connection for a query while I'm building a transaction on the same connection, I enter in a dead lock and the program blocks (I need to manually restart the database server to make it work again). In the example code below, I would get stuck at the select statement, and the second insert statement would never be executed (while if I remove the query the code executes normally).

tx, _ := connection.Begin()
tx.Exec(insert_statement)
rows, _ := connection.Query(select_statement)
rows.Close()
tx.Exec(insert_statement_2)
tx.Commit()

Is this normal? Should I create a new database connection every time I want to use transactions?

David Budworth · Accepted Answer · 2016-06-12 00:15:31Z

2

The connection.Query function does not execute on the same session as your tx.XXX functionsso if select_statement happens to reference anything that was written to by insert_statement, you potentially would block.

try tx.Query(select_statement) to see if that one blocks.

answered Jun 12, 2016 at 0:15

David Budworth

11.7k1 gold badge38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

splinter123 Over a year ago

Yes, tx.Query() would work, but I cannot always control that. The transaction tx is used to do some updates to the db by the admin, while the query is called by users visiting the website. So the two things might happen at the same time and I'd prefer if it didn't shut down my database!

David Budworth Over a year ago

I would only expect this to deadlock when being executed back to back in same goroutine. You get this issue when different routines are executing different the various things? Also, postgresql SELECT statements don't lock unless you do "for update", are you doing that?

splinter123 Over a year ago

I haven't tested it on different goroutines, I was trying to naively replicate a concurrent behavior by doing different things on the same thread, but probably it's not the good way to go. The select statement is just a plain "select * from mytable", where the same "mytable" was modified by the previous insert.

Collectives™ on Stack Overflow

Dead Lock in golang database/sql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related