Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do create a SQL Server Table Lock?

Posted on 2014-03-30
8
Medium Priority
?
650 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 70

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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