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