Skip to content

Transactions

Transactions are a set of database queries that represent and form a logical whole. They should be integral or not at all. An example of a very popular transaction is a transfer. To make a basic transfer, you need to perform two operations:

  • update of the sender's account to reduce the amount transferred
  • update of the recipient's account to increase the total amount on the account.

Both operations can be performed as UPDATE queries. The danger of running two independent queries can be a failure that will be located between the first and second update. There shouldn't be a situation in which money disappears from one account and does not appear in the other account. These operations should be performed as part of a transaction in which 3 stages can be specified:

  • start of the transaction
  • execution of instructions
  • approval or withdrawal of the transaction

The transaction is initiated using the following instructions:

START TRANSACTION;

All further operations will be part of the transaction and will only be visible in the session until they are approved. Approval is carried out using the following instructions:

COMMIT;

The transaction consists of the following instructions:

START TRANSACTION;
SQL statements;
COMMIT;

The example of a bank transaction described could look like this:

START TRANSACTION:
UPDATE BankAccount SET Balance=Balance-40 WHERE Id=1;
UPDATE BanAccount SET Balance=Balance+40 WHERE Id=2;
COMMIT;

Cancellation of the transaction

The transaction can be either committed with the COMMIT statement, or canceled with theROLLBACK statement. Performing the transaction below will not make any real changes to the database. 

START TRANSACTION:
UPDATE BankAccount SET Balance=Balance-40 WHERE Id=1;
UPDATE BankAccount SET Balance=Balance+40 WHERE Id=2;
ROLLBACK;

Automatic approval

Databases like MySQL have theautocommit option turned on by default. This means that executing a single query automatically makes changes to the database. Thanks to this, such an instruction will always be executed completely or not executed at all. In this way, we can avoid a situation where only some commands would be modified due to a failure.

Automatic approval can be turned off using the instruction:

SET autocommit=0;

and re-enabled with the instruction:

SET autocommit=1;

ACID

ACID is a set of properties that ensure that transactions are processed correctly in the database. This name comes from 4 key properties that transactions should be characterized by:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

ACID

Atomicity

The transaction must be indivisible, i.e. it will be performed in full or not at all, e.g. as part of a bank transfer, it cannot happen that the money will be withdrawn from the account, but will not be transferred to the recipient of the transfer.

Consistency

The state of the database must always reflect the state before the transaction or be correctly updated after the correct execution of the transaction. There cannot be a situation where the database will reflect the state in execution.

Isolation

Transactions must be performed independently of any other operation performed, including other transactions.

Durability

As a result of any failure, the transaction must be completed in full or it must be canceled (not executed at all).