Solved

vb-access connectivity

Posted on 2004-04-07
5
147 Views
Last Modified: 2010-05-02
what is mean by optimistic or pessimistic lock?
0
Comment
Question by:rajasreedk
5 Comments
 
LVL 19

Accepted Solution

by:
BrianGEFF719 earned 20 total points
ID: 10773021
When working with Recordset objects that lock data at the page or record level, you must specify which type of locking you want to use. There are two of types of locking available when you use page- and record-level locking: pessimistic locking and optimistic locking.

You can set the type of locking for an ADO Recordset object by specifying either the adLockPessimistic or the adLockOptimistic constant in the LockType argument of the Open method of the Recordset object. You can't change the type of locking after you open an ADO Recordset object as you can with a DAO Recordset object by setting its LockEdits property.

Pessimistic Locking
With pessimistic locking, a record (or the page the record resides on) is locked once you begin editing the record. In ADO you don't use the Edit method to start an editing operation as you do in DAO. To edit a field's value in ADO, you simply change the Value property of a Field object. The record or page remains locked until you save your changes to the record by moving to a new record or by using the Update method. If you aren't using batch updating, you must use the CancelUpdate method to cancel the edit before moving to a new record.

The main advantage of pessimistic locking is that after you have obtained a lock, you know that you won't encounter any locking conflicts as long as the record is locked. Additionally, pessimistic locking is the only way to guarantee that your solution reads the most current data, because one user can't change a record after another user has started to edit it.

The disadvantage to using pessimistic locking when you are using page-level locking is that the entire page is locked for the duration of the procedure. Therefore, other users can't change any records on that page until the lock is released. However, by default, both ADO and DAO Recordset objects use record-level locking, so this is only an issue if you override the default setting.

To use pessimistic locking in your code

Implement pessimistic locking by setting the LockType argument of the Open method of the Recordset object to adLockPessimistic when you open the Recordset object.


Move to the record that you're interested in.


Edit the record by specifying changes to Field objects. When you use pessimistic locking, ADO attempts to lock the record as soon as you start editing the first Field object. If the lock fails, try again.


When the record is locked, make your changes to the record.


Save your changes to the record by moving to a new record or by using the Update method. After your changes are saved, the lock is released.
Optimistic Locking
With optimistic locking, a record or page is locked only when you try to save the changes to the record by moving to a new record or by using the Update method. Because the lock is applied only when your solution tries to update the record, you minimize the time the lock is in place; this is the main advantage of optimistic locking.

The disadvantage of optimistic locking is that when you are editing a record, you can't be sure that the update will succeed. Your attempt to update the record with your edits will fail if another user updates the record first.

To use optimistic locking in your code

Implement optimistic locking by setting the LockType argument of the Open method of the Recordset object to adLockOptimistic when you open the Recordset object.


Move to the record that you're interested in.


Edit the record by specifying changes to Field objects. When you use optimistic locking, this doesn't lock the record.


Save your changes to the record by moving to a new record or by using the Update method. This attempts to lock the record.


Check to see if the Update method succeeded. If it didn't, try again.
It's possible for the Update method to fail in optimistic locking. For example, if one user has a Recordset object open with pessimistic locking, and another user tries to update data on the same page by using optimistic locking, the second user's attempt to update will fail.

Note   Optimistic locking turns into pessimistic locking when transactions are used. Because a transaction locks data so that users can't change it until the transaction is committed, pessimistic locking is used even though the LockType argument may have been set to adLockOptimistic. For more information about transactions, see "Using Transactions" later in this chapter.

Checking for Errors with Record-Level and Page-Level Locking
When you use record- or page-level locking, before proceeding, your code must check to see if the attempted lock succeeded. As with exclusive mode and recordset locking, you should turn off error handling, attempt the operation, check for errors and handle any that occur, and finally, turn on error handling.

The following table describes the three most common errors that your solution may encounter when you use record- or page-level locking. These errors are returned by the Jet Database engine.

Error number and text Cause and suggested response
3218 "Could not update; currently locked." This error occurs when a user tries to save a record that is locked by another user.
To handle this error, program your solution to wait for a short period of time, and then try to save the record again. Or, you can display a message that explains the problem and give users the opportunity to try the operation again.
 
3197 "The database engine stopped the process because you and another user are attempting to change the same data at the same time." This error occurs if another user has changed the data since the current user started trying to update the record. When this error is triggered depends on the locking mode you are using:
If you are using pessimistic locking, this error occurs when the current user attempts to start editing the record after the other user has saved changes to the record.


If you are using optimistic locking, this error occurs when the user attempts to save changes by using the Update method after the other user has already saved changes to the record.
In either situation, to handle this error, program your solution to display a message that informs the user that someone else has changed the data. You may want to display the current data and give users the choice of whether to overwrite the other user's changes or cancel their own edits.
 
3260 "Couldn't update; currently locked by user <name> on machine <name>." This error occurs when a user attempts to edit a record and the current record (or if you are using page-level locking, the page it is on) is locked.
If you are using page-level locking, this error also occurs when a user uses the AddNew method or the Update method to save a record on a locked page. This situation can occur when the user is trying to save a new record or when optimistic locking is in place and another user locks the page.

To handle this error, program your solution to wait for a short period of time, and then try to save the record again. Or, you can inform users of the problem and allow them to indicate whether or not they want to retry the operation.
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10774149
I think this is the longest most thorough answer I've ever seen given on this site.  Good job.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now