Record Locking - Issue from a bill of materials

[AndrewMozley]

A screen prompts the user for a product code which is being manufactured from a list of components. The screen checks to see if there is sufficient stock of each of the components. If there is, it offers the user the chance to post the transaction; this would increase the stock of the finished product and reduce the stock of components.

In due course (on the same screen) the user decides to go ahead with the manufacture. The screen then does several things, including reducing the stock Qty on each component stock record.

I would like the screen to be able to meet its commitments; so if it has told the user that there is sufficient stock, it would be nice if this is still the case when the user clicks on ‘Go ahead’

What is the best way of achieving this - perhaps by locking the relevant stock records? How do other developers handle this?

Thanks. Andrew

 

[Olaf Doschke]

Infinite supplies = no problem.

You have about the same problem with seat reservations, shopping carts and inventory, etc. Depends on your data model, but locking only helps with one record per item, and you rather have a count value inside a stock record.

Well, simulate the real shopping cart, you have items in a transition, taken from stock but not yet sold. So either you put a field with a count of on hold items to the stock items or have a separate new table for that. And I mean table, not just a cursor. Think of the worst case the client reduces stock and puts it into a cursor, then crashes and you have a wrong stock.

Having the two counts in the same table you could address them with the same UPDATE statement:

* Allocate items
Update Components Set Stock=Stock-m.lnCountNeededitems, Allocated=Allocated+m.lnCountNeededitems Where ID = m.someID
* Check if allocation went OK:
Select Stock From Components Where ID = m.someID Into Cursor crsCheckAllocation
If crsCheckAllocation.Stock<0
   * No it didn't work out. Either stock was insufficient or someone did a concurrent update at another client
   Update Components Set Stock=Stock+m.lnCountNeededitems, Allocated=Allocated-m.lnCountNeededitems Where ID = m.someID
   * Exit, put back Cart you have so far (if any).
Else
   * Success, we allocated the needed number of items
   Insert Into Cart (UserID, ComponentID, iCount) Values (goApp.LoggedinUserID, m.someID, m.lnCountNeededitems)
   * continue with the next component.
EndIf

Just some idea, nothing that is out in the field this way, it also doesn't yet manage a cart session and its expiration.

This is not only about low level locks and transactions. You can't lock something, change data buffered or within a transaction, as other clerks have to immediately see the stock reduced, and also still need the same read and write access, even before they go ahead. Otherwise two users may use the same stock and one of them doesn't really get it. So the supermarket principle of products in a transition zone, the cart, is really a helpful picture to use here.

Bye, Olaf.

PS: m.someID, m.lnCountNeededitems obviously has to come from the bill of materials needed, which you scan here. Only if each component had success you finally have success. If not you empty the cart and put components back into the stock table, don't forget to change stock inverse to Allocated count also in that stage. If allocated>0 at the end of the day, there was some cart error or crash, if it's even <0 then even worse. You can have a correction run at night and of course from time to time you'll check stock anyway, because of things not going through the system, eg theft, due dates, broken units, etc.

 

[AndrewMozley]

Thank you Olaf, for your thoughts on this matter; I believe I have taken them on board.

I first establish whether there is enough component stock, by examining the stock records of the half dozen or so components; at this stage there is no locking of records. The user is told that there is enough stock.

A few seconds later the user indicates that he wants the transaction to go through. This is the general structure of the update program which is executed at this point :

BEGIN TRANSACTION
    lSuccess = .T.
    Read and lock finished goods record
    Update finished goods stock level
    Unlock finished goods record

    Read and lock production batch record
    Update status of production batch
    Unlock production batch

    SCAN Component stock records
        Read and lock stock record
        If there is now insufficient stock
            lSuccess = .F.
            EXIT
          ELSE
            Update component stock record
            Unlock component stock recordc
            ENDIF
        ENDSCAN

If lSuccess = .T.
    END TRANSACTION
    Report that transaction is successful
  ELSE
    ROLLBACK
    Tell user that the transaction has not succeeded
    ENDIF

There are a few other instructions, (e.g. if the production batch cannot be locked) but this is the essence of the code and I believe that it will achieve the result. But grateful to learn if other persons would handle this differently.

Thanks again. Andrew

 

[Olaf Doschke]

If it works for you, it works.
I wonder how you'd benefit from the locks, locks don't prevent others to read eg stock levels, and if others can't lock, they must wait.

My main idea was to work without any locks, instead subtracting from the stock, before checking sufficient stock and rather check afterwards,if you're still >0. That's the main point, the same goes with exclusive access: You don't first check, if anyone else has exclusive access, you first try and if you get it you have success. In the same manner you subtract from the stock and if remaining stock is still positive you had success. If not you stop going further and instead put your removed items back to get a positive stock, again. That way several users can use same components at the same time.

