Database Transactions : Concurrency Control
Tags: database, transaction, concurrenc, performance
Published: 2024-06-26 18:47:41

The number of users who can use the database engine concurrently is a significant criteria for classifying the database management systems. A DBMS is single-user if at most one user at a time can use the engine and it is multiuser if many users can use it concurrently. Most of the DBMSs need to be multiuser, for example databases used in banks, insurance agencies, supermarkets should be multiuser, hundreds of thousands of users will be operating on the database by submitting transactions concurrently to the engine.

**Why Concurrency Control is needed**

When executing concurrent transaction in an uncontrolled way, many issues can rise like dirty read, non-repeatable read, phantom read, lost update. We will go through each of those issues.

*Dirty Read*

Is a situation in which a transaction T1 reads the update of a transaction T2 which has not committed yet, then if T2 fails, then T1 would have read and would have worked with a value that does not exist and is incorrect.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wv556n5ytlfscffz54mh.png)

*Non-repeatable Read*

Is a situation in which transaction T1 may read a given value from a table, if another transaction T2 lated updates that value and T1 reads that value again, it will see a different value from the first one it got initially.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3zbcpe949c0b6vw6714k.png)

*Phantom Read*

Is a situation in which a transaction T1 may read a set of rows from a table based on some condition specified in the query where clause, then transaction T2 will insert a new row that also satisfies the where clause condition used in T1, then if T1 tries to perform the same query it will get the newly added row this time

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1lf1zjybrgy3niilcu54.png)

*Lost Update*

after the T1 commits the change by T2 will be lost considered as if it was never done.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/op4i8xdvj8ha1k8oyswt.png)

*Dirty Write*

Is a situation in which one of the transactions takes an uncommitted value (dirty read), modifies it and saves it.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ri7i77n6fpwix8cpduib.png)

**Strategies for dealing with concurrent transactions**

We have 2 options for controlling the execution of concurrent transactions, we either choose to run the access operation to a specific data item sequently across transactions, or we choose to parallelize the execution that access, there are multiple isolation levels that can be used for implementing these choices and each one of them may prevent and allow some of the issues related to concurrency.

In the SQL standard, there are 4 isolation levels : 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7ra8rue7a7tu1eh5hu40.png)

In Oracle, there are only 2 isolation levels : 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4v10jvcxus465687esf9.png)

Dirty Reads are not allowed since read committed is the lowest isolation level supported in Oracle

In PostgreSQL, there are 4 isolation levels, and read committed and read uncommitted behaves in the same way : 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2vp9ou0v1rwc4gwdei9w.png)

read uncommitted and read committed behave in the same way and they don't allow dirty read and dirty write issues to happen

In MySQL, there are 4 isolation levels, and read uncommitted prevents dirty write and allows dirty read : 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dv5h5dyg0b6t4u67lv42.png)