Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Question Unanswered: Unable to Lock Record

    I created the following code sample to test Record/Row Locking in VB6 via ADO and SQL Server 2000. I ran two instances of the code, the 2nd instance indicate that the record is lock. Both instances are functioning correcting when referencing the PUBS database/tables (microsoft sample database). But when I change the reference to a database/table that I created, the 2nd instance does not indicate that the record is locked. Since my code sample/test works with the PUBS database I can only assume that I missing something on the SQL Server side in regards to the database/table that I created.

    Any ideas what I am doing wrong or missing?
    Or any suggestions of a better way to lock a record and indicate/tell if a record if locked?

    General Declarations:
    Dim rs1 As New ADODB.Recordset

    Private Sub Form_Load()
    Dim ConnString As String
    Dim cn As New ADODB.Connection

    On Error GoTo ErrLock

    'ConnString = "driver={SQL Server};server=;database=TestDB;uid=sa"
    ConnString = "driver={SQL Server};server=;database=PUBS;uid=sa"

    cn.CursorLocation = adUseServer
    cn.ConnectionString = ConnString
    cn.Mode = adModeShareExclusive

    Dim sql As String
    'TestDB SQL
    'sql = "SELECT * " & _
    ' "FROM ClmRecs AS clm " & _
    ' "WHERE (clm.claim_id = 630);"

    sql = "select title_id, title, price, ytd_sales from titles where title_id = 'BU1032'"

    Set rs1.ActiveConnection = cn
    rs1.Open sql, cn, adOpenKeyset, adLockPessimistic, adCmdText
    ' Edit to Lock Record
    rs1(0).Value = rs1(0).Value

    Exit Sub

    If cn.Errors.Item(0).NativeError = 1222 Then ' Lock Timeout
    MsgBox "Row is locked by another user"
    End If
    Resume Next
    End Sub

  2. #2
    Join Date
    Oct 2003
    In the world of SQL servers, the concept of "locking a record to edit it" really does not exist. Using so-called "updatable query views" is really not a good idea. In many installations it does not work or is not allowed.
    SQL servers don't enjoy having locks around and work hard to avoid them. Instead of using "pessimistic" locking (i.e. "I assume someone will edit this record while I'm editing it, so I'll lock it"), they use "optimistic" ("I bet that a user won't edit exactly the same record, so I'll check to see if someone did so only at the instant that I go to save these changes"). You can see that optimistic locking avoids the overhead of keeping and checking for lots of locks. Locks only exist for microseconds. But you have to anticipate the possibility that, indeed, your optimism might be misplaced: in that case, the attempt to save-changes will fail.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts