I have a distributed application with a central database.
Fred uses the application to modify Employee Bob. Bob's records are read
from the database, and Fred eventually makes the modification.
Jim uses the application to modify Employee Bob. Just after Bob's records
are read by Fred, they are read by Jim. The data that is read is required
to make the correct decision regarding the edit.
If the table is locked on the initial read, then another user may be waiting
some time. If the table is locked on the update, then the data will need to
be re-read and locked before the update. What is the usual approach? I'm
new to concurrent updates.

--
Mike

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

First, this has little to do with how an application is distributed.
This same problem exists when both Fred and Jim are using terminal on
the same host machine.

Second, if you are locking TABLES, then this issue is bigger than two
managers both trying to touch Bob's records. With table locks, Jim is
blocked even when he want to edit Judy's records.

SO don't do table locks! Page locks are nearly as bad, too.

Now you're back to the issue of both Fred and Jim wanting to touch the
same record. I would argue this to be a business decision. If the
records were all paper, you would not have a problem. If Fred pulled
Bob's paper file, Jim wouldn't even be able to find it. Jim has to
wait, but meanwhile he can work on Judy's file.

Okay, you still want both managers to hack away at poor Bob's file.
You still have the issue of old data, but one way might be to have a
separate table of updates (a work in process WIP queue). Both get to
read Bob's records as they are now. Both insert changes into the WIP
(providing an audit trail as a benefit) and commit their changes.
Eventually a background process handles the WIP queue and applies the
updates and flagging the WIP records as completed.

There are a lot of scenarios that you need to consider. (What if Fred
decides to NOT commit his change?) But a WIP queue will help.

But like I suggested, go back to the business analyst and ask why both
managers are working on the same personel record. Make sure you
understand the real problem to be solved.

Good luck. With table locks, you're going to need it!
Ed

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

This depends on your database and environment. (Of course, you should
lock the record and not the table if at all possible).

You want to look at either pessimistic locking or optimistic locking.

With pessimistic locking you will need the support of the database. It
will allow a user to read (and lock) a record in the database. The
DBMS will then prevent other users from setting a lock on that record.
Preferably you can notify the users of the lock rather than waiting or
simply 'quitting'.

Optimistic locking doesn't require database support. Primarily you
allow any/all users to read records. At the time they go to update you
compare the record originally read with what exists now. If they are
the same commit changes, if they are different then you have to
determine whether to commit the changes or not. This method has become
much more popular lately because of non-persistent connections to the
database (ie. web).

hth
Colin