Roman Cheplyaka

How to run SQL actions in persistent

Published on

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 SqlPersistM action

Finalizing transactions

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…

Overlapping transactions

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

Resource deallocation

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:

  1. No exception is thrown; resources are deallocated by an explicit release call.
  2. An exception is thrown, preventing the release action from happening. However, once the exception escapes the enclosing ResourceT block, 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:

  1. Make SqlPersistT correspond to logical transactions in your application.
  2. Make ResourceT computations as short-lived as possible. Ideally, don’t catch exceptions inside ResourceT; use finally instead.
  3. Use a connection pool.

Disclaimer

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.