The concept of transaction provides a mechanism for describing logical units of database processing, there are a lot of systems with large databases and hundreds of concurrent users executing database transactions, examples of such systems include airline reservation, banking, supermarket checkout and many others.

What is a Transaction

A transaction includes one or more database access operations forming a single unit of business work that either should be completed in its entirety or not done at all

If all the operations of a transaction are executed successfully, the transaction will be committed and the changes mades by its operations are going to be kept and persisted on the target database, on the other hand, if any operation fails the database will be rolled back to its initial state as if there was no execution of the transaction.

If the database operations in a transaction do not update the database but only retrieve data, the transaction is called a read-only transaction, otherwise it is known as read-write transaction

You may wonder about how a transaction would look like in real life, in fact a transaction can either be specified in a higher level language like SQL or can be embedded within an application program.

When specifying a transaction in SQL it is not mandatory to start it with the BEGIN or START statement, no matter what is the value of the auto commit option of the underlying database management system, however whenever we want to commit or rollback the transaction we must specify the COMMIT and ROLLBACK keywords for setting a clear boundaries to the transaction and preventing any confusion about that, here is an example :

When choosing to embed transaction within application programs, most of the cases we will benefit from an abstract management of the lifecycle related transactions, including committing and rollbacking, here is an example of a transaction in Spring Boot application :

Desirable Properties of a Transaction

For a safe transaction processing, transactions should have 4 properties called ACID properties :

Atomicity

A transaction is unbreakable, it is an atomic unit of processing, Either all its operations are reflected properly on the database, or none are, it is the responsibility of the recovery subsystem of a DBMS to ensure the atomicity. If a transaction fails to complete for some reason, such as a system crash in the midst of execution, the recovery technique must undo any effects of the transaction on the database. On the other hand, write operations of a committed transaction must be eventually written to disk

Consistency

A transaction should be consistency preserving, meaning that should take the database from its initial state into a state respecting integrity constraints, this property is the responsibility of both the programmer would should not perform wrong operations in the transaction, nothing will prevent him from deleting the entire database in his transaction.

Isolation

A transaction should appear as though it is being executed in isolation from other transactions, even though many transactions are execut- ing concurrently, this property is enforced by the concurrency control subsystem of a DBMS.

Durability

The changes applied to the database by a committed transaction must persist in the database, and must not be lost because of any failure. This is the responsibility of the recovery subsystem.