Structured Query Language/Transactions

From Wikibooks, open books for an open world
Jump to: navigation, search

← NULLs and the Three Valued Logic | Glossary →

Transactions[edit]

A transaction is an embracing of one or more SQL statements - especially of such statements, which write to the database such as INSERT, UPDATE or DELETE, but also the SELECT command can be part of a transaction. All writing statements must be part of a transaction. The purpose of transactions is the guarantee that the database changes only from one consistent state to another consistent state fading out all intermediate situations. This holds true also in critical situations such as parallel processing, disc crash, power failure, ... . Transactions ensure the database integrity.

To do so they support four basic properties, which all in all are called the ACID paradigm.

Atomic All SQL statements of the transaction take place or none.
Consistent The sum of all data changes of a transaction transforms the database from one consistent state to another consistent state.
Isolated The isolation level defines, which parts of uncommited transactions are visible to other sessions.
Durable The database retains committed changes even if the system crashes afterwards.

Transaction Boundaries[edit]

As every SQL statement which writes to the database must be part of a transaction, the DBMS silently starts a transaction for every of them, if actually there is no transaction started. An alternative is that the application/session starts a transaction explicitly by the command START TRANSACTION.

All subsequent SQL commands are part of this transaction. The transaction remains until it is confirmed or rejected. The confirmation takes place with the command COMMIT, the rejection with the command ROLLBACK. Before the COMMIT or ROLLBACK command is submitted, the DBMS stores the results of every writing statement into an intermediate area where it is not visible to other sessions (see: Isolation Levels). Simultaneously with the COMMIT command all changes of this transaction ends up in the common database, are visible to every other session and the transaction terminates. If the COMMIT fails for any reason, it happens the same as when the session submits a ROLLBACK command: all changes of this transaction are discarded and the transaction terminates. Please notice, that a session can revert its complete writing actions, which are part of the actual transaction, by submitting the single command ROLLBACK.

An Example:

-- Begin the transaction with an explicit command (In general not necessary. Not supported by Oracle.)
START TRANSACTION;
-- Insert some rows
INSERT ... ;
-- Modify those rows or some other rows
UPDATE ... ;
-- Delete some rows
DELETE ... ;
-- If the COMMIT succeeds, the results of the above 3 commands have been transfered to the 'common' 
-- database and thus 'published' to all other sessions.
COMMIT;
--
-- 
START TRANSACTION;
INSERT ... ;
UPDATE ... ;
DELETE ... ;
-- Discard INSERT, UPDATE and DELETE
ROLLBACK;

Savepoints[edit]

As transactions can cover a lot of statements, it is likely that runtime errors or logical errors arise. In some of such cases applications want to rollback only parts of the actual transaction and commit the rest or resume the processing a second time. To do so, it is possible to define internal transaction boundaries which reflects all processing from the start of the transaction up to this point in time. Such intermediate boundaries are called savepoints. COMMIT and ROLLBACK statements terminate the complete transaction including its savepoints.

-- Begin the transaction with an explicit command
START TRANSACTION;
--
INSERT ... ;
-- Define a savepoint
SAVEPOINT step_1;
--
UPDATE ... ;
-- Discard only the UPDATE. The INSERT remains.
ROLLBACK TO SAVEPOINT step_1;
-- try again (or do any other action)
UPDATE ... ;
-- confirm INSERT and the second UPDATE
COMMIT;

During the lifetime of a transaction a savepoint can be released if he is no longer needed. (At the end of the transaction he is implicitly released.)

-- ...
-- ...
RELEASE SAVEPOINT <savepoint_name>;
-- This has no effect to the results of previous INSERT, UPDATE or DELETE commands. It only eliminates the
-- possiblity to ROLLBACK TO SAVEPOINT <savepoint_name>.

Atomicity[edit]

Transactions guarantees that the results of all of its statements are handled on a logical level as one single operation. All writing statements have a temporary nature until the COMMIT command terminates successful.

