Can I lock a table only for a specific user for some seconds

Question

Can I lock a table only for a specific user for some seconds

MotivateMan1394

Hall of Fame

Points: 3876
More actions
April 8, 2015 at 1:28 am

#302830

Hi I ned to do some operations on a table when any user dont work with it.
How do i do it?
Do i lock the table? and is it possible?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

MotivateMan1394 (4/8/2015)
Hi I ned to do some operations on a table when any user dont work with it.
How do i do it?
Do i lock the table? and is it possible?

Quick suggestion, DENY Object Permissions

😎

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 2

Yes It's a solution

but our web site is a busy web site . is this possible for it? If user select to a table that dosnt have permission , create error and this is not good solution.

in the other hand.

we have only one user on site

and we like to do this with the same user.

thank you

Phil Parkin SSC Guru Points: 246712 More actions · Answer 3

MotivateMan1394 (4/8/2015)
Hi I ned to do some operations on a table when any user dont work with it.
How do i do it?
Do i lock the table? and is it possible?

What do you mean by 'operations'?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 4

some updates on some fields thats important for us

we need to re arange these fields once or twice a day . (when any one dont any change on the table and if cant select is better)

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 5

(I'm sorry to write this problem in 2 groups. I'll keep track Solutions from this page)

Can i use transaction for this problem?

I'm waiting

:doze:

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

While executing your transaction you can use hint "TABLOCKX" refer following MS link for further details.

https://technet.microsoft.com/en-us/library/aa213026(v=sql.80).aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398697 More actions · Answer 7

The TABLOCK is probably the way to go, but understand, you're likely to be blocked by processes that are reading from the table. You can't take that kind of lock until they're clear. And, while you're "rearranging columns," what ever that means, you will be blocking access to the table which could lead to timeouts.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Phil Parkin SSC Guru Points: 246712 More actions · Answer 8

MotivateMan1394 (4/8/2015)
some updates on some fields thats important for us
we need to re arange these fields once or twice a day . (when any one dont any change on the table and if cant select is better)

Perhaps you can make a physical copy of the table and do your 'operations' on the copy, before switching synonyms or renaming to make the copy table the live table.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.