How to lock tables without putting the database in single user mode?

Question

Post reply

How to lock tables without putting the database in single user mode?

pedro.ribeiro

Hall of Fame

Points: 3201
More actions
April 27, 2009 at 6:43 am

#72017

How to lock tables without putting the database in single user mode?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

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]

pedro.ribeiro Hall of Fame Points: 3201 More actions · Answer 2

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.

Lowell SSC Guru Points: 323482 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 4

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com