Re: MATREADU/MATWRITE statements timing out

Hi Joe
Comments embedded
"Joe" <breakfast@xxxxxxxxx> wrote in message
news:Xns99F4561F11220nospamforme@xxxxxxxxxxxxxxxx
"Peter McMurray" <excalibur21@xxxxxxxxxxx> wrote in
news:cI52j.16675$CN4.11852@xxxxxxxxxxxxxxxxxxxxxxxxxx:

Hi Joe
I would be delighted to hear how you resolve the issue of deciding
that the record set is as the changer first saw it and what you do if
it is not.

In my current environment, a simple checksum suffices. Immediately
after reading the record set, a checksum is determined. Immediately
prior to the potential update, the same record set is reread and a
second checksum is determined. If the two checksums don't match, the
user is told that the record set they're attempting to update is
obsolete and they must either try again or bail out. It's not an issue,
as the application isn't terribly dynamic.

A good idea for static data although it may well upset the operators if not
implemented with considerable care. Having a long note rejected because
somebody else changed Miss to Mrs is not conducive to harmony.

Unfortunately the real world examples that I use are the
facts of life. I am baffled as to how a locked record is complicated.

The locked record itself isn't complicated, however, the description of
the two screens and associated logic is.
This I find intriguing as the two screen method represents a bare minimum in
an accounting system. The simple facts of life are that many if not all
staff need to look up data such as Stock Master, Pricing, Debtor Credit
Rating, General ledger codes, the list is practically endless, however only
a select few will ever be in a position to change and even fewer to create
these items. We originally allowed for the security to be executed at the
menu level which is simple for the client to maintain. However post
Sarbanes/Oxley this has proven inadequate and auditors have required us to
take the security to individual screen by individual user. In fact when we
did the Fixed Asset system for Vic Health & Community Services we were
required to take the security down to the field by user level. In this case
User A might be able to look at a sub set of departments but with some
fields blanked out.

Personally I have looked at many ways of solving the issue and
pessimistic locking is without doubt the safest however I am always
happy to learn a better way.

Pessimistic locking is by no means wrong; it's just impossible over a
stateless connection. But this has been more than adequately dealt with
in other posts such as Kevin's.
I would disagree that pessimistic locking is impossible over a stateless
connection however one may choose to track it with a specific lock record
rather than a READU. It is no harder to track than say a set of purchases
on Amazon. I have seen systems where this method of tracking locks was
used before the internet because the software suppliers felt that locked
records were a nuisance. To explain, one reads the record with a READU,
writes a control record say Recordid , plus user id with time and date then
Release the lock. Whenever an update is requested the control record file is
checked first.

Yes it is possible to track who has the
lock even in the sea of statelessness. By the way deletion has no
place regardless of whether one is stateful or stateless.
Peter McMurray

Now that's an interesting comment.
I am assuming that you are referring to deletion having no place in the
options. If not please disregard the comments following. Funnily enough
the last person I saw fall for the Create/Alter/Delete trick was called Joe.
He happily demonstrated a new method of programming by creating a debtor (if
I remember correctly), refreshing it then happily deleting it. The sound of
shocked intake of breath was most audible and the look of horror on the
faces of the dozen clients was quite spectacular. In fact I think the only
time I have known it topped was when a young lady demonstrated her General
Ledger offering at AWA. As things progressed the clients got more and more
edgy, finally one said but what about Year End, to which she gaily replied
Oh! that's fine the system date changes automatically and everything is
closed. Oops! back to the drawing board.
Anyway I have seen for example a stock record program with this apalling
booboo. Client sells oil, client staff member services car using said oil,
deletes stock record and recreates with the balance one drum less. Or
Operator is having an affair with one of the drivers so when a large cash
sale comes up she gives it to him, he does the delivery, she adjusts the
stock for the amount delivered and they spend the money at the casino.
These are of course straight out theft however just as much damage can be
done to the integrity of a database by deleting a Master File Reference
after it has been used as a cross reference. It is the one thing that is
expressly blocked in an SQL database that we can accidentally do in an MV
database. Freedom requires careful use.
Peter McMurray


"Joe" <dinner@xxxxxxx> wrote in message
news:Xns99F2BB4C8939Enospamforme@xxxxxxxxxxxxxxxx
"Excalibur" <excalibur21@xxxxxxxxxxx> wrote in
news:S912j.16590$CN4.5448@xxxxxxxxxxxxxxxxxxxxxxxxxx:


"Kevin Powick" <kpowick@xxxxxxxxx> wrote in message
news:5d233461-dac0-478e-a965-
af8b540e25fc@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
.
On Nov 23, 4:36 am, Michael Findlay <mich...@xxxxxxxxxxx> wrote:

Hi Martin

I have to disagree. Locking is essential to avoid losing
transactions.

He's not saying that locks shouldn't be used. Just that they
shouldn't be used across user interaction. Locks should only be
taken and held very briefly, usually just prior to a write
operation.

I won't go into all the details, which inevitably lead to religious
debates (Been there. Done that), but you can easily code
applications, such as a product maintenance screen, so that one
does not lock the product record the entire time a user has the
record called-up on the screen.
Hi
Anything can be done but some things should never be done. This is
one of them. A master file update should always be atomic. That is
one change must be completed before another is started. This
requires that the record be locked for the entire time that the
person making the change has the item on the screen. Otherwise you
wind up with a garble of changes. Of course you could make yourself
very unpopular by checking that the record has not been changed by
someone else in the meantime and rejecting the first person's
carefully composed entry when they go to file it. That emphasises
the point that every master file requires at least two separate
screens, a CREATE and ALTER that is of a high security level, LOCKS
the record and not available to all, and an ENQUIRY screen that is
generally available. Please do not even think about that
programmer's favourite DELETE it is extremely unlikely that this
would ever be appropriate on a running system, and yes I have seen a
STOCK delete and a DEBTOR MASTER delete on a system. Peter McMurray

Unnecessarily convoluted and impractical.

Data integrity demands only that the record set (note the word 'set')
should be created/updated/deleted only if the entire set is the same
at the point of disk update as it was at the point of the initial
read.

Pessimistic locking doesn't happen with a stateless UI.


If anything, time limits should be
applied to items locked on idle, and when timed out, should blow
them off the record and allow others to access and update.

No. Just No. More potential problems than I care to imagine.
Wasn't it an arbitrary time limit on locks that was the impetus for
this entire thread?

--
Kevin Powick









.

Relevant Pages

  • Re: MATREADU/MATWRITE statements timing out
    ... record set is as the changer first saw it and what you do if it is not. ... He's not saying that locks shouldn't be used. ... file requires at least two separate screens, ...
    (comp.databases.pick)
  • Re: MATREADU/MATWRITE statements timing out
    ... after reading the record set, ... place regardless of whether one is stateful or stateless. ... He's not saying that locks shouldn't be used. ... screens, a CREATE and ALTER that is of a high security level, LOCKS ...
    (comp.databases.pick)
  • Re: MATREADU/MATWRITE statements timing out
    ... after reading the record set, ... mismatched checksums from an optimistic lock. ... of the two screens and associated logic is. ... By the way deletion has no ...
    (comp.databases.pick)
  • Re: MATREADU/MATWRITE statements timing out
    ... He's not saying that locks shouldn't be used. ... A master file update should always be atomic. ... separate screens, a CREATE and ALTER that is of a high security level, LOCKS ... running system, and yes I have seen a STOCK delete and a DEBTOR MASTER ...
    (comp.databases.pick)