Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Transaction scope for a batch
Add to briefcase

Transaction scope for a batch Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
shahgols
Posted Monday, February 04, 2013 12:16 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:12 AM
Points: 578, Visits: 3,258
Hi everyone,

I wanted to know what is the transaction scope for the following batch. Does SQL Server commit the changes after every delete or does it commit after the last delete? And SET IMPLICIT_TRANSACTIONS is set to OFF. Thanks in advance.

DELETE FROM table1

DELETE FROM table2

DELETE FROM table3

DELETE FROM table4

DELETE FROM table5

DELETE FROM table6


Post #1415451
happycat59
Posted Monday, February 04, 2013 8:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:55 PM
Points: 2,471, Visits: 2,066
By default, each statement is a transaction of its own. So, each delete statement is committed before the next one starts.


Post #1415557
shahgols
Posted Tuesday, February 05, 2013 10:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:12 AM
Points: 578, Visits: 3,258
Thanks! Made a mistake at work by thinking that the whole batch is a transaction. Yikes. Good thing I test my backups regularly.


Post #1415992
GilaMonster
Posted Tuesday, February 05, 2013 11:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025
However this isn't that much better.

BEGIN TRANSACTION
DELETE FROM table1

DELETE FROM table2

DELETE FROM table3

DELETE FROM table4

DELETE FROM table5

DELETE FROM table6
COMMIT TRANSACTION

What do you think will happen if the delete from table3 fails (foreign key violation)?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1416017
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.