What are isolation levels and how can they affect a transaction
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
In this article we will talk about one of the properties ACID of RDBMS and more specifically about his status Isolation.
During relational database transactions, many users will try to access the same data at the same time.
At this point it plays its role isolation level.
What is isolation level
Depending on the isolation level, the effects of concurrent access change.
As much as lower level is isolation, more users can access the data, but with some effects such as the effect of dirty reads, non-repeatable reads and phantom reads.
As much as bigger is the level of isolation, these phenomena disappear, but increasing the probability to blocking to objects that are accessed by multiple users at the same time.
Before we continue we should know what locks and blocks are
That lock is a lock on a piece of data to ensure that depending on the isolation level no other transaction will be able to access that data.
The block occurs when two or more transactions try to access the same data. So the first transaction creates a lock on the data and the second one waits for the first transaction to complete and so on.
What are dirty reads, phantom reads, non-repeatable reads and what are they showing
Dirty reads
The dirty reads occur when you allow a transaction to read data while another transaction is in progress modifying it.
A simple example we can think of is a transaction that changes a value from 20 to 21 without being committed. A second transaction reads this field. Then due to this phenomenon the second will read the value 21 while it is uncommitted.
Non-repeatable readings
The non-repeatable reads occur when a record is read more than once in a transaction but the value returned differs each time it is read.
For example, we read a field that has the value 20 with the first transaction, while then a second transaction changes its value to 21 and commits completing it. So if we re-read the value in the first transaction it will have become the value of 21.
Phantom reads
The phantom reads occur when during one transaction, a second transaction tries to add or remove records. As a result if the first transaction re-reads the data, it will return a different number of records than last time.
For example with one transaction we read a table that returns 3 records and in a second transaction we insert a record into the table. In this case when we read it again with the first transaction we will have 4 entries in the table this time.
Let's see the list of ISO SQL standard isolation levels
Serializable
Serializable is the most powerful isolation level. Creates locks (locks) in all records of when reading and writing until the end of the transaction (ie to be done commit).
By using it, the phantom reads, as well as any other phenomenon of lower isolation level such as dirty reads and non-repeatable reads.
Repeatable-Read
repeatable-Read creates locks (locks) in selected registrations while reading and writing until the end of the transaction (ie to be done commit).
Unlike serializable using them allows the phantom reads but still avoided dirty reads and non-repeatable reads.
Read committed
The read committed creates locks (locks) in selected records on write until the end of the transaction, but on read release the lock when the transaction finishes reading.
This results in the possibility of occurring except phantom reads and non-repeatable reads, since you allow the value to change after the first read.
Read uncommitted
read uncommitted is the weakest isolation level. At this level are allowed except for phantom reads, non-repeatable read and dirty reads.
So a transaction can read data that can be changed by a second transaction, without being completed (committed).
Nolock query hint
In closing, it should be pointed out that by using query hints we can override the isolation level that has been set. One of the most used is the with (nolock).
It allows us to access the data we want, ignoring the lock that may exist from another transaction.
As is the case with the read uncommitted isolation level.
With this behavior we can have phantom reads, non-repeatable reads and dirty reads.
