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.
Stateful/Transactional/Conversational Servers
Different IT shops have different names for these sorts of servers, but the implications are the same. Essentially, if we go for a solution to the situation above, where we rollback through transactions, our transactions can easily span multiple interactions between the ordering clerk and the database. This is how cursors are often used-when it isn't necessary. It is also how transactions are frequently used.
This means that the data may be locked for an extended period-take the extreme case of the ordering clerk dying of a heart attack brought on by the stress of continual uncertainty (due to locking problems) about the length of a task. Suppose also that our system copes with such problems as this, power failures, and so on by writing state data to the local disk for later recovery.
Even if our system users don't die mid-transaction, we are facing a situation of potentially long locks. Add to this a database server that has page-level locking, and make the system one with a high transaction rate and concurrent user numbers, and you have a problem waiting to occur. If we are going to lock, we have to do it in a way that interferes with as few users as possible, so that reading data without the intention of updating is still allowed. This is usually done with software locks (the application writes a value when it gets the data, and checks the value as it puts it back) or timestamp fields.
