Introduction to Transactions

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing a transaction on the database.

Example

Let's say you are transferring money from one account to another. The transaction would involve two operations: deducting the amount from one account and adding the amount to another account. Both operations must be completed successfully; otherwise, the database should be rolled back to the state before the transaction started.

ACID Properties

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

Atomicity

Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

Consistency

The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors do not result in the violation of any defined rules.

Isolation

The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on the concurrency control method, the effects of an incomplete transaction might not even be visible to other transactions.

Durability

Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

Lock Types

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. Locks are used to prevent data corruption and ensure data integrity. Locks are used to prevent two or more transactions from modifying the same data at the same time. Locks are used to ensure that the data is consistent and that the data is not corrupted by multiple transactions.

Types of Locks

There are two types of locks: shared locks and exclusive locks.

Shared Locks

Shared locks are used when a transaction is reading data. Shared locks allow multiple transactions to read the same data at the same time. Shared locks are compatible with other shared locks but are not compatible with exclusive locks. Shared locks are used to prevent transactions from modifying data that is being read by other transactions.

Exclusive Locks

Exclusive locks are used when a transaction is modifying data. Exclusive locks prevent other transactions from reading or modifying the data that is being modified by the transaction. An exclusive lock is not compatible with other exclusive locks or shared locks.