[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 33885
  • Last Modified:

adOpenKeyset, adLockOptimistic. What does it do?

Hi,
I'm trying to understand the locking mechanism using adOpenKeyset, adLockOptimistic.
If I've opened an record set using adOpenKeyset, adLockOptimistic, what is the implication to others reading the same record?
Can someone else open the same record with adOpenKeyset, adLockOptimistic and update it?
thanks,
phil
0
spoowiz
Asked:
spoowiz
  • 5
  • 3
1 Solution
 
Michael_DCommented:
First of all there is two different parameters for those values: CursorType and LockType

CursorType specifies the type of cursor used in a Recordset object.

adOpenDynamic - Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.

adOpenForwardOnly - Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.

adOpenKeyset - Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.

adOpenStatic - Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

==========================

LockType specifies the type of lock placed on records during editing.

adLockBatchOptimistic - Indicates optimistic batch updates. Required for batch update mode.

adLockOptimistic - Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.

adLockPessimistic - Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.

adLockReadOnly - Indicates read-only records. You cannot alter the data.

If you still have questions I'll be happy to help
0
 
spoowizAuthor Commented:
what does optimistic or pessimistic mean?
what does data changes by other users are still visible, mean?
0
 
Michael_DCommented:
Optimistic means that record will be locked only the updating time
Pessimistic means that record will be locked all the time from first change and til updating

ex.

rs.Open sSQL, Cn, adOpenDynamic,adLockPessimistic
rs.Fields("SomeField")="some value"  ' <= Record locked
rs.Update '<=Record Unlocked

Locked records  are visible to other users but they will get an error if they try to modify locked records
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Michael_DCommented:
What do you want to do? Maybe I can help you to make decision what type to use?
0
 
spoowizAuthor Commented:
I'm basically using these 2 types:
'adOpenForwardOnly, adLockReadOnly
'adOpenKeyset, adLockOptimistic
Any comments?

So, in using adLockOptimistic, I'm being "optimistic" that 2 users won't be changing the record at the same time, yes?
If user1 opens a recordset with recordx, then user2 opens recordx also, then user1 does update, then user2 does update, does user1 changes get overwritten by user2's update?
0
 
Michael_DCommented:
yes,if  user2 updating record AFTER user1 FINISHED update then it will be overwritten. If User 2 trying to update record at the time when record is being updated by user1 then user 2 will set error message. But you can be "optimistic" because this is very unusual scenario.:)

Opposit If Lock type pessimestic  
user1 made some changes in recordx and still working on it (not updated record)
if user2 just try to modify this record he will get error message saying that record has been locked
Usualy Optimistic lock is enough for most applications.    
0
 
spoowizAuthor Commented:
THANKS
0
 
Michael_DCommented:
Did you understand difference between Cursor types? This is more important than lock type.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now