Polimi CS - Notes

3 - Transactional Systems

7 min read

  • transaction
  • acid properties
  • atomicity
  • consistency
  • isolation
  • durability

Transactions are fundamental components of any transactional system, functioning as atomic units of work that applications perform.

Example

To illustrate the need for transactions, consider the example of a concert ticket purchase system. When a user decides to buy a ticket, several critical steps must occur to ensure that the transaction is processed smoothly and accurately.

Initially, the system checks whether the user’s account has sufficient funds for the withdrawal. If the account balance is adequate, the system proceeds to debit the account. At this point, the account is marked as debited, but the funds are still pending disbursement. Once the transaction is validated, the money is disbursed, and the debit is confirmed, ensuring that the user is charged the correct amount. A receipt is then printed, providing the user with a record of the transaction.

Definition

A transaction can be defined as an elementary, atomic unit of work performed by an application. Each transaction is conceptually encapsulated by two commands:

BEGIN TRANSACTION
...
END TRANSACTION

Within this framework, the transaction is concluded by executing one of two commands: commit-work (which finalizes the transaction) or rollback-work (which aborts the transaction if something goes wrong).

This structure ensures that either all actions within the transaction are completed successfully, or none are applied, maintaining data integrity.

It is important to differentiate between an application and a transaction: a transaction represents a singular, indivisible unit of work that cannot be subdivided, whereas an application encompasses a series of actions, which may consist of multiple transactions.

In the context of a banking system, transactions play a crucial role in ensuring that financial operations are executed reliably and maintain data integrity.

Example: Simple Bank Transfer

In a simple bank transfer, we start a transaction to move funds between accounts. The process begins with begin transaction. The system then updates the balance of account 12202 by adding 10 and decreases the balance of account 42177 by the same amount. This ensures accurate fund transfer.

The transaction ends with commit-work, finalizing the changes. If any step fails before the commit, the transaction is rolled back, keeping the database consistent.

begin transaction;    -- starting point of the transaction
 
update Account
set Balance = Balance + 10
where AccNum = 12202;
 
update Account
set Balance = Balance - 10
where AccNum = 42177;
 
commit-work;          -- end transaction

In a more complex scenario, a bank transfer can include conditional checks to prevent overdrawing an account. The transaction starts with begin transaction. Funds are transferred by updating the balances of the two accounts. After these updates, the system retrieves the balance of the account from which funds were withdrawn (account 42177) and stores it in a variable A.

If A is greater than or equal to zero, the transaction is finalized with commit-work, applying the changes. If A is less than zero, the transaction is aborted with rollback-work, ensuring no changes are made and preventing overdraft.

begin transaction;  -- starting point of the transaction
 
update Account
set Balance = Balance + 10
where AccNum = 12202;
 
update Account
set Balance = Balance - 10
where AccNum = 42177;
 
select Balance into A
from Account
where AccNum = 42177;
 
if (A >= 0) then
    commit-work; -- end transaction with success
else
    rollback-work; -- end transaction with failure

ACID Properties of Transactions

The ACID properties are essential characteristics of database transactions that ensure the reliability and consistency of data in the event of system crashes, power failures, or other anomalies. These properties include Atomicity, Consistency, Isolation, and Durability, and they are crucial for ensuring that transactions execute in a reliable, controlled manner.

Atomicity

Atomicity refers to the idea that a transaction is an indivisible unit of work. This means that either all the operations within a transaction are executed successfully, or none of them are executed at all. The system ensures that no partial transaction can occur, preventing inconsistencies in the database.

Example

For instance, in a bank transfer, if only the debit operation were executed without the corresponding credit, it would create discrepancies.

Thus, if an error occurs before the transaction is completed, the system triggers a rollback to undo all changes and restore the database to its original state. The moment the commit is executed marks the successful completion of the transaction, after which no rollback can affect it.

Errors that may trigger a rollback can be caused by either the application itself (through an explicit ROLLBACK command) or by the DBMS for reasons such as violation of integrity constraints or concurrency issues. After a rollback, the database returns to the state it was in before the transaction started, and it is the responsibility of the application to decide whether to retry the transaction or abandon it.

Consistency

Consistency ensures that a transaction maintains the integrity of the database by ensuring that any integrity constraints defined in the database schema are not violated. If the database is in a consistent state before the transaction begins, it must remain consistent after the transaction is completed. However, this does not necessarily apply to the intermediate states during the transaction’s execution. For instance, while work hours may be shifted between tasks during the transaction, by the time it is completed, the total worked hours must match the planned hours for the project.

Example

Imagine a database where the sum of worked hours for tasks in a project must equal the total planned hours. If this constraint is satisfied before a transaction, it must still be satisfied after the transaction ends.

Temporary violations of this constraint may occur while the transaction is in progress, but the transaction must ensure that these violations do not persist once the transaction is complete.

Isolation

Isolation ensures that the execution of a transaction is independent of other transactions running concurrently. Even if multiple transactions are happening at the same time, their results must be as if they were executed sequentially.

For example, if two transactions, and , are running concurrently, their final result should be equivalent to one of the following sequences: executing followed by , or followed by .

This property ensures that each transaction is isolated from others, preventing conflicts or interference between transactions that might affect data integrity.

However, complete isolation can sometimes come at the cost of performance. In systems with heavy transaction loads, databases may implement levels of isolation to balance consistency and performance.

Durability

Durability guarantees that once a transaction has been successfully committed, its effects will persist in the database, even in the event of a system failure. This means that once a transaction’s changes are saved, they will not be lost due to power outages, crashes, or other failures. The database system ensures this by writing the changes to persistent storage (such as disk) during the commit phase.

For example, after a bank transfer is committed, even if the system crashes immediately afterward, the transaction’s changes (such as account debits and credits) will remain intact.

This property ensures the reliability of the system, making certain that completed transactions are durable and permanent.

Backlinks

Graph View