Transaction control

The operations that make up a transaction do not all happen simultaneously. While they are being carried out therefore, a second user can query the data. If that happens, the second user will get an incorrect impression of the state of the database. For example if User2's query happens after all records have been inserted but before the invoice record has been updated with the tax, the customer's bill will appear smaller than it actually is.

It is therefore important that before the entire transaction is complete, none of the updates are visible to other database users. To accomplish this, the DBMS effective creates a copy of the database objects that are affected by the transaction. The changes are made in the copy, and only when they are all complete are the changes made permanent in the database. Performing the operations in a protected copy also means that if any one operation should fail, all update can be abandoned.

This behaviour can be demonstrated by using two independent connections to the same database to simulate two different users. This can be done in Oracle by logging into SQL Developer as the HR user, and logging in as HR through SQL*Plus at the same time.

With a small amount of investigation, you will find that there are 25 records in the countries table, but it does not contain a record for Spain. In SQL*Plus, type the following:

    INSERT INTO countries (country_id, country_name, region_id)
                   VALUES ('ES', 'Spain', 1);

Now the two connections show different states of the database. Try the following query in both SQL*Plus and SQL Developer:

1	`SELECT COUNT(*) FROM countries;`

To make the change permanent, you need to tell the database that the transaction is complete by issuing the command:

    COMMIT;

Try it now, and then check that both connections now show the same number of countries.

If for some reason you do not want the transaction to go ahead after all, you can undo the changes by issuing the command:

1	`ROLLBACK;`

This exercise demonstrates the property of isolation by which other users are protected from a transaction until it is complete.

Consistency is another property of transactions which ensures that a transaction returns a database to a consistent state once it is complete. A consistent state in this context is one in which no constraints are violated. By default, Oracle validates each constraint immediately, even during a transaction. Using the appropriate commands, however, a database programmer can defer constraint checking until the end of the transaction. In this situation records can be inserted even if foreign key values do not match primary keys elsewhere. As long as all the data is in place by the end of the transaction, everything completes as normal. On the other hand, if the COMMIT command is issue to terminate the transaction and some constraints are still violated, the whole transaction is rolled back.

Transaction control

Further reading