 |
» |
|
|
|
|
|
5.4 How are transactions used?
Key points
- A transaction consists of one or more SQL statements. A transaction begins
with a
BEGIN WORK statement and ends with either a COMMIT WORK or a
ROLLBACK WORK statement.
- A transaction is a unit of work. Either all of the statements contained
within a transaction will be executed, or none of them will.
- Locks are normally obtained by transactions when SQL statements are executed.
By default, locks are held until the transaction ends.
- Locking can degrade performance in two ways:
- Locking reduces concurrency -- Concurrency is the degree to which data
can be simultaneously accessed by multiple users. For example, a table that
can be accessed by one hundred users at a time has better concurrency than a
table that can only be accessed by one user at a time. Locking affects the
number of users that can access a specific portion of data at the same time.
For example, if one user is updating a row, no other user is allowed to access
that row until the first user is done. A transaction must wait if the data
that it needs is already locked in an incompatible mode by another
transaction; when transactions wait, concurrency is reduced.
- Deadlocks sometimes occur -- A deadlock occurs when two or more
transactions are waiting for each other to complete. A deadlock degrades
performance because CPU must be used by:
- ALLBASE/SQL to resolve the deadlock situation (one of the transactions is
automatically rolled back).
- The application program to redrive the cancelled transaction.
- Deadlocks will be explained in greater detail later in this module.
- The major objectives of transaction management are:
- Ensure logical data integrity. Transactions should be written so logical
data corruption does not occur. For example, a transaction should not update
a row without locking it first; when the row is locked, its value cannot
change before the update completes. If the row is not locked and its value
changes before the update completes, the updating transaction will overwrite
(that is, lose) the changes made by another transaction.
- Minimize lock contention (maximize concurrency and minimize
deadlocks).
Page last updated on November 29, 1995
|
|
|
Printable version
