Database locking

I don't think 10 points is enough for this question.  Explaining database locking and how to work with it is not worth the time.

I'll agree... 10 points questions are questions that can be answered in 2-3 words max.  Mcblake, point values at Experts-Exchange are based on a question difficulty.  Common point values for questions for various difficulties are as follows:

  25: Very Easy
  50: Easy
100: Medium
150: and up Hard

Try setting the Exclusive property(or smth like it, I don't remember the name exactly) to true. If you get an error then it's locked by some other application.

Matvey

Sorry, I've misunderstood your question.
I haven't worked dmuch with multiuser databases...

Matvey

I wanna know about 'record' locking not database locking.
Thanks

What is your environment? VB4, VB5? Access/Jet, xBase, some SQL server?

The only sure way to check in all environments is to try to wirte the row.  If using Jett to remote SQL databases, all locking is done with optimisitc concurrency, rows are only checked for conflict at update time.

The current Microsoft Databases (JET/Access and SQL Server) only perform page locking.  Any row selected for update using and updateable cursor will lock the entire page on which that row is contained.  Mltiple row recordsets lock multiple pages.  Order by and large resultsets can be escalated to entire table locks without your knowing it by the server.  IMHO, MS has provided a fairly weak solution to a strong problem, but promises to implement row level locking "real soon now"

My database information is Foxpro as my database. I use Visual basic 4.0 (16-bit version). I have plan to install my application in each workstation and share only database on the server. Anyway, I would like to know what command to check whether the required record was locked by other user. So, I can create some other option when the require record can't lock for update because it was locked by other user.

you cant in simple terms.
What you need to do is when you open the record to lock it, write to a field on that record the user id that locked the record. When the reocord become unlocked, clean that field and it will show available.
Thus - simplist way is to program it into your system.

McBlake, why don't you try error traping, for example, create a function called IsLocked that you can pass the recordset, to and...


On Error Goto CheckError

'Try to edit a specific field in a specific record - but don't actually edit or update the record.
IsLocked = False
Exit Function

CheckError:
'If this is executed - check if err is one associated with attempting to edit a locked record - if so
IsLocked = True
else: You choose, looks as if the record can't be edited anyway, might as well be IsLocked = True
Exit Function
End Function

The code below is correct, and the microsoft recommended way to handle database locking.  It is Cut/Pasted from the Mastering VB5 CD, so I know it's correct.  I think I deserve more than 70 points for this too.

This code handles locking errors on the edit command, assuming an edit button was pushed:

Private Sub cmdEdit_Click()
      On Error GoTo HandleError
      rs.Edit
      ButtonEditAddMode
      Exit Sub
HandleError:
      Select Case Err.Number
      Case 3260 'page currently locked
            MsgBox "Record is currently locked. Try again later."
      Case 3197 'data has changed
            MsgBox "Data has been changed and will be refreshed."
            rs.Bookmark = rs.Bookmark 'get updated data
            FillFields
            rs.Edit
      Case Else
            MsgBox Err.Number & ": " & Err.Description
      End Select
End Sub


This code handles errors from the update command
Private Sub cmdSave_Click()
      Dim answer As Integer
      On Error GoTo HandleError
      rs.Fields("Category Name") = txtCategoryName.Text
      rs.Fields("Description") = txtDescription.Text
      rs.Update
      rs.Bookmark = rs.LastModified
      FillFields
      ButtonNavigateMode
      Exit Sub
HandleError:
      Select Case Err.Number
      Case 3260
            MsgBox "Record is currently locked. " & _
                  "Try Save again later or cancel changes."
      Case 3197
            answer = MsgBox("Data has been changed by another user. " & _
                  "Overwrite changes?", vbYesNo)
            If answer = vbYes Then
                  Resume
            Else
                  rs.Bookmark = rs.Bookmark 'refresh w/other users changes
                  cmdCancel_Click
            End If
      Case Else
            MsgBox Err.Number & ": " & Err.Description
      End Select
End Sub
 

This handles an error on a deleted record by another user:
Private Sub cmdMoveFirst_Click()
      On Error GoTo err_movefirst:
      retry_MoveFirst:
      rs.MoveFirst
      txtLName.Text = rs("Last Name")      'Error may occur
      Exit Sub
err_Movefirst:
      If Err.Number = 3167 Then
            'record is deleted,
            'remove entry from your dynaset
            rs.Delete
            Resume retry_MoveFirst
      Else
            MsgBox Err.Description
            Exit Sub
      End If
End Sub
 

mrmick:

I pretty much agree.  This solution may be a little more robust, where your solution considered every error a database locking error, which may not be the case.  However, an exact example may answer his question better

No anthony, I said check only for a locked error - which would satisfy the question on hand.  Then I suggested that if there were any error, might as well treat it as a locked record because it was unavailable for edit.  I suggested this because of the last two words of the question are "or unavailable".

Would your solution work for RDO?

whoops, I meant to ask: what is "ButtonEditAddMode"?

Mcblake,

Please comment whenever you reject an answer so the person offering you a solution will understand how he/she  misinterpreted your question or learn why the solution doesn't work for you.

I don't agree with treating all errors like locked records.  You can make debugging and support of your application more difficult than it needs to be.

anthonyc, an error at the point in question means that the record is unavailable for edit for whatever reason, this is the reason for my suggestion.  Sure, there's no reason why you couldn't invoke a msgbox displaying the error that has caused the condition - and if I took a look at the project, there may be other suggestions I could make, but for now, I addressed the question at hand.

Mcblake was simply asking "How to determine weather a record was LOCKED or UNAVAILABLE?".  Do you see what I'm saying?