EDIT: The way I do it the database computes the new value, not you, and you don't revert to the old value, you add the same value you subtracted, that's important for the concurrent access. Let me illustrate that.
Let's say you have 4 items of ComponentX and two clerks process two orders, one of them needs (besides many other things 2x ComponentX, the other needs 3x ComponentX. And lets say their checking of stock by coincidence happens at the same time (worst case scenario). Clerk 1 subtracts 2 from the stock, ending with 2, he get's the stock, clerk 2 subtracts 3 and gets -1, he stops and puts back 3 to get back to 2, instead of reverting to 4. In case both subtractions happen before the check of stock being >0, both clerks fail, but that's not a big deal, as one of them can try a minute later and have success.

The essential thing is: If you read something to check before locking or changing data you potentially always have an outdated info, the info you read is normally valid for hours, but in worst case some other user is changing data in the moment right after you read info. If you subtract to begin with, your info might also outdate and the way I proposed is taking that into account in a way you already have computed the future reduced stock.

You can also lock a record, check sufficient funds, subtract from it, knowing no one else can write at the same time. In the worst case you make other clerks fail not during the check but during the moment of trying to get the lock and update stock, while there normally might still be plenty stock available. The way to lock data would compare to a store locking any shelve of product someone takes from to his shopping cart until that customer pays. If items are put into some kind of shopping cart you already have the updated amount and others can "shop" in parallel, not only the customers themselves, but also clerks processing bills of materials.

Bye, Olaf.

 

[Olaf Doschke]

If anyone is interested here's a much more detailed implementation of the idea to work without locks and transactions. It actually makes use of a transaction in one place and could start with another one, but the details are explained in code comments:

* some existing temp or test directory of your choice, you might use GETENV("TEMP")
#Define ccBaseDir "D:\test\"

Close Tables all
Close Databases all
Erase (ccBaseDir+"Warehouse.*")
Erase (ccBaseDir+"Carts.*")
Erase (ccBaseDir+"Sessions.*")

Create Database (ccBaseDir+"Warehouse")
Create Table (ccBaseDir+"Warehouse");
  (iId I AutoInc, cName C(10), iStock I, iAllocated I Default 0)
Create Table (ccBaseDir+"Carts");
  (iId I AutoInc, iSessionID I, iComponentID I, iAmount I)
Create Table (ccBaseDir+"Sessions");
  (iID I AutoInc, cStartedBy C(50), tStartedAt T Default Datetime())
Close Tables All

* Warehouse, representing all your warehouse stock
Insert Into Warehouse (cName, iStock) Values ("Nuts",200)
Insert Into Warehouse (cName, iStock) Values ("Bolts",200)


* Start a Shopping Cart Session (order processing from a clerk)
Local lnMySessionID, llSuccess
Insert Into Sessions (cStartedBy) Values (Id())
lnMySessionID = Sessions.iID

* Bill of Materials, representing detailed items needed for a product or order of several products
Create Cursor crsBillOfMaterials (iID I autoinc, iComponentID I, iNeededAmount I)

*Bill of Materials: 12 Nuts and 12 Bolts...
Insert Into crsBillOfMaterials (iComponentID, iNeededAmount) Values (1,12) && ID 1 for nuts
Insert Into crsBillOfMaterials (iComponentID, iNeededAmount) Values (2,12) && ID 2 for bolts
* Bill of Materials is what you want to put into your shopping cart.

* First, virtually take from the warehouse shelves:
Update Warehouse ;
   Set iStock     = iStock     - iNeededAmount ;
   ,   iAllocated = iAllocated + iNeededAmount ;
   From crsBillOfMaterials ;
   Where Warehouse.iID = crsBillOfMaterials.iComponentID
* This database change is "non desctructive". No information is destroyed or depends on
* the current state of the clients memory. The sum iStock+iAllocated is kept constant.
* In case of a crash right now we still know the real iStock.
* This database change is atomic, it happens in an instant with automatic lock.
* No further manual lock or transaction is needed, as this all takes place in a single table.
* Other clerks order processing can be done in parallel.
   
* Second, check if you took out more than available:
Select Warehouse.iID, iStock ;
   From Warehouse ;
   Inner Join crsBillOfMaterials ;
   On Warehouse.iID = crsBillOfMaterials.iComponentID ;
   Where Warehouse.iStock<0;
   Into Cursor crsMissingStock
   
llSuccess = (_Tally=0)
* crsMissingStock includes any insufficient supply of components also due to other clerks
* concurrent activity even including parallel virtually taken or put back items.
* That means: If reading back current iStock values <0 reveals no record, there is no problem,
* which includes the luck a clerk was putting back items right at the time we needed them
* or there was sufficient stock for two or more currently processed bills of materials.

If NOT m.llSuccess
   * Insufficient stock, put back virtually taken items instead of putting them into a cart:
   Update Warehouse ;
      From crsBillOfMaterials ;
      Set iStock     = iStock     + iNeededAmount ;
      ,   iAllocated = iAllocated - iNeededAmount ;
      Where Warehouse.iID = crsBillOfMaterials.iComponentID
   * Putting the virtually taken items back immediately helps ohter clerks to see real 
   * stock values. Again - a non destructive, atomic, automatically locked database change.
   * This update resembles a Rollback, but also is correct in case other clerks worked on
   * warehouse data in the meantime, so it's even better than a transaction, as it doesn't 
   * lock out anybody.
   
   * Display the message afterwards:
   MessageBox("Insufficient Stock.",64+0,"Info for the clerk")
   * You might also display crsMissingStock now
   
