Database Reference
In-Depth Information
Transaction ID
Transaction Description
1
1. Change the Price value for part number DW11 to $389.99
2
1. Add a record to the Orders table: OrderNum of 21700,
OrderDate of 10/24/2013, CustomerNum of 282
2. Add a record to the OrderLine table: OrderNum of 21700,
PartNum of DW11, NumOrdered of 3, QuotedPrice of $389.00
3. Add a record to the OrderLine table: OrderNum of 21700, PartNum of
KL62, NumOrdered of 2, QuotedPrice of $346.50
4. Change the OnHand value for part number DW11 to 9
5. Change the OnHand value for part number KL62 to 10
6. Change the Balance value for CustomerNum 282 to $2,321.50
7. Change the Commission value for RepNum 35 to $39,346.20
243
3
1. Add customer 510
4
1. Delete part AT94
FIGURE 7-10
Four sample transactions
Suppose these four transactions are the first transactions in a day, immediately following a backup of
the database, and they all complete successfully. In this case, the log might look like the sample log shown
in Figure 7-11.
Transaction ID
Time
Action
Record Updated
Before Image
After Image
1
8:00
Start
2
8:01
Start
2
8:02
Insert
Orders (21700)
(new values)
3
8:03
Start
1
8:04
Update
Part (DW11)
(old values)
(new values)
2
8:05
Insert
OrderLine (21700, DW11)
1
8:06
Commit
4
8:07
Start
3
8:08
Insert
Customer (510)
(new values)
2
8:09
Insert
OrderLine (21700, KL62)
(new values)
3
8:10
Commit
2
8:11
Update
Part (DW11)
(old values)
(new values)
2
8:12
Update
Part (KL62)
(old values)
(new values)
4
8:13
Delete
Part (AT94)
(old values)
2
8:14
Update
Customer (282)
(old values)
(new values)
4
8:15
Commit
2
8:16
Update
Rep (35)
(old values)
(new values)
2
8:17
Commit
FIGURE 7-11
Sample log in which all four transactions commit normally
Before studying how the log is used in the recovery process, examine the log itself. Each record in the log
includes the ID of the transaction, as well as the time the particular action occurred. The actual time would be
more precise than in the example, the DBMS would process the actions much faster, and the date would also be
included in the log. For simplicity, each action occurs one minute after the preceding action. The actions are
Start to indicate the start of a transaction, Commit to indicate that the transaction completed successfully,
Insert to identify the addition of a record to the database, Update to identify the change of a record, and Delete
