Solved

How do create a SQL Server Table Lock?

Posted on 2014-03-30
8
537 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
8 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 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 50 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 50 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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:
ScottPletcher earned 350 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now