Table Lock

[Joško Šugar]

Which lock type or isolation level should I use to be sure that no one
will read or write or do anything with the table I'm using?
Code block should look something like this:

lock table

read value from table
change value to new_value
update table set value = new_value

release lock
While I'm changing the value absolutly no one should be able to read
from the table.

[Razvan Socol]

I think you want something like this:

BEGIN TRAN
DECLARE @value int
SELECT @value=YourColumn FROM TheTable WITH (TABLOCKX)
SET @value=...
UPDATE TheTable SET YourColumn=@value
COMMIT

But you should consider performing the modification directly in the
UPDATE statement.

Razvan

[Joško Šugar]

Razvan Socol wrote:

I think you want something like this:

BEGIN TRAN
DECLARE @value int
SELECT @value=YourColumn FROM TheTable WITH (TABLOCKX)
SET @value=...
UPDATE TheTable SET YourColumn=@value
COMMIT

But you should consider performing the modification directly in the
UPDATE statement.

Razvan

Might be... but I'm not sure that no one can read data from table
between SELECT and UPDATE. If you set isolation to dirty read than
you're able to read uncommited data, right?
Does "WITH (TABLOCKX)" lock the table until the end of transaction?

[Razvan Socol]

> Does "WITH (TABLOCKX)" lock the table until the end of transaction?

Yes

I'm not sure that no one can read data from table
between SELECT and UPDATE. If you set isolation to dirty read than
you're able to read uncommited data, right?

You are right, if someone uses "SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED" or "WITH (NOLOCK)", then the uncommitted data may be read,
regardless of the TABLOCKX hint. You cannot guard against such a case
(except placing the entire database in SINGLE_USER mode).

Razvan

[Erland Sommarskog]

Jo¨ko ¦ugar (josko_bla@netgen_rem0ve_this_and_bla.hr) writes:

Which lock type or isolation level should I use to be sure that no one
will read or write or do anything with the table I'm using?
Code block should look something like this:

lock table

read value from table
change value to new_value
update table set value = new_value

release lock
While I'm changing the value absolutly no one should be able to read
from the table.

Razvan has already answered how you could do it. Now, next question is:
do you really want to do this? I have no idea what sort of application
you are working with, but generally you want as much concurrency in a
database system. Why lock the whole table, if you are changing only
one row? Not that I know if you are only changing one row, but I would
like you to think twice.

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

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

[Jo¨ko ¦ugar]

Erland Sommarskog wrote:

Razvan has already answered how you could do it. Now, next question is:
do you really want to do this? I have no idea what sort of application
you are working with, but generally you want as much concurrency in a
database system. Why lock the whole table, if you are changing only
one row? Not that I know if you are only changing one row, but I would
like you to think twice.

You are right. It's about only one row. Is it possible to do something like:

lock row in table

read value from table
change value to new_value
update table set value = new_value

release lock
Or... can I do it in one statement?
This is wrong, but I hope you understand what I'm trying to do:
update table set value = (select @n = value from table) + 1

I need to read the value, then save it in variable and then increment it
and update.

[AK]

why don't you begin transaction, update, and then select?

[Joško Šugar]

AK wrote:

why don't you begin transaction, update, and then select?

Ok, I will :)
Hope it does the trick. Thanks!