phantom read dbms

Phantom Read Problem in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Phantom Read Concurrency Problem in SQL Server with Examples

In this article, I am going to discuss the Phantom Read Concurrency Problem in SQL Server with Examples. Please read our previous article where we discussed the Non-Repeatable Read Concurrency Problem in SQL Server with an example. At the end of this article, you will understand what is phantom read problem is and when it occurs in SQL Server as well as you will also understand how to solve the phantom read problem in SQL Server.

What is Phantom Read Concurrency Problem in SQL Server?

The Phantom Read Concurrency Problem happens in SQL Server when one transaction executes a query twice and it gets a different number of rows in the result set each time. This generally happens when a second transaction inserts some new rows in between the first and second query execution of the first transaction that matches the WHERE clause of the query executed by the first transaction.

Understanding Phantom Read Concurrency Problem in SQL Server

Let’s understand Phantom Read Concurrency Problem in SQL Server with an example. We are going to use the following Employees table to understand this concept. 

Please use the below SQL Script to create and populate the Employees table with the required sample data.

Phantom Read Concurrency Problem in SQL Server:  

Let say we have two transactions Transaction 1 and Transaction 2. Transaction 1 starts first and it reads the data from the Employees table where Gender is Male and it returns 2 rows for the first read and then Transaction 1 is doing some other work. At this point, Transaction 2 started and it inserts a new employee with Gender Male. Once Transaction 2 inserts the new employee, then Transaction 1 makes a second read and it returns 3 rows, resulting in a Phantom Read Concurrency Problem in SQL Server. 

Phantom Read Concurrency Problem in SQL Server with an Example:  

Let us understand Phantom Read Concurrency Problem in SQL Server with an example. Open 2 instances of the SQL Server Management Studio. From the first instance execute the Transaction 1 code and from the second instance execute the Transaction 2 code. Notice that when Transaction 1 is completed, it gets a different number of rows for reading 1 and reading 2, resulting in a phantom read problem. The Read Committed, Read Uncommitted, and Repeatable Read Transaction Isolation Level causes Phantom Read Concurrency Problem in SQL Server. In the below Transactions, I am using REPEATABLE READ Transaction Isolation Level, even you can also use Read Committed and Read Uncommitted Transaction Isolation Levels.

Transaction 1

Transaction 2, how to solve the phantom read concurrency problem in sql server.

You can use the Serializable or Snapshot Transaction Isolation Level to solve the Phantom Read Concurrency Problem in SQL Server. Please have a look at the following table which shows which isolation level solves which concurrency problems in SQL Server.

In our example, to fix the Phantom Read Concurrency Problem let set the transaction isolation level of Transaction 1 to serializable. The Serializable Transaction Isolation Level places a range lock on the rows returns by the transaction based on the condition. In our example, it will place a lock where Gender is Male , which prevents any other transaction from inserting new rows within that Gender. This solves the phantom read problem in SQL Server.  

When you execute Transaction 1 and 2 from 2 different instances of SQL Server Management Studio. Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same number of rows. Modify the Transaction 1 code as follows:

What are the Differences between Repeatable Read and Serializable Isolation levels?

The Repeatable Read Transaction Isolation Level prevents Non-Repeatable Read, Dirty Read, and Lost Update concurrency problems. In order to do this, the Repeatable Read adds additional locks on the data read by the transaction, which ensures that once a transaction reads the data, then those data will be prevented from being read, update or delete by any other transaction. But it allows to insertion of new data into the database, as a result, it does not prevent Phantom Read Concurrency Problem .

On the other hand, the Serializable Isolation Level prevents all sorts of concurrency problems such as Non-Repeatable Read, Dirty Read, Lost Update, and Phantom Read . In order to do this, it places a range lock on the data return by the transaction which ensures that once a transaction reads the data, then no other transaction can read, update, delete or insert new data within that range. 

In the next article, I am going to discuss the Snapshot Isolation Level in SQL Server with an example. Here, in this article, I try to explain the Phantom Read Problem in SQL Server with Examples. I would like to have your feedback. Please post your feedback, question, or comments about this article.

About the Author:

Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

• Engineering Mathematics
• Discrete Mathematics
• Operating System
• Computer Networks
• Digital Logic and Design
• C Programming
• Data Structures
• Theory of Computation
• Compiler Design
• Computer Org and Architecture

