CODEWITHSUNDEEP
sqlsql-serversql-server-2012
CharlesKSQL
CharlesKSQL

Reputation: 37

IMPLICIT TRANSACTIONS ON/OFF and BEGIN /COMMIT Transaction

I believe the default setting is for DML statements to be automatically committed (default is IMPLICIT TRANSACTIONS ON) but if I need to perform a BEGIN/COMMIT Transaction, does the IMPLICIT TRANSACTION setting automatically turn OFF within that BEGIN/COMMIT transaction?

Upvotes: 2

Views: 2186

Answers (1)

M.Ali
M.Ali

Reputation: 69554

No default behavior is not Implicit Transactions, In Implicit Transactions each statement is wrapped in a new Transaction and then you have to explicitly commit it or rollback it.

Default behavior is Auto-Commit where each statement is wrapped in a transaction and on successful execution it is committed.

Explicit Transactions are when you explicitly Begin a transaction and explicitly commit a transaction, you do not need to turn off or on anything for this, just use BEGIN TRANSACTION before your SQL Statement and you would have started an explicit transaction, now you will have to explicitly COMMIT TRANSACTION or ROLLBACK TRANSACTION .

  1. If no BEGIN TRANSACTION statement is executed , it will be the default Auto-Commit transaction mode

  2. If you use BEGIN TRANSACTION statement it will be an Explicit Transaction.

  3. Lastly to make use of Implicit Transaction you will need to turn them on by using SET IMPLICIT_TRANSACTIONS ON;

Upvotes: 4

Related Questions