Time-out when record is locked?

[epasman]

I have a VB .NET-program where I select a record, change the value and save the new value to the database using a ODBC-connection. The record is locked during this operation.

When I start the same application on another client, the Select-query hangs, because the record is locked. How can I set a time-out to fire an exception on this second client, so that I can show a message that the record is in use?

This is my code:

Public Class Form1

    Public gobjODBCConn As Odbc.OdbcConnection
    Public gobjODBCCmd As Odbc.OdbcCommand
    Public gobjODBCDataAdapter As OleDb.OleDbDataAdapter

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        gobjODBCConn = New Odbc.OdbcConnection
        gobjODBCConn.ConnectionString = "uid=dba;pwd=sql;DSN=Test"
        gobjODBCConn.Open()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        TextBox1.Text = LoadNumber()
    End Sub

    Private Function LoadNumber() As String
        Dim SqlLot As String
        Dim strArtName As String

        Dim rdr As Odbc.OdbcDataReader

        SqlLot = "Select " _
                & " * from Article with (XLOCK) " _
                & " where ArticleId = 24"

        gobjODBCCmd = gobjODBCConn.CreateCommand
        gobjODBCCmd.CommandText = SqlLot

        On Error GoTo QueryErr
        rdr = gobjODBCCmd.ExecuteReader
        rdr.Read()
        strArtName = rdr("ArtDescr")

        LoadNumber = strArtName
        Exit Function
QueryErr:
        MsgBox("Error...", MsgBoxStyle.Critical)
        Exit Function

    End Function

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ' Dim er As rdoError
        Dim SqlLot As String
        Dim RowCount As Long
        Dim rdoLotList As Object

        On Error GoTo UpdateError

        SqlLot = "Update Article" _
                 & " set ArtDescr = '" & TextBox2.Text & "'" _
                 & " where ArticleId = 24"

        gobjODBCCmd = gobjODBCConn.CreateCommand
        gobjODBCCmd.CommandText = SqlLot

        gobjODBCCmd.ExecuteNonQuery()

        Exit Sub

UpdateFailed:

        MsgBox("Update failed " + Err.Description)
        Exit Sub

UpdateError:

        Resume UpdateFailed
    End Sub

End Class

 

[cjard]

please read this:
Visual Basic .NET Forums - BB Code List

about code tags

 

[epasman]

thanx I added the code-tags

 

[Stonkie]

You open the connection at form.load and never close it? The connection simply times out on the server. You should keep connections closed until you need them, then open them on a per action (one or more sequential queries) basis... Now you end up with a connection just waiting for the user.

By the way, I think "On error goto" isn't a viable strategy for error handling in .NET : Life Without On Error Goto Statements

I had never seen this so I thought it was a backward compatibility feature I never needed, but they say it's not even in the language. Are you sure you're using .NET and not just plain old VB6 (which I never touched actually)?

 

[epasman]

What I want to do in the program is lock the record that is loaded, to prevent it from being updated by the other client that reads the same record.

What is the best way to do this?

 

[MattP]

There are several ways to handle concurrency in your applicaton. I can give you a high level description of several options so you can read up on which one sounds the best fit.

Pessimistic:
Put a bit column in your table and set it to 1 when you're modifying a record and set it back to 0 when you're done. Then either make it so users can't SELECT a record that is locked (WHERE Lock <> 1) or can't UPDATE a locked record. Hope somebody doesn't open a record and then leave for lunch or have the program crash.

Optimistic:
(Timestamp)
Use a timestamp column to mark when the last time the record was modified. If when you're doing your UPDATE the timestamp is the same as when you SELECTED the record then nobody has modified it.

(All values)
When updating check if the values in the database match the original values from your SELECT statement. If they match no changes have been made to the database.

(Changed Values)
Only check the values that you're looking to change in the record. If they match then make the update. Usefull if 2 people can be making changes to a record that are affecting separate fields. You'll need to take care of the logic on which fields need to be checked.

 

[Stonkie]

MattP's post is sums it up, great answer!

Actually, the only way to make pessimistic work (if a program/system/network crash corrupts the database, the method doesn't work) is to use time stamp and say "if this was locked more than X minutes ago, I will use the row anyway because it probably was a crash somehow". Then force your client to refresh their locks every X minutes to make sure they keep the time stamp smaller than x minutes when they are using it. This is not a standard method though so you must make sure no client accesses these data using another concurrency mechanism.

Now, I say it would work... Not that I would do it! The connection's timeout mechanism is exactly the same as that, but without you having control over it...

By the way, by default, ADO.NET checks concurrency using the "All values" method. It tries the update or delete query filtering to the row with the exact same data as the one it loaded before you made modifications. If the update or delete affect no rows, the table adapter's update method figures the row was modified since you loaded it and it throws an Exception.

 

[cjard]

The best way to solve concurrency issues, is to use Oracle