Introduction to DataBase Isolation Levels
Transaction specifies an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation Levels are described in terms of side-effects of concurrent access or modification by number of users at a same time to the database.
Generally, following four isolation levels have been identified:
READ UNCOMMITTED: In this level, changes made by user B can be read by user A. Changes made by user B are not committed yet. In this isolation level, performance should be as quick as possible. Uncommitted changes made by user B can be read by user A. After making changes, changes can be committed or rolled back. In this way, user A can read correct data or can read dirty data.
To understand this isolation level, Lets we have a scenario:
Suppose User A executes the query : Select * from customers
Suppose there are 1000 records which are being fetched from database. In the meantime concurrently user B executes update query on this table to update several rows. Since user A can see uncommitted changes also with isolation level READ UNCOMMITTED hence he will see updates user B’s query is making. But eventually if User B’s query is rolled back due to an issue, user A has already got dirty data.
READ COMMITTED: This is default isolation level of database. In this isolation level, user A cannot read uncommitted changes made by user B. Any user can read/access only committed data of database at that particular time.
For the same query: Select * from customers
User A will read correct/saved/committed data from database, User A will not get uncommitted data which is being updated by user B.
REPEATABLE READS: In this Isolation Level, every time different data is achieved when querying the database. In this, user cannot read uncommitted data. Repeatable Read situation occurs suppose when user A queries the database to access some values (and his query needs to read the data multiple times, like in a subquery or self join) and transaction starts for user B on same data. Until transaction is committed by user B, user A can access only previously committed data. After commit by user B, user A will read different data when queried again. In this way, user A reads two different values of data: one before commit by user A and one after commit by user A.
For this scenario, suppose user A queries “Select * from customers”;
User A will read committed data for this query. While user A is reading fetched records, transaction for updating records started by User B. User B has committed his updation transaction. After few seconds/minutes, user A again queries to read the data. This time user A will read different data for records which are changed by user B. So, user A has read two different data within few seconds/minutes.
User(A) : read
User(B) : update
User(A) : read.
So, we can see that User A queries for reading the data before and after update command by User B. The process of reading different data before and after transaction is called repeatable read. Both times, user A will read committed data.
SERIALIZABLE: In this isolation level, transactions are completely isolated from each other. Transactions are executed in sequential manner. So, data read/accessed by different users are always correct data. But this isolation level causes slow performance in accessing database.
In this isolation level, when transaction for User A starts on same object like on “customers” table. Transaction may contain read statements/queries only or may contain read/update both.
The transaction for User B will not start until transaction for User A completes or committed by User A. In this way, transactions are executed serially and thus performance becomes slow.