Transactions and ACID Attributes are two of the important concepts when learning and working with databases. Transactions and ACID properties are closely related and in this article we will learn about each concept in detail through simple examples.
Viewing: What is Acid
The first and very important thing when learning about databases is that you need to have a solid understanding of the concept of database transactions.
What is Database Transaction
A database transaction is a sequence of many different actions performed on the database, these actions are closely related to each other and are considered as a single unit.
Example: Company AAA opens an account at Vietcomank Bank with the first deposit amount of 500 million VND. One fine day, Alice, an employee of the accounting department, withdrew 20 million dong to reward Bob for his excellent performance in the month. Mr. Bob also opened an account at Vietcombank and Alice transferred this bonus to Mr. Bob Vietcombank’s account.
If we analyze the bonus transfer action from a database perspective, the remittance action is segregated into two different actions:
Deduct 10 million VND from AAA company’s deposit account. Add 10 million VND to Bob’s deposit account.
Although separated into the above two separate actions, both are combined into a single transaction because they are each closely related and we need to make sure both of these actions are performed successfully or won’t. What action is taken if an error occurs in the database that causes one of the two actions to fail (e.g., shortly after the first action takes place, the server room loses power).
The characteristic of a transaction is that the actions in the transaction are treated as a single unit and do not allow any action to be performed successfully if one or several other actions are not performed. presently. In the above example, if the first action fails and the second action is still successful, this will lead to Mr. Bob receiving another 10 million when Alice then performs the transfer again. million for him. Of course Mr. Bob would be happy about this, but the banker wouldn’t necessarily feel the same way.
It is also important to note that not every action that takes place in a consecutive sequence is considered a transaction. The following example is not a transaction:
Bob transferred 3 million from Vietcombank account to his savings account at Sacombank.Bob transferred 5 million from Vietcombank account to his securities investment account at ACB.Bob withdrew 2 million from Vietcombank account to go. drink with friends.
With the above example, even if they happen to be close in time, it is not considered a transaction because these actions can take place separately. For example, a failure of the 3rd action does not cause the second or first action to be cancelled.
In the next section, we will learn about the ACID property of transactions.
See also: What is autism – Is autism dangerous?
What is ACID Transaction
ACID is a property which consists of a set of four different properties that apply to a database transaction. These four characteristics include: atomicity, consistency, isolation, and durability. The word ACID is an acronym made up of the first letters of the four words above.
Atomicity (also known as atomic for short) stipulates that all actions of a transaction need to be completed successfully or conversely if an action is not performed, no action will be taken. any other action is successfully executed.
Called atomicity because each of these individual actions are like electrons revolving around a nucleus and when the nucleus moves all these electrons move together without allowing a single electron to leave. trajectory.
The characteristic of consistency (or shorter, consistent) stipulates that at any point in time, before or after a transaction is executed with or without errors, the database must be kept in a valid state (e.g. data must conform to the rules defined for the database).
In the bonus transfer example mentioned at the beginning of the article, if the first transfer of the transaction encounters an error, the second action will need to be canceled (or rolled back).
The independence feature (isolation or isolation) stipulates that each different transaction needs to be performed in an independent environment, if there are two transactions happening at the same time, a mechanism is needed to ensure this transaction does not affect. to another transaction.
For example, if there is a customer who transfers 10 million VND to AAA company’s account at Vietcombank right at the time when Alice withdraws 10 million VND. What will happen? At this point the database will must perform the following two actions at the same time:
Update the amount in ACB’s account by subtracting 10 million from the current balance (= 500 million = 10 million) Update the amount in ACB’s account by adding 10 million from current balance (= 500 million + 10 million)
How much will the AAA bank account of Vietcombank after these two actions in the end be 500 million (= 500 million – 10 million + 10 million) or 510 million (500 million + 10 million) or will it be only 490 million ( 500 million – 10 million)?
With the independent feature of the transaction is introduced to ensure that when the transaction takes place, the related data on the database will be locked. Therefore other actions (or other transactions) will either have to wait until the original transaction is completed or will be reported to have encountered an error.
Consistency or consistency stipulates that when a transaction is performed (successful or rolled back on failure), then no matter what happens to the database (power failure, server overflow, etc.) ), when restored, the restored data will remain in the state before the crash (make sure no previous transaction is not lost).
For example, after the bonus transfer transaction in the first example was successfully performed and 3 hours later the server room lost power, when restoring data to the database (using data that was automatically backed up earlier). 1 minute), then this transaction (as well as all transactions that took place before the time the server performed the automatic backup) needs to be restored and displayed in the database after the restore.
See also: What is Nhieu Thi – Nhuoc Thi causes blindness
The two concepts of transaction and ACID in the database are often mentioned quite often in interviews and although it is not too difficult to follow, there are still many people who are quite vague about them. Hopefully after reading this article you have had an overview of these two concepts as well as the relationship between them. In the following articles, we will learn how to create a transaction in the MySQL database.