Read Lock a Table in SQL Server?
-
I want to lock a table in SQL Server, within a transaction. Unless i release lock, no other transaction should not be able to even read (execute select statement) on that table. Any Ideas? kumar
Hi Kumar You should be able to do the following within a transaction:
SELECT @dummy = COUNT(*) FROM MyTable WITH (TABLOCKX, HOLDLOCK)The "TABLOCK" and "HOLDLOCK" keywords are locking hints. You should be able to look these up in your Books-Online. What are you trying to do while the table is locked? I would advise you to keep your transaction reeeeaaaally short. Regards Andy -
Hi Kumar You should be able to do the following within a transaction:
SELECT @dummy = COUNT(*) FROM MyTable WITH (TABLOCKX, HOLDLOCK)The "TABLOCK" and "HOLDLOCK" keywords are locking hints. You should be able to look these up in your Books-Online. What are you trying to do while the table is locked? I would advise you to keep your transaction reeeeaaaally short. Regards Andythanks Andy, Actually this is just for testing purpose, i want other transaction to wait till the current transaction releases the lock. Is there any mechanism, where in we can hold the lock on table for few seconds so that other transaction should be able to atleast execute Select command?
-
thanks Andy, Actually this is just for testing purpose, i want other transaction to wait till the current transaction releases the lock. Is there any mechanism, where in we can hold the lock on table for few seconds so that other transaction should be able to atleast execute Select command?
-
sorry, i am correcting my statement above, "so that other transaction should not be able to atleast execute Select statement"
I normally test these things by opening two sessions in Query Analyzer. However if you want to test this using a stored procedure then use the "WaitFor" T-SQL command to wait for a period of time.
-
I want to lock a table in SQL Server, within a transaction. Unless i release lock, no other transaction should not be able to even read (execute select statement) on that table. Any Ideas? kumar
-
Hi Kumar You should be able to do the following within a transaction:
SELECT @dummy = COUNT(*) FROM MyTable WITH (TABLOCKX, HOLDLOCK)The "TABLOCK" and "HOLDLOCK" keywords are locking hints. You should be able to look these up in your Books-Online. What are you trying to do while the table is locked? I would advise you to keep your transaction reeeeaaaally short. Regards Andyhi, i want to use TABLOCKX lock on my table. i have used same query given in above post. but problem is lock does not get released after completion of transaction holding lock on table. hence another transaction keeps waiting. following are my 2 query i tried in query analyzer of sql server. Please help me... Query 1: BEGIN TRAN A SELECT * FROM alerts with (TABLOCKX, HOLDLOCK) declare @cnt bigint set @cnt = 1000000 print 'start time = '+cast(getdate() as varchar) while @cnt > 0 begin print 'Cnt = ' + cast(@cnt as varchar) set @cnt = @cnt - 1 end print 'end time = '+cast(getdate() as varchar) COMMIT TRAN A ----------------------------------------------------------------------------------- Query 2: BEGIN TRAN B print 'start time = '+cast(getdate() as varchar) SELECT * FROM alerts --with (TABLOCKX) print 'end time = '+cast(getdate() as varchar) COMMIT TRAN B Please let me know if i'm doing anything wrong. Thank in advance... Rajesh.