?
Solved

How do create a SQL Server Table Lock?

Posted on 2014-03-30
8
Medium Priority
?
603 Views
Last Modified: 2014-03-31
Basic question for a newbie. I need to create a SQL statement and/or stored procedure (in MS SQL Server) that will:

1) lock a table (to prevent read/update, etc.)
2) DELETE FROM Customers
3) INSERT INTO Customers (CustomerName, Country) SELECT CustomerName, Country FROM OldCustomers
4) clear the table lock

Any suggestions of how I should code this?
0
Comment
Question by:eeyo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 39964980
Rather than attempting to lock the table, have you considered using Transactions as in:
1. BEGIN TRANSACTION
2. DELETE Customers
3. INSERT Customers
4.a. COMMIT TRANSACTION if no error,
4,b. ROLLBACK TRANSACTION if errors.
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 200 total points
ID: 39964978
Assumes referential-integrity won't get in the way:
	begin tran

	DELETE FROM Customers
	if @@error <> 0 goto ErrorFound

	INSERT	Customers
	(	CustomerName
	,	Country
	)
	SELECT	CustomerName
	,	Country
	FROM	OldCustomers
	if @@error <> 0 goto ErrorFound

	commit tran
	goto EndofProcessing

ErrorFound:
	rollback tran

EndofProcessing:
	print 'end of processing'

Open in new window

0
 
LVL 9

Assisted Solution

by:edtechdba
edtechdba earned 200 total points
ID: 39965056
How about using TABLOCKX? Or TABLOCK for an exclusive lock on the table?

DELETE Customers;
-- If there are many records in the Customers table, then TRUNCATE TABLE, may increase the processing time in order to avoid excessive logging.

INSERT INTO Customers WITH (TABLOCKX)
SELECT CustomerName, Country
FROM OldCustomers;

Open in new window


Here's an informative article about the differences between the table locks:
Table Hints (Transact-SQL)

Here are a few excerpts from the article:
"TABLOCKX: Specifies that an exclusive lock is taken on the table."

"TABLOCK: When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. "
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:eeyo
ID: 39965649
Regarding the Transaction solutions (sorry, newbie question):

if other users begin queries (not using this stored procedure) of this customer table during this transaction, will these other queries be placed on hold until the transaction is committed?  I don't want other users to read partially complete table rows.
0
 

Author Comment

by:eeyo
ID: 39965861
by: edtechdbaPosted on 2014-03-30 at 09:50:39ID: 39965056
Rank: Guru
How about using TABLOCKX? Or TABLOCK for an exclusive lock on the table?

DELETE Customers;
-- If there are many records in the Customers table, then TRUNCATE TABLE, may increase the processing time in order to avoid excessive logging.

INSERT INTO Customers WITH (TABLOCKX)
SELECT CustomerName, Country
FROM OldCustomers;

If I use the script above (and without using a transaction), would it be possible for another user to submit a different query of the Customers table immediately after the DELETE statement and before the INSERT statement so that the query would return no rows?
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39966247
Or you should be able to set a tablock(x) while using the DELETE command too.
DELETE Customers  WITH (TABLOCKX);
-- If there are many records in the Customers table, then TRUNCATE TABLE, may increase the processing time in order to avoid excessive logging.

INSERT INTO Customers WITH (TABLOCKX)
SELECT CustomerName, Country
FROM OldCustomers;

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1400 total points
ID: 39966643
You need to start a transaction, then get an exclusive lock.  The lock will be held until the transaction commits, although, just to be sure, we can code the xlock in every statement:

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM Customers WITH (TABLOCKX)
INSERT INTO Customers  WITH (TABLOCKX) (CustomerName, Country)
SELECT CustomerName, Country
FROM OldCustomers
COMMIT TRANSACTION --will free the xlock
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION --will free the xlock
--...other error code...--
END CATCH
0
 

Author Closing Comment

by:eeyo
ID: 39966866
Thanks for the final detailed answer with the error catching.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question