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.|
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
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.
-- 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;
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 it's no longer needed. (At the end of the transaction it's 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>.
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 business logic. E.g.: 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 successful COMMIT).
There are more detailed informations about the atomicity property at Wikipedia.
Transactions guarantees that the database is in a consistent state after they terminate. This consistency occurs at different levels:
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 efficient. 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.
The standard identifies three problematic situations:
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|
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.
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.
Manage research, learning and skills at IT1me. Create an account using LinkedIn to manage and organize your IT knowledge. IT1me works like a shopping cart for information -- helping you to save, discuss and share.