Lecture 19
Transactions
Transaction Concept
A transaction is a unit of program execution that accesses
and possibly updates various data items. This is usually from a user-written
program within a block, delimited by a set of statements such as, begin
transaction and end transaction. Transactions must maintain
database integrity during this updates, so we require the following properties
(called the ACID properties):
-
Atomicity -- All operations in the transaction are reflected properly or
not at all.
-
Consistency -- No transactions depends on another transaction.
-
Isolation -- Even though multiple transaction may execute concurrently,
each is independent of the other and does not infer with any other transaction.
-
Durability -- After the transaction has completed, any changes the transaction
made are permanent.
To illustrate a set of two transactions that means the test:
Ti: read(A);
A := A - 50;
write(A);
read(B);
B := B + 50;
write(B);
Transaction State
In the absence of failures, all transactions complete successfully.
One that does has a failure does not complete successfully and is said
to have aborted. Any changes that an aborted transaction has
made must be removed from the database or rolled back. Successful
transactions must have the changes made permanent or committed.
A simple transaction model has the transaction in one of the five following
states:
-
Active: the initial state and the transaction stays in this state
while it is executing.
-
Partially committed: After the final statement has executed.
-
Failed: After the discovery that normal execution can no longer proceed.
-
Aborted: After the transaction has been rolled back and the database
has been restored to its state before the transaction started. This
is a terminal state.
-
Committed: After successful completion. This s a terminal state.
Concurrent Executions
Transaction-processing systems usually allow multiple transactions to run
concurrently, and extra work must be done to allow this. The reasons
for allowing concurrency.
-
A transaction consists of multiple steps. The parallel actions of
the CPU and I/O system allow multiple transaction because one can be reading
while another can be processing. System throughput is increased
and CPU and disk utilization is increased.
-
Some operations are short, others are long, when running concurrently,
average response time is improved.
The DBMS must use concurrency control to regulate interaction among
concurrent transaction to prevent them from destroying the consistency
of the database.
An example of an concurrent execution that would produce an incorrect
state:
| T1 |
T2 |
read(A);
A := A - 50; |
|
|
read(A);
temp := A * 0.1;
A := A - temp;
write (A);
read(B); |
write(A);
read(B);
B := B + 50;
write(B) |
|
|
B := b + temp;
write(B) |
|
|
The first transaction transfers $50 from the account of person A to the
account of person B. The second one transfers ten percent from the
account of person A to the account of person B. In this example,
the ten percent transfer from A is lost and the deposit of $50 to B is
lost. You can prove it to yourself. The sum of A + B should
be the same before and after both transactions.
Serializability
The two parts of the transactions that we will focus on controlling is
the read and write operations. When managing conflict serializability
there are only four cases to consider where there are two transactions
processing the same data item:
-
I1 = read(Q), Ij = read(Q).
The order does not matter.
-
I1 = read(Q), Ij = write(Q).
The order does matter.
-
I1 = write(Q), Ij = read(Q).
The order does matter.
-
I1 = write(Q), Ij = write(Q).
The order does not matter for transactions one and two, but it does
affect transaction three.
Simply put, any time a transaction is going to update a particular
item, after the read occurs, the DBMS must prevent another traction from
accessing that item until write operation is completed.
Recoverability
If a transaction Ti fails for any reason, we need to undo the
effect of this transaction to ensure the atomicity property of the transaction.
In a system that allows concurrent execution, it is necessary also to ensure
that any transaction Tj that is dependent on Ti
(that is, Tj has read data written by Ti ) is also
aborted. This is referred to as cascaded rollback, which undesirable
since it leads to the undoing of a significant amount of work.
Isolation
Schedules that are conflict serializable and cascadeless satisfy the requirements
to leave the database in a consistent state.
One scheme for concurrency control is for a transaction to acquire a
lock on the entire database before it starts, and releases the lock after
it has committed. While one transactions hold a lock, no other transaction
is allowed to acquire the lock and must therefore wait for the lock to
be released. As a result of the locking policy, only one transaction
can execute at a time. Therefore only serial schedules are generated,
but this leads to poor performance by forcing transactions to wait until
they can acquire a lock. An improvement on this schema is to limit
the lock to a particular tuple or a single attribute of a particular tuple.
Transaction Definition in SQL
The SQL standard specifies the current transaction begins implicitly.
Transactions are ended by one of the following:
-
commit work commits the current transaction and begins s new one.
-
rollback work causes the current transaction to abort.
The keyword work is optional.
The standard also specifies that the system must ensure both serializability
and freedom from cascading rollback. The levels of consistency are:
-
serializable is the default.
-
repeatable read allows only committed records to be read and further
required that, between two reads of a record by a transaction, no other
transaction is allowed to update the record.
-
read committed allows only committed records to be read, but does
not require repeatable reads.
-
read uncommitted allows uncommitted records to be read. It
is the lowest level of consistency allowed by SQL-92.
CSEE
| 461
| 461
F'98 | lectures
| news
| help