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: 182920 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: 246992 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'?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 27164 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: 398913 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: 246992 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel