Once more - Pessimistic Locking ADO.NET 2.0 SQL Server 2005

[aaapaul]

Hello !

I read any articles about locking so far, but I am not able to make a
solution for my project.
(I really want pessimistic locking - no such optimistic/pessimistic
discussion please)

This is my idea: (I made some experiencies with a new field "locked"
in the orders-table - read committed - this worked, but now I want an
other solution):
I created a separate locking table.
If anyone opens a datarow e.g. in the table orders in editmode, a
locking datarow is inserted in the locking table, when storing or
canceling, the row is deleted.

This is my code:

Protected m_objSQLConnection As SqlConnection
Protected m_objSQLCommand As SqlCommand
Protected m_objSQLTransaction As SqlTransaction

Try
m_objSQLConnection.Open()
m_objSQLConnection.BeginTransaction 'Isolationlevel
necessary ????
m_objSQLCommand.Transaction = Me.m_objSQLTransaction
Dim strSQL As String
strSQL = "SELECT * FROM tblSperrungen "
'"WITH (HOLDLOCK, ROWLOCK etc necessary ???) "
strSQL &= " WHERE (tablename = @tablename) AND (lineID =
@lineID)"
m_objSQLCommand.CommandText = strSQL
m_objSQLCommand.Parameters.Clear()
m_objSQLCommand.Parameters.AddWithValue("@tablename", ...)
m_objSQLCommand.Parameters.AddWithValue("@lineID", ...)

Dim objRd As SqlDataReader
objRd = m_objSQLCommand.ExecuteReader
If objRd.Read Then
'Sorry row is locked
Else
blnErg = True
End If
objRd.Close()


System.Threading.Thread.Sleep(intPause) ' only for testing
others try to lock and have success - why ??

If blnErg Then
strSQL = "INSERT INTO
tblSperrungen(tablename,lineID,GesperrtVon,GesperrtAm) VALUES
(@tablename,@lineID,@GesperrtVon,@GesperrtAm)"
m_objSQLCommand.CommandText = strSQL
m_objSQLCommand.Parameters.Clear()

m_objSQLCommand.Parameters.AddWithValue("@tablename", ...)

m_objSQLCommand.Parameters.AddWithValue("@lineID", ...)

m_objSQLCommand.Parameters.AddWithValue("@GesperrtVon", ...)

m_objSQLCommand.Parameters.AddWithValue("@GesperrtAm", ...)
m_objSQLCommand.ExecuteNonQuery()
'Locking OK
End If
TransCommit()
CnClose()
Catch objE As Exception
TransRollback()
cWindows.FehlerWin("Fehler bei Sperren-Funktion " &
objE.Message, True)
End Try

My problem now is, that I have to guarantee that between the 2 SQL-
Statements (SELECT and UPDATE) no other user can select the row. How
can I prevent this row from reading.

I played with some isolation levels and sql hints, but with no
success.
(stored procedures are not allowed in my project)

Many Thanks
aaapaul

 

[Guest]

aaapaul,

Have you tried:

WITH (UPDLOCK, NOWAIT)

Kerry Moorman

 

[Cor Ligthert [MVP]]

AAAPaul,

When you real want pessimistic locking and know that all other people like
more optimistic locking because it has more benefits, why do you then ask it
to a newsgroup.

Seems for me the same asking the newsgroup how to put the steering wheel in
the backsit, because you want it. If you want it, no problem, but don't
disturp us that we have to read your message (that is as you sent it to a
newsgroups, and than it is something crazy)

Thanks in advance

Cor

 

[aaapaul]

Thanks Kerry !

Now it works WITH (ROWLOCK, XLOCK) is the best option for me.

To Cor:
We have 5 persons creating and updating orders in a system.
When 2 persons want to edit the same order, I think its good when the
second person sees, that the order is edited and it can wait.
Sample: Original oder 5000 pieces
With no locking we have the problem of a lost update.
How can this solved with optimistic locking?

Thanks in advance
Paul

 

[Cor Ligthert [MVP]]

Why pessimistic locking is left as standard while it has been as long as
there where databases.

Do you think that you are unique with the problem you describe, however
statics has showed that we are mostly shooting on a not existing cow. In a
good design is the change on a concurrency error very low and is the
optimistic method the most efficient, even in databases larger than 100
users.

Cor

 

[Paul Werkowitz]

Am Sat, 19 May 2007 02:31:12 +0200 schrieb Cor Ligthert [MVP]:

Why pessimistic locking is left as standard while it has been as long as
there where databases.

Do you think that you are unique with the problem you describe, however
statics has showed that we are mostly shooting on a not existing cow. In a
good design is the change on a concurrency error very low and is the
optimistic method the most efficient, even in databases larger than 100
users.

Cor

Well, my experience is different. The optimistic locking examples are
schoolbook scenarios with no relation to real world problems. E.G. they
assume that there is only one table. If you have heavy dependencies in your
database, it simply is difficult.

Paule

 

[Paul Werkowitz]

Am 18 May 2007 12:55:58 -0700 schrieb aaapaul:

Thanks Kerry !

Now it works WITH (ROWLOCK, XLOCK) is the best option for me.

But, what happens if one of the machines that holds a lock, chokes?

And, what happens, when someone leaves the programm running with an open
record and goes home? Goes to hollidays?

No good! You need mechanisms to deal with such problems. How do you do it
in your program?

Paule

 

[Cor Ligthert [MVP]]

Paul,

Have you ever tried the transaction with the connection, you can have
endless tables using that.

Cor

 

[Paul Werkowitz]

Am Sat, 19 May 2007 09:53:43 +0200 schrieb Cor Ligthert [MVP]:

Paul,

Have you ever tried the transaction with the connection, you can have
endless tables using that.

Sure (but atm only with Access-Database)
What happens if you open a transaction, make some changes to some
tables.... then another user tries to read and then update one of these
tables.... while the transaction is still open.

Second user sees (and updates) either prestate or poststate, which is both
wrong. Do I overlook something? We decided to use manual locking mechanism
many years ago, and I can't remember the exact reasons. We are using
transactions, of course, to guard complex operations. But they cannot
alleviate multiuser access problems, AFAIK.

Greetz
Paule

 

[Cor Ligthert [MVP]]

Paul,

Try it once with not letting the dataadapters stop on all errors.
Check the HasErrors and than the error in the rowstate.

Be as well aware that optimistic concurrency checking is based on the fact
that the thought in past that every write would throw a concurrency error.
Some changes are normal that the may overwrite each other. (By instance NAW,
by the right authorised persons of course)

Cor

 

[Paul Werkowitz]

Am Sat, 19 May 2007 20:45:00 +0200 schrieb Cor Ligthert [MVP]:

Paul,

Try it once with not letting the dataadapters stop on all errors.
Check the HasErrors and than the error in the rowstate.

Hello Cor,
I cannot see a relation to my scenario. Here, again:

*What happens if you open a transaction, make some changes to some
*tables.... then another user tries to read and then update one of these
*tables.... while the transaction is still open.
*Second user sees (and updates) either prestate or poststate, which is both
*wrong.

Please..... what happens then? And is this something we want to happen? I
don't think so.

Pessimistic locking is a way to solve the problem. Checking HasErrors or
the rowstate only indicates a problem, it does not lead to a solution.

Paule

 

[Cor Ligthert [MVP]]

Paul,

The deadlock with Pessimistic locking is very much knowed. If it is for a 5
person database than the change that there will be an concurrency error is
low with either which.

*What happens if you open a transaction, make some changes to some
*tables.... then another user tries to read and then update one of these
*tables.... while the transaction is still open.
*Second user sees (and updates) either prestate or poststate, which is
both
*wrong.

You can commit or rollback your actions

Pessimistic locking is a way to solve the problem. Checking HasErrors or
the rowstate only indicates a problem, it does not lead to a solution.

It is possible that, that is happening with you, I agree with you that
optimistic concurrency is build for computers with thousand of users while
some of them can be offline.

Cor