Transaction processing & locking in JDE

“To lock or not to lock” – that is the question!

To say that JDE does not do any locking is not quite true…  It calls the database functions that do the locking, so it does have a lot of responsibility in this.

Let’s look at a simple scenario, SOE and stock commitment…  Wait, that is not simple at all – but it’s understandable!

We have a person entering a sales order.  We know that this goes across a number of screens in JDE -

We know that this adds records to a number of tables

We want integrity in our database

We do not use fancy things like foreign keys – we use application logic to enforce transactional boundaries and we use database agnostic manual commits.

There are two things at play here though…  Locking and transaction processing…  They work together but are not necessarily the same time.

Locking

Let’s look at locking by itself.  I’m using a lot of information from here https://docs.oracle.com/cd/E24705_01/doc.91/e24234/record_locking.htm#EOTBF00095

I’m also reaching back to when I was programming and before JDE in C…  dark repressed memories.

In the scenario of SOE, we don’t really want to lock anything, as they are really new lines being added to a bunch of tables.

We do however want to lock the inventory for the orders so that there is enough to fulfill the order – so this is the lock we are talking about

We want to select for update over the F41021 (?) so ensure that we have enough inventory and then update when the order is committed.

JD Edwards does locking in two ways, optimistic and pessimistic.

optimistic is half glass full locking.  This is where you do not expect any problems, but if there are – you have a solution.  This involves manually checking timestamps and things…  If there is a situation, deal with it then.

pessimistic is where you ensure that there is never going to be a conflict – coz you’ve prevented it in the first place.  You are selecting the record for update (i.e. selecting the current stock levels)  and then you update when you are ready and the lock goes away.  Note that this is different to including the update in a transaction (with your inserts into the F4201 and F4211 – as they are naturally going to lock the record that is in the transaction boundary)…  See how they are the same but different.

Transaction Processing.

As demonstrated above, TP actually enforces pessimistic locking at the database level.  If you are doing an update as part of a transaction, the database is going to lock that record until the commit (or rollback) is issued, this is the purpose of TP and Multi Statement Transactions (MST).  Hopefully your database has row level locking and then you are not going to cause cascading of locks to pages and then tables… arrgghhh.

Transaction processing is all about manual commits.  It’s a series of transactions to databases with commit points, any errors will rollback the entire transaction – not just a single statement. handy.

Note that this DOES get more difficult with JDE.  We have this awesome thing called OCM which allows us to distribute logic and data.  Different logic locations can enforce a single transaction (using native DB tech like DTP Distributed Transaction Processing – I hope), neither will different data sources (event if they point to the same database).  This is because JDE opens the database connection and sets the commitment mode to manual or automatic.  It cannot programmatically enforce these boundaries with non standard logic and data setups.  This does get pretty complex under the scenes – stay standard and you will not have a problem.

Note that you also must look out for a BSFN (that runs on the enterprise server locking a record) and then having java code (or JDBC) reading or manipulating the same data – you are going to create some problems if they are not part of the single transaction. 

Want to know all of the areas in JDE that use TP?

https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1208208.1&id=1320968.1

Some of your purists out there might be surprised by the small number of listed programs (both batch and interactive).

You might be asking yourself about dirty reads

Not magazines passed around the boarding house… NO!  different dirty reads.

I’m talking about the above scenario when you are updating and inserting records in the database as a single transaction – do you allow people to see the uncommited version of the data or the commited version? Lets be honest for OLTP, you should only see committed data.  (You can see that massively parallel databases and noSQL databases might do something else).

This introduces the concept of isolation level

1. Level 1: READ COMMITTED
   • This is default setting in SQL Server to prevent SQL from dirty read (which reads dirty page which is (not-yet) committed data)
2. Level 0: READ UNCOMMITTED
   • This is to read uncommitted data that is, intentionally to have dirty read

 

Summary:

There seems to be enough tools and examples of TP and locking in the existing JD Edwards code and base code for you to be able to create some tight code, whether you are half glass full (optimistic) or half glass empty (pessimistic).  Remember that you need to manually do this locking if you are using technology like BSSV by using The startPublishedMethod, finishPublishedMethod, and close methods.  If you are using AIS, you are going to benefit from inheriting the TP functionality from forms that you are calling!  Nice.

Note that if you are manually calling BSFN’s – all at your own peril!