• Write an Interview Experience
• ACID Properties in DBMS
• Precedence Graph For Testing Conflict Serializability in DBMS
• Cascadeless in DBMS
• Two Phase Locking Protocol
• Thomas Write Rule in DBMS
• Multiple Granularity Locking in DBMS
• Concurrency Control Techniques
• Transaction States in DBMS
• Main difference between Timestamp protocol and Thomas write rule in DBMS
• Starvation in DBMS
• Deadlock in DBMS
• Transaction Isolation Levels in DBMS
• Recovery from failures in Two Phase Commit Protocol (Distributed Transaction)
• Recovery With Concurrent Transactions
• Physical and Logical Data Independence
• View Serializability in DBMS
• Domain Relational Calculus in DBMS
• Types of Locks in Concurrency Control
• Attribute Closure Algorithm and its Utilization

Concurrency problems in DBMS Transactions

Concurrency control is an essential aspect of database management systems (DBMS) that ensures transactions can execute concurrently without interfering with each other. However, concurrency control can be challenging to implement, and without it, several problems can arise, affecting the consistency of the database. In this article, we will discuss some of the concurrency problems that can occur in DBMS transactions and explore solutions to prevent them.

When multiple transactions execute concurrently in an uncontrolled or unrestricted manner, then it might lead to several problems. These problems are commonly referred to as concurrency problems in a database environment. 

The five concurrency problems that can occur in the database are: 

• Temporary Update Problem
• Incorrect Summary Problem
• Lost Update Problem
• Unrepeatable Read Problem
• Phantom Read Problem

These are explained as following below. 

Temporary Update Problem:  

Temporary update or dirty read problem occurs when one transaction updates an item and fails. But the updated item is used by another transaction before the item is changed or reverted back to its last value. 

Example:  

In the above example, if transaction 1 fails for some reason then X will revert back to its previous value. But transaction 2 has already read the incorrect value of X. 

Incorrect Summary Problem:  

Consider a situation, where one transaction is applying the aggregate function on some records while another transaction is updating these records. The aggregate function may calculate some values before the values have been updated and others after they are updated. 

In the above example, transaction 2 is calculating the sum of some records while transaction 1 is updating them. Therefore the aggregate function may calculate some values before they have been updated and others after they have been updated. 

Lost Update Problem:  

In the lost update problem, an update done to a data item by a transaction is lost as it is overwritten by the update done by another transaction. 

In the above example, transaction 2 changes the value of X but it will get overwritten by the write commit by transaction 1 on X (not shown in the image above) . Therefore, the update done by transaction 2 will be lost. Basically, the write commit done by the last transaction will overwrite all previous write commits. 

Unrepeatable Read Problem:  

The unrepeatable problem occurs when two or more read operations of the same transaction read different values of the same variable. 

In the above example, once transaction 2 reads the variable X, a write operation in transaction 1 changes the value of the variable X. Thus, when another read operation is performed by transaction 2, it reads the new value of X which was updated by transaction 1. 

Phantom Read Problem:  

The phantom read problem occurs when a transaction reads a variable once but when it tries to read that same variable again, an error occurs saying that the variable does not exist. 

In the above example, once transaction 2 reads the variable X, transaction 1 deletes the variable X without transaction 2’s knowledge. Thus, when transaction 2 tries to read X, it is not able to do it. 

To prevent concurrency problems in DBMS transactions, several concurrency control techniques can be used, including locking, timestamp ordering, and optimistic concurrency control.

Locking involves acquiring locks on the data items used by transactions, preventing other transactions from accessing the same data until the lock is released. There are different types of locks, such as shared and exclusive locks, and they can be used to prevent Dirty Read and Non-Repeatable Read.

Timestamp ordering assigns a unique timestamp to each transaction and ensures that transactions execute in timestamp order. Timestamp ordering can prevent Non-Repeatable Read and Phantom Read.

Optimistic concurrency control assumes that conflicts between transactions are rare and allows transactions to proceed without acquiring locks initially. If a conflict is detected, the transaction is rolled back, and the conflict is resolved. Optimistic concurrency control can prevent Dirty Read, Non-Repeatable Read, and Phantom Read.

In conclusion, concurrency control is crucial in DBMS transactions to ensure data consistency and prevent concurrency problems such as Dirty Read, Non-Repeatable Read, and Phantom Read. By using techniques like locking, timestamp ordering, and optimistic concurrency control, developers can build robust database systems that support concurrent access while maintaining data consistency.

Please Login to comment...

• demishassabis
• somaghosh8202
• DBMS-Transactions and Concurrency Control

Please write us at contrib[email protected] to report any issue with the above content

Improve your Coding Skills with Practice

Phantom read/update problem

Phantom problem is a phenomena . A data problem during concurrency update.

In the phantom problem, a transaction accesses a relation more than once with the same predicate in the same transaction, but sees new phantom tuples on re-access that were not seen on the first access.

In other words, a transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

Articles Related

• Concurrency - Concurrency
• Transaction - Isolation (Level|Degree) - (Locking Level ?)
• Phenomena - Data problem (Concurrency Problem/ Data Corruption)
• A transaction queries the number of employees.
• Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire.
• More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.

This is because two-phase locking at tuple-level granularity does not prevent the insertion of new tuples into a table. Two-phase locking of tables prevents phantoms, but table-level locking can be restrictive in cases where transactions access only a few tuples via an index.

15.7.4 Phantom Rows

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “ phantom ” row.

Suppose that there is an index on the id column of the child table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:

The query scans the index starting from the first record where id is bigger than 100. Let the table contain rows having id values of 90 and 102. If the locks set on the index records in the scanned range do not lock out inserts made in the gaps (in this case, the gap between 90 and 102), another session can insert a new row into the table with an id of 101. If you were to execute the same SELECT within the same transaction, you would see a new row with an id of 101 (a “ phantom ” ) in the result set returned by the query. If we regard a set of rows as a data item, the new phantom child would violate the isolation principle of transactions that a transaction should be able to run so that the data it has read does not change during the transaction.

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “ gap ” before the index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

When InnoDB scans an index, it can also lock the gap after the last record in the index. Just that happens in the preceding example: To prevent any insert into the table where id would be bigger than 100, the locks set by InnoDB include a lock on the gap following id value 102.

You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “ lock ” the nonexistence of something in your table.

Gap locking can be disabled as discussed in Section 15.7.1, “InnoDB Locking” . This may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Transaction Isolation Levels (ODBC)

• 5 contributors

Transaction isolation levels are a measure of the extent to which transaction isolation succeeds. In particular, transaction isolation levels are defined by the presence or absence of the following phenomena:

Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.

Nonrepeatable Reads A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted.

Phantoms A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 generates a new row (through either an update or an insert) that matches the search criteria for transaction 1. If transaction 1 reexecutes the statement that reads the rows, it gets a different set of rows.

The four transaction isolation levels (as defined by SQL-92) are defined in terms of these phenomena. In the following table, an "X" marks each phenomenon that can occur.

The following table describes simple ways that a DBMS might implement the transaction isolation levels.

Most DBMSs use more complex schemes than these to increase concurrency. These examples are provided for illustration purposes only. In particular, ODBC does not prescribe how particular DBMSs isolate transactions from each other.

It is important to note that the transaction isolation level does not affect a transaction's ability to see its own changes; transactions can always see any changes they make. For example, a transaction might consist of two UPDATE statements, the first of which raises the pay of all employees by 10 percent and the second of which sets the pay of any employees over some maximum amount to that amount. This succeeds as a single transaction only because the second UPDATE statement can see the results of the first.

Submit and view feedback for

Additional resources

Programming Geeks Club

• April 7, 2023

Phantom Read Concurrency Problems in DBMS Transactions

When working with database management systems (DBMS), transaction isolation is an essential feature to ensure data consistency and prevent issues such as dirty reads, non-repeatable reads, and phantom reads. Phantom reads are a concurrency problem that can occur in DBMS transactions, leading to inconsistent query results and data integrity issues.

In this article, we’ll explore what phantom read is, its causes, and the techniques used to prevent it.

Definition of Phantom Read in DBMS Transactions

Phantom reads occur when a transaction reads a set of records twice but gets different results each time. This can happen when another transaction inserts or deletes records that match the criteria of the first transaction between its two reads. As a result, the first transaction “sees” records that didn’t exist during its initial read, hence the term “phantom” read.

For example, suppose a transaction selects all records with a value of “foo” from a table. Then, another transaction inserts a new record with the value “foo” before the first transaction completes its second read. In that case, the first transaction will see an additional record, which it didn’t see during its initial read. This can lead to inconsistent query results and data integrity issues.

Causes of Phantom Read in DBMS Transactions

Phantom reads occur due to concurrent transactions and the isolation level used by the DBMS. Concurrent transactions are transactions that execute at the same time, accessing and modifying the same data. The isolation level determines how concurrent transactions interact with each other, allowing or preventing certain concurrency problems like phantom reads.

Isolation Levels in DBMS Transactions

DBMS supports several isolation levels, such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable, which provide different levels of data consistency and transaction concurrency. Each isolation level uses a different mechanism to control concurrent access to data, such as locking or multiversion concurrency control.

Read Uncommitted

The Read Uncommitted isolation level allows a transaction to read uncommitted changes from other transactions, allowing dirty reads, non-repeatable reads, and phantom reads. This level provides the highest concurrency but the lowest data consistency, making it unsuitable for most applications.

Read Committed

The Read Committed isolation level only allows a transaction to read committed changes from other transactions, preventing dirty reads but allowing non-repeatable reads and phantom reads. This level provides a reasonable balance between concurrency and data consistency, making it suitable for most applications.

Repeatable Read

The Repeatable Read isolation level ensures that a transaction sees a consistent view of the database throughout its execution, preventing dirty reads and non-repeatable reads but allowing phantom reads. This level achieves data consistency by acquiring shared locks on all rows read by a transaction until the transaction completes.

Serializable

The Serializable isolation level provides the highest level of data consistency, preventing all concurrency problems such as dirty reads, non-repeatable reads, and phantom reads. This level achieves data consistency by acquiring shared locks on all rows read by a transaction and preventing other transactions from acquiring locks on those rows.

Best FREE YouTube Video Downloader (2023)

How to pickle and unpickle objects in python: a complete guide, ultimate python multithreading guide, google playstore banned irecorder app for using ahrat malware, examples of phantom read in dbms transactions.

Let’s consider an example to illustrate how phantom reads can occur in a DBMS transaction. Suppose a user wants to transfer $100 from their checking account to their savings account. The following SQL statements are executed:

Transaction 1:

Transaction 2:.

Transaction 1 selects the checking account record for update, subtracts $100 from its balance, and commits.

Transaction 2 inserts a new checking account record with a balance of $500 and commits. Suppose the DBMS uses the Read Committed isolation level.

Now, suppose Transaction 1 executes the SELECT statement before Transaction 2 executes the INSERT statement. Transaction 1 will see the original checking account record with a balance of $300 and subtract $100 from it, leaving a balance of $200. However, after Transaction 1 commits, Transaction 2 inserts a new checking account record with a balance of $500. If Transaction 1 re-executes the same SELECT statement, it will see two checking account records, the original one with a balance of $200 and the new one with a balance of $500. This is an example of a phantom read.

Impact of Phantom Read on DBMS Transactions

Phantom reads can have a significant impact on DBMS transactions, leading to inconsistent query results and data integrity issues. Suppose a user executes a query that involves a phantom read. In that case, the query results may include records that didn’t exist during the initial read, leading to incorrect data analysis or decision-making.

Phantom reads can also cause data integrity issues. Suppose a user executes a query that selects all records that match a certain criteria, then deletes them. If another transaction inserts new records that match the same criteria before the delete operation completes, the delete operation will not delete those records, leading to data inconsistency.

Techniques to Prevent Phantom Read in DBMS Transactions

To prevent phantom reads in DBMS transactions, several techniques can be used, such as locking and multiversion concurrency control.

Locking is a technique that prevents concurrent access to data by acquiring locks on rows or tables that are being read or modified by a transaction. Locking can prevent phantom reads by locking all rows that match the criteria of a SELECT statement until the transaction completes. However, locking can reduce concurrency and cause deadlocks, where two or more transactions are waiting for each other to release locks.

Multiversion Concurrency Control (MVCC)

Multiversion concurrency control is a technique that allows multiple versions of a record to exist simultaneously, each associated with a different transaction. MVCC can prevent phantom reads by allowing a transaction to read a consistent view of the database at the start of the transaction, even if other transactions modify the same data during the transaction. MVCC achieves this by creating a snapshot of the database at the start of the transaction and using that snapshot to ensure data consistency.

