January 17, 2005 at 2:08 pm
I have a stored procedure that needs to hold an exclusive lock on a table. It needs to prevent any sort of read from any other source for the duration of the stored procedure. The procedure itself is made up of two select statements, an if statement, and an update. Currently, I am trying to roll the entire stored procedure into one transaction, and using the XLOCK table hint on the first select query. My question is whether or not putting the table hint on the first select query holds the exclusive lock for the duration of the stored procedure. I have read that it should, but Microsoft's documentation on table locks is vague at best, so I just wanted to check. If you need a reference, my stored procedure is below.
CREATE PROCEDURE sp_Check_Import_Table @Process_Name nvarchar(50) AS
BEGIN TRANSACTION
DECLARE @CheckSelf INT
DECLARE @CheckReport INT
SET @CheckSelf =
(
SELECT Running_IN
FROM Import_Status_T WITH (XLOCK)
WHERE Step_Name_NV = @Process_Name
)
SET @CheckReport =
(
SELECT Running_IN
FROM Import_Status_T
WHERE Step_Name_NV = 'OLAP_Update'
)
IF @CheckSelf = 0 AND @CheckReport = 0
BEGIN
UPDATE Import_Status_T
SET Running_IN = 1
WHERE Step_Name_NV = @process_Name
END
COMMIT TRANSACTION
Thanks,
Chris
January 17, 2005 at 2:42 pm
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
set the isolation leve either REPEATABLE READ or SERIALIZABLE. So that you don't worry about the locking hint. The basic thing is you don't want to do a dirty read and anyother process shouldn't touch the table until your process is done.
Thanks,
Ganesh
January 17, 2005 at 3:21 pm
Do I put the transaction isolation level before the start of the transaction, or right after?
January 17, 2005 at 8:46 pm
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
--
--
...
COMMIT TRANSACTION
January 19, 2005 at 3:32 am
Setting the ISOLATION LEVEL is the way to go but just to answer your question the locking hint would work within the transaction as you required.
You can test this by opening 2 windows in Query analyser.
In the first run a statement such as:
BEGIN transaction
select * from myTable with (TABLOCKX)
Then in the second window:
select * from myTable
You will not get any results in the second window unti you do a COMMIT or ROLLBACK in the first.
You could also try :
BEGIN transaction
select * from myTable with (TABLOCKX)
declare @delaycount int
set @delaycount = 1
WHILE @delaycount < 10
BEGIN
set @delaycount = @delaycount + 1
exec sp_lock
END
COMMIT transaction
Nigel Moore
======================
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply