Transactions and Locks
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 COMMIT
or
ROLLBACK
statement.
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 ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.