Best Practices for Dealing with Phantom Read in DBMS Transactions

To minimize the impact of phantom reads in DBMS transactions, several best practices should be followed, such as selecting appropriate isolation levels, designing database schemas carefully, and minimizing transaction duration.

Select Appropriate Isolation Levels

Selecting the appropriate isolation level is essential to prevent phantom reads. If high concurrency is required, Read Committed is a suitable isolation level. If data consistency is critical, Serializable is a suitable isolation level.

Design Database Schemas Carefully

Careful database schema design can minimize the occurrence of phantom reads. For example, using constraints to enforce data integrity can prevent phantom inserts or updates.

In this example, the Employee table has a foreign key constraint on the Department table, ensuring that all employees belong to a valid department. This prevents phantom reads caused by querying non-existent departments.

Minimize Transaction Duration

Phantom reads are more likely to occur in long-running transactions that involve multiple SELECT statements. Minimizing transaction duration can reduce the likelihood of phantom reads and improve overall transaction performance.

In this example, the transaction only updates a specific set of records, minimizing the lock duration and reducing the chance of conflicts with other transactions.

Discover the best free YouTube video downloader websites that let you save and change YouTube videos to MP4, MP3, and…

Learn how to pickle and unpickle objects in Python using the pickle module. Find out the benefits, drawbacks and best…

Master Python multi-threading with our comprehensive guide. Unlock superior performance and efficiency in your Python applications….

Phantom reads are a concurrency problem that can occur in DBMS transactions, leading to inconsistent query results and data integrity issues. They occur due to concurrent transactions and the isolation level used by the DBMS. Preventing phantom reads requires selecting appropriate isolation levels, using techniques like locking and MVCC, and following best practices such as careful database schema design and minimizing transaction duration. Understanding phantom reads and their impact on DBMS transactions is essential for designing high-performance and data integrity-preserving database systems. DBMS users and developers must take into account phantom reads when designing applications and selecting appropriate isolation levels to ensure consistent and accurate data processing.

Join Our Newsletter!

Join our newsletter to get our latest ebook " Ultimate JavaScript Cheat-Sheet ", and Tips, Articles..

We don’t spam! Read our privacy policy for more info.

You’ve been successfully subscribed to our newsletter, and will be sent to your mail address.

Tags: concurrency database DBMS

You may also like...

rest api to upload image in Golang

Golang Maps

How to Convert Int to String In Golang

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

 Yes, add me to your mailing list

• Next Read File Line by Line In Rust
• Previous Choosing Between Rust and Swift: The Ultimate Guide for Developers

NPM Packages Found Hiding Dangerous TurkoRat Malware

The Recent Security Threats on Python Package Index (PyPI) and Its Implications

Stay Alert: The Rising Threat of Malicious Extensions in Microsoft’s VSCode Marketplace

How to Create Custom Array/Slice Methods in Go: A Simple Guide

Revolutionizing AI Tech: ChatGPT Code Interpreter Plugin Outshines GPT-4

AutoGPT: A Game-Changer in the World of AI Applications

# PHANTOM read

# isolation level read uncommitted.

Create a sample table on a sample database

Now open a First query editor (on the database) insert the code below, and execute ( do not touch the --rollback ) in this case you insert a row on DB but do not commit changes.

Now open a Second Query Editor (on the database), insert the code below and execute

You may notice that on second editor you can see the newly created row (but not committed) from first transaction. On first editor execute the rollback (select the rollback word and execute).

Execute the query on second editor and you see that the record disappear (phantom read), this occurs because you tell, to the 2nd transaction to get all rows, also the uncommitteds.

This occurs when you change the isolation level with

(opens new window)

← Encryption Filestream →

Phantom Phenomenon

When there are multiple transactions that are taking place at the same time in an uncontrolled or unrestricted manner, sometimes, the order of ‘select’ and  ’insert/delete ’ commands may allow the database in different states. This state is called the Phantom Phenomenon .

Example-12 :

Consider the following statements.

      (a) Select  FName 

From  FACULTY

Where salary < 50000 ;

      (b) Insert into FACULTY values(‘111’, 22,‘CSE’,’Ramu’,’PhD’, 44000) ;  

Here, the order of (a) and (b) will result in different states of the database. This situation is known as ‘ Phantom Phenomenon ’.

Reference Link