Connection usage when using Spring transaction management

[dnc253]

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.

[Marten Deinum]

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

[dnc253]

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.

[Marten Deinum]

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.

[dnc253]

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.

[dominic_veit]

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.