connection pooling and commits

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using ado.net connection pooling and am calling stored procedures using ExecuteNonQuery. My manager says the following: "if you are using pooling, and you don't do your commit, then the implied transaction will remain open until the connection closes...if someone else jumps into the connection, then they can actually rollback your updates.

Is this true? After calling ExecuteNonQuery do I have to manually commit
 
One of the criteria when "inheriting" a connection is that it must be in the
same transaction context. You can't open a connection that some other
process' transaction pending.

Always commit your transactions--don't depend on any other tear-down to do
this for you.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Darin said:
I am using ado.net connection pooling and am calling stored procedures
using ExecuteNonQuery. My manager says the following: "if you are using
pooling, and you don't do your commit, then the implied transaction will
remain open until the connection closes...if someone else jumps into the
connection, then they can actually rollback your updates."
 
Consider that each operation has an implied transaction that the server
manages. It's not necessary to manage the transactions yourself when a
single table operation is being done (that might be what you're doing).
Frankly, I don't recommend client-side transactions at all--I recommend that
folks use stored procedures that handle any transactions when changing more
than one base table.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Darin said:
Thanks for the reply Bill.

When I'm inserting/updating multiple tables I do the
trans.begintransaction and trans.commit and then close the connection.
My question is when I'm inserting/updating a single table I call
ExecuteNonQuery and call the Close() method of the connection object without
any commits in my code. Now that the connection went back to the pool is a
transaction is still pending since I didn't do any manual commits or does
the connection know I'm done and commits since I called Close()?
 
I agree with Bill on the client side transactions, whenever you can move the
transaction to the server it will be beneficial to do so. Local transactions
are better the shorter and more atomic they are.

As far as your scenario:
If you call BeginTransaction (if you start the transaction using our api)
and then call connection close we will know that there is an active
transaction on the connection and we will roll this back for you on close.

If you start a transaction using tsql (command.ExecuteNonQuery(); when the
command.CommandText ="begin transaction") and then you call close without
calling commit or rollback then we will have no way to determine that a
transaction was started and we will place the connection back in the pool
with the transaction still live. As soon as somebody else takes this
connection from the pool and tries to use it we will realize that there was
previous state and clear it (this is guaranteed). At this point your
transaction would get rolled back. Worst case scenario in this case is that
you are locking the resources that you have touched with your transaction
for the time it remains unused on the pool.

As far as your question :
"if someone else jumps into the connection, then they can actually rollback
your updates." -> If you do not call Commit then rollback is guaranteed in
all cases so this does not make any sense.

Hope this helps.
 
Back
Top