Introduction

Managing transactions is crucial for ensuring data consistency and integrity in database systems. Transactions consist of operations such as Create, Read, Update, and Delete (CRUD) that are executed together as a single unit of work.

ACID transactions can provide strong consistency guarantees, but can also be expensive and have a high overhead. Isolation levels in databases are used to provide a balance between consistency and performance in multi-user database environments, depending on the specific needs of the application.

In the next sections we will explore behaviors called transaction phenomena, and their impact on data integrity. We will also discuss transaction isolation levels in Oracle database and show some real world examples of their consequences.

Transactions Phenomena

Transaction phenomena refer to behaviors that can emerge during the concurrent execution of transactions in relational databases, potentially affecting data integrity. These phenomena are typically the result of using weaker isolation levels than those provided by standard ACID transactions. The SQL-92 standard identifies three¹ such phenomena, which include:

Dirty Read

A transaction reads uncommitted data that was written by another transaction.

Non-repeatable Read

A transaction reads the same data twice, and the value changes between the reads due to another transaction.

Phantom Read

A transaction reads a set of rows, and a new row appears in the set due to another transaction.

Transactional Isolation levels

In an ideal world, if transactions could execute one after another without any overlap, there would be no need for isolation levels. However, this isn't feasible in real-world scenarios due to performance constraints. The ANSI SQL standard defines four isolation levels:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Each level provides different guarantees regarding data consistency and isolation from other transactions

Here are default isolation levels for some transactional databases:

• Oracle: Read committed
• SQL Server: Read committed
• PostgreSQL: Read committed
• MySQL: Repeatable Read

It is worth noting that Oracle Database supports only two isolation levels: Read Committed and Serializable, as it considers the two other levels to provide weaker consistency guarantees than those supported.

Non-intuitive examples

Examples provided in section "Transactions Phenomena" were pretty simple. In this section I will show you two sample non-intuitive examples when those phenomena may occur. Please keep in mind that those examples are a result of bad system design and should not be used in real world application! They are intended as a warning against such usage. The examples were run on Oracle database on default isolation level.

Example 1: update with nested select statement

Let's say that we want to update a user's status to 'inactive' if they haven't made any orders since a specific date:

UPDATE user 
SET status = 'inactive' 
WHERE id NOT IN (SELECT user_id 
                 FROM order 
                 WHERE order_date > :since_date);

The issue with this query is the potential for Phantom Read in the orders table. The orders table is not locked during this query in any way. This query is equivalent to the following:

SELECT user_id 
FROM order 
WHERE order_date > :since_date;

UPDATE user 
SET status = 'inactive' 
WHERE id NOT IN (:user_ids_from_previous_query);

The only difference is that, in the first case, a race condition is less likely because the entire query is executed in a single database call.

Suppose a user places an order precisely when the above query is run. We might end up with an inactive user who placed an order a while ago. In the diagram below, we'll use two separate queries (select + update), but it works exactly the same when using update with a nested select.

Example 2: insert from result of select

In this example, we have a table called "work_lock" that serves as a critical section for certain processes. If a process wants to execute some work, it first needs to insert the work id into the "work_lock" table to prevent the work from being executed multiple times:

INSERT INTO work_lock (work_id, locked_by) 
SELECT id, 'my_worker' 
FROM work 
WHERE status = 'NEW' 
  AND id not in (select work_id from work_lock)

Similar to the previous example, the SELECT statement is prone to Phantom Read. There is still a possibility that two processes might attempt the same work. Additionally, there is also another issue: the query is likely to cause a deadlock. However, we will address this topic in a separate blog post.

How can we prevent that

To prevent transaction phenomena, you can use pessimistic locking, optimistic locking, or change the isolation level. However, adjusting the isolation level isn't suggested because it can affect speed and increase the risk of deadlocks. Changing the isolation level can prevent transaction phenomena, but it's generally not recommended as a first approach. Higher isolation levels provide better consistency guarantees but may result in decreased performance, increased resource usage, and a higher likelihood of deadlocks. The choice between pessimistic or optimistic locking depends on the specific use case, which will be covered in a separate blog post.