13.2.4- What is Locking?

What is Locking?

We're all familiar with locking. If you lock your front door then it prevents burglars from walking off with your TV and video recorder. If you lock your cellar door then it stops your teenage children drinking your much-valued 1961 Chateaux Petrus! The same applies to records in a data store – if you lock the records then it prevents other people from changing them.

The locking type is closely related to whether or not the recordset is updateable. If you're using your recordset to query a data store, then the recordset is a copy of the records that you requested in the data store. That means that there's one copy of the record in your recordset and another copy of the record in the data store. Then, updating a record is a two-stage process:

• First, you edit the copy of the record that's contained in your recordset

• Second, you update your changes to the copy of the record that's in the data store

Here's where the locking comes in: you can choose to lock the data store copy of the record while you're making your changes – and keep other users from touching them. There are four types of locking you can use:

• Read-only (adLockReadOnly): This gives you a non-updateable recordset. No locking is performed, since you can't change the data in a read-only recordset. This is the default.
• Pessimistic (adLockPessimistic): This gives you an updateable recordset, in which the lock type is very protective. In this case, the copy of the record that exists in the data store is locked as soon as you start editing it. This means that no one else can change the record until you release the lock, which is after you finished editing the record and have committed the update.
• Optimistic (adLockOptimistic): This also gives you an updateable recordset, but the lock type is a little more carefree. In this case, the copy of the records in the data store remains unlocked while you're editing your changes within the recordset. The data store records are only locked when you update your changes. So, if you choose this setting you're assuming that no one else will edit the record while you are editing it. If this does happen then the person who commits their update first will "win". The first person will successfully update the record. The second person's initial state will be checked and found to differ for the current state of the database and the change will be rejected.
• Optimistic Batch (adLockBatchOptimistic): Batch update mode allows you to modify several records, and then have them updated all-at-once, so this only locks each record as it is being updated.

The LockType Property

Recalling the Recordset.asp example once again, you can see that it was the Open method's fourth parameter that allowed us to set the lock type (this requested a read-only recordset, because we weren't planning to change any data):

objRS.Open "Movies", strConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

Again, we can also set the lock type by using the Recordset object's LockType property:

objRec.LockType = adLockReadOnly

Again, you can read the LockType property at any time, but you can only set it before the recordset is assigned a live connection.