
Testing Database Transactions in Go - marvinblum
https://marvinblum.de/blog/testing-database-transactions-in-go-jEaOGXravM
======
theptip
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.

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

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

~~~
marvinblum
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.

------
sethammons
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](https://golang.org/pkg/database/sql/#DBStats)

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

------
ugizashinje
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

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

~~~
rivo
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.

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

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

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

~~~
marvinblum
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...](https://medium.com/@dekugelschieber/testing-database-transactions-
in-go-12cf617cbe21)

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

~~~
marvinblum
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

------
throwaway189262
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

~~~
morelisp
> 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.

