• Atomicity: Either all of a transaction happens or none of it happens.
• Consistency: A transaction takes the database from one consistent state to the next.
• Isolation: The effects of a transaction may not be visible to other transactions until the transaction has committed.
• Durability: Once the transaction is committed, it is permanent.
Transaction Control Statements
You should always explicitly terminate your transactions with a COMMIT or ROLLBACK; otherwise, the tool or environment you’re using will pick one or the other for you. If you exit your SQL*Plus session normally, without committing or rolling back, SQL*Plus assumes you wish to commit your work and it does so. If you just exit from a Pro*C program, on the other hand, an implicit rollback takes place. Never rely on implicit behavior, as it could change in the future. Always explicitly COMMIT or ROLLBACK your transactions.
Transaction control statements available to us:
• COMMIT:
• ROLLBACK:
• SAVEPOINT: A SAVEPOINT allows you to create a marked point within a transaction. You may have multiple SAVEPOINTs within a single transaction.
• ROLLBACK TO <SAVEPOINT>: This statement is used with the SAVEPOINT command. You can roll back your transaction to that marked point without rolling back any of the work that preceded it. So, you could issue two UPDATE statements, followed by a SAVEPOINT and then two DELETE statements. If an error or some sort of exceptional condition occurs during execution of the DELETE statements, and you catch that exception and issue the ROLLBACK TO SAVEPOINT command, the transaction will roll back to the named SAVEPOINT, undoing any work performed by the DELETEs but leaving the work performed by the UPDATE statements intact.
• SET TRANSACTION: This statement allows you to set various transaction attributes, such as the transaction’s isolation level and whether it is read-only or read-write.
Atomicity
Statement-Level Atomicity
Oracle achieves this statement-level atomicity by silently wrapping a SAVEPOINT around each of our calls to the database. The preceding two INSERTs were really treated like this:
Savepoint statement1;
Insert into t values ( 1 );
If error then rollback to statement1;
Savepoint statement2;
Insert into t values ( -1 );
If error then rollback to statement2;
In Oracle, this statement-level atomicity extends as deep as it needs to.If the INSERT INTO T fires a trigger that updates another table, and that table has a trigger that deletes from another table (and so on, and so on), either all of the work succeeds or none of it does. You don’t need to code anything special to ensure this; it’s just the way it works.
Procedure-Level Atomicity
Oracle treated the stored procedure call as an atomic statement. The client submitted a block of code—BEGIN P; END;—and Oracle wrapped a SAVEPOINT around it. Since P failed, Oracle restored the database back to the point right before it was called.
Transaction-Level Atomicity
DDL and Atomicity
Durability
Normally, when a transaction is committed, its changes are permanent—you can rely on those changes being in the database even if the database crashed the instant after the commit completed. This is not true, however, in two specific cases:
• You use the new (as of Oracle 10g Release 2 and above) WRITE extensions available in the COMMIT statement.
• You issue COMMITs in a non-distributed (accesses only a single database, no database links) PL/SQL block of code.
WRITE Extensions to COMMIT
Starting with Oracle Database 10g Release 2 and above, you may add a WRITE clause to your COMMIT
statements. The WRITE clause allows the commit to either WAIT for the redo you generated to be written to
disk (the default) or NOWAIT—to not wait—for the redo to be written. The NOWAIT option is the new capability—a capability that must be used carefully, with forethought, and with understanding of exactly what it means.
Normally, a COMMIT is a synchronous process. Your application invokes COMMIT and then your application waits for the entire COMMIT processing to be complete
When would you want to use this new capability to commit without waiting then? Three scenarios
come to mind:
• A custom data load program. It must be custom, since it will have additional logic to deal with the fact that a commit might not persist a system failure.
• An application that processes a live data feed of some sort, say a stock quote feed from the stock markets that inserts massive amounts of time-sensitive information into the database. If the database goes offline, the data stream keeps on going and the data generated during the system failure will never be processed (Nasdaq does not shut down because your database crashed, after all!). That this data is not processed is OK, because the stock data is so time-sensitive, after a few seconds it would be overwritten by new data anyway.
• An application that implements its own “queuing” mechanism, for example one that has data in a table with a PROCESSED_FLAG column. As new data arrives, it is inserted with a value of ROCESSED_FLAG='N' (unprocessed). Another routine is tasked with reading the PROCESSED_FLAG='N' records, performing some small, fast transaction and updating the PROCESSED_FLAG='N' to 'Y'. If it commits but that commit is later undone (by a system failure), it is OK because the application that processes these records will just process the record again—it is “restartable.”
COMMITS in a Non-Distributed PL/SQL Block
Since PL/SQL was first introduced in version 6 of Oracle, it has been transparently using an asynchronous commit. That approach has worked because all PL/SQL is like a batch program in a way—the end user does not know the outcome of the procedure until it is completely finished. That’s also why this asynchronous commit is used only in non-distributed PL/SQL blocks of code; if we involve more than one database, then there are two things—two databases—relying on the commit being durable. When two databases are relying on the commit being durable, we have to utilize synchronous protocols or a change might be committed in one database but not the other.
ops$tkyte%ORA11GR2> create table t
2 as
3 select *
4 from all_objects
5 where 1=0
6 /
Table created.
ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from all_objects )
5 loop
6 insert into t values X;
7 commit;
8 end loop;
9 end;
10 /
Logically, that code is the same as:
ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from all_objects )
5 loop
6 insert into t values X;
7 commit write NOWAIT;
8 end loop;
9
10 -- make internal call here to ensure
11 -- redo was written by LGWR
12 end;
13 /
Distributed Transactions
One of the really nice features of Oracle is its ability to transparently handle distributed transactions. I can update data in many different databases in the scope of a single transaction. When I commit, either I commit the updates in all of the instances or I commit none of them (they will all be rolled back). I need no extra code to achieve this; I simply “commit.” A key to distributed transactions in Oracle is the database link.
select * from T@another_database;
There are some, but not many, limitations to what you can do in a distributed transaction, and they are reasonable (to me, anyway, they seem reasonable). The big ones are as follows:
• You can’t issue a COMMIT over a database link. That is, you can’t issue a COMMIT@remote_site. You may commit only from the site that initiated the transaction.
• You can’t do DDL over a database link.
• You can’t issue a SAVEPOINT over a database link.
Autonomous Transactions
Autonomous transactions allow you to create a “transaction within a transaction” that will commit or roll back changes independently of its parent transaction. They allow you to suspend the currently executing transaction, start a new one, do some work, and commit or roll back—all without affecting the currently executing transaction state. Autonomous transactions provide a new method of controlling transactions in PL/SQL and may be used in
• Top-level anonymous blocks
• Local (a procedure in a procedure), stand-alone, or packaged functions and procedures
• Methods of object types
• Database triggers
How Autonomous Transactions Work
ops$tkyte%ORA11GR2> create or replace procedure Autonomous_Insert
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 'Autonomous Insert' );
6 commit;
7 end;
8 /
Note the use of the pragma AUTONOMOUS_TRANSACTION. This directive tells the database that this
procedure, when executed, is to be executed as a new autonomous transaction, independent from its parent transaction.
And here’s the “normal” NONAUTONOMOUS_INSERT procedure:
ops$tkyte%ORA11GR2> create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( 'NonAutonomous Insert' );
5 commit;
6 end;
7 /
Now let’s observe the behavior of the nonautonomous transaction in an anonymous block of
PL/SQL code:
ops$tkyte%ORA11GR2> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert
As you can see, the work performed by the anonymous block, its INSERT, was committed by the NONAUTONOMOUS_INSERT procedure. Both rows of data were committed, so the ROLLBACK command had nothing to roll back.
ops$tkyte%ORA11GR2> begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t;
MSG
-------------------------
Autonomous Insert
Here, only the work done by and committed in the autonomous transaction persists. The INSERT done in the anonymous block was rolled back by the ROLLBACK statement on line 4. The autonomous transaction procedure’s COMMIT has no effect on the parent transaction started in the anonymous block.
Data Integrity
Techniques for Guaranteeing Data Integrity
When designing a database application, developers have various options for guaranteeing the integrity of data stored in the database. These options include:
・Enforcing business rules with triggered stored database procedures
・Using stored procedures to completely control access to data
・Enforcing business rules in the code of a database application
・Using Oracle Database integrity constraints, which are rules defined at the column or object level that restrict values in the database
Advantages of Integrity Constraints
The best practice is to enforce data integrity by means other than integrity constraints only when an integrity constraint cannot be used. Advantages of integrity constraints over alternatives for enforcing data integrity include:
・Declarative ease
Because you define integrity constraints using SQL statements, no additional programming is required when you define or alter a table. The SQL statements are easy to write and eliminate programming errors.
・Centralized rules
Integrity constraints are defined for tables and are stored in the data dictionary. Thus, data entered by all applications must adhere to the same integrity constraints. If the rules change at the table level, then applications need not change. Also, applications can use metadata in the data dictionary to immediately inform users of violations, even before the database checks the SQL statement.
・Flexibility when loading data
You can disable integrity constraints temporarily to avoid performance overhead when loading large amounts of data. When the data load is complete, you can re-enable the integrity constraints.
Types of Integrity Constraints
Oracle Database enables you to apply constraints both at the table and column level. A constraint specified as part of the definition of a column or attribute is called an
inline specification. A constraint specified as part of the table definition is called an
out-of-line specification.
Table 5-1 describes the types of constraints. Each can be specified either inline or out-of-line, except for NOT NULL, which must be inline.
Constraint Type | Description |
NOT NULL | Allows or disallows inserts or updates of rows containing a null in a specified column. |
Unique key | Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null. |
Primary key | Combines a NOT NULL constraint and a unique constraint. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. |
Foreign key | Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key. |
Check | Requires a database value to obey a specified condition. |
REF | Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values. In an object-relational database, a built-in data type called a REF encapsulates a reference to a row object of a specified object type. Referential integrity constraints on REF columns ensure that there is a row object for the REF. |
Nulls and Foreign Keys
The relational model permits the value of foreign keys to match either the referenced primary or unique key value, or be null. For example, a user could insert a row into hr.employees without specifying a department ID.
Parent Key Modifications and Foreign Keys
When a parent key is modified, referential integrity constraints can specify the following actions to be performed on dependent rows in a child table:
・No action on deletion or update
In the normal case, users cannot modify referenced key values if the results would violate referential integrity.
・Cascading deletions
A deletion cascades (DELETE CASCADE) when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to also be deleted.
・Deletions that set null
A deletion sets null (DELETE SET NULL) when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to set those values to null.
Indexes and Foreign Keys
As a rule, foreign keys should be indexed. The only exception is when the matching unique or primary key is never updated or deleted. Indexing the foreign keys in child tables provides the following benefits:
・Prevents a full table lock on the child table. Instead, the database acquires a row lock on the index.
・Removes the need for a full table scan of the child table. As an illustration, assume that a user removes the record for for department 10 from the departments table.
States of Integrity Constraints
Checks for Modified and Existing Data
You can set constraints to validate (VALIDATE) or not validate (NOVALIDATE) existing data. If VALIDATE is specified, then existing data must conform to the constraint. For example, enabling a NOT NULL constraint on employees.department_id and setting it to VALIDATE checks that every existing row has a department ID. If NOVALIDATE is specified, then existing data need not conform to the constraint.
Deferrable Constraints
Every constraint is either in a not deferrable (default) or deferrable state. This state determines when Oracle Database checks the constraint for validity.
Nondeferrable Constraints
If a constraint is not deferrable, then Oracle Database never defers the validity check of the constraint to the end of the transaction. Instead, the database checks the constraint at the end of each statement. If the constraint is violated, then the statement rolls back.
For example, assume that you create a nondeferrable NOT NULL constraint for the employees.last_name column. If a user attempts to insert a row with no last name, then the database immediately rolls back the statement because the NOT NULL constraint is violated. No row is inserted.
Deferrable Constraints
A deferrable constraint permits a transaction to use the SET CONSTRAINT clause to defer checking of this constraint until a COMMIT statement is issued. If you make changes to the database that might violate the constraint, then this setting effectively lets you disable the constraint until all the changes are complete.
You can set the default behavior for when the database checks the deferrable constraint. You can specify either of the following attributes:
・INITIALLY IMMEDIATE
The database checks the constraint immediately after each statement executes. If the constraint is violated, then the database rolls back the statement.
・INITIALLY DEFERRED
The database checks the constraint when a COMMIT is issued. If the constraint is violated, then the database rolls back the transaction.
