Concurrency in Databases
In the previous section, we learnt about concurrency and how it is accomplished in applications. Let's now look at databases. Imagine, multiple people trying to update their contact details in a database at the same time. This is an example of concurrent requests to a database. Before going into how concurrency is achieved, we will go through various problems introduced by concurrency.
Problems
As we know, that databases provide ACID constraints. To maintain these properties, databases have a concept of transactions.
Transaction: A Transaction is a single unit of work that is treated to be independent of other transactions. It is reliable and used to maintain data integrity. There are different levels of how transaction operations, each level determines the control of the resources and data of database.
Dirty Write
Dirty writes happen when two transactions try to update the same row at the same time. Say T1 and T2 reads a row value and have to do some processing. T1 updates the value, and commits the transaction. Later T2 also updates the same row according to its processing and commits the transaction. In this process, the update by T1 gets lost and is called a Dirty Write.
Dirty Read
Dirty reads happen when one transaction is trying to update a row and the other transaction is reading the same row. Say T1 updates a row, and is uncommitted at the moment when T2 reads it and uses it for its purpose. Now if T1 fails and decides to rollback, the value used by T2 will be invalid and will be called a Dirty Read.
Non-Repeatable Reads
Non-Repeatable reads happen when one transaction reads one row twice and gets different value at both times. Say T1 reads a row, and another transaction T2 updates the same row and commits it. Now if T1 reads the same row again, it will receive a different value.
Phantom Reads
Phantom reads happen when two queries on execution returns different number of rows. Note, this is different from above one. In non-repeatable reads, we see mismatch in a row's value, while in phantom reads, we mismatch in number of rows(as if a phantom row has appeared). Say T1 reads the number of rows in a table, and T2 inserts another row concurrently. Now if T1 reads the number of rows again, it will see a mismatch.
Isolation Levels
There are different isolation levels that a database supports. Each isolation level, also known as transaction level supports various degrees of locking on data modifications to support data integrity and prevent above issues.
Read Uncommitted
This is the lowest transaction level. Read Uncommitted marks a transaction to read any of the uncommitted data from other transactions, but places a write lock on the row, hence not allowing another transaction to update it. In other words, it prevents Dirty Writes but since a transaction is not isolated from other transaction in reads, it can cause Dirty Reads.
Read Committed
Read Committed transaction level allows a transaction to read only committed data, implying the data read is valid. This prevents Dirty Reads, and places a read-write lock on the rows of the current executing query(a transaction can have multiple queries, and only places locks on rows affected by the current query). However non-repeatable reads can still happen, if another transaction commits a row that is read by the current transaction in its previous query. Since this isolation level only places locks on the rows for the query , it can allow two transaction to update the same row.
Repeatable Read
Repeatable reads places locks on all the rows impacted by the transaction(all rows referenced by all queries in the transaction). This will prevent dirty reads as well as non-repeatable reads. However phantom reads can still happen, since phantom reads are usually associated with inserting new rows, and insertions can still happen from other transactions.
Serializable
This is the highest form of isolation level, where each transaction is expected to execute serially and transactions don't interleave with each other. This is usually avoided, since it doesn't provide any concurrency.
For More Reading
Last updated