Some learning on ACID, Transaction in DBMS(Database Management System)

The first time I heard about concept of the transaction in SQL is back to the days when I was at my previous job to handle, manage and check API calling error to see if the data was successfully written into the Database ,which there is rollback or commit on each transaction under certain circumstances.

The purpose of transaction processingm, especially the ACID principles is to have consistency in data, like banking system, or systems that invlove payment flow like e-shops or booking sytems.

ACID

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four concepts are essential for ensuring the reliability and integrity of transactions in a database.

Atomicity

A transaction is an indivisible and complete entity, with only two options: "execute all" or "not execute all".

Consistency

The consistent state of the database. It means that it is consistent before and after the transaction. The consistency of the database has not been destroyed, which means that the written data must fully comply with all preset specifications.

Isolation

The data or intermediate results used during the execution of something do not involve other transactions reading or writing until it is in the Commit stage, which means a transaction will not be affected by one another. There are 4 common situations. Some DBMS define the isolation into different isolation levels as below, in the order from least strict to most strict:

• Read uncommited : Transaction A has updated but not yet commited, and transaction B can only read.
• Read commited : The data read by the transaction must be commited.
  • To avoid dirty read (To read an uncommit transaction, and it is easy to tell by its meaning "dirty", an uneffective data).
• Repeatable read : The data read twice in the same transaction must be the same.
  • To avoid unrepeatable read (If transaction A reads data before and after transaction B, different results will be obtained).
• Serializable (range-locks) : Requires read and write locks on selected objects not to be released until the end of the transaction.
  • To avoid phantom read (One user is repeating a read operation on the same records, but has new records in the results set)

Durability:

Once a transaction is submitted, the change to the database arepermanently effective.

Overall, it's important to consider these principles when designing APIs.