Locking scheme for ODBC linked tables? Access + SQL Server

[kiln]

Sorry for posting in three newsgroups, but all seem appropriate and the
topic seems to have sparse coverage in general.

Using Access 2003 linked tables to SQL Server 2000 via ODBC. What's the
simplest way to implement explicit control over record locking? Even
with a timestamp in the table, letting Access' built in form record
locking warnings seem a bit tardy.

Probably my ideal scenario is something like: A user finds a record in a
form, which is not editable by default. They click on a button and edits
are enabled, unless someone beat them to it. If someone is already
editing the record a notification would appear. I don't think that this
would be a major programming feat, but it would be interesting to see
what others have come up with.

 

[Sylvain Lafontaine]

Well, this newsgroup is about ADP project and not ODBC linked tables, so
it's not appropriate.

 

[Guest]

Hi Sylvain :~)

You will find that if you edit the "Newsgroups" list in your reply
to field, you can limit your responses to only the groups in which
you wish the reply to appear.

Kiln:

The traditional/original Access method is to use a Separate
Form for edit/update. One form for finding and selecting
the record (read only, no locks), another form for edit/update
of the specific record. (pessimistic locking).

It works well, and is really easy to implement.

(david)

 

[kiln]

I posted to the adp list because I figured the level of experise with
sql server and access, including odbc, was high there. Not a perfectly
appropriate ng by name, but probably the right people.

Sylvian, I've read around a good bit and there are many comments from
folks like Mary Chipman to the effect that pessimistic locking can
create a massive performance hit and should be avoided. What has been
your experience? Also, how do you implement pessimistic with odbc?
According to help and what I've read, the locks setting on Access forms
is ignored if using an odbc data source. It's always "no locks". I've
spent a couple hours reading on this subject and haven't come up with a
lot of specifics that sound like they'd work, mostly things that don't
work. I've also experimented, using two forms in diff dbs that pull odbc
data from the same db and record, and the behavior I see seems to be
like the regular jet optimistic locking.

Just using a separate form for updating wouldn't seem to provide any
inherant solution. But however you've come to a solution, if you could
provide a bit of detail, that would be useful. Thanks

 

[Sylvain Lafontaine]

The topic of optimistic and pessimistic locking could easily be the subject
of a whole book, so I won't try to cover this topic here. However, here a
few points:

1- Like you said, Access doesn't use pessimistic locking (if I remember
correctly) when linking to a SQL-Server; so you have to use unbound forms if
you want to use pessimistic locking. This is true for both ODBC linked
tables and ADP projects.

I think that the commentary of David about using a second form for
pessimistic locking is for when you are using JET as the backend and not
SQL-Server. (But as always, I might be wrong.)

2- Like Mary C. has said, using pessimistic locking can create some massive
performance hit so instead of using it, you should replace it with some
another scheme; for example the one that you have mentionned. You should
also take into consideration the possibility that someone may *forget* to
release the lock after a certain amount of time and provide a mecanism to
override it.

3- Finally, you should ask yourself if you really need to use pessimistic
locking. Myself, I can't remember the last time I used it in a project and
I'm perfectly happy with the optimistic locking of Access since many years.
For example, in a sport software that I'm now in the process of creating, I
don't see why two people should (try to) update the statistics for the same
team and the same game at the same time. Doing so will probably be the
result of some other error by these two people.

There is the very light possibility that someone may try to change some
detail for a player (for example his permanent code) while another will try
to change some other detail (his telephone number?) at the same time;
however, if this happens one time in the next 10 years, all they will have
to do will be for one of these two persons to reenter the value a second
time. Not a big deal in my opinion. The only thing important important to
me is that they got an error message from Access.

In some occasions, pessimistic locking is required; however and too
often, it's only the search of *perfection* by the programmer without any
real need behind it.

 

[kiln]

In this case, the client wants user B to be told the rec is already
being edited by user A before user B starts working on the record. They
have opt locking in place and have had too many collisions, want an
alternate route.

I can code something for this, but was rather hoping that someone who
has done it before could share some specfics. I think it could be a bit
tricky to implement. It'd be better to deploy a scheme that is mature.

 

[david epsom dot com dot au]

Before opening the Edit form, check the user ID on the record.

When you open the Edit form, you write the user ID to the record.

When you close the edit form, you clear the user ID from the record.

You need to have a separate 'clear locks' function in case
you loose a connection.

If you have a very large number of records, you do it by writing
the locks to a separate table (so you don't waste the space of
the lock field on every record.)

If you have incoming records, you do it by moving the incoming
record out of the incoming table and into the edit table when
selected by a user.

Actually locking the record (which can be done using a transaction)
is a bit tricky, and you still have to do special tests to check
the lock status because by default Access will want to 'try again
later' if the record is locked.

(david)

 

[kiln]

Thanks for that input. I'd like to hear other approaches and any gory
detail that might help with the actual implementations with any approach
(ie what are the issues and solutions re david's last sentance)