RE: Transaction processing - COMMIT/ROLLBACK


RE: Transaction processing - COMMIT/ROLLBACK

From: "Richard Jackson"
<[email protected]>
To: <[email protected]>
Subject: RE: Transaction processing - COMMIT/ROLLBACK
Date: Wed, 1 Nov 2000 19:33:25 -0700
I wrote this message over the weekend and then sat on
it. It was a response
to some questions asked last week and attached to the
bottom of this note.
I sent it off to someone for review, he thought that I
should post it here.
I am not very happy with the way that this is written
but it discusses many
parts of the problem and raises a number of questions
that have to be
considered. I would like to make it better but I no
longer have the time to
spend. Please forgive the fragmentary thoughts and poor
Perhaps we can improve some of the explanations thought


The default commitment control condition is off. The
question is how to
turn it on and what happens if you do turn it on.

The problems that you mention will not be addressed by
transactions. I can
create sales order details without a header then commit
the order and
everything works just perfectly - except there is no
F4201 record. There is
nothing in the definition of transaction that says the
the application has
to do the right thing before commit - only that whatever
it does can be
rolled back if you so desire.

Suppose that you assert a referential integrity rule in
your production
database, "F4211 cannot be written unless there is a
F4201 record with
matching order type and order number". I think that you
will find that the
application writes the details then writes the header -
a perfectly
reasonable state of affairs. Asserting this integrity
rule will make a
working application stop working. You could take the
position that the
application does it backwards. Taking a contrary
position, I could assert
that I must write the details first because the header
contains a total
weight or summary cost. You could insist that I write
the header first then
update it later. I would point out that requires an
insert and a update to
the record thus creating a potential performance issue
merely to allow your
integrity rule to work right. The point of this
paragraph is that
referential integrity has to be considered during
application design.
Depending on specific cases, it can be difficult or
impossible to retrofit
later and that retrofitting may have undesirable