This behaviour helps to ensure the logical integrity of bussiness logic. Eg: If one wants to transfer some amount of money from one account to another, at least two rows of the database must be modified. The first modification decreases the amount in one row and the second one increases it on a different row. If there is a disc crash or power failure between this two write-operations, the application has a problem. But the atomicity property of transactions guaranties that none of the write-operations reaches the database (in the case of any failure or a ROLLBACK) or all of them (in the case of a successfull COMMIT).

There are more detailed informations about the atomicity property at Wikipedia.

Consistency[edit]

Transactions guarantees that the database is in a consistent state after they terminate. This consistency occurrs at different levels:

  • The data and all derived index entries are syncronized. In most cases data and index entries are stored at different areas within the database. Nevertheless after the end of a transaction both areas are updated (or none).
  • Table constraints and column constraints may be violated during a transaction (by use of the DEFERRABLE key word) but not after its termination.
  • There may be Primary and Foreign Keys. During a transaction the rules for Foreign Keys may be violated (by use of the DEFERRABLE key word) but not after its termination.
  • The logical integrity of the database is not guaranteed! If in the above example of a bank account the application forgets to update the second row, problems will arise.

Isolation[edit]

In most situations there are a lot of sessions working simultaneously on the DBMS. They compete for their resources, especially for the data. As long as the data is not modified, this is no problem. The DBMS can deliver the data to all of them.

But if multiple sessions try to modify data at the same point in time, conflicts are inescapable. Here is the timeline of an example with two sessions working on a flight reservation system. Session S1 reads the number of free seats for a flight: 1 free seat. S2 reads the number of free seats for the same flight: 1 free seat. S1 reserves the last seat. S2 reserves the last seat.

The central result of the analysis of such conflicts is that all of them are avoidable, if all transactions (concerning the same data) run sequentially: one after the other. But it's obvious that such a behavior is less efficent. The overall performance is increased if the DBMS does as much work as possible in parallel. The SQL standard offers a systematic of such conflicts and the command SET TRANSACTION ... to resolve them with the aim to allow parallel operations as much as possible.

Classification of Isolation Problems[edit]

The standard identifies three problematic situations:

  • P1 (Dirty read): "SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed." [1]
  • P2 (Non-repeatable read): "SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted." [1] Non-repeatable reads concern single rows.
  • P3 (Phantom): "SQL-transaction T1 reads the set of rows N that satisfy some search condition. SQL transaction T2 then executes SQL-statements that generate one or more rows that satisfy the search condition used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same search condition, it obtains a different collection of rows." [1] Phantoms concern result sets.

Avoidance of Isolation Problems[edit]

Depending on the requirements and access strategy of an application some of the above problems may be tolerable - others not. The standard offers the SET TRANSACTION ... command to define, which are allowed to occur within a transaction and which not. The SET TRANSACTION ... command must be the first statement within a transaction.

-- define (un)tolerable conflict situations (Oracle does not support all of them)
SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED |
                                 READ COMMITTED   |
                                 REPEATABLE READ  |
                                 SERIALIZABLE];

The following table shows which problems may occur within each level.

Isolation level Dirty reads Non-repeatable reads Phantoms
Read Uncommitted may occur may occur may occur
Read Committed - may occur may occur
Repeatable Read - - may occur
Serializable - - -

At Wikipedia there are more detailed informatiton and examples about isolation levels and concurrency control.

Durability[edit]

Transactions guarantees that every confirmed write-operation will survive (almost) every following disaster. To do so, in most cases the DBMS writes the changes not only to the database but additionally to logfiles, which shall reside on different devices. So it is possible - after a disc crash - to restore all changes from a database backup plus these logfiles.

There are more detailed informations about the durability property at Wikipedia.

Autocommit[edit]

Some DBMS offers - outside of the standard - an AUTOCOMMIT feature. If it is activated, the feature submits automatically a COMMIT command after every writing statement with the consequence that you cannot ROLLBACK a logical unit-of-work consisting of a lot of SQL statements. Furthermore the use of the SAVEPOINT feature is not possible.

In much cases the feature is activated by default.

References[edit]