Testing Database Transactions in Go
marvinblum.de
Testing Database Transactions in Go
1–10 of 22 posts
Re: Testing Database Transactions in Go
#2Hey 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.
Re: Testing Database Transactions in Go
#3Hey 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.
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 - disclaimer, we are the authors.
Re: Testing Database Transactions in Go
#4Hey 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.
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 - disclaimer, we are the authors.
> 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
Re: Testing Database Transactions in Go
#5The easiest way I have found to avoid this error is simply not to pass the `db` around so much. Every data access function (even seemingly trivial ones) should take a `Tx` instead. This also makes your code "composition-ready" if necessary later.
IMO the standard library should provide something that wraps a `Tx` and a `Context` together, as usually I want every statement issued from a transaction bounded in lifetime by the same one I provided to `BeginTx`. This would provide even more incentive to pass around `Tx`s rather than `DB`s, since those functions also often need a context anyway.
Re: Testing Database Transactions in Go
#6Hey 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.
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.
Re: Testing Database Transactions in Go
#7Hey 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.
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 th…
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.
Re: Testing Database Transactions in Go
#8The easiest way I have found to avoid this error is simply not to pass the `db` around so much. Every data access function (even seemingly trivial ones) should take a `Tx` instead. This also makes your code "composition-ready" if necessary later. IMO the standard library should provide something that wraps a `Tx` and a `Context` together, as usually I want every statement issued from a transaction bounded in lifetime…
I agree. I usually have a "model" package abstracting the database access behind functions accepting a transaction. But since I'm using the max open connections for all tests, I can be pretty confident I don't have open transactions somewhere, so I often take the shortcut and just query on "db" directly.
Re: Testing Database Transactions in Go
#9Don’t forget to measure first. You can metric out the number of connections periodically and see if you are capping out. See https://golang.org/pkg/database/sql/#DBStats
Re: Testing Database Transactions in Go
#10Hey 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.
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 th…
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.)