[ Home ] [ Up ] [ DML Statements ] [ DDL Statements ] [ Transaction Statements ]

Transaction Management

Starting a Transaction

In SQL, there is no explicit statement that starts a transaction. A transaction will be started implicitly (if one isn't already started) whenever you execute a SQL statement that requires a transaction context. If you don't tell the system differently, the transaction will have the following default characteristics:

• It will permit both read and write (update) operations
• It will have the maximum possible isolation from other concurrent transactions

If you want to change these characteristics, you must use the SET TRANSACTION statement to explicitly set them:

SET TRANSACTION
READ ONLY,
ISOLATION LEVEL READ UNCOMMITTED

The SET TRANSACTION statement must be the first statement executed in a transaction.

Committing a Transaction

Once all the operations in a transaction have successfully completed, you can commit those changes by using the COMMIT statement:

COMMIT;

Rolling Back a Transaction

If you decide that a transaction's actions should not be (or cannot be) completed, you can roll those actions back by using the ROLLBACK statement:

ROLLBACK;

Mixing DML and DDL in a Transaction

Some database systems allow you to mix DML and DDL in a single transaction. For example:

1. Update a row in the Employees table, then
2. Create a new table, Customers, then
3. Delete a row from the Departments table
4. etc.

Other database systems to not allow you do mix the two. If you attempt to mix them, you may encounter errors, or you may find that a single DDL statement execution will cause the transaction to commit implicitly.

If you are writing code that should run against several database systems, you would be well advised to avoid such mixing of DML and DDL.

AutoCommit Mode

Some systems (such as ODBC and JDBC) have a mode called autocommit mode, which causes an implicit and automatic commit to occur after every SQL statement execution.  For anything other than a trivial transaction, this is not an appropriate choice.