TRANSACTION

ISOLATION LEVELS
INSTRUCTOR: TRẦN CÔNG ÁN
SUBJECT: ORACLE DBMS

GROUP MEMBERS:
LÊ NHẬT BẰNG – B2205974
HUỲNH TÚ PHƯƠNG – B2206005
ĐẶNG NHẬT TƯỜNG – B2206021
LÂM THẾ VINH – B2206
 CONTENTS
DEFINITION & READ TRANSACTION ISOLATION
01 02
PHENOMENON LEVELS

ADVANTAGES & CONCLUSION

03 04
DISADVANTAGES
 RECAP
A transaction is a unit of program execution that accesses and possibly
updates various data items.

Atomicity Isolation
PROPERTIES OF THE
TRANSACTION : ACID

Consistency Durability
DEFINITION & PHENOMENON
Transaction isolation levels define the degree to which the operations in one
transaction are isolated from the operations of other concurrent transactions.
Dirty read: A dirty read is a type of situation that occurs when a transaction
reads the data and that data has not been committed yet.
Non-repeatable read: The non-repeatable Read occurs when the transaction
reads a single row multiple times and gets a different value on each read.
Phantom read: When the two queries are executed, but they retrieve the two
rows differently, at that time, phantom read occurs.
TRANSACTION ISOLATION LEVELS

• READ UNCOMMITTED
• READ COMMITTED ( DEFAULT )
• REPEATABLE READ
• SERIALIZABLE
 READ UNCOMMITTED
Read-uncommitted: is the lowest level of isolation where a transaction can see
uncommitted changes made by other transactions.
Pros:
• Fast Processing Speed: Transactions can read data immediately without
waiting, resulting in quicker response times.
• No Blocking: Reading transactions do not hinder other transactions from
updating data, allowing smoother system operations.
Cons:
• Allows dirty reads, non-repeatable reads, and phantom reads.
 Dirty Reads
Allows reading data from uncommitted transactions, leading to potentially
inaccurate information.
Example: Set Balance A=1.000.00, Balance B=500.000
Transaction 1 Transaction 2 RESULT
READ (A)
A:=A-100.000 A=900.000
WRITE (A) B= 600.000
READ (B)
B:=B+100.000
WRITE (B)
---------------------------------------------------------------------
…
-
…
READ (A)
READ (B)
COMMIT
ROLLBACK;
Non-repeatable Reads
Allows changes from other transactions to affect the data being read by the
current transaction, resulting in different values on subsequent reads.
Example: Set Balance A =1.000.00
Transaction 1 Transaction 2 RESULT
READ (A)
------------------------------------------------------------- BEFORE: A=1.000.000
READ(A) AFTER: A=1.500.000
A:=1.500.000
WRITE(A)
COMMIT
----------------------------------------
READ (A)
COMMIT
Phantom Reads
Allows changes from other transactions to affect the result set of a query, leading
to differences in query results within the same transaction.
Example: Category include “Product A” and “Product B”
Transaction 1 Transaction 2 RESULT
READ (A)
READ (B) BEFORE: PRODUCT A, B
----------------------------------------------------- AFTER: PRODUCT A,B,C
---ADD NEW C
READ (C)
WRITE (C)
COMMIT
READ (A) -----------------------------------------
READ (B) -
READ (C)
COMMIT
------------------------------------------------------
 READ COMMITTED

Read committed: is an isolation level in database management systems that

ensures any data read during a transaction is committed at the moment it is read.
Pros:
• Prevents Dirty Reads
• Performance
Cons:
• Allows non-repeatable and phantom reads
• You can be blocked by another transaction
 REPEATABLE READ

Repeatable read: This is the most restrictive isolation level. Until the transaction is
completed, other transactions cannot read, update, or delete those data units.
Pros:
• Prevents other transactions from modifying the data you are reading, non-
repeatable reads
• Prevents dirty reads
Cons:
• Allows phantom reads
• You can be blocked by a REPEATABLE READ transaction.
 SERIALIZABLE
Serializable: This is the highest isolation level. All transactions must be executed
sequentially
Pros:
• Prevents any and all isolation anomalies
• Provides a rigorous guarantee that each transaction sees a wholly consistent
view of the database, even when there are concurrent transactions
Cons:
• Dirty Reads
• Non-repeatable Reads
• Phantom Reads
SUMMARY
 ADVANTAGES & DISADVANTAGES

Advantages Disadvantages

Improved concurrency: by allowing multiple Increased overhead: because the database

transactions to run concurrently without interfering management system must perform additional checks
with each other. and acquire more locks.

Control over data consistency: Isolation levels Decreased concurrency: such as Serializable, can
provide control over the level of data consistency decrease concurrency by requiring transactions to
required by a particular application. acquire more locks, which can lead to blocking.
 ADVANTAGES & DISADVANTAGES
Advantages Disadvantages
Reduced data anomalies: such as dirty reads, Limited support: Not all database management
non-repeatable reads, and phantom reads. systems support all isolation levels, which can limit the
portability of applications across different systems.
Flexibility: The use of different isolation levels
provides flexibility in designing applications that
Complexity: The use of different isolation levels can
require different levels of data consistency.
add complexity to the design of database applications,
making them more difficult to implement and maintain.
CONCLUSION
References