Re: Pessimistic Locking

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

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.

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


SM


"Ferdinand Zaubzer" <ferdinand.zaubzer@xxxxxxxxxx> wrote in message
news:uFibNx9%23FHA.1124@xxxxxxxxxxxxxxxxxxxxxxx
> 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: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I checked my existing connections events in the profiler, ... I don't see anything about isolation level. ... only using transaction in stored procedures. ... I though it was always Exclusive lock. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction isolation levels
    ... SELECT 'X' INTO #TEMP ... locks the row in table TICKETS until the entire transaction has ... The lock taken out on TICKETS will be a shared lock ... that under the default isolation level will be released. ...
    (comp.databases.ms-sqlserver)
  • Re: Transactions in SQL Server
    ... >>4) Close the transaction ... >>because of the SET TRANSACTION ISOLATION LEVEL default behavior? ... > Hi Todd, ... > exclusive lock to do the update. ...
    (microsoft.public.sqlserver.server)
  • Re: controlling lock order in transactions
    ... isolation level appeared in the options. ... If I set isolation level in my stored procedure will it affect client ... If I require Exclusive lock to perform my INSERTS how does Isolation ...
    (microsoft.public.sqlserver.programming)