4 ms·

Hey guys, this is just a quick tip on how to make sure you don't screw up the database connection pool in Go. This might also apply to other languages and frame

Hey guys, this is just a quick tip on how to make sure you don't screw up the database connection pool in Go. This might also apply to other languages and frameworks. I've seen dozens of articles about concurrency and multithreading, but not so many about this type of deadlocks, and I experienced some production bugs because of this.

majodev 6y ago
Great points! Any hints regarding preexisting linters for go code that errors if we execute outside (an already opened) db transaction? Also, regarding writing tests against real PostgreSQL databases, isolated per test and without losing speed, I can recommend to take a look at https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql - disclaimer, we are the authors.
- marvinblum 6y ago
  > Great points! Any hints regarding preexisting linters for go code that errors if we execute outside (an already opened) db transaction? I'm limiting the open connections at the moment and using the default Goland linter. It won't show you these kind of errors though and I just remembered that I saw it somewhere else, but I don't remember where it was :D
  - morelisp 6y ago
    You might be thinking of https://github.com/ryanrolds/sqlclosecheck https://github.com/ryanrolds/sqlclosecheck but it will only catch leaks, not deadlocks.
rivo 6y ago
Maybe I'm assuming too much but it wouldn't occur to me to let tx.Commit() depend on the outcome of another connection (here: db.Query()). Either use tx.Query() (which I guess is partly what you're advocating) or put db.Query() in a different goroutine and let tx.Commit() proceed. It is quite similar to other Go concepts. For example, you don't want to have circular dependencies between channels. The difference is that such a thing would fail very quickly whereas dependent DB connections would only fail after the connections are exhausted.
- marvinblum 6y ago
  Exactly! I just found this problem harder to catch, as most devs probably won't think about it as much as they do about their concurrent code.
- jerf 6y ago
  There's a lot of people who have a lot of SQL database experience in a synchronous, one-thread context, who know how to use transactions and have simply never thought about the way the implicit guarantees of a synchronous, one-thread context harmonize conveniently with transactional usage. Once you have interleaved computations, either via some sort of callback system or via threading, suddenly there's some extra concepts to understand, such as the way most database systems (if not all of them, but I'm hedging here) tie transactions to the specific DB socket you are communicating with, and this concept tends to poke its way up to even the highest-level API. APIs like Go's designed to support this by having an explicit concept of "transaction" also tend to be tricky because it's still so easy to use a non-transactional select even in the middle of a transaction just by accident, because it's surfacing a concept that the accidental context of the other APIs people have used always took care of automatically, with no visible signature on the API. I don't think there's necessarily anything "special" about this, I think it's just a case where there's a lot of people with years or even decades of experience in that context, and it simply doesn't immediately occur to them that in a higher-concurrency world they need to modify these skills. I suspect there's also rather a lot of database + application combinations out in the real world that, by coincidence and a bit of hacking around problems as they arise, "just happen to work" with the highly characteristic access patterns used by the web pages that can access the DB and the transaction isolation settings in the DB. Using any more concurrent and looser access to the DB is likely to expose a lot of problems that in some sense existed all along, but were just never quite uncovered before with the old access patterns. (It isn't really Go. Threading an old-school C program will raise the same issues, or going async in a scripting language.)

majodev 6y ago

Great points! Any hints regarding preexisting linters for go code that errors if we execute outside (an already opened) db transaction? Also, regarding writing tests against real PostgreSQL databases, isolated per test and without losing speed, I can recommend to take a look at https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql - disclaimer, we are the authors.

marvinblum 6y ago
> Great points! Any hints regarding preexisting linters for go code that errors if we execute outside (an already opened) db transaction? I'm limiting the open connections at the moment and using the default Goland linter. It won't show you these kind of errors though and I just remembered that I saw it somewhere else, but I don't remember where it was :D
- morelisp 6y ago
  You might be thinking of https://github.com/ryanrolds/sqlclosecheck https://github.com/ryanrolds/sqlclosecheck but it will only catch leaks, not deadlocks.

rivo 6y ago

Maybe I'm assuming too much but it wouldn't occur to me to let tx.Commit() depend on the outcome of another connection (here: db.Query()). Either use tx.Query() (which I guess is partly what you're advocating) or put db.Query() in a different goroutine and let tx.Commit() proceed. It is quite similar to other Go concepts. For example, you don't want to have circular dependencies between channels. The difference is that such a thing would fail very quickly whereas dependent DB connections would only fail after the connections are exhausted.

marvinblum 6y ago
Exactly! I just found this problem harder to catch, as most devs probably won't think about it as much as they do about their concurrent code.
jerf 6y ago
There's a lot of people who have a lot of SQL database experience in a synchronous, one-thread context, who know how to use transactions and have simply never thought about the way the implicit guarantees of a synchronous, one-thread context harmonize conveniently with transactional usage. Once you have interleaved computations, either via some sort of callback system or via threading, suddenly there's some extra concepts to understand, such as the way most database systems (if not all of them, but I'm hedging here) tie transactions to the specific DB socket you are communicating with, and this concept tends to poke its way up to even the highest-level API. APIs like Go's designed to support this by having an explicit concept of "transaction" also tend to be tricky because it's still so easy to use a non-transactional select even in the middle of a transaction just by accident, because it's surfacing a concept that the accidental context of the other APIs people have used always took care of automatically, with no visible signature on the API. I don't think there's necessarily anything "special" about this, I think it's just a case where there's a lot of people with years or even decades of experience in that context, and it simply doesn't immediately occur to them that in a higher-concurrency world they need to modify these skills. I suspect there's also rather a lot of database + application combinations out in the real world that, by coincidence and a bit of hacking around problems as they arise, "just happen to work" with the highly characteristic access patterns used by the web pages that can access the DB and the transaction isolation settings in the DB. Using any more concurrent and looser access to the DB is likely to expose a lot of problems that in some sense existed all along, but were just never quite uncovered before with the old access patterns. (It isn't really Go. Threading an old-school C program will raise the same issues, or going async in a scripting language.)