Testing Database Transactions in Go | Hacker News Reader

Testing Database Transactions in Go

marvinblum.de

93 pointsmarvinblum21 comments

This covers one possible deadlock, but there are so many more ways to do this. More common (IME) is a true logic deadlock, particularly if you are using strict serialization (say, your DB stores a ledger of financial transactions).

It’s actually really hard to test that your DB is using transactions correctly, since the error cases tend to be races which don’t show up under low load.

We built a prototype tool in Python where you can monkeypatch the transaction context manager, and pause the primary thread’s execution just before committing, so that you can then do evil stuff like running a competing thread of the same or other DB operations to try to break invariants. But even this won’t catch everything; there is a combinatorial explosion of test points and you can’t compare them all in a large app.

I haven’t seen any folks writing about this, another approach would be to wire up some him thing smarter like Jepsen to direct the anomaly search, Kyle said some folks have reported doing this but nobody has published. I’m interested to know if anyone has had success with this sort of transaction/correctness testing.

Most people use queues to do this. At least I do.

The 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.

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.

Don’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

Thanks! Thats a really good tip. I didn't know I had easy access to the db client stats!

In order to get proper error response when deadlock occurs in testing (not just in Go), use isolation level serializable on every transaction / connection. This is simplest most effective way I have found so far, also you can get in detail why/where it happened. Watch out for different packages test executions since they run in parallel by default, use

go test -p 1

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.

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

You might be thinking of https://github.com/ryanrolds/sqlclosecheck but it will only catch leaks, not deadlocks.

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.

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.

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.)

Python context managers are a great way to prevent this type of issues. Anyway feels like a bit strange to not use the current connection (from between the transaction) to query things. Perhaps the problem is between the API, because it's not explicit the borrow/leave of connections from the pool.

This site is blocked on my office proxy under "Adult/Mature Content" category

Yeah because of my last name :( Some filters seem to be a bit too aggressive. I copied it to Medium:

https://medium.com/@dekugelschieber/testing-database-transac...

What is "blum"? Google is just returning kitchen cabinet parts, etc.

Blume means flower in german. But the "urban dictionary" spits out weird translations for it, and it seems like some blockers are configured in an interesting way :P

This problem is solved in most languages by wrapping endpoint code in an interceptor that creates and cleans up the transaction for you.

I'm not a fan of Go because it encourages hacks like this. The language is not expressive enough to handle database transactions properly. So it's suggested to try to catch bad situations with a linter instead.

What a timeless Goland solution to the problem. They should put "fix it with a linter" on hats and sell them at Go rallies. You could make billions

> an interceptor that creates and cleans up the transaction for you.

I'm not sure about the word "interceptor" here because in Go and every other language I know which does this it is merely a wrapper, but Go does this. From some vantage, the problem only arises because Go does this - if it instead forced you to create a transaction the resource allocation, and so possibility of exhaustion, would be a lot more obvious.

Reply on news.ycombinator.com