The need for transactions

To enable a DBMS to support recovery and provide concurrency, and for a program to be able to control its database updates, a program needs to be able to take action on completion of a transaction, allowing for one of only two possible situations:

• A transaction succeeds, the program instructs the DBMS to commit all changes to the database.
• A transaction fails, the program instructs the DBMS to rollback all changes to the database. The database returns to its state prior to the start of the transaction.

SQL Transaction Management

A single SQL statement is atomic. It can cause many rows to be inserted, deleted or updated. For each statement a DBMS must automatically ensure that either all database changes are completed or no database changes at all are made.

Example - if rows containing a column with a NULL value are to be copied from one table to another target table and the column of the target table has been defined as NOT NULL, the transaction must fail. No rows at all will be inserted into target table.

A trigger is also atomic, the update either succeeds or fails dependent on the result of the triggering action. This also applies to referential actions. A cascaded delete can be expressed as a single atomic statement.

Transactions are multiple statements, each one atomic. Each individual statement may fail, but the overall transaction may not fail as a result of individual failure.

An SQL transaction is defined with:

COMMIT WORK

ROLLBACK WORK

COMMIT - causes all statements in a transaction to be recorded in a database. ROLLBACK - ensures that a transaction being completed has no effect on a database.

WORK is optional. These two statements correspond to the completion of a transaction. There is no keyword to start a transaction. Transactions exist between the COMMIT or ROLLBACK statements and start automatically after the completion of the previous transaction.

Transaction statements cannot be included in database routines, they are embedded within the application program and remain under the control of an application process.

The use of triggers and referential actions when executed as part of an SQL statement can simplify the organization of transactions.

Concurrent transaction problems

• Lost update - Two users query the same data, they both realise that data requires updating. User A updates the table with data with certain values. User B updates the same data using different values. The data values input by user A is lost.
• Reading uncommitted data - User A updates the database. User B reads the data. User A then does a rollback. User B now believes that the data he has read is accurate. In reality the data was not committed. This is an example of uncommitted data or a dirty read.
• Non-repeatable read - Two users execute the same query with identical results. User A then does an update. Later, user B repeats the earlier query with different results. For user B the data is not consistent, other users have interfered with their view of the data.
• Phantom rows - Similar to the non-repeatable read problem. This time one user inserts a row. A second user, repeating an earlier query, now finds a row that did not previously exist.

Serializable execution

A partial solution is for a DBMS to allow only one transaction at a time, known as a serialized execution of transactions. However this is not practicable because one long transaction prevents all other users from accessing the data.

Serializable transactions provide the isolation of serialized execution whilst allowing the execution of many transactions concurrently.

Users of a shared database cannot expect data to remain unchanged by other users. Interference can only be prevented within a transaction.

Locking and isolation levels

A DBMS processes a concurrent transaction by marking any data involved as locked. If another transaction requires the locked data it must wait until the locked data is released upon completion of the transaction.

It is possible to relax the locking dependent upon the isolation level:

Comments, suggestions, ideas to
Stuart Banner