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]

Re: Table Lock

I think you want something like this:

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

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

Razvan

[Joško Šugar]

Re: Table Lock

Razvan Socol wrote:[color=blue]
> I think you want something like this:
>
> BEGIN TRAN
> DECLARE @value int
> SELECT @value=YourColu mn FROM TheTable WITH (TABLOCKX)
> SET @value=...
> UPDATE TheTable SET YourColumn=@val ue
> COMMIT
>
> But you should consider performing the modification directly in the
> UPDATE statement.
>
> Razvan
>[/color]

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]

Re: Table Lock

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

Yes
[color=blue]
> 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?[/color]

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]

Re: Table Lock

Jo¨ko ¦ugar (josko_bla@netg en_rem0ve_this_ and_bla.hr) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jo¨ko ¦ugar]

Re: Table Lock

Erland Sommarskog wrote:
[color=blue]
>
> 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.
>
>[/color]

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]

Re: Table Lock

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

[Joško Šugar]

Re: Table Lock

AK wrote:[color=blue]
> why don't you begin transaction, update, and then select?
>[/color]

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