Implementing Transactions

Transaction log:

Transaction log = an append only file consists of entries called log records
The information stored in the transaction log will enable the database system to:

There are, in general, 2 ways to implement transaction processing --- depending on when the write/update operation takes place:

(Transaction execution use in-place update/write operation) and (Transaction implementation uses an UNDO log )
(Transaction execution use deferred update/write operation) and (Transaction implementation uses a REDO log )

In place update/write operation
- In place update (or immediate update
  An
  When transaction T₁ updates the data item X (in place), and later, a transaction T₂ reads the data item X , then T₂ will read the new value of X

Undo log

An UNDO log is a log file that contains information to UNDO the effect of transactions
A effect of a transaction changing values in some records in the database
To undo the effect of a transaction is to undo the updates made by the transaction
In order to undo the updates made by the transaction, we save the original (old) value of every updated data item !!!

An UNDO log contains the following types of log records

[start, TID] : indicates that transaction TID has started
[write, TID, X, old_value]: indicates that transaction TID has over-written data item X whose value was old_value
[commit, TID] : indicates that transaction TID has completed successfully
[abort, TID] : indicates that transaction TID has been aborted

Operation:

NOTE:

crucial

You first write the WRITE action in the log and then update the database in-place

It you reverse these steps , then the transaction cannot be guarantee to execute atomically

Example transaction processing using in-place write and UNDO logs

Consider a simple transaction T that updates a value x :

T: x := x - 1000 y := y + 1000

(E.g., transfer $1000 from back account x to y )

Transaction processing is as follows:

When transaction T starts, the transaction processing system writes a start transaction to to log file:
Transaction T must read the data item x from disk into its memory buffer
To update data on disk , the transaction first update the data item x in its memory buffer
Transaction T writes the data item x from its buffer to disk:
Notice that the old value of x is written to the log before writing the data item x to to database
(We will soon see why the reverse will not work )
Transaction T then read y from disk into its memory buffer
T first update the data item y in its memory buffer
Transaction T writes the data from its buffer to disk:
Again: the old value of x must be written to the log before the data item x (to database)
(The reverse will not work )
Finally, transaction T completes:
Notice that a commit record written to the log

How in-place update + undo log ensures atomicity

Consider a system failure occurred at some step in the above execution:

System failed before step 4 is complete:

If system fail before writing of the log record , then x and y (in the database) are unchanged - no problem
If system fail after writing of the log record , but before writing of the database, then:
- x and y (in the database) are unchanged
- However, the log contains old x = 4000
We are NOT certain whether x was modified... but we need to make sure that x gets back its original value !!!
To make certain that the transaction is atomic , we restore the value of x to its old x = 4000
If system fail after writing of the database, then:
- x = 3000 and y is unchanged - We has lost $1000 !!!
- Fortunately the log contains old x = 4000
We again use the log record to restore x to its orginal old x = 4000

NOTE:

Here we see why we must write the log before updating the data in the database
If the data item x is updated first , and the system fails before the log is written , then we CANNOT restore x to its old value (because we don't have the log record - which could not be written because the system failed before it could write the log)

System failed before step 5 is complete:

x = 3000 and y = 6000 - We has lost $1000 !!!

Fortunately the log contains old x = 4000

We use the log record to restore (UNDO) x to its orginal old x = 4000

System failed before step 6 is complete:

Ditto: x = 3000 and y = 6000 - We has lost $1000 !!!

Fortunately the log contains old x = 4000

We use the log record to restore (UNDO) x to its orginal old x = 4000

System failed before step 7 is complete:

If system fail before writing of the log record (for y), then x = 3000 and y = 6000
We can use the log record for x to restore (UNDO) x to its orginal old x = 4000
We don't need to restore y !
If system fail after writing of the log record (for y), but before writing y, then:

x = 3000 and y = 6000 (in the database)

We are NOT certain whether y was modified... but we need to make sure that BOTH x AND y gets back its original value !!!
To make certain that the transaction is atomic , we restore the values of x to its old x = 4000 and y to its old y = 6000
If system fail after writing of the database, then:

x = 3000 and y = 7000

Although it is correct, we do NOT have any way of know that y was updated
To make certain that the transaction is atomic , we must mkae this choice:

restore the values of x to its old x = 4000 and y to its old y = 6000

That's the only safe option.

System failed before step 8 is complete:

There are 2 possibilities:

System failed before the COMMIT record was written
In this case, we don't have any way of knowing that both x and y were updated for certain .
We do have both old values, so:
System failed after the COMMIT record was written
In this case, we DO have the commit record to tell us that both x and y were updated for certain !!!.
So when we see a [commit, T] record, we do NOT undo the transaction T (using the old values)