IMPLICIT_TRANSACTIONS vs BEGIN TRAN

Question

Post reply

IMPLICIT_TRANSACTIONS vs BEGIN TRAN

Carlo Romagnano

SSC-Insane

Points: 22682
More actions
January 17, 2015 at 11:21 am

#313758

Comments posted to this topic are about the item IMPLICIT_TRANSACTIONS vs BEGIN TRAN
January 19, 2015 at 1:13 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

edwardwill SSCertifiable Points: 5856 More actions · Answer 1

I would have found this easier if there had actually been a question.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

Thank you Carlo, very good and interesting one.

(I actually do the same thing when data-fixing in the PROD, so it was easy for me to select proper choices except for one, I did selected the right one, but for incorrect reasoning. "when I_T is ON and in that batch if any code raises error, it continues" - I didn't literally tried it until now, but I knew this as "until the I_T is not turned OFF it does not switched back to auto-commit mode", so based on this I selected - yea delete can be rolled back in batch one as I_T is still ON.)

More to see such questions. Thank you again. 🙂

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Carlo Romagnano SSC-Insane Points: 22682 More actions · Answer 3

edwardwill (1/19/2015)
I would have found this easier if there had actually been a question.

That's true!

The question is "select six TRUE answer". It's "implicit".

🙂

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

serg-52

SSCrazy Eights

Points: 9913

January 19, 2015 at 2:37 am

#1771056

Quite well elaborated, thank you.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 5

Carlo Romagnano (1/19/2015)
edwardwill (1/19/2015)
I would have found this easier if there had actually been a question.
That's true!
The question is "select six TRUE answer". It's "implicit".
🙂

Good one.. 🙂

Thank you carlo,for all the questions on IMPLICIT TRANSACTIONS

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Nice question.

But I don't like the last sentence of the explanation

Transaction started by "BEGIN TRAN" may end prematurely and unexpected by some errors that may ROLLBACK transaction.

That suggests that only transactions that start with BEGIN TRAN can be prematurely ended with ROLLBACK by an error, but in fact transactions begun implicitly can suffer the same fate. Indeed that happens to the first transaction in batch one (started by "SELECT COUNT(*) AS TheBeginning FROM Tab"). The second transaction in batch one (started by "DELETE FROM Tab") is rolled back explicitly. The only transaction in batch one that is not rolled back is the single statement transaction ("SELECT COUNT(*) AS TheEnd FROM Tab") at the end.

Tom

Carlo Romagnano SSC-Insane Points: 22682 More actions · Answer 7

TomThomson (1/19/2015)
Nice question.
But I don't like the last sentence of the explanation
Transaction started by "BEGIN TRAN" may end prematurely and unexpected by some errors that may ROLLBACK transaction.
That suggests that only transactions that start with BEGIN TRAN can be prematurely ended with ROLLBACK by an error, but in fact transactions begun implicitly can suffer the same fate. Indeed that happens to the first transaction in batch one (started by "SELECT COUNT(*) AS TheBeginning FROM Tab"). The second transaction in batch one (started by "DELETE FROM Tab") is rolled back explicitly. The only transaction in batch one that is not rolled back is the single statement transaction ("SELECT COUNT(*) AS TheEnd FROM Tab") at the end.

Re-read the batch ONE, it ends with

SET IMPLICIT_TRANSACTIONS OFF

SELECT COUNT(*) AS TheEnd FROM Tab

At the end @@TRANCOUNT is ZERO.

I agree with you that batch ONE ROLLBACKS twice. But it doesn't matter, my hint is achieved: no modifications.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Carlo Romagnano (1/19/2015)
TomThomson (1/19/2015)
Nice question.
But I don't like the last sentence of the explanation
Transaction started by "BEGIN TRAN" may end prematurely and unexpected by some errors that may ROLLBACK transaction.
That suggests that only transactions that start with BEGIN TRAN can be prematurely ended with ROLLBACK by an error, but in fact transactions begun implicitly can suffer the same fate. Indeed that happens to the first transaction in batch one (started by "SELECT COUNT(*) AS TheBeginning FROM Tab"). The second transaction in batch one (started by "DELETE FROM Tab") is rolled back explicitly. The only transaction in batch one that is not rolled back is the single statement transaction ("SELECT COUNT(*) AS TheEnd FROM Tab") at the end.
Re-read the batch ONE, it ends with
SET IMPLICIT_TRANSACTIONS OFF
SELECT COUNT(*) AS TheEnd FROM Tab
At the end @@TRANCOUNT is ZERO.

Since every individual DML or DDL statement when implict transactions is OFF and there is no outstanding explicit transaction (ie in auto-commit mode) the termination of the transaction (whether by commit or rollback) takes place automatically, so of course @@trancount will be 0 after the statement. I can't imagine what makes you think I suggeted otherwise. A single statement transaction always leaves @@trancount at 0 on exit, if it didn't it wouldn't be a single statement transaction, would it, because it would need at least one more statement to terminate the transaction.

I agree with you that batch ONE ROLLBACKS twice. But it doesn't matter, my hint is achieved: no modifications.

I agree it doesn't matter as to the outcome for database state; but that doesn't detract from the fact than making a point that termination through error can happen for explicit transactions in connection with a question which is looking at differences in behaviour when working with explicit versus implicit transactions carries a risk of misleading people, perhaps making them believe that this is another point of difference, and all of us who write questions for QotD should try to avoid such risks, although it is easy to overlook them (I've done it often enough myself, so I don't expect others not to).

Tom

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

Very good question & explanation, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

Gaaaah, I forgot to tick one of the options.

MOAR caffeine!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

free_mascot One Orange Chip Points: 27169 More actions · Answer 11

Good One, Thank you.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

webrunner SSC-Dedicated Points: 31885 More actions · Answer 12

edwardwill (1/19/2015)
I would have found this easier if there had actually been a question.

+1

-------------------
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

NBSteve SSCarpal Tunnel Points: 4068 More actions · Answer 13

Good question, but I agree the explanation is a little fuzzy.

Just to reiterate:

Although it's never said, the question kindof implies that the transaction behaves differently in the two scenarios. This is not the case.

The difference is that in batch 1, the error rolls back the transaction immediately, and the delete statement immediately begins a second transaction due to IMPLICIT_TRANSACTIONS. In batch 2, the error rolls back the transaction immediately, but there is nothing to start a second transaction. Therefore, the batch 1 DELETE is inside a transaction and can be rolled back, while the batch 2 DELETE is not inside a transaction and commits immediately.