CODEWITHSUNDEEP
sqlsql-servertransactionscommitrollback
ask_Overflow
ask_Overflow

Reputation: 275

Use of explicit commit in transaction

Is there any difference in adding explicit commit in my transaction than auto commit. 

CREATE TABLE #test (test_col INT)

With explicit COMMIT

INSERT #test
VALUES (11)

BEGIN TRY
    BEGIN TRAN DELETE_TRAN

    DELETE FROM #test

    COMMIT TRAN DELETE_TRAN
END TRY

BEGIN CATCH
    ROLLBACK TRAN DELETE_TRAN

    SELECT ERRORMESSAGE = Error_message()
END CATCH

SELECT *
FROM   #test

Without explicit COMMIT

INSERT #test
VALUES (11)

BEGIN TRY
    BEGIN TRAN DELETE_TRAN

    DELETE FROM #test
END TRY

BEGIN CATCH
    ROLLBACK TRAN DELETE_TRAN

    SELECT ERRORMESSAGE = Error_message()
END CATCH

SELECT *
FROM   #test

Here both does the same thing. Can anyone tell is there any difference or advantage one over the another.

Upvotes: 4

Views: 1627

Answers (2)

TomS
TomS

Reputation: 82

The main functional difference I could see would be that by using the explicit COMMIT in your first example you are making sure that the table (temp table in this case) is unlocked for the SELECT statement at the end. Whereas in your second example, the SELECT would be blocked for other users, unless they were performing dirty reads (ie. WITH (NOLOCK), etc), until the implicit COMMIT is triggered.

Due to the fact that you're using a temp table it's not necessarily a big deal but if you were to change that to an actual table then you would have a difference in how long that table is locked due to the open TRAN on it. This would mean that concurrent calls would block for much longer and stack up behind each other. Or in the case of dirty reads, the other connections would not see your changes yet.

It's also a good standard practice to explicitly close any TRAN you open in SQL so that you are not relying on the caller to attempt to COMMIT the TRAN. Keep in mind that if the connection to SQL is closed and the TRAN has no COMMIT then the TRAN automatically gets a ROLLBACK instead.

Upvotes: 3

Moolshanker Kothari
Moolshanker Kothari

Reputation: 551

Implicit Commit is slower than Explicit Commit, we can turn ON or OFF the implicit depends upon our requirement, for further details you can see this link:SQL Server – performance and other stories

Upvotes: -1

Related Questions