Transaction Control

A TRANSACTION is a unit of work. A TRANSACTION may be a single action or it may be a series of actions. For example, if you add 5 records and all 5 records must be added before saving, that is a single transaction. If you add 5 records and you save between each, those are 5 discrete transactions.

NOTE: Whether or not the 5 transactions must be saved as one transaction or as multiple transactions is based on the business rules for an application. In general, the database does not care.

TRANSACTION CONTROL refers to the commands that allow you to save, or not, any data that you may have manipulated. Technically, transaction control has its own language, separate from DML called the Transaction Control Language or TCL. However, in most conversations, TCL is usually encompassed in DML. Regardless, TCL is important to understand before you start using DML.

Begin Transaction/End Transaction

The keywords “BEGIN TRANSACTION” and “END TRANSACTION” are part of the ANSI SQL standard. Unfortunately, not all vendors have implemented these keywords the same way. For some, the keywords are optional, other required. For some, END TRANSACTION is implicitly called by COMMIT or ROLLBACK and you will get an error by using this keyword. Others require an END TRANSACTION and will commit your data when called.

To make it a little more difficult, at least one database treats your DML and DDL commands differently depending on whether or not you call BEGIN TRANSACTION. For this database, if you do not explicitly begin the transaction, each successful DML command is auto-saved and any errors are rolled back. If you do explicitly begin the transaction, by calling BEGIN TRANSACTION, all commands are treated as a single transaction and are committed or rolled back together.

The easiest way to see if your database requires these commands is to check the documentation for your chosen database.

The important thing to remember about these keywords is that they really don't do anything to your data. They don't add data or change data. In some databases, using the END TRANSACTION will save any changes you have already made.

Synonyms for BEGIN TRANSACTION are BEGIN, BEGIN WORK and START TRANSACTION.

Rollback

ROLLBACK is arguably the most important command that you will learn. I call it, fondly, the “oops” command. With the ROLLBACK command you can undo any kind of DML. In many databases, you can even undo Data Definition Language (DDL) commands.

ROLLBACK will rollback all changes back to the beginning of a transaction. In some systems, that means back to the last BEGIN TRANSACTION, where as in other databases that means back to the last COMMIT or ROLLBACK.

In Oracle, DDL cannot be undone via ROLLBACK. DML can. You are not required to begin a transaction in Oracle and a ROLLBACK will rollback to the last COMMIT or ROLLBACK before it.

The syntax for a ROLLBACK is very simple:

ROLLBACK;

Many vendors like to extend the database with proprietary syntax extensions on the standard. Oracle, for example, adds the FORCE keyword to handle special situations with distributed transactions. One extension to rollback, albeit part of the standard, is rollback to savepoint. Not all databases support SAVEPOINT.

Savepoint

A savepointallows a developer or user to maintain multiple rollbackpoints in a SQLscript or procedure. You can use them interactively but that is less useful as it can quickly get confusing.

Savepoints allow nested rollbacks. You can execute a DML command, i.e. insert a record, issue a savepoint, insert another record and then rollback just the second insert. Without savepoints, a rollback would roll back both inserts.

Savepoint work by giving a name to a savepoint.

INSERT INTO taba(column1) VALUES (123);

SAVEPOINT first_savepoint;

INSERT INTO tabb(column2) VALUES (456);

To rollback all changes, we would use:

ROLLBACK;

To rollback just the second insert, we roll back to the named savepoint:

ROLLBACK TO first_savepoint;

It is possible for the script or session to have multiple savepoints.

INSERT INTO taba(column1) VALUES (123);

SAVEPOINT first_savepoint;

INSERT INTO tabb(column2) VALUES (456);

SAVEPOINT another_save;

INSERT INTO taba(column2) VALUES (456);

SAVEPOINT oogabooga;

INSERT INTO tabd(column3) VALUES (789);

We could roll back to any one of these:

ROLLBACK TO oogabooga;

ROLLBACK TO another_save;

Executing these two rollback commands would leave the first two inserts intact but would undo inserts 3 and 4.

Had we issued:

ROLLBACK TO first_savepoint;

None of the other savepoints would remain. Rolling back to a higher level savepoint invalidates the lower level savepoints. As a matter of a fact, if we executed:

ROLLBACK TO first_savepoint;

ROLLBACK TO oogabooga;

We would get an error in most databases.

Commit

The second most important command you can learn is the COMMIT command. Commit tells the database to save any additions, changes or deletions that you may have made. The basic syntax is very simple:

COMMIT;

In some databases, you may need to say:

COMMIT WORK:

In some databases, COMMIT WORK and COMMIT TRANSACTION are synonyms for COMMIT. As mentioned above, in some databases, END TRANSACTION is also a synonym for COMMIT.

Some extensions that you may see for the COMMIT keyword allow you to COMMIT your work but let you continue processing before the change is written to disk (NOWAIT) or to force a commit on distributed transactions (FORCE).

It's always important to review your vendor documentation for specific extensions to the SQL standard as some of these extensions are extremely valuable. For example, Oracle's NOWAIT extension can dramatically improve performance in certain situations where there is an extremely high transaction rate and during batch loads.