469,592 Members | 2,051 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

How do you lock an entire table?


Hi,
I need to lock a table so that Inserts are prevented as well as deleted and
updates. At present I'm thinking this might do it:

SELECT * FROM myTable WITH(UPLOCK)

but then again I'm not sure whether this will cover the insert case.

Thanks,

Robin
Jul 23 '05 #1
5 2806
Robin Tucker (id*************************@reallyidont.com) writes:
I need to lock a table so that Inserts are prevented as well as deleted
and updates. At present I'm thinking this might do it:

SELECT * FROM myTable WITH(UPLOCK)

but then again I'm not sure whether this will cover the insert case.


To cover inserts, you need the serializable isolation level, and as far
as I know, that is what UPDLOCK buys you. Testing is always a good idea,
though.

It would be interesting to know why you think you need to lock the entire
table. It may be the right thing to do, but it sounds a little funny.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

Yes, it is funny indeed. The basic situation is that I was spec'd to write
a "single user/client" system, installing MSDE on the client PC along with
the client software. All of a sudden "multi-user" is a basic product
requirement, although I didn't write a core suitable for concurrency (this
is kindof a real-time system, so concurrency really needed to be designed in
from the start. So, as a stop-gap measure, I am wanting to lock a table,
write a "I'm using this now" record, unlocking it when the user times out or
logs off. Okay, this is dumbass I know, but it will take 4 months to
rewrite the core to use the correct design pattern for this new
requirement - this is a quick "hack" to get things up and running pending
the new core ;)
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Robin Tucker (id*************************@reallyidont.com) writes:
I need to lock a table so that Inserts are prevented as well as deleted
and updates. At present I'm thinking this might do it:

SELECT * FROM myTable WITH(UPLOCK)

but then again I'm not sure whether this will cover the insert case.


To cover inserts, you need the serializable isolation level, and as far
as I know, that is what UPDLOCK buys you. Testing is always a good idea,
though.

It would be interesting to know why you think you need to lock the entire
table. It may be the right thing to do, but it sounds a little funny.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #3
Robin Tucker (id*************************@reallyidont.com) writes:
Yes, it is funny indeed. The basic situation is that I was spec'd to
write a "single user/client" system, installing MSDE on the client PC
along with the client software. All of a sudden "multi-user" is a basic
product requirement, although I didn't write a core suitable for
concurrency (this is kindof a real-time system, so concurrency really
needed to be designed in from the start. So, as a stop-gap measure, I
am wanting to lock a table, write a "I'm using this now" record,
unlocking it when the user times out or logs off. Okay, this is dumbass
I know, but it will take 4 months to rewrite the core to use the correct
design pattern for this new requirement - this is a quick "hack" to get
things up and running pending the new core ;)


If the table is only there to act as a locking mechanism, I strongly
recomend you to look at application locks instead. Look up
sp_getapplock and sp_releaseapplock in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
You know what? We solved this problem without having to write any SQL code
at all. We are now using USB dongle with a one license per server hard
limit on it. Just a bit of code in the client required. ;)

Now....... I'm working on that multi-user core.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Robin Tucker (id*************************@reallyidont.com) writes:
Yes, it is funny indeed. The basic situation is that I was spec'd to
write a "single user/client" system, installing MSDE on the client PC
along with the client software. All of a sudden "multi-user" is a basic
product requirement, although I didn't write a core suitable for
concurrency (this is kindof a real-time system, so concurrency really
needed to be designed in from the start. So, as a stop-gap measure, I
am wanting to lock a table, write a "I'm using this now" record,
unlocking it when the user times out or logs off. Okay, this is dumbass
I know, but it will take 4 months to rewrite the core to use the correct
design pattern for this new requirement - this is a quick "hack" to get
things up and running pending the new core ;)


If the table is only there to act as a locking mechanism, I strongly
recomend you to look at application locks instead. Look up
sp_getapplock and sp_releaseapplock in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #5
Robin Tucker (id*************************@reallyidont.com) writes:
You know what? We solved this problem without having to write any SQL
code at all. We are now using USB dongle with a one license per server
hard limit on it. Just a bit of code in the client required. ;)


Ah, creative solutions! I like that!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Richard Holliingsworth | last post: by
3 posts views Thread by rkusenet | last post: by
2 posts views Thread by xixi | last post: by
5 posts views Thread by xixi | last post: by
reply views Thread by Bruce Pullen | last post: by
9 posts views Thread by kavallin | last post: by
6 posts views Thread by michael.spoden | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.