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
Solved

How do create a SQL Server Table Lock?

Posted on 2014-03-30
8
565 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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