Record Locking in Access 2002

team lib

What is Locking Anyway?

Locking is a mechanism that controls the editing of records. It allows multiple users to share and modify data safely. It does this by blocking one user 's changes to records while they are being modified by another.

Without the use of locking two users could both make different changes to the same record. What happens when they finish editing and save the changes? The data that gets saved last will overwrite the first without any warning. This is a dangerous thing to allow. You can imagine a table of customers where perhaps one user changes an account's telephone number and another other the name of the sales rep assigned to it at the same time - both changes equally valid - one of them arbitrarily overwritten by the other! Locking helps to mitigate these problems.

In fact locking can also apply to a single user who happens to be using the same data from multiple applications or perhaps even from within the same application on multiple forms, say. It is useful anywhere that multiple editing of the same data can occur.

One other thing to bear in mind is that locking never prevents reading of data, only editing and/or writing. This allows other users to view records and perform reports etc. however many records are currently being edited by others. It also doesn't affect adding new records as these do not truly exist in the table until they have been saved by the application that created them.

There are a number of different forms of locking, which we will go through in detail below.

Page Locking vs. Record Locking

If you have used Microsoft Access before, you might have noticed that the Advanced page of the Tools/Options... dialog box now contains a checkbox that was not present in previous versions:

click to expand

We can now decide whether we want use record-level locking or page-level locking when we open databases.

In previous versions of Access, Microsoft employed a page-level locking strategy. That is to say that instead of placing locks on individual records, Access placed locks on the underlying data page on which the record resided. Data pages were 2Kb in length and could contain multiple records. What that means is that the act of placing a lock on a data page could lock a great many records apart from those that were actually being edited.

Well, page-level locking does have some advantages. The process of obtaining and releasing locks is an expensive process in terms of both the CPU activity and the memory overhead required to maintain the lock structure. Because page-level locking locked multiple records it meant that fewer locks were typically required than if record-level locking had been implemented. Fewer locks means less work for the processor and less memory overhead. However, it also means less concurrency (the ability for multiple users to access the same data at the same time). Microsoft believed that page-locking offered the best tradeoff to this problem.

By contrast, record-level locking places locks on individual records. So, if one record is locked, other users can still access all other records in the base table including the records on the same page as the locked record. To many people this will seem like an overwhelmingly obvious approach. Why did previous versions of Access not employ record-level locking in the first place?

Several things have changed since the days of page-level locking in early versions of Access. Dramatic improvements in processor performance, coupled with reductions in memory prices mean that for most modern PCs the overhead incurred by locking is less onerous than it once was. Record-level locking is a relatively less expensive option than it was originally.

Coupled to the improvement in PC performance is the fact that Access now offers full Unicode support. That means that Access can now support the characters that appear in a variety of international languages rather than simply supporting the 256 commonly used characters of the ANSI character set. The downside to this, however, is that Access now uses 2 bytes rather than 1 for each character. In order to support this, the size of data pages has grown from 2 Kb to 4 Kb. Now if locking a 2 Kb page full of records when only one record is being updated is a controversial move, then just think of the effect of locking a 4Kb page. In a table with relatively short records - say, an average of 20 bytes in length - then a 4 Kb page would contain approaching 200 records.

Another important factor behind choosing record-level locking is to maintain compatibility with Microsoft SQL Server, which also supports it. The use of this feature therefore allows developers to migrate applications from Access to SQL Server without having to worry about changes in locking strategies.

Record-Level Locking

As we mentioned before you can choose record-level locking by checking the Open databases using record-level locking checkbox on the Advanced page of the Tools/Options... dialog box in Access. Leaving it unchecked selects page-level locking.

Note that the option selected only applies to the future opening of databases and does not affect the one currently open, which continues to use whichever option was selected when it was first opened.

Which should you select? For most people, the default - record-level locking - will be the preferred choice because of the benefits it offers in terms of concurrency. Actually, if a database has been opened in record-level locking mode, JET will still use page locks in those situations where it is more appropriate to do so.

Specifically, if you perform set-based operations such as UPDATE , INSERT, or DELETE queries, then JET will temporarily switch to a page-locking mechanism as this will help to perform the query more effectively, regardless of the option selected. If, however, you are performing a cursor-based operation (such as looping through all of the records in a recordset and updating records individually) then JET will use record-level locking.

