ݺߣ

ݺߣShare a Scribd company logo

How to avoid connection pool deadlock

?Download as PPTX, PDF?
?
Stephan van Hoof
Stephan van Hoof

The document discusses avoiding connection pool deadlocks in a multithreaded server environment. It provides three rules: 1) the connection pool should respect the maxTotal limit and not create new connections when this limit is reached, 2) nested getConnection() calls should be avoided as this can cause a thread to block itself, and 3) the application should only use one connection at a time to avoid potential deadlocks. Following these rules can help ensure the connection pool does not experience deadlocks.

1 of 4
Download to read offline
Connection Pool Deadlock and some rules to avoid it Disclaimer I made all reasonable efforts to ensure that the information contained in this presentation is accurate at the time of it's creation and/or modification. However, I cannot guarantee the completeness or accuracy of information contained in it, and shall not be responsible for any errors, omissions, or inaccuracies and accept no liability whatsoever for any loss or damage howsoever arising.
Take a multithreaded server, like Tomcat in my case. Each HTTP request is served by a worker thread, and these threads share the connection pool. Threads are running simultaneously, and once a connection is occupied by a thread it is not available for use by other threads. For this setup I defined some rules; if you follow these no pool deadlock will occur. getConnection() HTTP worker threads Connection pool Database
Rule 1: Your connection pool should respect the maxTotal, that is: when maxTotal database connections exist, the pool should not create new ones* Some pool implementations seem to take the maxTotal as a soft limit, this is undesired behavior. * If your pool does not respect the maxTotal, you may run into the deadlock problem later. Many databases limit the number of concurrent connections and allow no new connection when this number is reached; in this situation the getConnection() on the pool is blocked by the back end database. How to verify the pool respects maxTotal? - Set maxTotal=1 (e.g. in your context.xml); - Write code which is expected to block (deadlock on pool) and check it really does: try (Connection conn1 = pool.getConnection() { try (Connection conn2 = pool.getConnection() { < deadlock on pool >
Rule 2: Dont nest getConnection() Doing so a thread can block itself, e.g. in case of maxTotal=1. Beware of hidden getConnection() calls, e.g. in case of calling another DAO method. Rule 3: Your (web)application should run on 1 connection, in other words when maxTotal=1 If it does not, your code contains one or more nested getConnection() calls. During development and test phase use maxTotal=1. In production use a reasonable setting for maxTotal; its value is surprisingly low*: (2 * #DB-cpu-cores) +1 Although it does not result in a pool deadlock, avoid long running HTTP threads due to long running queries. If some users are expected to run OLAP like queries (long running, e.g. reporting or exporting data) on your OLTP web application, increase maxTotal with the expect amount of concurrent users doing so. * https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

More Related Content

How to avoid connection pool deadlock

  • 1. Connection Pool Deadlock and some rules to avoid it Disclaimer I made all reasonable efforts to ensure that the information contained in this presentation is accurate at the time of it's creation and/or modification. However, I cannot guarantee the completeness or accuracy of information contained in it, and shall not be responsible for any errors, omissions, or inaccuracies and accept no liability whatsoever for any loss or damage howsoever arising.
  • 2. Take a multithreaded server, like Tomcat in my case. Each HTTP request is served by a worker thread, and these threads share the connection pool. Threads are running simultaneously, and once a connection is occupied by a thread it is not available for use by other threads. For this setup I defined some rules; if you follow these no pool deadlock will occur. getConnection() HTTP worker threads Connection pool Database
  • 3. Rule 1: Your connection pool should respect the maxTotal, that is: when maxTotal database connections exist, the pool should not create new ones* Some pool implementations seem to take the maxTotal as a soft limit, this is undesired behavior. * If your pool does not respect the maxTotal, you may run into the deadlock problem later. Many databases limit the number of concurrent connections and allow no new connection when this number is reached; in this situation the getConnection() on the pool is blocked by the back end database. How to verify the pool respects maxTotal? - Set maxTotal=1 (e.g. in your context.xml); - Write code which is expected to block (deadlock on pool) and check it really does: try (Connection conn1 = pool.getConnection() { try (Connection conn2 = pool.getConnection() { < deadlock on pool >
  • 4. Rule 2: Dont nest getConnection() Doing so a thread can block itself, e.g. in case of maxTotal=1. Beware of hidden getConnection() calls, e.g. in case of calling another DAO method. Rule 3: Your (web)application should run on 1 connection, in other words when maxTotal=1 If it does not, your code contains one or more nested getConnection() calls. During development and test phase use maxTotal=1. In production use a reasonable setting for maxTotal; its value is surprisingly low*: (2 * #DB-cpu-cores) +1 Although it does not result in a pool deadlock, avoid long running HTTP threads due to long running queries. If some users are expected to run OLAP like queries (long running, e.g. reporting or exporting data) on your OLTP web application, increase maxTotal with the expect amount of concurrent users doing so. * https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing