Announcement Announcement Module
No announcement yet.
Connection usage when using Spring transaction management Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Connection usage when using Spring transaction management

    We want to start using @Transactional annotations throughout our applications, but before we make such a switch, we want to make sure we understand how connections we'll be retrieved doing this. If I understand correctly, the first time it needs a connection, it will retrieve it from the pool, and then bind it to the thread. Does this mean that that connection is now inaccessible to any other threads and won't be returned to the pool until that thread dies? We have a lot of traffic, and we don't want to max out our database connections because each thread is holding onto it's own connection. Say we have 6 app servers and 250 incoming requests at any time. Does this mean there would be 1500 (250 * 6) connections to the database at any given time? Please help me if I'm not understanding something correctly. Thanks.

  • #2
    It depends... Depends on your datasource, connection pool and on the type of request (if it doesn't need a transaction nothing will be bound). But if it is a reassurance I have been using this in production with 100s/1000s of concurrent users, without any problems (even with JPA in the mix).


    • #3
      So, say we had @Transactional(propagation=NEVER) everywhere. Would this mean the connections would not be bound to the threads, and could be used as needed by different threads? Thus, there would really be no need to worry about maxing out database connections?

      Then, say we had @Transactional(propagation=REQUIRED) everywhere. Would this mean the connections would be bound to the current thread, and then not available to any other threads, meaning each thread would be getting it's own new connection from the pool? Thus, there could be a concern about maxing out database connections if the number of concurrent users gets too high?

      Obviously you wouldn't want to require transactions everywhere, but I just want to make sure I understand what is going on when we would want transactions, and when we wouldn't. Thanks.


      • #4
        If there is a unit-of-work that needs to succeed of fail you need a transaction.

        Also I think you are thinking this over too much, this setup is being used by 1000s of companies worldwide with even so much concurrent users without problems. As I already mentioned I myself used it in highly concurrent situations without a problem, also if you think this is a problem why should it be a problem, if it is your actions take too long and you should do something about it, in general they finish quickly without becoming a problem. (You should also be using a connection pool and not creating connections as you need as that is a serious performance drain).

        @Transactional(propagation=NEVER) states this method should never run in a transaction and will not start a transaction (and not acquire a connection) and throw an exception if it is called from within a transactional method.


        • #5
          Thank you for your response. I probably am over thinking things, but I'm just wanting to be sure we understand what is going on. The last thing I want is for us to put this in everywhere and it kills our database. I understand the propagation levels and what they mean on a high level, but I was hoping for some insight of what is actually happening underneath with all the connections. We are using a connection pool, so that is not a concern. We just don't want to have a case where we get a bunch of hits to some transactional code, and then max out our database connections.

          When you say "@Transactional(propagation=NEVER) states this method should never run in a transaction and will not start a transaction (and not acquire a connection)", doesn't it have to get a connection at some point? How else will it communicate with the database? Or am I just understanding that wrong? Perhaps the connection is returned to the pool after it completes it's operation, and thus the connection is available from the pool right away after the operation?

          And for a worst case scenario where everything does require a transaction. The connection has to be bound to the thread so it can keep all the operations on the same connection, right? So, in a poorly architected system with @Transactional(propagation=REQUIRED) everywhere, there would be a connection bound to each thread, and thus with a lot of threads, you could max out database connections, right?

          Again, I'm just trying to fully understand the implications of the transaction management, and gain some understanding of how it works underneath. I really appreciate your helping me to understand.


          • #6
            If you are manually coding JDBC now, and are using database transactions properly (autocommit = false, try - con.commit(), catch - con.rollback, and finally - con.close()) Then you are already binding connections to threads. When you go to Spring, you just get rid of the boilerplate.

            Propagation settings dont matter, until you start nesting @Transactional method calls among different classes. Then, propagation is how you tell Spring how to behave. Should it just continue on within the existing transaction, or should it suspend the current transaction, and start a new one.


            • #7
              If you have 6 appservers, each with a connection pool of 10, and you have 250 requests come in, the load balancer should send on average 250/6=41.6 requests to each appserver. Lets call it 40. The app server will be able to handle 10 requests asyncronously. So throughput will depend on the response time per request. Lets say the response time per request is 100ms, So, in the first 100ms, the server will process 10 requests, so it will be able to handle 40 in ~400ms.

              So, your maximum load will be appservers * connectionpool / response time --- 6 * 10 / .1 sec = 600 requests per second.

              Spring has nothing to do with the above calculations. However, the above is only correct if indeed, only one connection is consumed per request. Spring will make it less likely that, due to bad code or architecture, more than one connection is consumed per server request.

              Finally. if any single request ever requires more than one database connection, it is possible to hang the entire server. Because, if 10 of these requests start, and all are able to acquire their first connection, the connection pool will be full. None of these requests will be able to acquire a second connection and finish. No other request will be able to acquire a connection either. The server will be dead, until you reach the connection timeout.

              So, it is not a big deal that connections are bound to a thread, in fact this is what you want. However, it IS a HUGE deal if you have requests that use more than one connection. Spring makes this easier. Set your connection pool size to 1, and see if your stuff still runs. If it doesnt, then you have problems
              Last edited by dominic_veit; Feb 16th, 2012, 02:27 PM.


              • #8
                I really appreciate your helping me to understand. Attachment
                Attached Files