• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 816
  • Last Modified:

How do create a SQL Server Table Lock?

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
eeyo
Asked:
eeyo
4 Solutions
 
Anthony PerkinsCommented:
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
 
John_VidmarCommented:
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
 
edtechdbaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
eeyoAuthor Commented:
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
 
eeyoAuthor Commented:
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
 
edtechdbaCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
eeyoAuthor Commented:
Thanks for the final detailed answer with the error catching.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now