However, there are a couple of caveats. Firstly, due to the limitations of Access, you will only be able to realize the concurrency benefits of record-level locking in code if you place your data modification statements inside transactions.

A transaction is a method of programmatically grouping together a number of data modification statements into a single unit. They are normally used to promote database consistency, by ensuring that either all of the statements in the transaction are executed successfully, or none of them are. A discussion of transaction usage is a little beyond the scope of this chapter, but if you want to know more about transactions, you should take a look at the topic "BeginTrans, CommitTrans, Rollback Methods " in the Visual Basic help file.

Secondly, and potentially more confusingly, in a multi-user environment, the decision as to whether a database will be opened with page or row locking is determined by the first person that opens the database. So if the first person to open a database has record-level locking specified as an option, then the database will be opened with record-level locking and this option will apply to all other users who then access the database while it is open, irrespective of the locking level that they might have specified as an option. After the last person closes this database, the level of locking selected the next time the database is opened will be decided again by the person who opens the database first.

Important 

What this means is that in a multi-user environment there is no easy way of determining whether a database that you have opened in is in record-level or page-level mode!

To change the granularity of locking to record-level or page-level programmatically simply use the SetOption method of the Application object like this:

   Application.SetOption "Use Row Level Locking", True   

This has exactly the same effect as checking the Open databases using record-level locking checkbox on the Advanced page of the Options... dialog box in Access. Don't forget that changing this setting, either manually or programmatically, has no effect on any database that is currently open. Therefore, you must set this option before you open the database that you wish it to apply to.

So, now that we have looked at the way that Access provides a method of altering the granularity of locking, let's have a look at when JET places and releases locks.

Optimistic and Pessimistic Locking

Access 2002, just like previous versions of Access, employs two methods of locking records - optimistic and pessimistic locking:

  • With pessimistic locking, Access locks a record (or the page the record is on) whenever someone starts to change the contents of that record. This could be as a result of changes made to a form, table view, programmatically, or via any other method.

  • With optimistic locking, Access only tries to place the lock when someone tries to save the changes that they have made. Again this occurs no matter what method was used to save the changes.

Let's look at an example to clarify the difference between the two. Suppose John and Mary are both using the same database and are editing records in the same table. If John decides just to view record #30 then, irrespective of the type of locking strategy involved, Mary will still also be able to look at the contents of that record (locking doesn't affect read operations remember).

However, suppose John now decides to edit the contents of that record by typing a new value in one of the fields on his form. If pessimistic locking is being employed, Access will now lock that record so that only John can change it.

Important 

Pessimistic locking implies that when a user edits a record, Access pessimistically assumes that someone else will also want to edit that same record and so needs to lock it.

Mary will now see the 'record locked' indicator on her form, a circle crossed through, which tells her that she can't edit the record. The screenshot fragment below shows that the record with the CompanyID of 1 has been locked for editing by another user or process:

click to expand

Access simply will not allow Mary to type anything into any of the fields for the record that is locked. In fact, she will not be able to edit that record until John has finished with record #30 and has saved his changes.

click to expand

However, if an optimistic locking strategy were being used, Mary would have been able to make changes to record #30, even while John was editing it.

Important 

Optimistic locking implies that, when a user edits a record, Access optimistically assumes that no-one else will want to edit the same record, and so it doesn't lock it.

This can be a very dangerous scenario. Assume that both John and Mary are now editing the same record - what happens when John tries to save the record? Nothing out of the ordinary... he is able to save the record as if nothing had happened . But what happens when Mary tries to save the record?

click to expand

The answer is that when she tries to save a record that has changed since she opened it, she is presented with this dialog box:

click to expand

Sometimes, when a dialog box pops up you instantly know what is wrong and what you need to do. But this one carries a rather complicated message. You really need to think twice about how to respond to it and for those seeing it for the first time, it can be quite confusing. Essentially , the dialog box is telling Mary that she has three choices:

  • She can save her record. This will overwrite the changes that John has just made without John being aware of the fact.

  • She can copy her changes to the clipboard. These can then be pasted into another application like Word or Excel so that they can be saved for reviewing and/or reapplying. To reapply all the changes the record can be pasted back into the table without having to retype anything. Normally, however, you will want to save only some or merge the changes in some way - this has to be done manually.

  • Or she can just call it a day and drop the changes she has just made. In this case, John's amended record will remain in the table but the changes that she has made will be lost.

