"aaapaul" wrote:

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