IDS Forum

Re: locking error (problem)

Posted By: Art S. Kagel (Oninit LLC)
Date: Wednesday, 6 February 2008, at 10:44 a.m.

In Response To: Re: locking error (problem) (DANNY DE KOSTER)

DANNY DE KOSTER wrote:
> Art,
>
> First of all thanks for your comment....off course all the others also manny
> thanks.
>
> What I have done now.....
>
> I do first of all an insert....chech on status....eventually do an update.
> Then the first and second user can work normally.
> But a bit later in my program I did an update again...there is still fails.
>
> I now I need to minimize a transaction but in this case the user needs to
> control an invoice with changes of their price, etc..... So it can take a time
> before they finish there job because at the same time they need to pick up the
> phone and help there customers, etc...
> They needed the possibility at the end of the control not to accept there
> modifications. So I thought to use rollback work to not accept there
> modifications.
>
> Any other ideas? Or working with tempory tables.
>

No. You need to modify your applications to use what's known as an
optimistic locking protocol. I've posted something like this once or
thrice before, but here are the steps:

1- fetch the data, if it exists, without locking (so no FOR UPDATE
clause in the SELECT).
2- present the fetched data or a blank new record to the user for
modification IN MEMORY to a copy of the original record ONLY - it's
critical to keep an unmodified copy of the data.
3- ONLY after all data entry or modifications to all parent and child
records have been made, then refetch the record using the keys on screen
- so if it's a new record you just pretend it already existed but was
all blank - into a separate memory structure. This fetch is made inside
a transaction with a lock - so it should include a FOR UPDATE clause.
4- Compare the refetched data to the original unmodified data, and if
there have been no changes by another user - that's the optimism part -
you optimistically assume that only one user will be working on any
record at a time - then you update or insert the row or rows and COMMIT
WORK immediately. Rows are only locked momentarily and transactions are
never delayed while the user answers the phone, goes to the rest room,
or out to lunch. If the records do not match, then you ROLLBACK the
transaction freeing the locks even faster and tell the user that someone
else has modified the record he/she was trying to work on.

Now, to simplify all this and ease both the memory needed to handle the
three copies of the data and the time spend verifying that there hasn't
been an interfering update, you can use a timestamp column in each
table, or at least in the parent table, (defined as YEAR TO FRACTION(3)
and with USEOSTIME turned on in the ONCONFIG file) that is always
updated when the record (or one of its children) is modified. This can
be done with a DEFAULT CURRENT YEAR TO FRACTION(3) option on the column
so that the column is auto-populated at INSERT time and by an UPDATE
TRIGGER on the table and/or any of it's children that will update the
timestamp column in the row (or the parent row) when one of those
records is modified. Then you ONLY have to compare the original
timestamp to the one you refetch just before attempting the updates.
This is guaranteed to work EVEN if users modify the rows outside of your
application since the timestamps are being maintained by the engine.

Optimistic locking works because in the real world of business, it is
rare for more than one user to modify the same records at the same
time. It might happen if say two contacts at a customer site contacted
your company at the same instant both wanting to modify the same order
at the same moment, so the software handles it, but it is safe to assume
that this will be rare enough to not interfere with the normal daily
work flow.

Art S. Kagel
> Danny
>

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================

Messages In This Thread

• locking error (problem)
  DANNY DE KOSTER -- Wednesday, 6 February 2008, at 7:47 a.m.
  • Re: locking error (problem)
    Rob Prop -- Wednesday, 6 February 2008, at 8:07 a.m.
    • Re: locking error (problem)
      DANNY DE KOSTER -- Wednesday, 6 February 2008, at 8:24 a.m.
      • RE: Re: locking error (problem)
        Marcus Haarmann -- Wednesday, 6 February 2008, at 8:35 a.m.
        • Re: RE: Re: locking error (problem)
          DANNY DE KOSTER -- Wednesday, 6 February 2008, at 8:47 a.m.
          • Re: locking error (problem)
            Art S. Kagel (Oninit LLC) -- Wednesday, 6 February 2008, at 9:07 a.m.
            • Re: locking error (problem)
              DANNY DE KOSTER -- Wednesday, 6 February 2008, at 9:41 a.m.
              • Re: locking error (problem)
                Rob Prop -- Wednesday, 6 February 2008, at 9:54 a.m.
              • Re: locking error (problem)
                Keith Simmons -- Wednesday, 6 February 2008, at 10:21 a.m.
                • Re: locking error (problem)
                  DANNY DE KOSTER -- Wednesday, 6 February 2008, at 10:27 a.m.
                  • Re: locking error (problem)
                    Keith Simmons -- Wednesday, 6 February 2008, at 11:05 a.m.
              • Re: locking error (problem)
                Art S. Kagel (Oninit LLC) -- Wednesday, 6 February 2008, at 10:44 a.m.
                • Re: locking error (problem)
                  DANNY DE KOSTER -- Wednesday, 6 February 2008, at 10:55 a.m.
                  • Re: locking error (problem)
                    Keith Simmons -- Wednesday, 6 February 2008, at 11:02 a.m.
                    • Re: locking error (problem)
                      DANNY DE KOSTER -- Wednesday, 6 February 2008, at 11:11 a.m.
      • Re: locking error (problem)
        Keith Simmons -- Wednesday, 6 February 2008, at 8:43 a.m.
        • Re: locking error (problem)
          DANNY DE KOSTER -- Wednesday, 6 February 2008, at 8:55 a.m.
  • Re: locking error (problem)
    Keith Simmons -- Wednesday, 6 February 2008, at 8:12 a.m.
  • RE: locking error (problem)
    Marcus Haarmann -- Wednesday, 6 February 2008, at 8:24 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.