Journal/Temporary Entry
Let's look at the stock checking step in our hypothetical application: find the order item with its stock level. We weren't saying, "Lock that stock level until we have finished completing our order." If we don't lock the stock level, we can obviously get situations where, when we first checked, there was enough stock to place the order, but by the time we input our order stock levels have fallen. (We are successful-orders are coming in all the time!). If that is the case, again our poor old stored procedure is going to have to check, raise an error, and offer the customer-via the user interface-the opportunity to accept either a back-order for that item, drop the order line, or drop the whole order.
We could implement this step differently: when we check stock levels, we could say lock, or temporarily allocate, that stock, so that when our order clerk says, "We have that item in stock," it remains true when the order is placed. This can be done as the usual kind of transaction-insert/update records in this allocated stock table. The problem comes if the link between the ordering clerk and this stock allocation table entry goes down before the order is placed and the stock committed.
This problem is usually dealt with by grouping the stock allocation(s) and the order committal into a standard database transaction so that either they both work, or neither does. This opens us up to locking problems, and also to issues of deciding exactly when the transaction has failed. Has it, for instance, failed if upon committing the order we get a rollback because the customer's credit limit has been exceeded? Does this circumstance mean that all the stock for all the order lines should no longer be allocated? This can mean doing the stock allocations and all the stock checks all over again if there is some process for authorizing such orders or upping the customer's credit limit.