What about commitment control? Same thing. "Commitment
control" means that
if I execute a rollback instead of a commit, the
database state is (a)
stable and (b) contains no effect of the uncommitted (or
transaction. Let's look at that. In the case of sales
order, a rollback
means (among other things not mentioned here) that the
sales order document
number taken from the F0002 or F00021 file (next number
or advanced next
number) is not updated, that the inventory levels are
not effected by the
transaction (file F41021), no sales order header (F4201)
or sales order
details (F4211) are created, no manufacturing work order
or parts are
created (F4801 or F3111), no advanced warehouse details
are created (F4611),
no sales order detail history is created (F42199), and
inventory cardex is
not updated (F4111).

All these are things that must be cached or locks must
be held throughout
the transaction. What does that mean? Let's look at
one way to implement
the sales order program and how that interacts with
commitment control.

Here is a way to implement the program logic - no
thought is given to
commit. Allocate a next number (F0002). Edit each
detail line, reduce
inventory for each valid item (F41021) and write a
cardex record (F4111),
then write it to the F4211. When the order is
completed, create works
orders for manufactured or assembled items (F4801 and
F3111) and write an
order header (F4201). This order does not include
advanced warehouse
processing and it is the original entry so there are no
F42199 records -

Let's assume that we want to use commitment control for
this sales order
process. That means that while this order is being
entered, no one else can
enter a sales order. WHAT?! Sure. Because of what
"commitment control
rollback" means, I have to be able to roll back the next
number. If this
control flow is followed, next number is allocated right
at the beginning so
I have to hold a lock on it until the user either
commits or rolls back. If
I have it, then no one else can get to it so sales order
is "one order
entered at a time". I am actually holding a lock on the
one F0002 row for
system 42 in the file containing all 10 next numbers for
sales order so no
other system 42 application can get to that row during
the entry of this
order. Is there some alternative to locking the F0002
row? Nothing simple.
I'll look at some other considerations for this below.

Certainly single-user sales order is bad enough but does
anything else go

Imagine the Far Flung Fur and Feather Fez Company. At
the moment, they have
two order takers working the telephones and precisely
one Fendi fox fur fez
with the pheasant feather and florescent flaps for only
five thousand French
Francs. There are two customers on the phone and both
of them desperately
want that fox fur fez. Since FFF&FF actually is "far
flung", the order
takers are on opposite sides of the world - in other
words, they can't talk
to each other. Suppose the sales order program checks
inventory during the
order (is there one available?) but doesn't reduce
quantity on hand until
the stock is picked from the shelf and shipped. Both
customer orders will
be accepted. Both orders will be sent to the warehouse
for picking but, lo
and behold, there is only one Fendi fox fur fez with the
pheasant feather
and florescent flaps. Both orders were accepted within
the rules of the
system but one of the orders will be filled and the
other one cannot be.
Frederick Frobischer, the CFO for FFF&FF contacts the
disappointed customer
and tries to convince him that he really wanted the faux
fur fez for fifty
French Francs. Since the customer had his heart set on
the Fendi fox fur
fez and considers the faux fur fez a very feeble
substitute, you can imagine
his response to Mr. Frobischer. This missing software
functionality creates
a real-world customer satisfaction opportunity.

How to fix this problem? The program could fetch the
inventory quantity on
hand record for all Fendi fox fur fezes and hold it with
a lock until the
order is picked. That would insure that no one else
could sell the same
item being sold on the current order. But suppose that
FFF&FF had five
Fendi fox fur fezes and that they are very popular. If
the lock were held
until the fez was picked, other interested customers
would get tired of
waiting and would go down the street to the French
Foreign Fez Firm and we
would lose the business. The lock is too "broad" and it
is held too long.
There are two solutions - "soft-commit" and commitment

JDE uses an excellent high-volume technique to
soft-commit inventory during
order entry. If the order line says 3 "fake fezes", the
application fetches
the "fake fez" row in the F41021 file (for update, so
there is a lock)
deducts 3 from the quantity on-hand and adds 3 to the
"in an open order"
field, updates the record and releases the lock. If the
customer decides
that they really want 5 "fake fezes", the application
knows that the order
quantity used to be 3 so it fetches the "fake fez"
F41021 row (with a lock)
reduces "in an open order" by 3, adds 3 into the
quantity on-hand column
then deducts 5 from quantity on-hand, adds 5 to the "in
an open order"
field, updates the record and releases the lock. If the
application reduces
on-hand below zero and the processing option is set
correctly, the order
taker will be notified. This technique allows two order
takers to sell from
the same inventory without selling the same physical
items. The real (or
"hard") commit can take place in several different
places depending on
processing option settings but it should probably be
delayed until the stock
is picked. In other words, hard commit should probably
not occur until pick
or ship confirm.

Why go to all this trouble to talk about this excellent
high-volume updating
technique? Because I can practically solve the next
number issue but, if
the F41021 file participates in the transaction and can
be rolled back using
database facilities, there is no reason to have the soft
functionality. Since the records have been updated by
my transaction, I
hold a lock on them until I either commit or roll back
and no one else CAN
update them. In other words, I can have commitment
control for the F41021
or I can have soft-commit for the F41021 but I do not
see how to have both.

How to address the next number file lock? The obvious
technique is to just
forget about it. In other words, there is no sales
order with that number
and the number series has a gap. This is certainly fine
for most cases.
Unfortunately, France (and maybe Spain) has a rule that
has the force of
national law that order numbers have to be a continuous
series without gaps
or the company has to be able to explain what happened
to every missing
number. This is a tax and auditing rule. I don't know
what other software
companies do about this. I would love to know. In the
past, it was a kind
of anchor that JDE applications swing around but remain
attached to.

For the sake of argument, let us say that we just forget
about the lost next
number problem. Let us also say that we accept the
existing JDE technique
for soft commit - the application somehow deals with
issues in the
quantity-on-hand field. How do we deal with what is
left? The obvious
method is to cache everything until the customer accepts
the order then
update everything all at once. We can wait until the
order is accepted then
start a transaction, update everything, then commit, and
the database is in
good shape.

This is pretty cool! Is there anything wrong with this?
Well, I am a
performance guy so I will always whine about something.
In this case, the
database is tied up pretty tight while this order is
being generated. If
three order takers accept orders at the same time and if
it takes 10 seconds
to create and commit an order, the unlucky last person
will wait for 30
seconds for the system to accept their order. And it
will take 30 seconds
no matter how many CPUs the database server machine has
- it isn't scalable.
In other words, the tasks are serial by definition and
cannot be
parallelized unless you alter the size of the
transaction - "commit by line"
instead of "commit by order". In this case, that means
that the system
cannot accept more than 360 orders per hour (3,600
seconds per hour divided
by 10 seconds per order). This assumes 100 percent
resource utilization
and, based on queuing theory, that means that some of
those users will wait
for quite a while for access to the resource. I would
guess that the
practical limit would be more like 240 per hour. Isn't
that pretty good?
It depends on how many orders you have to handle at peak
times. If it takes
10 seconds to update and commit an order and you must
handle more than 360
orders per hour at the peak, this becomes an issue of
the first class.
There is no hardware solution.

Okay, where are we. If you take just any old order
entry/update process and
try to wrap commit control around it, there are things
to think about.

When you start to adapt to the consequences of
commitment control, you end
up having to choose between manual rollback ("do it
yourself" commitment
control) and automatic rollback ("done by the database")
for files like
F41021 and you have to decide how to cache all of this
stuff until the end
of the transaction. OneWorld uses the JDECache
functions and the UI files.
JDECache contents on the server aren't visible to the
client machines and
vice versa so when you assign business functions to
machines using the OCM,
all the BSFNs that share cache contents have to be on
the same machine or
interesting things may occur. This issue generates a
short cascade of
technical consequences. For example, how does one know
which BSFNs share
cache contents? How do the various valid combinations
effect network
bandwidth demand? Such questions are beyond the scope
of this little
exposition. OneWorld has had minor trouble with the UI
files since the
beginning. The key symptom has been records left in the
UI files when there
are no open transactions. Considering how long that
this has gone on, I
conclude that it must be hard to fix.

I said above, "All these are things that must be cached
or locks must be
held throughout the transaction." I hope that sentence
is clearer now. I
have tried to explain why why one cannot satisfactorily
enable commitment
control by just "flipping the switch".

The following things need to be added to this

1. how does transaction support work today? by release
2. how does transaction support work in a
multiple-database image
3. what are the requirements to make it work?
4. how does one determine how much concurrency that one
can get?
5. how does one tune a transaction environment?

I hope that all of this begins to explain why
"commitment control" (or
"transaction processing" or whatever one wants to call
all of this) is
difficult. One cannot just turn it on. There is a lot
more to it than
Richard Jackson
mailto:[email protected]
Voice: 1 (303) 808-8058
Fax: 1 (303) 663-4325
RE: Transaction processing - COMMIT/ROLLBACK

This is very good Sir. Your reasoning is sound and refreshing.

FWIW, I am not a big OneWorld fan, but it is growing on me. We've been live
since 10/2 (just went through our first monthend) and have been surprised
with how smooth---knock on wood---it is going. If this continues, I'll be
fully assimilated by the Borg this time next year :cool:>

I believe the integrity issues you discuss here probably date back many
years. Surely JDE did not radically redesign their core database shema to
accommodate a client/server environment. I imagine that they did the prudent
thing---reinvent as little as possible.

The sales order files are not optimally designed by any means. Totals do not
belong there. Having said that though, issues such as those are rarely
resolved without a performance price---all of us have probably broken the
rules for performance or simplicity sake. During the time these tables were
designed, the arguments for their design probably outweighed the
alternative. And as the system became larger & more complex, changing it
could not be justified.

I've always believed too that the professor types who tell us how to
normalize every relation down to the Nth level, backed up by relational
integrity & transaction control, never worked in the real world. We simply
still do not have the horsepower available to process those kinds of
databases. Further, can you imagine how s-l-o-w any ERP system would run if
every multi-table transaction was under commitment control?! Not me Tim.

So where are we? Probably as good as can be expected here in 2000. We'll
have to wait for next generation of ideas to solve our issues. Meanwhile,
just fix the data when systems break, treat the software with kid gloves,
and buy that OneWorld prayer book from

If you have any questions or concerns, feel free to e-mail or call.

Wynn Osborne

Senior Programmer/Analyst
Flair Corp