Re: RFC: Preventing double-updates

["David Bandel" <..hidden..>]

On 7/13/07, Chris Travers <..hidden..> wrote:
> Hi all;
>
> One of the problems that can occur in concurrent use, especially with
> payment processing is that two users could indeed run the same
> payments, print duplicate checks, etc.  This is a risk relating to
> paying vendors, and could be a means of embezzlement because a
> dublicate-looking check is printed without a record in the db.
>
> SQL-Ledger 2.8.0 and higher solves this problem with a "semaphore"
> table which essentially provides locking capabilities through the
> appliction.  This has been the source of a lot of problems in
> SQL-Ledger in part because of inherent problems in determining when a
> given lock should time out.  I propose a different solution.
>
> Instead of having one master "lock" table, we add locking columns to
> affected tables.  This field references the session id of the lock
> (with an on delete set null action).  The session table will be
> expanded to include a timeout value in seconds.  The first thing we do
> when we load a page is to delete stale sessions.  Then we can just
> check locks for the rest of the time the page is loading.
>
> Any feedback would be appreciated.

Actually, I would think a row-level trigger would be more appropriate.

Assumptions:
1.  Checks are sequentially numbered.
2.  Checks will only ever be printed once.

If the above is true, after AP is recorded, whoever can print checks
starts the process, either one at a time or in batch.  As a check is
printed, a row-level trigger fires to prevent access to that row (and
check number).  A second trigger fires later to update the table with
a boolean "printed" and the row is unlocked, but subsequent printing
is "disallowed" (obviously at the application level).   The trigger
could update the row with time/date of printing and user instead of
the boolean "printed".

Obviously, either your suggestion or mine would require any app
accessing the db to honor the locking mechanism, but that would be
made easier (I would think) using triggers to lock/unlock rows and
record pertinent accesses.  A trigger would also be dynamic and could
check for appropriate AP/vendor entries.  You could also provide a
"next check number" on the print screen for the operator as a
double-check.

The use of session tables sounds like unnecessary overhead.

Ciao,

David A. Bandel
--
Focus on the dream, not the competition.
           - Nemesis Air Racing Team motto