- Reality:
- Most of the time,
computer systems do
not fail
- But, from time to time, a computer system can fail
- Most of the time,
computer systems do
not fail
- Facts:
- Financial applications
(e.g., banking) often use
data base systems to
process their
transactions
- Computer failures have destrimental consequences in financial applications
- Financial applications
(e.g., banking) often use
data base systems to
process their
transactions
- Example:
- Customer A
transfers the sum of
$10,000 to the account
of customer B.
- Suppose the computer system fails
in the middle of the
transfer operation:
Computer Database on Disk ------------------------------ ---------------------------------- Initially.... A.bal = 500,000 B.bal = 70,000 Read A.bal from disk (500,000) A.bal = A.bal - 10000 Write A.bal to disk A.bal = 490,000 B.bal = 70,000 Read B.bal from disk (70,000) B.bal = B.bal + 10000 FAILURE
- Result:
- A has lost $10,000
- Customer A
transfers the sum of
$10,000 to the account
of customer B.
- A transaction
is a
"
logical unit of execution
in database processing"
Examples of transactions:
- deposit
the amount of $N into
account A
- Transfer
the amount of $N from
account A to account B
- Close (= delete) the account A
- deposit
the amount of $N into
account A
- Properties of a transaction
- Atomicity
- A transaction is an
atomic unit
of data processing
- In other words:
- a transaction is either performed in its entirety or not performed at all.
- A transaction is an
atomic unit
of data processing
- Consistency perservation:
- A transaction is consistency preserving if its complete execution takes the database from one consistent state to another consistent state
- Isolation:
- Although transactions are
executed concurrently,
each transaction should
appear as though
it is being executed
in isolation from
other transactions.
In other words:
- The execution of a transaction should not be interfered with by other transactions executing at the same time.
- Durability or
permanency:
- The changes made to the
database by a
comitted (= successful)
transaction must
persist in the database.
In other words:
- The changes must not be lost because of possible failure occuring immediately after the transaction.
- The changes made to the
database by a
comitted (= successful)
transaction must
persist in the database.
- Although transactions are
executed concurrently,
each transaction should
appear as though
it is being executed
in isolation from
other transactions.
- Atomicity
- Denoting a transaction
- We use the keywords
begin_transaction and
end_transaction
to
delimit the
operations
that pertain to
one transaction.
Graphically:
begin_transaction .... (oprations pertaining to one transaction) end_transaction
- We use the keywords
begin_transaction and
end_transaction
to
delimit the
operations
that pertain to
one transaction.
- Terminology:
- Transaction =
a block of operations between
begin_transaction and
end_transaction
- Commit =
a successful termination of
a transaction
- The operations of a comitted transaction is guaranteed to be performed on the data in the database.
- Abort =
an unsuccessful termination of
a transaction
- The operations of an aborted transaction is guaranteed not to be performed on the data in the database.
- Roll back =
the process of
undoing the updates
made by the
operations
of a transaction
- Redo = the process of performing the updates made by the operations of a transaction again
- Transaction =
a block of operations between
begin_transaction and
end_transaction
- Remember that:
- A transaction
cannot be executed
partially
- So you cannot perform only some operations of a transaction while not performing others !!!
- A transaction
cannot be executed
partially
- To implement
transaction processing ,
each transaction
is assigned a
unique transaction ID
- The transaction ID is used to identify the operations that a particular transaction has performed on the database
- Assumptions:
- Before we can discuss how to implement transactions, we need some basic assumptions about the database processing system
- Model assumptions:
- Database consists of
a number of
named
(= identifiable) data items
- The kinds of
data items
determines the
granularity of the
database
Examples:
- Very fine granularity:
a data item is a field
in a database record
- Very gross granularity: a data item is a table in a database
The granularity will determine the amount of parallelism (# concurrent operations)
- Very fine granularity:
a data item is a field
in a database record
- The operations that are defined
on a data item are:
- read_item(X): read the data item named X
- write_item(X): write the data item named X
- Database consists of
a number of
named
(= identifiable) data items
- Let me make sure
that you understand
what is going on inside the computer
when you
read/write
a data item
I will use a integer value as data item in the example below to explain the operations
- What happens in the
read_item(x)
operation:
- Locate the
disk block that contains the
data item x:
- Read (copy) the
disk block that contains the
data item x
into a system buffer
(inside the Operating System):
(In other words: the entire disk block is read in (not just the data item x !!!)
- Read (copy) the
data item x
from the
Operating System buffer
into a program variable
inside the DBMS:
Note:
- We will also
need a name to
refer to the
variable
that holds the data item
inside the main memory
- For convenience,
we will use the name of the data item
for the data inside the buffer:
- From the context, it should be clear which of the data is referred to by the name x
- We will also
need a name to
refer to the
variable
that holds the data item
inside the main memory
- Locate the
disk block that contains the
data item x:
- What happens in the
write_item(x) operation:
- First, note that the
DBMS variable x will be
used to
update the
data item x in the database.
Example:
Note:
- The x in write_item(x) refers to the memory variable x (with value 777) !!!
- Locate the
disk block that contains the
data item x:
- Read (copy) the
disk block that contains the
data item x
into a system buffer
(inside the Operating System):
Note: if the disk block has been previously read in and it is still available inside the Operating System buffers, then this step is skipped !
- Write (copy) the
value:
Note: the data item x inside the data base has not been updated yet !!!
- Write (copy) the
disk block to
disk:
Note: now the data item x is updated !!
- First, note that the
DBMS variable x will be
used to
update the
data item x in the database.
- Recall that:
- A transaction consists of
many operations.
In particular:
- A transaction consists of many write operations.
- A transaction consists of
many operations.
- Updating the
data in memory and
updating the
data in the database:
- When a transaction
updates a
variable in memory,
the update
must eventually be
written to the
database:
Important fact:
- We have a choice to select the moment in time when the updates to the memory variables will be written to the database
- When a transaction
updates a
variable in memory,
the update
must eventually be
written to the
database:
- Timing of
issueing/executing
a write operation:
- There are 2 commonly used moments to
issue write operations:
- Immediately
(as soon as the memory veriable is
updated,
the change is
written to the data in the
database)
- The rationale for this choice is the fact that we want to make the new value available to other transactions as soon as possible
- At the end of
the transaction
- The rationale for this choice
is the fact that the
same variable may be
updated multiple times
within one transaction
- This scheme will be more efficient
- The rationale for this choice
is the fact that the
same variable may be
updated multiple times
within one transaction
- Immediately
(as soon as the memory veriable is
updated,
the change is
written to the data in the
database)
- There are 2 commonly used moments to
issue write operations:
- In place
(or immediate) write:
- In place (or immediate) write = a write operation to the database is issued as soon as a memory variable is updated
- Deferred
write:
- Deferred write = all write operations to the database are issued at the end of the transaction
- Example 1 of a transaction:
transfer
an amount of $N from
account X to
account Y:
begin_transaction read_item(X) X.balance = X.balance - N write_item(X) read_item(Y) Y.balance = Y.balance - N write_item(Y) end_transaction
- Example 2 of a transaction:
deposit
an amount of $N into the
account X:
begin_transaction read_item(X) X.balance = X.balance + N write_item(X) end_transaction
- Fact:
- A database system is used
concurrently by
multiple users
- I.e.:
- Multiple transactions are processed concurrently
- A database system is used
concurrently by
multiple users
- Possible results of
concurrent execution of
database transactions:
- Lost updates
- Dirty read
Either of these situations will cause inconsistency in the database system
- Lost updates
- Lost update:
- An update is lost if after executing a transaction, the result of the transaction is not reflected in the final database state
- An example of
a concurrent execution of
database transactions that results in
a lost update:
Initial database state: X.balance = 1000 Y.balance = 2000
Transaction 1 Transaction 2 ====================================================================== $100 Transfer X -------> Y Deposit $1000 in X Correct final database state: X.balance = 1900 Y.balance = 2100
Sample concurrent execution of T1 and T2: $100 Transfer X -------> Y Deposit $1000 in X read_item(X) [1000] X.bal = X.bal - 100 [ 900] read_item(X) [var=1000] X.bal = X.bal+1000 [var=2000] write_item(X) [X=2000] write_item(X) [X=900] read_item(Y) [2000] Y.bal = Y.bal + 100 [2100] write_item(Y) [Y=2100]Result:
- After this concurrent execution of transactions, the update of transaction T2 is lost !!!
- Dirty read:
- A dirty read operation is a
read operation that
obtained an illegal value
- An illegal value is a value that does not exit in the database
- A dirty read operation is a
read operation that
obtained an illegal value
- Clarification:
- You may wonder how on earth
can a read operation read a
value that
does not exist
- Well, the matter is a bit complicated. What
actually happened is:
- The read operation read an
new value that
some transaction wrote;
but that transaction
failed to
complete
- Consequently, the new value written by the transaction should not exist.
- The read operation read an
new value that
some transaction wrote;
but that transaction
failed to
complete
- You may wonder how on earth
can a read operation read a
value that
does not exist
- An example of
concurrent execution of
database transactions that results in
a dirty read operation:
Initial database state: X.balance = 1000 Y does not exist (erroneous account)
Transaction 1 Transaction 2 ====================================================================== $100 Transfer X ---X---> Y Deposit $1000 in X Correct final database state: X.balance = 2000 Y does not exist
Sample concurrent execution of T1 and T2: $100 Transfer X ---X---> Y Deposit $1000 in X read_item(X) [1000] X.bal = X.bal - 100 [ 900] write_item(X) [X=900] read_item(X) [ var=900] ??? X.bal = X.bal+1000 [var=1900] write_item(X) [X=1900] read_item(Y) [ERROR] (restore X.bal back to 1000)Result:
- When the execution
of transaction T1 detects that
Y is invalid,
the
read operation of
transaction T2 becomes
a dirty read !!!
Because:
- The value 900 was
written by a transaction that has
failed
- A value created by a failed transaction is invalid
- The value 900 was
written by a transaction that has
failed
- When the execution
of transaction T1 detects that
Y is invalid,
the
read operation of
transaction T2 becomes
a dirty read !!!
- Techniques used to implement
transactions
- Logging:
- Logging ensures that
all operations
in a transaction are either:
- All
are executed,
or
- None are executed
- All
are executed,
or
- Logging ensures that
all operations
in a transaction are either:
- Synchronization methods:
- Synchronization methods ensure that the
concurrent execution of
multiple transactions are
consistent
(We will define what consistent means later....)
- Synchronization methods ensure that the
concurrent execution of
multiple transactions are
consistent
- Logging: