Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Explicit Transactional Scope

Author  Topic 

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-10 : 13:07:41
[Code]

Declare @i int
Begin Transaction
WHILE (@i <=10)
Begin
Insert into dbo.Employee1(EmpNo,EmpName,Period)
select @i,'empname' +@i,1
Set @i=@i + 1
END
Commit Transaction
[/code]

Normally sql server by default it will be implicit Transaction.I've seen in some of the stored procedure in our
company they have given explicitly..can i know what would be the reason behind this. I guess it would be realated to log file.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 13:14:27
the reason is they want to make all the activities inside it atomic.
ie in above case since the while loop is inside transaction, only if entire loop suceeds, the data will be commited. Any intermediate error in any of iteration will cause transaction to fail.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 13:38:34
quote:
Originally posted by sent_sara

[Code]

Declare @i int
Begin Transaction
WHILE (@i <=10)
Begin
Insert into dbo.Employee1(EmpNo,EmpName,Period)
select @i,'empname' +@i,1
Set @i=@i + 1
END
Commit Transaction
[/code]

Normally sql server by default it will be implicit Transaction.I've seen in some of the stored procedure in our
company they have given explicitly..can i know what would be the reason behind this. I guess it would be realated to log file.


I know you are showing the code as an example, but the way it is written, it won't insert anything into the table because @i has not bee initialized.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 13:49:32
One other thing to keep in mind is that if you run into any batch terminating errors, the behavior may not be as you expect. For example, how many rows if any would be inserted into the table in the query below? And, it will leave around an open transaction.
CREATE TABLE AX(id INT NOT NULL);

Declare @i INT = 1;
Begin Transaction
WHILE (@i <=10)
BEGIN
Insert into AX (id)
select @i
IF (@i >= 5) INSERT INTO AX VALUES (@i,'abcd');
Set @i=@i + 1
END
Commit TRANSACTION

DROP TABLE AX;
If you want a set of statements to be executed atomically, use the pattern on this MSDN page: http://msdn.microsoft.com/en-us/library/ms175976.aspx
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-10 : 15:15:24
Thanks James and visakh16

I got it.

how the log file will behave.
My thoughts on this is by giving explicit transaction ,it will enter the log file at final stage (ie) during commit trans

if explicit transaction not given then for each and every insert within the loop log entry will be there.

Correct me if I'm wrong..


quote:
Originally posted by James K

One other thing to keep in mind is that if you run into any batch terminating errors, the behavior may not be as you expect. For example, how many rows if any would be inserted into the table in the query below? And, it will leave around an open transaction.
CREATE TABLE AX(id INT NOT NULL);

Declare @i INT = 1;
Begin Transaction
WHILE (@i <=10)
BEGIN
Insert into AX (id)
select @i
IF (@i >= 5) INSERT INTO AX VALUES (@i,'abcd');
Set @i=@i + 1
END
Commit TRANSACTION

DROP TABLE AX;
If you want a set of statements to be executed atomically, use the pattern on this MSDN page: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 15:41:13
The internals of the log file are really not of much use from a logical perspective. Nonetheless: the log file records the begin transaction, each of the updates and the commit transaction. For implicit transactions, there will be a set of begin tran, update and commit for each update.
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-10 : 15:53:15
the reason why i raised this question is: during the execution one of our stored procedure we have got "Insufficient diskspace in tempdb" this is due to log file grow.

to avoid the log file growth,im in bit confusion to be with implicit
or explicit transaction.

so from your answer what i understood is:
Implicit transaction will have begin Trans and commit tran for each updates
Explicit transaction will have group of updates within the scope

so now my question is whether if begin transaction is given at beginning and do some several updates,nested stored procedures etc and finally commited (explicit transaction).. do the log file is grown on this?

quote:
Originally posted by James K

The internals of the log file are really not of much use from a logical perspective. Nonetheless: the log file records the begin transaction, each of the updates and the commit transaction. For implicit transactions, there will be a set of begin tran, update and commit for each update.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 16:15:42
If you are running out of diskspace on tempdb, before looking at specific queries and trying to optimize them to use less disk space, you should investigate why it is growing. There is a lot of useful information here that will help you with that. http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

There are some settings in SQL Server that can cause heavier usage of tempdb - for example, version store (which comes into play if you have enabled read committed snap shot isolation). Long running transactions (rather than many smaller transactions) cause version store to grow.

Also, tempdb is shared by all the databases on the server instance, so other databases and queries also will play into it.

In any case, take a look at the guidance on that page and see if that helps you.
Go to Top of Page
   

- Advertisement -