Connection pooling vs using a single connection

[Maxim Katcharov]

From what I understand:

Connection pooling is used primarily to allow for transactions using connection.commit() or connection.rollback() . However, transactions are not always neccisary. If an application doesn't use transactions, could it get away with having a single connection instead of a pool? And, more importantly, are there any major benefits to using that single connection, apart from the relatively slight overhead of the pool periodically connecting new connections?

[Shailesh Chandra]

Originally posted by Maxim Katcharov:
From what I understand:

Connection pooling is used primarily to allow for transactions using connection.commit() or connection.rollback()

From where did you get this thought ??? purpose of connection pooling is to reuse connection rather creating new connection.

Making a connection to database is an expensive operation, and Connection pooling can increase performance dramatically by reusing connections rather than creating a new physical connection each time a connection is requested.

I would recommend to read some resources on net for connection pooling

Shailesh
[ April 23, 2005: Message edited by: Shailesh Chandra ]

[Maxim Katcharov]

the reason for having multiple connections instead of just one constant one is for transactions, is it not? Otherwise, everything can be done with just that one connection, correct?

[Shailesh Chandra]

Originally posted by Maxim Katcharov:
the reason for having multiple connections instead of just one connection one is for transactions, is it not? Otherwise, everything can be done with just that one connection, correct?

I am not sure that I understood you question correctly.

We may some time maintain transaction by setting auto-commit mode to false then conn.commit() or conn.rollback() but connection pooling has nothing to do with it.

I think your interpretation of connection pooling is that multiple connection are used for trasaction because one connection is dedicated to one thread/code/user so that only allocated user can perform commit or rollback operation.

but connection poooling has different purpose as I explained in my previous post.

Shailesh

[Maxim Katcharov]

I understand what connection pooling is about. It's slow to create a new connection. This is not what I'm getting at. If you don't need transactions, then why would you pool multiple connections instead of using a single one?

[Shailesh Chandra]

Originally posted by Maxim Katcharov:
If you don't need transactions, then why would you pool multiple connections instead of using a single one?

I am confused how connection pooling maps with transaction

[Maxim Katcharov]

you need your own connection to do a transaction, yes? So if many threads are potentially doing transactions, you need many connections, and you can go with either opening a new one each time or pooling them.

But what if you aren't doing transactions? Then you only ever need to open a single connection, and have as many threads as you want use it. Which brings us back to my original post, I think.

[Ben Souther]

There are plenty of other reasons for needing more than one connection.
The best example would be a multi user app.

Most webapps would do very poorly if all requests had to queue up for a single connection. They also do poorly if a new connection has to be made for every request.

[Maxim Katcharov]

Thanks Ben, that's what I was wondering.

How much of an overhead is there for maintaining several connections to the database?

[Ben Souther]

I couldn't give you any numbers.
Someone else may chime in with links to actual test results or comparisons.
I think it's pretty insignificant on most modern servers.

I do know the time it takes to establish a new one is significantly higher than the time it takes to re-use an existing one.
[ April 24, 2005: Message edited by: Ben Souther ]

[Shailesh Chandra]

Originally posted by Maxim Katcharov:
you need your own connection to do a transaction, yes? So if many threads are potentially doing transactions, you need many connections, and you can go with either opening a new one each time or pooling them.

That is what I said some what in different word if you refer my previous post I have written that

Multiple connection are used for trasaction because one connection is dedicated to one thread/code/user so that only allocated user can perform commit or rollback operation.


lets assume you have no transaction and you are processing a resultset in one thread ? and sametime in multiuser app another thread want to process some other resource with same connection ? wont you prefer new connection as Ben said Most webapps would do very poorly if all requests had to queue up for a single connection

But there are many other resons for connection pooling, I dont think It relates to transactions

But what if you aren't doing transactions? Then you only ever need to open a single connection, and have as many threads as you want use it.

how multiple threads are going to use same connection ? If threads are waiting for other thread to release connection then you can follow same thing with transactions . and still you would be able to maintain transaction with one connection so where does pooling come in picture

My only idea is to explain that pooling is a differnt conecept which should not be related to transaction. someone correct me if I am unclear about this


Shailesh
[ April 24, 2005: Message edited by: Shailesh Chandra ]