578 | free ebooks ==> www.ebook777.

com
Chapter 20 z Transaction Management

Example 20.1 The lost update problem

An apparently successfully completed update operation by one user can be overridden by

another user. This is known as the lost update problem and is illustrated in Figure 20.4,
in which transaction T1 is executing concurrently with transaction T2. T1 is withdrawing
£10 from an account with balance balx, initially £100, and T2 is depositing £100 into the
same account. If these transactions are executed serially, one after the other with no inter-
leaving of operations, the final balance would be £190 no matter which transaction is
performed first.
Transactions T1 and T2 start at nearly the same time, and both read the balance as £100.
T2 increases balx by £100 to £200 and stores the update in the database. Meanwhile, transac-
tion T1 decrements its copy of balx by £10 to £90 and stores this value in the database, over-
writing the previous update, and thereby ‘losing’ the £100 previously added to the balance.
The loss of T2’s update is avoided by preventing T1 from reading the value of balx until
after T2’s update has been completed.

Figure 20.4
The lost update
problem.

Example 20.2 The uncommitted dependency (or dirty read) problem

The uncommitted dependency problem occurs when one transaction is allowed to see the
intermediate results of another transaction before it has committed. Figure 20.5 shows an
example of an uncommitted dependency that causes an error, using the same initial value
for balance balx as in the previous example. Here, transaction T4 updates balx to £200,

Figure 20.5
The uncommitted
dependency
problem.

WWW.EBOOK777.COM
www.it-ebooks.info
 free ebooks ==> www.ebook77 7.com
20.2 Concurrency Control | 579

but it aborts the transaction so that balx should be restored to its original value of £100.
However, by this time transaction T3 has read the new value of balx (£200) and is using
this value as the basis of the £10 reduction, giving a new incorrect balance of £190, instead
of £90. The value of balx read by T3 is called dirty data, giving rise to the alternative name,
the dirty read problem.
The reason for the rollback is unimportant; it may be that the transaction was in error,
perhaps crediting the wrong account. The effect is the assumption by T3 that T4’s update
completed successfully, although the update was subsequently rolled back. This problem
is avoided by preventing T3 from reading balx until after the decision has been made to
either commit or abort T4’s effects.

The two problems in these examples concentrate on transactions that are updating the
database and their interference may corrupt the database. However, transactions that only
read the database can also produce inaccurate results if they are allowed to read partial
results of incomplete transactions that are simultaneously updating the database. We illus-
trate this with the next example.

Example 20.3 The inconsistent analysis problem

The problem of inconsistent analysis occurs when a transaction reads several values from
the database but a second transaction updates some of them during the execution of the
first. For example, a transaction that is summarizing data in a database (for example,
totaling balances) will obtain inaccurate results if, while it is executing, other transactions
are updating the database. One example is illustrated in Figure 20.6, in which a summary
transaction T6 is executing concurrently with transaction T5. Transaction T6 is totaling
the balances of account x (£100), account y (£50), and account z (£25). However, in the
meantime, transaction T5 has transferred £10 from balx to balz, so that T6 now has the wrong
result (£10 too high). This problem is avoided by preventing transaction T6 from reading
balx and balz until after T5 has completed its updates.

Figure 20.6
The inconsistent
analysis problem.

WWW.EBOOK777.COM
www.it-ebooks.info
590 | free ebooks ==> www.ebook777.com
Chapter 20 z Transaction Management

Example 20.6 Preventing the lost update problem using 2PL

A solution to the lost update problem is shown in Figure 20.15. To prevent the lost update
problem occurring, T2 first requests an exclusive lock on balx. It can then proceed to read
the value of balx from the database, increment it by £100, and write the new value back to
the database. When T1 starts, it also requests an exclusive lock on balx. However, because
the data item balx is currently exclusively locked by T2, the request is not immediately
granted and T1 has to wait until the lock is released by T2. This occurs only once the com-
mit of T2 has been completed.

Figure 20.15
Preventing the lost
update problem.

Example 20.7 Preventing the uncommitted dependency problem

using 2PL

A solution to the uncommitted dependency problem is shown in Figure 20.16. To prevent

this problem occurring, T4 first requests an exclusive lock on balx. It can then proceed to
read the value of balx from the database, increment it by £100, and write the new value

Figure 20.16
Preventing the
uncommitted
dependency
problem.

WWW.EBOOK777.COM
www.it-ebooks.info
 free ebooks ==> www.ebook77 7.com
20.2 Concurrency Control | 591

back to the database. When the rollback is executed, the updates of transaction T4 are
undone and the value of balx in the database is returned to its original value of £100. When
T3 starts, it also requests an exclusive lock on balx. However, because the data item balx is
currently exclusively locked by T4, the request is not immediately granted and T3 has
to wait until the lock is released by T4. This occurs only once the rollback of T4 has been
completed.

Example 20.8 Preventing the inconsistent analysis problem using 2PL

A solution to the inconsistent analysis problem is shown in Figure 20.17. To prevent this
problem occurring, T5 must precede its reads by exclusive locks, and T6 must precede its
reads with shared locks. Therefore, when T5 starts it requests and obtains an exclusive lock
on balx. Now, when T6 tries to share lock balx the request is not immediately granted and
T6 has to wait until the lock is released, which is when T5 commits.

Figure 20.17
Preventing the
inconsistent
analysis problem.

It can be proved that if every transaction in a schedule follows the two-phase lock-
ing protocol, then the schedule is guaranteed to be conflict serializable (Eswaran et al.,
1976). However, while the two-phase locking protocol guarantees serializability, problems
can occur with the interpretation of when locks can be released, as the next example shows.

WWW.EBOOK777.COM
www.it-ebooks.info
592 | free ebooks ==> www.ebook777.com
Chapter 20 z Transaction Management

Example 20.9 Cascading rollback

Consider a schedule consisting of the three transactions shown in Figure 20.18, which con-
forms to the two-phase locking protocol. Transaction T14 obtains an exclusive lock on balx
then updates it using baly, which has been obtained with a shared lock, and writes the value of
balx back to the database before releasing the lock on balx. Transaction T15 then obtains an
exclusive lock on balx, reads the value of balx from the database, updates it, and writes the
new value back to the database before releasing the lock. Finally, T16 share locks balx and reads
it from the database. By now, T14 has failed and has been rolled back. However, since T15 is
dependent on T14 (it has read an item that has been updated by T14), T15 must also be rolled
back. Similarly, T16 is dependent on T15, so it too must be rolled back. This situation, in
which a single transaction leads to a series of rollbacks, is called cascading rollback.

Figure 20.18
Cascading rollback
with 2PL.

Cascading rollbacks are undesirable since they potentially lead to the undoing of a
significant amount of work. Clearly, it would be useful if we could design protocols that
prevent cascading rollbacks. One way to achieve this with two-phase locking is to leave
the release of all locks until the end of the transaction, as in the previous examples. In this
way, the problem illustrated here would not occur, as T15 would not obtain its exclusive
lock until after T14 had completed the rollback. This is called rigorous 2PL. It can be
shown that with rigorous 2PL, transactions can be serialized in the order in which they
commit. Another variant of 2PL, called strict 2PL, only holds exclusive locks until the end
of the transaction. Most database systems implement one of these two variants of 2PL.

WWW.EBOOK777.COM
www.it-ebooks.info