Ado.net and Locking

[Ferdinand Zaubzer]

I would like to use pissimistic locking for changing data on a database.
(Yes, I know what I'm doing, it will scale poorly!)

I tried it using a transaction with Isolationlevel "serializable".
I set the select, insert, update and delete command of the DataAdapter
to use this transaction.

If I open a Row for editing, any other client trying to change the same
row is blocked. But it is still possible to read the row which is locked
for editing.

So I have the following questions:

-Is there any possibility to prevent other clients to read the locked
data which is being updated?

if not:

-Is it possible to tell another client that this row is locked and
therfore cannot be updated instead of simply blocking the update operation?

-How can I know that the row cannot be updated when I populate the form
with the data read from the locked row.

Cheers
Ferdinand

[Sahil Malik [MVP C#]]

>I would like to use **pissimistic** locking for changing data on a

database.
Water and electronics don't mix ;-)
-Is there any possibility to prevent other clients to read the locked data
which is being updated?
Yes, right before your insert/update/delete .. run a Select (HOLDLOCK)
-Is it possible to tell another client that this row is locked and
therfore cannot be updated instead of simply blocking the update
operation?
Yes, run the other client in serializable isolation level - he will deadlock
and be made the deadlock victim.
-How can I know that the row cannot be updated when I populate the form
with the data read from the locked row.
Whoaa !! I don't understand this one .. "with the data read from the locked
row" .. if the row is locked, how did you read the data ;-)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
----------------------------------------------------------------------------

"Ferdinand Zaubzer" <fe***************@schendl.at> wrote in message
news:O8***************@tk2msftngp13.phx.gbl...I would like to use pissimistic locking for changing data on a database.
(Yes, I know what I'm doing, it will scale poorly!)

I tried it using a transaction with Isolationlevel "serializable".
I set the select, insert, update and delete command of the DataAdapter to
use this transaction.

If I open a Row for editing, any other client trying to change the same
row is blocked. But it is still possible to read the row which is locked
for editing.

So I have the following questions:

-Is there any possibility to prevent other clients to read the locked data
which is being updated?

if not:

-Is it possible to tell another client that this row is locked and
therfore cannot be updated instead of simply blocking the update
operation?

-How can I know that the row cannot be updated when I populate the form
with the data read from the locked row.

Cheers
Ferdinand