January 10, 2015 at 4:35 pm
Comments posted to this topic are about the item IMPLICIT_TRANSACTIONS & BEGIN TRAN
January 10, 2015 at 4:38 pm
Nice question.
I think that having BEGIN TRANSACTION increment trancount by 2 instead of 1 is utterly silly. It's a really nasty trap that can cause pointless and damaging rollbacks.
Tom
January 11, 2015 at 12:23 am
Nice question & informative explanation, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 11, 2015 at 8:25 am
Very interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 11, 2015 at 10:05 am
TomThomson (1/10/2015)
Nice question.I think that having BEGIN TRANSACTION increment trancount by 2 instead of 1 is utterly silly. It's a really nasty trap that can cause pointless and damaging rollbacks.
ROLLBACK TRAN, if not specified the name of transaction, rollbacks all open transaction and reset @@trancount to ZERO.
Some problem may occur if you think to commit all modifications.
So, when I want to be sure about commitment I run this command:
WHILE @@TRANCOUNT > 0
COMMIT
Here is an example of loosing data:
-- new connection
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRAN
UPDATE mytab SET mycol = 0
COMMIT -- Here, I think it's all right
-- disconnect = ROLLBACK
Here is an example of an application locking resources:
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRAN
UPDATE mytab SET mycol = 0
COMMIT
Application continues but it is still locking resources and in case of disconnection or rollback all modifications are lost.
January 11, 2015 at 11:51 pm
good question.
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
January 12, 2015 at 12:54 am
Nice question. Thanks for sharing
January 12, 2015 at 1:40 am
Interesting, if I run this in SQL Management Studio I get 0,0,2,2,1
but if I run it from TOAD for SQL Server I get 2,2,3,3,2
January 12, 2015 at 2:04 am
richxs (1/12/2015)
Interesting, if I run this in SQL Management Studio I get 0,0,2,2,1but if I run it from TOAD for SQL Server I get 2,2,3,3,2
From BOL:
Implicit transactions may unexpectedly be on due to ANSI defaults.
January 12, 2015 at 3:17 am
This was removed by the editor as SPAM
January 12, 2015 at 6:25 am
Good question. I don't like the increment by 2 business, but it probably exists for a reason.
January 12, 2015 at 6:53 am
I've never used this.
January 12, 2015 at 8:02 am
Great, thought-provoking question. Thanks.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 12, 2015 at 8:30 am
Thank you for the post, Carlo, really good one.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
January 12, 2015 at 10:33 am
I never liked implicit transaction, and I never will.
I am also glad that Carlo used PRINT, not SELECT. I thought that using SELECT would have started the implicit transaction, but I tested after answering this question and nothing changes if I use SELECT instead of PRINT. That was a surprise to me.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply