Oracle Certification - SQL Fundamentals I - Transactions

Control Transactions

A transactions consists of a number of DML statements and a COMMIT or ROLLBACK command. The SAVEPOINT command can also be used to five finer control over the transaction.

ACID

Any relational database must be capable of passing the ACID Test:

Atomicity

Database transactions are all or nothing. A transaction must succeed in it's entirety or fail. Atomicity must be maintained even in the case of power failures, crashes et cetera.

Consistency

The results of a query must be consistent with the state of the database when the query began running. So any changes to the database after the query starts running do not appear in it's result set. The upshot of this is for a rapidly changing database, and a long running query, you are likely to get an error message.

Isolation

Isolation means that only one transaction can affect data at one time. So until a transaction has completed and been committed no other transaction can see the final results.

Durability

Once a transaction has committed it is permanent, and remains so even in the event of power or hardware failures.

Starting a Transaction

A transaction is begun by any of the following:

• INSERT statement
• UPDATE statement
• DELETE statement

Essentially by the issue of a DML statement.

Ending a Transaction

The transaction then persists through any number of DML statements until the user issues a COMMIT or ROLLBACK statement. There are also certain circumstances under which the database will automatically issue a commit:

• DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE)

Other events cause the database to automatically ROLLBACK the transaction:

• the user session dies
• the system crashes

Finally if the user exits a tool in the middle of a transaction, whether a COMMIT or ROLLBACK is performed entirely depends on the tool in question.

COMMIT;

Once a COMMIT statement is issued the changes made in a transaction are immediately made permanent and visible to all other sessions. The database has actually made the changes for the transaction, but until the commit statement they are hidden from all other sessions in the database. Once the data is committed newly issued SQL statements can see the changes.

ROLLBACK [TO SAVEPOINT savepoint];

Whilst a transaction is in progress an image of the data before the transaction is maintained by the database. This is used for any sessions that access the data before the transaction has committed, and it is used to restore the database to it's previous statement in the case of a ROLLBACK.

Note: Whereas a commit has already taken place, and is therefore instantaneous, a rollback requires the database to restore it's past state and therefore takes almost as long as the original transaction. Rollbacks hamper the performance of the database.

SAVEPOINT savepoint;

A savepoint is a marker in a transaction that can control how far back a ROLLBACK statement will be affective. By rolling back a transaction to a named savepoint all transactions after that savepoint are undone, whilst all proceeding transactions remain.

Note: Savepoints are not standard SQL.

Summary

Next up is Tables.