SQL server, SQLServerPedia Syndication

Implicit and explicit transactions.

There are two ways a query can be committed in SQL. Implicitly and explicitly. Queries that have use the begin tran/commit statements are performing explicit transactions. Queries without the begin tran/commit statements are implicitly committed. There are some performance implications when not explicitly commiting a transaction. Example below:

— Create the table
CREATE table testins
(
id int constraint cix_id_testins primary key clustered identity(1,1),
some_data varchar(25) null
)

— implicit transactions, tran1
SET NOCOUNT on
DECLARE @counter int
SET @counter =1
WHILE (@counter < 100000)
begin
    insert into testins VALUES (‘Test Data’)
    set @counter=@counter+1
end

— explicit transactions, tran2
SET NOCOUNT on
DECLARE @counter int
SET @counter =1
BEGIN tran
    WHILE (@counter < 100000)
    begin
        insert into testins VALUES (‘Test Data’)
        set @counter=@counter+1
    end
commit

The implicit transaction (tran1) took 49 seconds to complete, whereas the explicit transaction (tran2) completed in 1 second. The diffrence in this scenario has to do with Log buffer flush. In the implicit trasactions, the log
blocks in memory are flushed to disk (log file) more often than when the explicit transactions was used. In the explicit transaction, the logs are flushed to the log file only when the log blocks in memory are full. To learn more about SQL Server transaction log files check out these links: log file logical architecture, log file physical architecture.

Advertisements
Standard

One thought on “Implicit and explicit transactions.

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s