Choosing a Locking Strategy

So which locking strategy should you choose? Optimistic or pessimistic? There is no simple answer, but in general:

  • If it is unlikely that any two users will want to amend the same record simultaneously , use optimistic locking.

  • If it is likely that two or more users may want to amend the same record simultaneously, choose pessimistic locking.

You are probably getting fed up with us saying this again, but developing Access applications involves a large degree of compromise. When choosing a locking strategy, the compromise is one of concurrency versus complexity.

 

Optimistic Locking

Pessimistic Locking

For

Locks are only in place for a short time, increasing concurrency.

If you are have started to modify a record, you know that you will be able to save those changes.

Against

You cannot guarantee that you will be able to save a record once you have started to modify it. This leads to complex decision-making to decide which changes will receive priority and be committed.

Locks are in place for a long time (from when a user starts to edit a record until the changes are saved or cancelled). This means concurrency is reduced and users may have to wait longer before they can access data.

Important 

Using record-level locking makes pessimistic locking a significantly less expensive strategy in terms of concurrency than it was in some previous versions of Access where only page-level locking was available.

Setting the Default Record Locking Mechanism

To choose a particular locking strategy for your Access environment, you should select the appropriate option from the Advanced page of the Options... dialog box:

click to expand

No Locks invokes optimistic locking . A record is locked only when it is actually being saved.

All Records (exclusive locking) causes the entire table or tables, which form the recordsource of the object, to be locked when any record is edited. This is fairly extreme and generally should only be used by the administrator, when performing maintenance.

Edited Record invokes pessimistic locking . A record will be locked as soon as someone starts to edit it. This is normally the best compromise option.

Try It Out-Changing the Default Locking Mechanism

  1. Open up IceCream.mdb and make sure the Default Record Locking option on the Advanced page of the Tools Options... dialog box is set to No Locks . Click the OK button.

  2. Create a new module called Chapter 17 Code and type the following procedure into it:

       Sub SetLocking ()     Application.SetOption "Default Record Locking", 2     End Sub   
  3. Run this procedure by typing SetLocking in the Immediate window.

  4. Now open the Advanced page of the Options... dialog box again. The Default Record Locking option should now be Edited Record :

How It Works

There are three different settings for the Default Record Locking option in Access. The table below shows the different options and the VBA statement that is used to set each of those options. As with all multi-select options, the value used in the SetOption method corresponds to the zero-based order in which the option appears on the Tools/Options ; dialog box.

This option...

...is also known as...

...and is set like this

No Locks

Optimistic Locking

Application.SetOption "Default Record Locking", 0

All Records

Exclusive Locking

Application.SetOption "Default Record Locking", 1

Edited Records

Pessimistic Locking

Application.SetOption "Default Record Locking", 2

In order to determine what the current locking option is, you can use the GetOption method of the application object. Here's a procedure you can write to allow you to set and then read the Default Record Locking option:

   Sub SetAndGetLocking(strLockType As String)     Dim intLockIndex As Integer     Dim strLockDesc As String     'Convert the string argument into a option index     Select Case strLockType     Case "Optimistic"     intLockIndex = 0     Case "Exclusive"     intLockIndex = 1     Case "Pessimistic"     intLockIndex = 2     Case Else     intLockIndex = -1     End Select     'Set default record locking option     If intLockIndex <> -1 Then     Application.SetOption "Default Record Locking", intLockIndex     End If     'Now determine and display default record locking option     Select Case Application.GetOption("Default Record Locking")     Case 0     MsgBox "The default locking method is optimistic."     Case 1     MsgBox "The default locking method is exclusive."     Case 2     MsgBox "The default locking method is pessimistic."     End Select     End Sub   

If you run this procedure by typing the following in the Immediate window,

   SetAndGetLocking "Exclusive"   

the default locking option will be set to Exclusive (All Records) and you will see this dialog box:

Implementing Record Locking on Forms

So far, we have only really considered the record-locking mechanism that applies to the tables and queries in your database. But you can also specify the locking method used by individual forms and reports. You do this by setting the RecordLocks property of the object concerned . For example, look at this code:

   Dim frm As Form     DoCmd.OpenForm "frmCompany"     Set frm = Forms!frmCompany     frm.RecordLocks = 2   

