Databases Reference
In-Depth Information
transaction log until a backup of the log is run, and then delete the completed
transactions at that time.
What if a hardware error occurs before the changes can be written perma-
nently to the hard disk? That is where the transaction log becomes a critical part
of transaction management, through a process know as
recovery.
After you
restart the DBMS after a failure, the DBMS checks the transaction log for com-
pleted, which is to say committed, transactions. These transactions are
rolled
forward,
which means that the database table updates are made from the changes
recorded in the transaction log. Otherwise, the change might be lost. Uncommit-
ted transactions are treated as if they don't exist, with no changes made to the
database.
10.1.2 Using Transaction Commands
The basic commands defined for controlling transactions are:
▲
BEGIN TRAN[SACTION]:
this identifies the starting point of the trans-
action. Statements executed after this command are treated as part of the
transaction.
▲
COMMIT TRAN[SACTION]:
this is used to identify successful comple-
tion of the transaction. Run COMMIT TRAN when you are sure that all
updates have been made and that no errors have occurred. This can also
be run as COMMIT WORK.
▲
ROLLBACK TRAN[SACTION]:
run this command when an error
occurs. All statements executed since the BEGIN TRAN statement are
rolled back. This can also be run as ROLLBACK WORK.
Some DBMSs support additional statements that let you name a transac-
tion or let you set points within the transaction that let you perform a
partial
rollback,
which is when changes are reversed back to a specified point within
the transaction, but we're limiting our discussion to these basic transaction
commands.
Many programming languages provide direct support for transaction man-
agement. For those that don't, you control transactions by passing the transac-
tion commands to the database server. Either way, it is strongly suggested that
database applications be designed so that all modifications to the database are
performed in the context of a transaction.
When run in a batch or as part of an application, logic must be included to
test for or respond to errors, so that the program can correctly commit or roll
back the transaction. When using transactions from the command line, you
need to manually commit or roll back the transaction. One way of doing this,
demonstrated in Figure 10-2, is having a decision point after each statement is
executed to determine whether or not an error occurred. Figure 10-2 represents
