How do you lock an entire table?

[Robin Tucker]

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

[Erland Sommarskog]

Robin Tucker (id************ *************@r eallyidont.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****@sommarsk og.se

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

[Robin Tucker]

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****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Robin Tucker (id************ *************@r eallyidont.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****@sommarsk og.se

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

[Erland Sommarskog]

Robin Tucker (id************ *************@r eallyidont.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_releaseapplo ck in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[Robin Tucker]

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****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Robin Tucker (id************ *************@r eallyidont.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_releaseapplo ck in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[Erland Sommarskog]

Robin Tucker (id************ *************@r eallyidont.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****@sommarsk og.se

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