Databases Reference
In-Depth Information
Primary Key Column
EMPLOYEES
(Parent) Table
EMPLOYEES_SSN
(Child) Table
Foreign Key Column
Transaction Processing
As you've learned, constraints created on columns of a table help you to main-
tain integrity and consistency in the database at the statement level.
Transactions
go beyond individual INSERT or UPDATE statements and allow you to ensure that
multiple DML statements against the database either all succeed or all fail.
From a DBA's perspective, the transaction concept is important to understand
when allocating disk space. The more activity that occurs within a transaction,
the greater the need for disk space to maintain
read consistency
in the database.
If a user initiates a long-running SELECT statement, the table data seen by the
user will appear to be unchanged, even if other users are subsequently making
changes to the same rows while the SELECT statement is executing. As a result,
additional disk space (known as undo or rollback space) must be allocated to
hold both the old and new versions of the rows being read by one user and writ-
ten to by another user.
Transactions begin with a single DML statement and end (successfully or
unsuccessfully) when one of the following events occurs:
◆
transaction
A logical unit of work consisting of one or
more SQL statements that must all suc-
ceed or all fail to keep the database in a
logically consistent state. A transfer of
funds from one bank account is a logical
transaction, in that both the withdrawal
from one account and the deposit to
another account must succeed for the
transaction to succeed.
Either a COMMIT or ROLLBACK statement is executed. A COMMIT statement
makes the changes to the table permanent, while the ROLLBACK undoes the
changes to the table.
The user exits SQL*Plus or iSQL*Plus normally (automatic COMMIT).
◆
A DDL (Data Definition Language) or DCL (Data Control Language)
statement is executed (automatic COMMIT).
◆
The database crashes (automatic ROLLBACK).
◆
The SQL*Plus or iSQL*Plus session crashes (automatic ROLLBACK).
◆
