Re: Pessimistic Locking

Tech-Archive recommends: Speed Up your PC by fixing your registry

Sahil Malik [MVP C#] wrote:

>>-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)

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.

>>-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.

And is there any possibility of not getting blocked, but checking if the
row is locked
and if it is locked cancelling the operation.

>>-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 ;-)

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

>
> - Sahil Malik [MVP]
> ADO.NET 2.0 book -
> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
> ----------------------------------------------------------------------------
>
> "Ferdinand Zaubzer" <ferdinand.zaubzer@xxxxxxxxxx> wrote in message
> news:%23C6EWH2%23FHA.3136@xxxxxxxxxxxxxxxxxxxxxxx
>
>>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
>
>
>
.

Relevant Pages

  • Re: Pessimistic Locking
    ... > A normal select statement executed in a transaction with serializable ... > isolation level was still possible, ... > was called which should lock the row. ... > in a transacion with serializable isolation level and another client has ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Seeking advice regarding asynchronous (responseless?) HTTP
    ... the server and the result is sent back to the client. ... posting the small amount of transaction data ... transaction to process on the server and for the response to return is the ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: transactions in webservices
    ... What you really want to think about is how badly you want the client to ... and handle the transaction inside of your service boundary. ... Due to the extreme nature of having clients control transaction ... any one have any idea how transactions could be enabled in webservices. ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: transactions in webservices
    ... and handle the transaction inside of your service boundary. ... Due to the extreme nature of having clients control transaction ... > client controlled, independent operation. ... > any one have any idea how transactions could be enabled in webservices. ...
    (microsoft.public.dotnet.framework.webservices)
  • Seeking advice regarding asynchronous (responseless?) HTTP
    ... I have a transaction processing pipeline which is built on HTTP. ... the server and the result is sent back to the client. ... transaction to process on the server and for the response to return is the ...
    (microsoft.public.dotnet.framework.aspnet)