This will set pessimistic locking ( Edited Records ) for the frmCompany form. In other words, as soon as someone edits a record using frmCompany , Access will attempt to lock that record (or the data page that the record is on if page-level locking is implemented). This will prevent anyone else from editing that record (or page), whether from frmCompany or any other forms, or with a query or directly within the table where that record exists.

The RecordLocks property uses the same three arguments as the Application.SetOption method. They are described below:

This option...

...is set like this...

...and has this effect

No Locks

.RecordLocks = 0

The default setting. Tries to obtain a lock on the underlying data only when a user attempts to save an amended record.

All Records

.RecordLocks = 1

Tries to lock all records in the underlying table(s) whenever someone has the form open. This option is only really useful when the form is designed to modify all of the records in the table itself (such as for some kind of maintenance, perhaps resetting sales bonus points once a year for the whole sales team). It should certainly only be used when absolutely necessary.

Edited Records

.RecordLocks = 2

Tries to obtain a lock whenever someone starts to amend a record on the form. The lock stays in place until the user finishes editing the record.

If you don't explicitly set the RecordLocks property for a form, report or query, the object inherits the default record locking option that was in place when the object was created.

Recordsets and Record Locking

Earlier in the book we looked at how we could create Recordset objects. The basic syntax for creating a Recordset object is as follows :

 Set rs = db.OpenRecordset(source, type, option, lockedits) 

The source argument simply defines where the records will come from that will populate the recordset. This can be the name of a table or query, or a SQL string. You should be familiar with this by now, but what of the other arguments? Well, let's take a more detailed look now at the other arguments that we can supply to the OpenRecordset method and, in particular, how these arguments affect the way that locking is handled.

The Type Argument

In Chapter 8 we said that there were four different types of recordset that JET provides. To specify the type of recordset we wish to open, we use the appropriate type argument with the OpenRecordset method. The following four intrinsic constants can be used for that type argument to create JET Recordset objects:

  • dbOpenTable

  • dbOpenDynaset

  • dbOpenSnapshot

dbOpenForwardOnly

You should be familiar with these arguments by now. If you feel a little unsure of the differences between these types of Recordset objects, you should run through that chapter again. As far as we are concerned right now, however, the major difference is that you cannot edit the records in a snapshot or forward-only Recordset object.

The Option Argument

The third argument affects the updateability of the recordset is as a whole. Valid choices for this argument, together with their meanings, are shown below:

This option...

...has this effect

dbDenyWrite

No one else can modify or add records while we have the recordset open.

dbDenyRead

No one else can read data in the table while we have the recordset open.

dbSeeChanges

If one person tries to save changes to a record that another user has modified since the first user started editing it, Access generates a run-time error.

dbAppendOnly

We can only add records to the recordset and cannot view or amend existing ones. We can only use this option with dynaset-type recordsets.

dbInconsistent

We can modify all columns on both sides of the join in a dynaset built from multiple tables. This can leave the recordset in an "inconsistent" state as these changes may violate the join rules. It is recommended that you avoid using this option if at all possible for this reason.

dbConsistent

We can only modify columns that leave the dynaset consistent. So we can't alter the joined field on the 'many' side of a one-to-many join to a value that doesn't appear on the 'one' side.

If you need to, you can combine two or more of these options in a single statement like this:

   Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbConsistent + dbDenyRead)   

There are a few other valid constants you can supply as the option argument, but they are either for use with non-Access databases or are present only for backwards compatibility.

The LockEdits Argument

The final argument is used to specify the type of record locking that will be used when we - or other users - try to edit records that appear in the recordset.

This argument...

...has this effect

dbReadOnly

No one else can amend records that appear in our recordset so long as we have the recordset open.

dbPessimistic

A pessimistic locking strategy is applied (see earlier).

No one else can amend records that appear in our recordset if we are in the process of editing them. Similarly, we can't edit a record in our recordset if someone else is already editing it.

dbOptimistic

An optimistic locking strategy is applied (see earlier).

Two or more users can try concurrently to amend a record that appears in our recordset. However, only the first person to save their changes will be successful. When other users try to save a record that the first user has changed, Access generates a run-time error.

Again, there are another couple of arguments as well, but they are only for use with ODBCDirect, so we don't need to worry about them.

So, if we wanted to create a dynaset-type recordset based on the table tblSales that would allow us to add and edit records to the table, but didn't allow anyone else to view the records in tblSales while the recordset was open, we would use the following code:

   Dim db As Database     Dim rec As Recordset     Set db = CurrentDB()     Set rec = db.OpenRecordset("tblSales", dbOpenDynaset, dbDenyRead)     .     .     .     rec.Close     db.Close   

The LockEdits Property

Once a recordset is open, we can also change its locking behavior by setting its LockEdits property. To change the locking behavior for a recordset to optimistic locking, we set the LockEdits property of the recordset to False . To apply a pessimistic locking strategy, we set the LockEdits property of the recordset to True .

For example, the following piece of code opens a recordset and changes the locking behavior of the recordset to optimistic:

   'This opens a recordset with pessimistic locking (default)     Set rec = db.OpenRecordset("Country", dbOpenDynaset)     'This line sets the locking behavior to optimistic locking     rec.LockEdits = False     ...'Do something with the records     rec.Close      'Close the recordset   

Although the LockEdits argument of the OpenRecordset method and the LockEdits property of a Recordset object do the same thing, the difference is when they are used. The LockEdits argument of the OpenRecordset method can only be used when you open a recordset, whereas you can set the LockEdits property of a recordset any time after the recordset has been opened, until the recordset is closed.

Important 

Some recordsets, such as those based on tables in ODBC data sources, do not support pessimistic locking. Attempting to set the LockEdits property of such a recordset to True will cause Access to generate a run-time error.

Handling Record-Locking Errors

It is all very well to say that, when a recordset is opened with the dbDenyWrite option, no one else can add or edit records in the underlying table(s), but what actually happens when a procedure attempts to open an exclusively locked table? The answer is that a run-time error occurs and, if we don't have any error handling, our application will stop. It is important, therefore, to know the types of record-locking errors that can occur at run time, and how our error-handling code should deal with them.

Optimistic Locking Errors

With optimistic locking we should not encounter any errors when attempting to edit a record - only when we try to update or add one.

Likely Errors

If we are using optimistic locking, the three most common error codes we will encounter are 3186 , 3197, and 3260 .

Error 3186 - Could not save; currently locked by user < xxx > on machine < xxx >

This error only occurs when optimistic locking is being used. It indicates that we are trying to save a record that is locked.

Error 3197 - The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

This error occurs when we try to use the Update method but another user has changed the data that we are trying to update. The other user will have changed the data between the time we used the Edit method and the Update method. This is the same situation that led to the Write Conflict dialog box that we saw at the start of this chapter.

Error 3260 - Couldn't update; currently locked by user < xxx > on machine < xxx >

This error will occur if we use the Update method to try to update a record we have added or changed, but where another user has since locked that record.

If page-level locking is in place, this error may also occur when we use the AddNew method to add a record to a recordset where the page on which the new record resides is locked.

You might have noticed that errors 3186 and 3260 have similar causes. In fact, although they can occur at subtly different times, as far as we are concerned we should handle them in exactly the same way.

How to Deal with Optimistic Locking Errors

For error codes 3186 and 3260 , we should wait a short period of time and then attempt to save the record again. If we still can't save the record after several attempts, we should cancel the operation, inform the user of what has happened and let them do something else.

For error code 3197 , we should requery the database to see what the new value of the record is, display it to the user and ask them if they want to overwrite the record with their own changes. Depending on your particular application it might be possible to automatically resolve the problem by checking which fields conflict and performing some kind of a merge. In practice however, unless the two edits are in fairly self contained parts of the record (for example, one has changed sales info and the other accounts info ) this is unlikely to work well and you will have to resort to human intervention to sort the problem out. If the users are not likely to be well trained, or if it is very likely that they will be editing the same records at the same times, then you may have no other option but to resort to pessimistic locking.

The following sample of code illustrates how we can gracefully handle the type of errors that occur when we are using optimistic locking:

   Function OptErrors() As Boolean     Dim db As Database     Dim rec As Recordset     Dim intLockRetry As Integer     Dim i As Integer     Dim intRetVal As Integer     Dim recClone As Recordset     Const LOCK_RETRY_MAX = 5     Const LOCK_ERROR$ = "Could not save this record. " & _     "Do you want to try again?"     Const SAVE_QUESTION$ = "Do you want to save YOUR changes?"     On Error GoTo OptErrors_Err     Set db = CurrentDb()     Set rec = db.OpenRecordset("tblCountry", dbOpenDynaset, ,dbOptmistic)     '     ' This is the main body of your code     '     OptErrors = True     OptErrors_Exit:     Exit Function     OptErrors_Failed:     OptErrors = False         'This is where you put code to handle what     'should happen if you cannot obtain a lock     GoTo OptErrors_Exit     OptErrors_Err:     Select Case Err.Number     Case 3197                     'Data has changed         'Make a copy of the recordset     Set recClone = rec.OpenRecordset()     'Move to amended record     '...     'Display amended record     '...     'Ask user what to do     intRetVal = MsgBox(SAVE_QUESTION$, vbExclamation + vbYesNo)         'If the user wants to save their changes     If intRetVal = vbYes Then     'Try to update again     Resume     Else     'Else just call it a day     Resume OptErrors_Failed     End If     Case 3186, 3260         'Record is locked so add 1 to counter     'indicating how many times this happened     intLockRetry = intLockRetry + 1     'Have you already retried too many times?     If intLockRetry < LOCK_RETRY_MAX Then         'If you haven't, then wait for a short period     For i = 0 To intLockRetry * 1000     Next         'Now try again     Resume     Else         'But if you have already tried 5 times     'ask if user wants to retry.     'If they say yes then...     If MsgBox(LOCK_ERROR$, vbExclamation + vbYesNo) = vbYes Then         intLockRetry = 0  '...set counter to 0     Resume            'and do it over     Else       'But if they have had enough     'just call it a day         Resume OptErrors_Failed         End If     End If     Case Else           'Catch all other errors     MsgBox ("Error " & Err.Message & ": " & str(Err.Number))     Resume OptErrors_Failed     End Select     End Function   

Pessimistic Locking Errors

If we are using pessimistic locking, we can normally guarantee that we will be able to save any record that we have opened with the Edit method. For this reason, we shouldn't encounter error 3186 . However, we may come across the other two errors.

Likely Errors

Error 3197 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

When using pessimistic locking, this error occurs if we try to use the Edit method on a record but the data in the record has changed since it was last accessed. This may happen, for example, if someone has changed or deleted the record since we opened the recordset.

Error 3260 Couldn't update; currently locked by user < xxx > on machine < xxx >

Don't be misled by the word 'update' in the message. If we are using pessimistic locking, this error will occur if we try to use the Edit or AddNew methods on a record where the record (or page) is already locked by someone else.

How to Deal with Pessimistic Locking Errors

For error code 3260 , we should wait a short period of time and then attempt to edit the record again. If we still can't edit the record after several attempts, we should give the user the choice of continuing to attempt to edit the record or canceling the operation.

For error code 3197 , we should requery the database to see what the new value of the record is and try the Edit method again. If the record had only been changed, we should be able to edit it now. If it was deleted though, we will encounter error code 3167 (Record is deleted).

The function below contains an error handling routine that should take care of these errors:

   Function PessErrors() As Integer     Dim db As Database     Dim rec As Recordset     Dim intLockRetry As Integer     Dim i As Integer     Const LOCK_RETRY_MAX = 5     Const LOCK_ERROR$ = "Could not save this record. " & _     "Do you want to try again?"     On Error GoTo PessErrors_Err     Set db = CurrentDb()     Set rec = db.OpenRecordset("tblCountry", dbOpenDynaset)   
   '     ' This is the main body of your code     '     PessErrors = True     PessErrors_Exit:     Exit Function     PessErrors_Failed:     PessErrors = False     'This is where you put code to handle what should     'happen if you cannot obtain a lock after many attempts     GoTo PessErrors_Exit     PessErrors_Err:     Select Case Err     Case 3197           'If data has changed, then     rec.Requery     'simply refresh the recordset     Resume          'and try again.     Case 3167     'You have not got much choice     'if someone else has deleted this record     MsgBox "Someone else has deleted this record"     Resume PessErrors_Failed     Case 3260     'But if the record is locked, add 1 to counter     'indicating how many times you have retried     intLockRetry = intLockRetry + 1         'Have you already retried 5 times?     If intLockRetry < LOCK_RETRY_MAX Then         'If not then wait for a short period     For i = 0 To intLockRetry * 1000     Next         'Now try again     Resume         Else     'If you have already tried 5 times     'ask the user if they want to retry     'If they hit the yes button then...     If MsgBox(LOCK_ERROR$, 'vbExclamation + vbYesNo) = vbYes Then         'Set counter to 0 and do it over again     intLockRetry = 0     Resume     Else     'But if they have had enough     'just call it a day     Resume PessErrors_Failed     End If     End If     Case Else     MsgBox ("Error " & Err & ": " & Error)     Resume PessErrors_Failed     End Select     End Function   

Deadlocks

Deadlock is the name for the potentially disastrous situation where two users get stuck, each waiting for the other to unlock a required table. Maybe an example might help to make things a little clearer:

John's program needs to lock records A and B to perform an update operation. Mary's program needs to lock records B and A to perform a different update operation.

John's program locks record A. Mary's program locks record B. John then attempts to lock record B. Mary attempts to lock record A.

The result is a deadlock . Both users get stuck waiting for the other to complete, which they can't do because they're waiting for the other to complete, who can't complete

Unfortunately Access currently has no built-in methods for detecting and dealing with a deadlock gracefully. There are, however, a number of techniques to help prevent this situation arising in the first place:

  1. Only use locks where absolutely necessary and only apply them for as short a time as necessary. Obvious advice but all too often ignored!

  2. Try to write all code so that it always locks tables and records in the same order. In the example above deadlock would be avoided if this technique had been employed as Mary would not have locked record B before attempting to lock record A (which was already locked by John). This is sensible but will not get you out of every hole and may mean that you have to hold more tables or records locked for longer if you need to use them in a different order.

  3. Add error trapping code so that if the attempt to lock the second table fails the whole operation is abandoned and the locks on all other tables and records are released. This is fine if there are only a couple of tables involved but can get messy if the operation is very complex and uses several tables. In addition, in order for this to work well you must lock all tables or records required before beginning any editing or undoing changes may become extremely complex or even impossible . This will also increase the likelihood of locking out other users of course.

  4. Write all updates using transactions. Transactions are beyond the scope of this book but essentially they allow the rollback (or cancellation) or the entire operation much more easily than coding by hand.

Other Solutions

Possibly the best solution to the problems of concurrent editing is to try to avoid it completely if at all possible! Normalization, or the splitting of data down to its atomic (or indivisible) parts, can really help here. While a full discussion of normalization is completely beyond the scope of this book (it would fill several large tomes and be very, very boring to read!) a simple example may help to demonstrate the principle:

Consider a customer table, perhaps similar to tblCompany in the Ice Cream database. You may require several contact names and numbers stored in each record rather than the one we have at the moment. To do this you could simply add more fields to the table but, if instead of this you create a new table tblCompanyContacts , use this to store individual contact names and numbers, and then link them to the main tblCompany then you have just normalized that part of the data.

Obviously this has just added an extra layer of complication to your application. You will need a sub-form to display the list of contacts alongside the main company info for example. The payoff comes when a user needs to edit a contact number though. They will only need a lock on that particular contact record and not the entire company record, which is much less likely to affect anyone else. Two users can also edit two different contacts completely without problems. There are other advantages too; there are also now no limits to the number of contacts you can store for each company, 1, 2, 100, 1000, and storage will only be used as required (if you had to allow 100 extra contact fields "just in case" you will soon be buying more hard disk space!).

You can apply these normalization techniques on many occasions in order to help solve locking problems but as always there is a tradeoff; although normalization can remove duplicated data it always makes the database more complicated and this can adversely affect performance, particularly for complex reports that may now need to collate information together from several tables instead of one or two. As always, there is no simple answer; you must make the decision based on the needs of your users.

Well, that's about all we are going to say about record locking. I hope you have managed to keep track! In practice, managing locking is not too tricky provided that you take the time to think, before you start building the database, about how users are going to be interacting with it. Predicting usage patterns is as vital a part of the analysis process as any. If you take the time to do it properly, then you will be able to produce a more appropriate database (and code) design and you will end up - all things being equal - with happier customers.

 
team lib


Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net