Else && Success!
   * Start cart processing
   * We put the whole BillOfMaterials into the current session cart:
   Insert Into Carts (iSessionID, iComponentID, iAmount);
   Select m.lnMySessionID ;
      ,   iComponentID    ;
      ,   iNeededAmount   ;
      From crsBillOfMaterials
   * This now reflects where iAllocated counts went to
   
   * Putting bill of materials into the cart could go into a transaction together with the
   * initial virtual warehouse update and the select checking stock, but that would block out 
   * others too early.
   * There is no need to worry about crsBillOfMaterials data change. We put same things into 
   * the cart as we removed from the warehouse. The crsBillOfMaterials is the leading data for 
   * that information anyway.
   * Warehouse data is always intact, as the iAllocated count accounts for not yet really removed 
   * stock. The information about the allocation now just isn't anonymous anymore. We just might 
   * need to rethink that, if it's stretched out into several UI forms, but that makes a transaction 
   * even less possible.
   * Finally: It's a plus we don't needed a transaction, this means more in parallel processing
   * possibilities, as unlikely the timely concurrence of these processing may be.
   
   * Next step:
   * Depending on a final clerk decision the cart content goes back to the warehouse or is 
   * sold (which could mean forwarding to product assembly or packaging and delivery)
   lnAnswer = MessageBox("Sufficient Stock. Sell?",32+4,"Question for the clerk")
   
   Local lnInitialTransactionLevel
   lnInitialTransactionLevel = Txnlevel()
   Try
      * Here we put the final change of Warehouse and Cart into one transaction:
      Begin Transaction
      If lnAnswer = 6 && Yes, we have a sell
         Update Warehouse From Carts ;
            Set iAllocated = iAllocated - Carts.iAmount;
            Where Warehouse.iID = Carts.iComponentID;
            And   Carts.iSessionID = m.lnMySessionID
         * Notice: Stock remains at its previously reduced value.
         * Reducing iAllocated means this amount of items
         * now is finally removed from warehouse stock.
         * Again a non destructive, atomic, automatically locked database change
      Else
         * No sell, put the cart data back into warehouse stock
         Update Warehouse From Carts ;
            Set iStock     = iStock     + Carts.iAmount ;
            ,   iAllocated = iAllocated - Carts.iAmount ;
            Where Warehouse.iID = Carts.iComponentID   ;
            And   Carts.iSessionID = m.lnMySessionID
         * Again a non destructive, atomic, automatically locked database change   
      EndIf
      * End cart processing      
      Delete From Carts Where iSessionID = m.lnMySessionID
      End Transaction
      * It's not very essential to have Warehouse and Cart changes in a transaction,
      * because the anonymous iAllocated count is good enough to know at any time.
      * Having further user/pc related cart data just helps finding out to whom and where
      * a misfortune situation occurred.
   Catch
      * Something went wrong, perhaps at the hardware level
      If Txnlevel()>lnInitialTransactionLevel
         RollBack
      Endif
   EndTry
EndIf
* End session
Delete From Sessions Where iID =  m.lnMySessionID
* You might set an end date instead of deleting, too.

* Worst case scenartio: A crash of the software at any line
* You will have not closed sessions, filled carts and Warehouse.iAllocated>0 to check at end 
* of day database integrity checks, But there is no place in this code putting the database 
* into a transient and incomplete state with important info about what other changes to make 
* only existing at the client side. That's not the case.

* If any Warehouse.iAllocated is >0 you can mend data from putting back carts of todays 
* unfninshed/undeleted sessions or just move iAllocated itself back to iStock.

* Even though there might be a crash inbetween adding to the cart and subtracting from 
* warehouse carts are not important, if sum of all cart items differes from sum of all 
* iAllocated this simply hints on crashes between the initial update and inserting into the 
* Carts table, you just don't have the info on where the items were processed by whom, but 
* iAllocated is reliable info on its own.
* You can deduct the client PC from it missing on other current sessions and carts.

* You may log such bad scenario data for further in depth analysis of what went wrong when 
* and at which client PC with the help of Sessions records (including the deleted ones).

That whole code would not be used as this monolithic block, obviously the separate steps would most probably happen in several tabs of a form or even several forms UI wise. It also makes some simplifications most probably not fitting any real world warehouse or shop system, but it is just there to illustrate you can work on data in a way not needing locks. It's for example not available to the mere mortal MS SQL Server user to lock records. There is no finer granularity than locking pages anyway, containing several records. You also see from this, that a real world transaction as the check and sale of a bill of materials isn't a single transaction database wise, transactions always must be kept short and without user interactions keeping them indefinitely long. They happen in a way to keep a database from a transient state which isn't reflecting the real world, eg only allow reducing stock at the same time it reappears in a cart record, but with appropriate table design you can have intermediate states only needing changes in a single table, ie via iAllocated in the warehouse table parallel to iStock, then the automatic locks of dbfs or automatic transactions of MSSQL queries are sufficient.

Bye, Olaf.