Transactions¶

Transactions are a set of database-based queries that represent and form a logical case. They should be made integrally or not at all. An example of a very popular transaction is transfer. To perform a basic transfer, you must perform two operations:

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

Both operations can be performed as UPDATE queries. The danger of performing two independent queries may be a failure that will be located between the first and the second update. There should not be a situation in which money will disappear from one account and will not appear on the other account. These operations should be carried out as part of a transaction in which three stages can be specified:

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

The transaction is initiated using the instructions:

START TRANSACTION;

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

COMMIT;

The transaction consists of the following instructions:

START TRANSACTION;
instructions SQL;
COMMIT;

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

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

Transaction cancellation¶

The transaction can be both confirmed with the COMMIT statement, but also canceled with theROLLBACK statement. Performing the following transaction will not introduce any real changes to the database.

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

Automatic Approval¶

Databases such as MySQL have theautocommit option enabled by default. This means that executing a single query automatically makes changes to the database. Thanks to this, such instructions will always be executed in full or not at all. In this way we can avoid a situation in which only part of the data would be modified due to a failure.

Automatic approval can be turned off using the instructions:

SET autocommit=0;

and re-enabled using the instructions:

SET autocommit=1;

ACID¶

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

• Atomicity
• Consistency
• Isolation
• Durability.

Atomicity¶

n The transaction must be indivisible, i.e. it will be carried out in full or in general, e.g. as part of a bank transfer, it may not happen that the money will be withdrawn from the account, but it will not be transferred to the recipient of the transfer.

Consistency¶

The database status must always reflect the state before the transaction or be correctly updated after the transaction has been successfully completed. There cannot be a situation where the database will reflect the state during its execution.

Isolation¶

Transactions must be performed independently of other operations performed, including other transactions.

Durability¶

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