Control TransactionsA 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.
ACIDAny relational database must be capable of passing the ACID Test:
AtomicityDatabase 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.
ConsistencyThe 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.
IsolationIsolation 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.
DurabilityOnce a transaction has committed it is permanent, and remains so even in the event of power or hardware failures.
Starting a TransactionA transaction is begun by any of the following:
- INSERT statement
- UPDATE statement
- DELETE statement
Ending a TransactionThe 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)
- the user session dies
- the system crashes
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.