Everything You Need To Know About Transactions And A.C.I.D

What happens if there is a failure in our database system? Will operations that are being processed be performed or will they be canceled?

Well, reality is usually not as rosy as we would like, failures have happened, are happening and will continue to happen. However, is it possible to do something that would guarantee the execution of the operation or its rollback in the event of a failure?

In such situations, a mechanism called transactions comes to our aid.

What is a transaction?

It is nothing more than a method used by the application to group, read and write operations into one logical unit. The result of a transaction is one of two states: Success (the transaction is committed) or Failure (the transaction is canceled or rolled back).

Maybe this is an example?

Let’s look at a classic transaction example: a bank transaction exchanging funds between two accounts, Account A and Account B.

The owner of Account A wants to transfer PLN 100 to Account B

Generally, we have two approaches in this example.

Approach 1:

• Operation 1: Account A balance = Account A balance - PLN 100;
• Operation 2: Account B balance = Account B balance + PLN 100;

Approach 2:

• Operation 1: Account B balance = Account B balance + PLN 100;
• Operation 2: Account A balance = Account A balance - PLN 100;

In fact, the whole operation is a bit more complicated (there are checks, business and legal rules of the bank, etc.), but the idea is the same.

If a failure occurs while an operation is being performed, we have a problem. Either PLN 100 will evaporate from Account A and will not appear in Account B, or it will appear in Account B, but the balance of Account A will not be changed.

A transaction comes to the rescue, as it covers the execution of an operation in one of the Approaches and guarantees: successful execution of both operations (COMMIT), or no changes in the states of both Accounts (rollback / ROLLBACK)

BEGIN [TRANSACTION]
             AccountStateA = AccountStateA - 100;
             AccountBan = AccountB + 100;
END [TRANSACTION] (COMMIT)

Is Transaction a cure for all evil?

Transactions, like any other thing, have their advantages and limitations. They are not always the answer to every problem, and they are certainly not completely free (e.g. in terms of performance).

Do you need a transaction then? To answer this clearly, we need to understand the details and flavors behind the “security guarantees”.

Guarantees have been with us since 1983 🙂 you know them by the acronym - A.C.I.D.

• A (Atomicity)
• C (Consistency).
• I (Isolation)
• D (Durability).

They are a set of mechanisms for providing fault tolerance. What are the components of this acronym and what real impact it has on our work, see below.

A - ATOMICITY

It is an assurance that an operation or set of operations included in a transaction will be completed in full or canceled.

Example:

OPERATION 1 ————————> SUCCESS (e.g. INSERT)

OPERATION 2 ————————> SUCCESS (e.g. DELETE)

OPERATION 3 ————————> FAILURE (e.g. INSERT - to archive)

OPERATION 4 ————————> SUCCESS (e.g. UPDATE on data added to the archive)

For databases that ensure atomicity, in the above example the transaction will be canceled and the changes made will be rolled back ROLLBACK. If all operations were successful, the transaction would be accepted COMMIT.

In the absence of atomicity, and one or more operations fail at the same time, it will be difficult to determine exactly what actions were performed (unless the results of the actions were obvious). Retrying the same transaction may lead to data errors (overwriting values, adding values and duplicates, etc.).

C - CONSISTENCY

Consistency is understood differently depending on the context; in ACID theory, consistency is responsible for the fact that “the database is in good condition” and should actually be treated as a property of the application and not a feature of the database.

What does application property and database in good condition mean?

A feature of the application may be the fact that in the example of a bank transaction, the balance of the account from which we send money must allow the operation to be performed. I.e. after performing the operation, the account balance must be non-negative (we assume that we do not have any credit limits, etc.) It is simply a set of rules that must be followed within the application. Fulfillment of the rule is defined by the fact that the database is in good condition.

In most cases, it is the application creator who defines the rules and is responsible for observing them (he may transfer some of the responsibility to the database in the form of TRIGGERS (triggers), CONSTRAINTS (restrictions, e.g. foreign or unique keys), etc.), but due to errors in the application incorrect data will be recorded, the database itself will not stop you from doing so.

I - ISOLATION

Isolation means that simultaneously executed transactions are isolated from each other - they cannot influence each other. This is especially important in the case of access to the same resource (same table) - in the case of access to different objects, the conflict will not occur.

Another definition is one in which isolation is a guarantee that the parallel execution of transactions will leave the database in a state as if the operations were performed sequentially. It is worth remembering that isolation is an exchange of database integrity at the expense of performance.

Example:

D - DURABILITY - DURABILITY

Durability is the promise that after successfully accepting a transaction, the saved data will not be forgotten (regardless of emergency situations - hardware/base failure).

This can be achieved by writing data to disks (SSD / HDD) + adding the operation to the WAL log (Write Ahead Log - the operation is first written to the log and subsequently performed) or by database replication.

Thanks,
Krzysztof

Want to be up to date with new posts?
Use below form to join Data Craze Weekly Newsletter!