SQL Transactions

Transactions define boundaries around a group of SQL commands such that they either all successfully execute together or not at all. This is typically referred to as the atomic principle of database integrity. A classic example of the rationale behind transactions is a money transfer. Say a bank program is transferring money from one account to another. The money transfer program can do this in one of two ways: first insert (credit) the funds into account 2 and then delete (debit) it from account 1, or first delete it from account 1 and insert it into account 2. Either way, the transfer is a two-step process: an insert followed by a delete, or a delete followed by an insert.

But what happens if, during the transfer, the database server suddenly crashes or the power goes out, and the second operation does not complete? Now the money either exists in both accounts (the first scenario) or has been completely lost altogether (second scenario). Either way, someone is not going to be happy. And the database is in an inconsistent state. The point here is that these two operations must either happen together or not at all. That is the essence of transactions.

Transaction Scopes

Transactions are issued with three commands: begin, commit, and rollback.

begin starts a transaction. Every operation following a begin can be potentially undone and will be undone if a commit is not issued before the session terminates. The commit command commits the work performed by all operations since the start of the transaction. Similarly, the rollback command undoes all the work performed by all operations since the start of the transaction. A transaction is a scope in which operations are performed and committed, or rolled back, together. Here is an example:

sqlite> begin;
sqlite> delete from foods;
sqlite> rollback;
sqlite> select count(*) from foods;

count(*) -------- 412

We started a transaction, deleted all the rows in foods, changed our mind, and reversed those changes by issuing a rollback. The select statement shows that nothing was changed.

Conflict Resolution

Constraint violations cause the command that committed the violation to terminate. What exactly happens when a command terminates in the middle of making a bunch of changes to the database? In most databases, all of the changes are undone. That is the way the database is programmed to handle a constraint violation.