How to run SQL actions in persistent
When I started writing an application that used persistent to interact with a MySQL database, I decided to put the whole application inside one big
SqlPersistM action, and run it once inside
main. (To make it clear, this is not a Yesod application; I simply use persistent as a standalone library.)
However, as I learned more about persistent and how it worked, it became clear that this was the wrong way to use persistent. Here’s why.
Problems of one big
persistent’s SQL layer treats an
SqlPersistT action as a single transaction. Thus, until you run the action, the transaction is not committed. Obviously, this is an issue for any long-running server application.
You could work around this by calling
transactionSave manually. Now you have a different but related problem…
Normally a single SQL connection can participate in just one SQL transaction. (There are probably exceptions to this rule which I am not aware of, but this is how it happens unless you do something special.)
Thus, assuming your application is multithreaded, you’ll end up committing other threads’ transactions that are active at the same time.
(Besides, I am not sure whether executing multiple SQL statements over the same connection simultaneously is supported at all.)
persistent uses resourcet to ensure that resources (such as buffers that hold result sets) are released as soon as they are not needed.
resourcet works by handling these two scenarios:
- No exception is thrown; resources are deallocated by an explicit
- An exception is thrown, preventing the
releaseaction from happening. However, once the exception escapes the enclosing
ResourceTblock, it triggers the exception handler inside
runResourceT. The exception handler then performs deallocation.
When your application consists of one long-running
SqlPersistM action, chances are you’re catching some exceptions inside the
ResourceT block, by the means of monad-control. Doing that invalidates resourcet’s assumptions: an exception prevents the
release action from happening, and yet it never makes it up to
runResourceT, and so your long-running app leaks resources.
Do it right
It implies from the above considerations that the right way to use
persistent with a SQL backend is:
SqlPersistTcorrespond to logical transactions in your application.
ResourceTcomputations as short-lived as possible. Ideally, don’t catch exceptions inside
- Use a connection pool.
I am not an expert in either persistent or SQL databases; I am in the process of figuring this out myself. Corrections (and confirmations) are welcome.