April 27, 2009 at 6:43 am
How to lock tables without putting the database in single user mode?
April 27, 2009 at 8:46 am
What are you actually trying to achieve?
Do you want to make the table read-only or do you want to restrict access to a table so you can run a ALTER TABLE command?
Of course if you just want to lock a table, there's the option of using a locking hint (TABLOCKX) to lock a table, but somehow I have the feeling that's not exactly what you need.
[font="Verdana"]Markus Bohse[/font]
April 27, 2009 at 9:07 am
The case is this:
suppose i have a procedure, that executes the following:
create proc test ()
as
begin
update table1 ................
Insert into table 2...............
Update table 3....................
insert into table2 ...............
insert into table 3..............
end
I need to lock the tables that are updated, and insert trought the stored procedure, befour the stored proc is executed.
Is there any t-sql to do this?
I would like that nodoby can use this tables to update or insert data while the stored proc is running.
April 27, 2009 at 9:24 am
I answered in the other thread that was alonst the same subject:
a specific example for you:
create proc test ()
as
begin
BEGIN TRAN
update table1 WITH(TABLOCKX)................
Insert into table 2 WITH(TABLOCKX)................ ...............
Update table 3 WITH(TABLOCKX)................ ....................
insert into table2 WITH(TABLOCKX)................ ...............
insert into table 3 WITH(TABLOCKX)................ ..............
COMMIT TRAN
end
not sure why you'd want to lock a table up, but I would do it with a transaction.
one of the HINTs you can use is to exclusively lock a table:
From Query Window #1:
begin transaction
--noone can even select from this table until you commit or rollback your transaction, unless they use the hint NOLOCK
select * from YOURTABLE with (TABLOCKX)
--do more work....
From a Second Query Window:
--waits for the transaction to complete:
select * from YOURTABLE
From a Third Query Window:
--able to read the info fromt he table due to the hint
select * from YOURTABLE with (NOLOCK)
don't forget to rollback or commit your transaction in window one!!!!
Lowell
April 27, 2009 at 12:43 pm
You also don't necessarily need to worry about locking. SQL Server handles most of that.
It would help if you specifically noted what you are trying to achieve. You may be solving something you don't need to worry about, or you may need other advice.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply