Locking Code

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Oct-2002 >

Locking Code


Peter Vogel


Last month, Peter Vogel examined the issues around locking

records and concluded that, most of the time, you don’t want

to lock your records. In this article, he reviews that discussion

and then shows the code that you can use to avoid

record locking.


LAST month I went into tedious detail on when you

should and shouldn’t lock records. Specifically, I

looked at when you needed to lock records early in

the process. “Early in the process” means that records are

locked either when they are first read or when the user

first indicates that they intend to make a change to the

record. Record locking early in the process, usually

referred to as ‘pessimistic locking,’ should be avoided

wherever possible, as it reduces your application’s

scalability—its ability to handle many users. As you’ll see,

writing code that avoids record locking also speed up

your updates.


I claimed that record locking should only be done for

changes when it’s possible that:

• two users will update a record at the same time.

• a user will update a field that’s related to a second

field in the same record.

• the user won’t update the second field in the

same record.

• there’s a business rule that would prevent the

update to the first field based on data in the second

field (in other words, some combinations of data in

the two fields in the same record are forbidden); or

• you’re updating every field in a record, even if it

isn’t changed.


For deletes, records only needed to be locked if there

were one or more fields in the record that would prevent a

record from being deleted (that is, there’s a business rule

that prevents a record in a specific state related to fields in

the record from being deleted).


For multiple record updates, I encouraged you to

use transactions to manage updating several records

rather than using record locking with all its convoluted

code. As you’ll see, the code that I’ll show you here is

easily extended to handle multiple record updates

through transactions.


In this article, I’m going to concentrate on the code

necessary for implementing the SQL commands that will

update only changed fields. See the sidebar “Access

Object Locking” for what you can do in this area in databound

Access forms.


Before looking at these SQL and code-based solutions,

I should point out that if your back-end database supports

triggers, then you should consider using those to

avoid record locking. As I said before, locking is only

required if there’s a business rule specifying invalid field

combinations (in an update) or deleting a record in a

specific state. If your database supports triggers it might

be a better idea to incorporate these business rules into

server-side trigger code.


One final caveat: in the following code I’ve assumed

that all the fields in my recordsets are string fields. For

numeric and date fields you’ll need to add code to handle

placing the right delimiters around the data. I’ve

just assumed that everything should be enclosed in

single quotes.


Insert and deletes

I’ll begin with the simplest cases: inserting a new record

and deleting an existing record. Inserts can be ignored—

there’s no existing record to lock. Deletes, however, may

require a record to be locked if it’s possible that a field

that the user doesn’t update can prevent the record from

being deleted.


The scenario that most programmers have in mind

when locking for delete is this one:

1. User 1 retrieves the record and decides to delete it.

2. User 2 retrieves the same record and updates the

DoNotDelete field.

3. User 2 saves the changes, gets no message, and is

convinced that the record is protected.

4. User 1 deletes the record.


I don’t consider it a problem if user 2 saves the

changes after user 1 deletes the record. I have two reasons

for this devil-may-care attitude:

• First, it’s a fact of life that the person who saves their

record first, wins. If user 1 saved on Monday and

user 2 tried entering the data to prevent the update

on Tuesday, we wouldn’t feel that anything has

gone wrong.

• More importantly, user 2 will get a message that

the change wasn’t made because the record had

been deleted.


This scenario can be handled without record locking.

The typical Delete statement in this scenario would ...


Read More Here:

Locking Code