Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
edcas
Starting Member
5 Posts |
 Posted - 2006-02-06 : 05:05:47
|
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
12543 Posts |
Posted - 2006-02-06 : 06:48:09
|
| 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. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-06 : 08:18:00
|
| "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 |
|
|
|
edcas
Starting Member
5 Posts |
Posted - 2006-02-06 : 20:45:03
|
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 profilerset implicit_transactions on...perform all the update---set implicit_transactions off
TQ  |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-06 : 21:12:21
|
| All update statements are run in a trnsactionWith 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. |
|
|
|
|
|
|
Implicit transaction mode