SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Implicit transaction mode
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

edcas
Starting Member

Australia
5 Posts

Posted - 02/06/2006 :  05:05:47  Show Profile
hi,

may i know what are the best way to test on implicit transaction ON and OFF. My company's application is set to implicit transaction ON, and i would like to find out what would be the impact if i set the implicit transaction mode to OFF.

I try to trace the differences of both transaction mode by using sql profiler, but find it crazy to read all the code line by line . Is there a good way of doing it??

thanks

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2006 :  06:48:09  Show Profile  Visit nr's Homepage
Don't thghink there is an easy way. You'll just have to test every aspect of the app and see if it works.
If the app is built to expect it then I would be surprised if it works. The only way it could would be if the app is built to add an explicit transaction to every update statement or to check the transaction state after every update and to commit if necessary.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/06/2006 :  08:18:00  Show Profile
"If the app is built to expect it then I would be surprised if it works"

Me too ... surely the APP is going to have no Begin Trans but lots of Commits. They aren't going to work if there is no corresponding Begin Trans, or am I being thick?

Kristen
Go to Top of Page

edcas
Starting Member

Australia
5 Posts

Posted - 02/06/2006 :  20:45:03  Show Profile
the app works in both mode, but, are there any tools that i can trace on it. Like how many active transaction are being called?

btw, what are the major different for both of the mode? currently, i found out that if i hav the implicit transaction mode set to OFF, sql server will issue the ON & OFF mode manually. For example:


quote:

Capture from SQL profiler

set implicit_transactions on
...
perform all the update
---
set implicit_transactions off



TQ

Edited by - edcas on 02/06/2006 21:10:46
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2006 :  21:12:21  Show Profile  Visit nr's Homepage
All update statements are run in a trnsaction
With implicit transactions off the transaction automaticaaly commits at the end of a statement.
With implicit transactions on the transaction does not automaticaaly commits at the end of a statement and needs to be committed explicitely.
In both cases if the statement is run in an open transaction another transaction is not started.

As I said before the only way the app could work in both modes is if it always runs updates within an explicit transaction or if it checks the trancount on return and commits if necessary.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000