Pessimistic Locking

  • Thread starter Thread starter Ferdinand Zaubzer
  • Start date Start date
F

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
 
-Is there any possibility to prevent other clients to read the locked data
which is being updated?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
 
Sahil said:
Yes, right before your insert/update/delete .. run a Select (HOLDLOCK)
Click to expand...

A normal select statement executed in a transaction with serializable
isolation level was still possible, after the DataAdapter.fill() method
was called which should lock the row(s). So it seemed to me that the
locked data is still readable for other clients.
Yes, run the other client in serializable isolation level - he will deadlock
and be made the deadlock victim.
Click to expand...

And is there any possibility of not getting blocked, but checking if the
row is locked
and if it is locked cancelling the operation.
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 ;-)
Click to expand...

The funny thing is, that I could read the data, even though data is read
in a transacion with serializable isolation level and another client has
a lock on the row. With this data I could populate the form for editing
the data.

-- Ferdinand
 
A normal select statement executed in a transaction with serializable
isolation level was still possible, after the DataAdapter.fill() method
was called which should lock the row(s). So it seemed to me that the
locked data is still readable for other clients.
Click to expand...

Not at DataAdapter.Fill .. run it right before Update, and keep the
connection open.
And is there any possibility of not getting blocked, but checking if the
row is locked
and if it is locked cancelling the operation.
Click to expand...

The only way is to let SQL Server handle deadlock resolution by choosing a
deadlock victim. You will get an exception I think.


SM
 
Back
Top