?
Solved

Database locking

Posted on 1997-12-28
18
Medium Priority
?
268 Views
Last Modified: 2006-11-17
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?
0
Comment
Question by:Mcblake
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +4
18 Comments
 
LVL 6

Expert Comment

by:anthonyc
ID: 1447780
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
 
LVL 8

Expert Comment

by:mrmick
ID: 1447781
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
 
LVL 3

Expert Comment

by:Matvey
ID: 1447782
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:anthonyc
ID: 1447783
Sorry, I've misunderstood your question.
I haven't worked dmuch with multiuser databases...

Matvey
0
 
LVL 3

Expert Comment

by:Matvey
ID: 1447784
I wanna know about 'record' locking not database locking.
Thanks
0
 

Author Comment

by:Mcblake
ID: 1447785
What is your environment? VB4, VB5? Access/Jet, xBase, some SQL server?
0
 
LVL 4

Expert Comment

by:rantanen
ID: 1447786
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
 
LVL 9

Expert Comment

by:cymbolic
ID: 1447787
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
 

Author Comment

by:Mcblake
ID: 1447788
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
 

Expert Comment

by:staffiidba
ID: 1447789
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
 
LVL 8

Expert Comment

by:mrmick
ID: 1447790
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
 
LVL 6

Accepted Solution

by:
anthonyc earned 280 total points
ID: 1447791
As far as checking if a record is locked - which is/was the question - This is no different from my suggestion.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1447792
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
 
LVL 6

Expert Comment

by:anthonyc
ID: 1447793
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
 
LVL 8

Expert Comment

by:mrmick
ID: 1447794
whoops, I meant to ask: what is "ButtonEditAddMode"?
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1447795
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
 
LVL 8

Expert Comment

by:mrmick
ID: 1447796
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
 
LVL 6

Expert Comment

by:anthonyc
ID: 1447797
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question