A transaction is a logical unit of work performed to change the state of a database.
It is a unit of work that manipulates data in a database.
A transaction can perform several operations.
If even one operation fails while a transaction is running, the entire transaction is rolled back. If all operations succeed, a commit is performed.
What Is Transaction ACID?
Atomicity
Ensures that one transaction is not partially executed or interrupted.
Consistency
Means that even when a transaction completes successfully, the database remains in the same valid state as before the work.
Isolation
Ensures that other work cannot interfere while a Transaction is being performed.
Durability
Means that successfully completed transactions must be reflected permanently.
Notes When Using Transactions
Minimize the scope of a transaction.
It is important to minimize the scope of a transaction.
Because the number of database connections is limited, the time a connection is held should be minimized.
Otherwise, other services may have to wait to use the connection.
What Are the Types and Characteristics of Transaction Isolation Levels?
READ UNCOMMITTED
When executing a SELECT query, data that has not been committed by another transaction can be read.
Reading uncommitted data is called a dirty read.
Be careful because data that has only been inserted and may be rolled back, meaning data that has not been committed, can be read.
READ COMMITTED
Read Committed guarantees a level where only committed data is visible during SELECT, and most DBMSs use Read Committed as the default.
Read Committed guarantees that dirty reads, which occur in Read Uncommitted, do not occur.
Even if a transaction has not performed COMMIT, the value may already be reflected in the DB. To guarantee data before COMMIT, the process of restoring uncommitted queries is needed. In other words, Consistent Read must be performed at this point.
The problem with Read Committed is that it does not guarantee that data remains the same each time SELECT is performed within a single transaction. This is because if another transaction has committed that data, Read Committed returns the committed data.
For this reason, Read Committed is also called Non-repeatable Read.
REPEATABLE READ
Unlike Read Committed, Repeatable Read guarantees that values read do not change even if SELECT is performed repeatedly within a transaction.
A Repeatable Read transaction records the time when the first SELECT is performed, and for all subsequent SELECTs, performs Consistent Read based on that point in time.
Therefore, even if another transaction commits during the transaction, newly committed data is not visible.
This is because it reads the snapshot created at the first SELECT.
SERIALIZABLE
Serializable provides the highest isolation level, as if all work were processed in one transaction.
A common issue with both Read Committed and Repeatable Read is that Phantom Read can occur.
What is Phantom Read?
Phantom Read refers to a case where an UPDATE command in one transaction is lost or overwritten, or where after UPDATE and COMMIT, a subsequent query shows unexpected values or data has been lost.
Unlike those levels, in SERIALIZABLE, all SELECT queries are automatically changed to SELECT … FOR SHARE, which can prevent several situations that Repeatable Read cannot prevent.
Commit and Rollback
Commit
When a transaction ends, it notifies the transaction manager that it has completed.
Rollback
When transaction processing ends abnormally and breaks database consistency, rollback means canceling all operations.