Survival Guide ‐ Connection pool and Deadlocks

If you are crafty enough, you will doubtlessly be able to create situations where a subtle use of concurrency (even bounded) and nested connection acquisition from a pool will end up in a deadlock. Let's see how this can happen:

Let's set the scene

You try naïvely to do database insertions concurrently, because you think this is a good idea. Beyond considerations like "won't each insertion lock the table so that I'm in fact getting throttled?", you think to yourself: I will bound the concurrency so that I will only process batches that amount of the number of connections I have allocated to my application. This is sensible so far, and there is a function that just does that: pooledForConcurrentlyN_:

pooledForConcurrentlyN_ 
  env.dbConfig.connections -- Amount of connections for the app, and so size of the batch
  dependencies -- Vector of things to insert
  persistImportDependency -- Operation to apply on each dependency

Now, in itself, although it is quite greedy – you could have more than one instance of that code path running, or just other queries waiting to acquire a connection in the same instance of the application – it is not immediately obvious that this will react badly with another thing that you have done afterwards.

On-the-spot connection acquisition

In an application that talks with the database through transactions, you want those transactions to be finalised pretty rapidly, otherwise the connection to which they are associated won't be available to other parts of the application, and this will lead to connection exhaustion (manifesting in slower resource acquisition from the pool).

Let's explore a disastrous scenario where only one connection is available:

timeline
    title Timeline of resource usage
    section Operation 1
      database operation: Initial Insertion
      network operation: Cache query
      filesystem operation: Optional read if the cache turns out to be empty
      database operation: Update

    section Operation 2
      database operation: Quick lookup

    section Operation 3
      database operation: Quick lookup

Loading

Understandably, people on the other end of operations n°2 & n°3 will not be happy that their quick lookup operations are effectively queued because operation n°1 is hogging the connection, even when not sending database queries.

So you want to acquire connections at use-site in order to release the connections when they are not needed:

timeline
    title Timeline of resource usage
    section Operation 1 (with connection)
      database operation: Initial Insertion
    section Operation 2 (with connection)
      database operation: Quick lookup
    section Operation 3 (with connection)
      database operation: Quick lookup
    section Operation 1 (without connection)
      network operation: Cache query
      filesystem operation: Optional read if the cache turns out to be empty
    section Operation 1 (with connection again)
      database operation: Update

Loading

Disaster strikes

And in a fit of inattention, you lay out the connection access as such:

withReadWritePool . withReadOnlyPool pool $ do -- ⬅️ 2 connections will be acquired by each operation,
                                               -- because we wanted to separate read-write
                                               -- and read-only operations but couldn't be arsed
                                               -- to make `withReadWritePool` handle read-only operations.
  upsertPackage package -- ⬅️ 1 connection is acquired here to upsert the package.
  pooledForConcurrentlyN_ connections -- ⬅️ N more conns are acquired/transactions are started to upsert
                                      -- the packages that this package depends on.
    dependencyPackages upsertPackage 
  pooledForConcurrentlyN_ connections -- ⬅️ And these N more conns are re-acquired here to register
                                      -- the dependencies associated with the package.
    dependencies persistImportDependency 

And so we have a problem here: Let's say you provisioned 15 connections per application. You acquire 1 connection, which starts a transaction, then you try to acquire 15×2 transactions simultaneously in the first pooledForConcurrentlyN_. Since we do not have that many connections available, each worker is thus stuck, waiting for their second connection that will never arrive.

Without surprise, the fix looks like this:

withReadWritePool pool $ do -- ⬅️ Open one connection, and release it at the end of the do-block.
  upsertPackage package 
  forM_ dependencyPackages upsertPackage 
  forM_ dependencies persistImportDependency

Which indeed makes the various inserts sequential, but this simplification is worth it because then we don't have to take into account both bounded concurrency considerations and the access patterns against PostgreSQL and its locks. This is your reminder to read https://postgreslocksexplained.com.

What I learned

• This was starting from a good intention: Release connections when not needed, and especially during other expensive operations.
• This needed a fuller view of the code that was around before.
• If I had written down my expectations and compared it to all the code (and not just the changed parts highlighted in a diff) I would have probably spotted something. Even more certain if I was pairing with someone else.
• Glory be to PostgreSQL's own observability utilities like pg_stat_activity.
• Glory be to logging the time it takes for a connection to be acquired from the pool.

Thanks for reading so far. I hope others will learn from my mistakes and their resolution.