Geeks With Blogs

News

 Subscribe to my blog


Post Categories

Jim Lahman's Blog Fortitude|Endurance|Faith|Teamwork

Transactions are a great way to perform many actions but make it appear as one unit.  Always start a transaction with BEGIN TRAN.  Let's take a look at these statements:

   1:  BEGIN TRAN
   2:       DELETE SCHEDULES

 

When this code runs, SQL Server opens a transactions and tentatively deletes all records from the SCHEDULES. The records are not actually deleted until a COMMIT.  When a tranmsaction starts, SQL Server places a lock on the SCHEDULES rows or the entire table.  This lock will prevent other users from making changes to the SCHEDULES table until either a COMMIT TRAN or ROLLBACK TRAN is issued and completed.  If neither are issued, the SCHEDULES  table will be blocked. 

Here is an example:

 

   1:  use chtl_l2_warehouse
   2:  go
   3:  begin tran
   4:         delete schedules
   5:  commit tran
   6:  go
   7:  drop table schedules
Posted on Tuesday, April 5, 2011 3:33 PM | Back to top


Comments on this post: Using transactions to delete records from SQL Server

# re: Using transactions to delete records from SQL Server
Requesting Gravatar...
If you're just going to delete a table it may be better to use the Truncate command instead of Delete.

http://www.mssqltips.com/tip.asp?tip=1080
Left by Ryan on Apr 05, 2011 4:30 PM

# re: Using transactions to delete records from SQL Server
Requesting Gravatar...
Ryan:

Yes, I agree if you are just going to delete a table, then the truncate command would be the better solution.
However, truncate does not log the records that are deleted. Because of this, the truncate command should be used with caution. There is no going back; you cannot change your mind.

Jim
Left by Jim Lahman on Apr 05, 2011 4:37 PM

# re: Using transactions to delete records from SQL Server
Requesting Gravatar...
I just want to add something,
at this point
"begin tran
delete schedules"
the lock is no on the Table Schedules, table will NOT be blocked! The lock is on the deleted ROWS. Another process can make operations over the table like INSERTs.
Also, the behaviour of locks in transactions may depend on the transaction isolation level.

RGarvao
Left by Ricardo on Apr 06, 2011 5:31 AM

# re: Using transactions to delete records from SQL Server
Requesting Gravatar...
Ricardo:

Thank you for your feedback.

Yes, the last sentence should read "If neither are issued, users will be blocked".

Thank you for pointing this out.

Jim
Left by Jim on Apr 06, 2011 8:48 AM

Your comment:
 (will show your gravatar)


Copyright © Jim Lahman | Powered by: GeeksWithBlogs.net