How to lock the table?
-
Hi,
How to lock the table in SQL Server?
Within my stored procedure, I want to lock and release.
In between, I want to do some inserts/updates to the table.
In the mean time, I don't want to do any insert/updates from the concurrent sessions.
Thanks,
Netaji
-
Begin tran
select col1 from mytable (tablock)
-- et voila, it's locked
this thread can do anything with the table, others cannot !
commit tran -- / rollback tran
-- et voila, it's UNlocked
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
My Question would be why would you want to force a table lock?
Sql server handles row/page/table locks as required by the insert/update/delete statement.
If you are inserting/modifying a row sql server will automatically handle locking for that row so 2 threads cannot modify the same data at the same time.
unless you have your transaction isolation level set below sql server default you should not have problems with this.
If you table lock and for some reason your transaction takes a long time to commit, all other users trying to access that table get to wait.
If your transaction hangs or fails and rolls back it will hold your transaction open far longer than is necessary.
I have yet to see an instance where I needed to lock an entire table.
You can use other locking hints to help you beside an entire table lock.
for instance an Update Lock But manually using lock hints interferes with the internal locking mechanism microsoft has spent tons of time developing and testing.
my 2cents
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply