Database locking

How can I give the computer to check whether this record was locked by other users or not? I just know how to lock record but I need to know that when other user lock record, how can I check that it was lock or unavailable?
McblakeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anthonycCommented:
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.
0
mrmickCommented:
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


0
MatveyCommented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Matvey
0
MatveyCommented:
I wanna know about 'record' locking not database locking.
Thanks
0
McblakeAuthor Commented:
What is your environment? VB4, VB5? Access/Jet, xBase, some SQL server?
0
rantanenCommented:
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"
0
cymbolicCommented:
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.
0
McblakeAuthor Commented:
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.
0
staffiidbaCommented:
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

0
mrmickCommented:
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
 

0
anthonycCommented:
As far as checking if a record is locked - which is/was the question - This is no different from my suggestion.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrmickCommented:
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
0
anthonycCommented:
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?
0
mrmickCommented:
whoops, I meant to ask: what is "ButtonEditAddMode"?
0
mrmickCommented:
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.
0
mrmickCommented:
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.
0
anthonycCommented:
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?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.