Transactions

A transaction is a sequence of database operations that are treated as a single unit. In other words, either all operations are executed successfully, or, in case of an error, none of them are executed. A transaction will never complete successfully if at least one operation in the sequence fails due to an error.

Consider an example. Imagine transferring money from one bank account to another:

Start of transaction: you initiate a money transfer.
Withdrawal: a certain amount of money is debited from your account.
Deposit: that amount of money is added to the recipient's account.
End of transaction: if both operations are completed successfully, the transaction is finalized.

If an error occurs (for example, insufficient funds in the account), the transaction is canceled, and no changes to the financial state of the accounts take place.

Transactions protect data integrity and guarantee that all parts of a transaction are executed in full. This helps avoid situations where partially completed operations could lead to errors or data inconsistency.

Transaction Properties

Every transaction in a database must comply with four fundamental properties known as ACID:

Atomicity. Atomicity means "all or nothing." In the context of a transaction, this means that all operations (steps) in the transaction must be executed successfully, or, in case of failure of even one of them, no operation will be executed.

Example: if you are writing a text message and it fails to send due to a lack of network, the message remains unsent rather than being sent halfway.
Consistency. Consistency ensures that a transaction transitions the database from one valid state to another valid state, adhering to all database rules.

Example: if you have a rule that the balance of a bank account cannot be less than 0, then a transaction that reduces the balance will not be allowed if it would result in a balance less than 0.
Isolation. Isolation means that transactions are executed independently of one another. Changes made by one transaction do not affect other transactions until they are completed.

Example: if two people simultaneously update the same record in the database, the first will not see the changes made by the second until their own transaction is completed.
Durability. Durability guarantees that once a transaction is completed, its results remain in the database even in the event of a system failure, such as a power outage.

Example: after a document has been successfully saved, it will remain saved even if the computer suddenly shuts down.

ACID properties are important for ensuring data integrity in a database. They guarantee that applications using databases are protected from errors, unforeseen crashes, and issues with concurrent data access. This is especially important in financial systems, enterprise resource management systems, and generally anywhere data integrity matters.

Using Transactions in Databases

Transaction operations are quite important. They guarantee that the database remains in a consistent state and allow you to easily roll back changes if necessary.

As an example, let's use our company database and create another table in it. Imagine that our company has an internal currency that employees can transfer to each other as a thank-you or as a gift. To store information about employee accounts, we will need the following table:

CREATE TABLE BonusAccounts (
AccountID INT AUTO_INCREMENT,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE,
Balance INT,
PRIMARY KEY (AccountID)
);

By the way, note the FOREIGN KEY construct. It indicates that the EmployeeID field contains the employee ID from the employees table. ON DELETE CASCADE means that if an employee is deleted from the employees table, their account data from this table will also be deleted. Instead of CASCADE, you can also use SET NULL (then the employee ID field will simply become empty) or NO ACTION (then the employee cannot be deleted from the database as long as they have an account).

Now let's create bonus accounts for two of our employees. Each account will have an initial balance of 5:

INSERT INTO BonusAccounts (EmployeeID, Balance)
VALUES ('1', '5'),
   ('2', '5');

Let's perform a transaction and transfer one unit of internal currency from the account of employee with ID 1 to the account of employee with ID 2. To start a transaction, use the START TRANSACTION statement:

START TRANSACTION;
UPDATE BonusAccounts SET Balance = Balance - 1 WHERE EmployeeID = '1';
UPDATE BonusAccounts SET Balance = Balance + 1 WHERE EmployeeID = '2';
COMMIT;

Inside the transaction, the data of two accounts will be updated: one balance will increase by 1, and the other will decrease. Next, we need to commit these changes to make sure they are permanent. This can be done using COMMIT.

Let's verify the result of the transaction execution using a SELECT query:

SELECT * FROM company.BonusAccounts;

If an error occurs during the execution of a transaction and you need to undo the changes, you can use the ROLLBACK statement. Then the transaction will be "rolled back," the insert and update statements will not be executed, and no changes will occur in the database:

START TRANSACTION;
UPDATE BonusAccounts SET Balance = Balance - 1 WHERE EmployeeID = '1'; UPDATE BonusAccounts SET Balance = Balance + 1 WHERE EmployeeID = '2';
SELECT * FROM company.BonusAccounts;
ROLLBACK;
SELECT * FROM company.BonusAccounts;

As you can see, transactions are a very convenient built-in mechanism that allows you to ensure data consistency quite easily.