The ACID properties of a transaction can only be
implemented by restricting simultaneous changes to the database. This
is achieved by placing locks on modified data. These locks persist until the
transaction issues a
Without locks, a change made by one transaction could be
overwritten by another transaction that executes at the same time.
Consider, for example, the scenario shown in Figure 8-1, based on the
tfer_funds procedure of Example 8-2. When two different
sessions run this program for the same account number, we encounter
some obvious difficulties if locks are not in place.
In this scenario, account 2 starts with a balance of $2,000. Transaction A reduces the balance of the account by $100. Before transaction A commits, transaction B increases the account value by $300. Because transaction B cannot see the uncommitted updates made by transaction A, it increases the balance to $2,300. Because we allowed two transactions to simultaneously modify the same row, the database is now in an inconsistent state. The end balance for the account will be the value set by whichever transaction commits last. If transaction B is the last to commit, then the owner of account 2 will have $100 more than she should. On the other hand, if transaction A commits first, the account owner will ...