Transaction Modes

Question

Transaction Modes

cdun2

SSCertifiable

Points: 7893
More actions
May 15, 2003 at 11:20 am

#80902

Hello,
I have intended to follow up on some questions I had about transaction modes. I have revisited BOL, and want to express some some points about transactions to make sure I have a clear understanding. In short, I interpret Autocommit, Implicit, and Explicit transactions modes as follows:
1. Autocommit: In this mode, every Transact-SQL statement is committed or rolled back when it completes, without the need to express COMMIT or ROLLBACK. So in the following code:
***************************
Update tblCLTestScoresTemp
set tested_grd_lvl = '08'
where tested_grd_lvl ='8'
Update tblCLTestScoresTemp
set tested_grd_lvl = '09'
where tested_grd_lvl ='9'
Insert tblCLTestScores
Select
tmp.Permnum,
tmp.BookID,
tmp.TestDate,
tmp.Tested_Grd_Lvl,
tmp.LSRaw,
tmp.RRaw,
tmp.WRaw
from tblCLTestScoresTemp tmp
Where Not Exists (Select * from
tblCLTestScores TS
WhereTS.Permnum=tmp.Permnum
AND
TS.BookID=tmp.BookID
AND
TS.TestDate=tmp.TestDate)
************************
If the connection operates in Autocommit mode, that means that there will be three seperate transactions that will COMMIT or ROLLBACK automatically. If only the INSERT encounters a problem, the INSERT will be rolled back and the UPDATE statements will commit.
2. Implicit: If this mode is turned on for the current connection, Autocommit will be overridden. Transactions must explicitly state COMMIT or ROLLBACK, but BEGIN TRAN does not need to be expressed. In this mode, the above code would generate three transactions, but the transactions would not COMMIT or ROLLBACK unless these statements were explicitly addressed after each of the two UPDATEs and the one INSERT.
3. Explicit: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement. A BEGIN TRANSACTION statement overrides Implicit and Autocommit mode. It would be possible with the above code to have the two UPDATE commands and the INSERT command execute as one transactions in this mode.
Is my understanding of the different Transaction Modes correct. If not, could you please help clarify? When might there be a situation where I would want to use Implicit Transaction Mode instead of Explicit or Autocommit?
Thanks
CSDunn

Viewing 5 posts - 1 through 4 (of 4 total)

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

GRN Hall of Fame Points: 3260 More actions · Answer 1

almost there , but the implicit mode would not create 3 transactions , just one transaction - you need to commit the whole transaction just once..

Personally i use explicit transactions whenever needed

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thanks for your help. Is the only difference between Implicit and Explicit transaction mode the fact that Explicit transaction mode requires 'BEGIN TRAN'? If that is true then why not just do away with Implicit Transaction mode? If that is not all there is to it, then what am I missing?

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

I think your unstanding about Implicit transactions is correct.

From BOL.

"Implicit transactions

A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement."

You do nothing to delineate the start of a transaction, you only commit or roll back each transaction.

Implicit transaction mode is set either using the Transact-SQL SET statement, or through database API functions and methods.

GRN Hall of Fame Points: 3260 More actions · Answer 4

hmm...you are right - sorry should have checked what i was posting - my mistake

Thanks