There are other answers here, but they don't give any details about the underlying database architecture, which makes it difficult to understand why the transaction isolation levels function the way they do, and what problems are solved.
----
#### General Overview of Common Problems in Concurrent Environments
Database systems permit multiple concurrent connections. This leads to the same kinds of problems seen in other concurrent systems. For example, in multithreaded environments, mutexes prevent concurrent access to memory, thus solving the problem of race conditions which can lead to corrupt or invalid data.
In a similar way, because database systems permit concurrent CRUD operations (update, insert, delete, select), concurrent operations by multiple connections can lead to undesirable observed behaviour.
Note that the atomicity of database row operations prevents outright data corruption or inconsistency, so there is always a base level of transaction isolation enforced at all times.
For more information on this, see [ACID][1]. (Atomic, Consistent, Isolation, Durability.) The short explanation is that *on a per-row basis*, operations are atomic. This means prevents data corruption by preventing a situation whereby one connection would write part of a rows data, before another connection corrupted that data by partially writing its data to the same row. (This will be more intuitive to those familiar with multithreaded environments.)
The above described problem is analagous to problem seen in multithreaded programming whereby one thread begins writing to a block of memory, and then another thread comes and partially writes its data to the same block, before the first thread is finished. This results in inconsistent data.
It is important to understand the atomic nature of row operations first, because this already provides a base level of protection.
#### Types of Transaction Isolation Levels
We will look at the following Transaction Isolation levels, whcih are available in MariaDB and many other SQL database implementations.
We first need to know what the different isolation levels are:
- Read uncommitted
- Read committed
- Repeatable read
- Serializable
#### What problems are solved by the Transaction Isolation Levels?
Before explaining what these different options do, it is important to understand the problems solved by each of them. Here is a list of potential unwanted behaviours.
- Dirty read
- Non-repeatable read
- Phantom read
##### Dirty Read:
Database operations are often grouped together into a transaction. This transaction is then either committed to the database as a group of operations, or a rollback is perfomed to discard the group of operations.
If one Connection starts a series of operations as a transaction, and then a second Connection begins reading data from the same table(s), the second Connection could either read the data from the database which has been *committed*, or it could also read the changes made as part of the open, and yet uncommitted, transaction.
This defines the difference between **read committed** and **read uncommitted**.
This is conceptually unusual, because it often doesn't make much sense to read uncommitted data. The whole point of transactions is to ensure data in the database *does not* change in this way.
To summarize:
- Connection A opens a transaction and begins queueing up modifying (write) operations
- Connection B opens in read-uncommitted mode, and reads data from the database
- Connection A continues queueing up further modifications
- If Connection B reads the data again, it will have changed
- If Connection A performs a rollback, and then Connection B performs another read, the data read will have changed due to the rollback of the uncommitted data
- This is known as a *dirty read*
- This isn't a situation you usually have to worry about because you probably shouldn't be working in read-uncommitted mode as a general rule because it makes data appear as if transactions didn't exist, which is weird
##### Non-Repeatable Read
Given the above, a Non-Repeatable read may occur if the read operation mode is set to "Read Committed". This mode solves the problem of Dirty Read because only committed data is read.
The possible write operations are:
- update
- insert
- delete
A Non-Repeatable Read occurs when a read operation reads a set of rows, and then the operation is repeated and the same set of rows (same keys) are returned, but the non-key data has changed.
For example:
- Connection A may read a set of rows from a table. These rows are selected based on some selection criteria, for example a `where` clause.
- Connection B may alter one or more of the rows in this set, as well as others
- If Connection A repeats the same read query, then the same set of rows will be returned, but data which is not part of the "key" may have changed.
- The "key" is defined by the selection criteria in the `where`, or other filter, clause.
##### Phantom Reads
Phantom Reads are an extension of the Non-Repeatable read. An `insert` or `delete` operation may change the rows returned in the set of returned rows. An insert operation may add new rows to the set of returned rows. A delete operation may do the opposite and remove rows from the set of returned rows.
To summarize:
- Connection A perfoms a read operation
- Connection B performs an insert, or delete, operation
- Connection A performs the same read operation resulting in a different set of rows being returned. New rows may appear. Existing rows may dissappear. Hence "Phantom" Read.
#### Isolation Levels
Given our understanding of the potential undesirable behaviours, this is what the isolation levels do:
- Read uncommitted does nothing to prevent any of these problems. However there is still a base level of protection due to atomic row operations.
- Read committed prevents Dirty Reads only.
- Repeatable Read prevents Dirty and Non-Repeatable Reads, but not Phantom Reads.
- Serializable prevents all of the above.
Higher levels of isolation require more data in the database to be "locked" preventing concurrent access. This can be undesirable, because if a DMBS is holding a lock over a whole table, then no other connections can modify that data. This might cause other processes which need access to the database to hang.
Read Committed is typically the most sensible choice. It ensures that you, the Database Administrator, see only the committed data (data which is persistent, not transient) and it will not cause other processes to hang.
#### Bibliography
Further reading:
[Wikipedia ACID Operations][1]
[Isolation Levels in MariaDB][2]
[Geeks for Geeks Isolation Levels][3]. (Be aware some of the information doesn't make any sense, for example the explanation of Read Committed, which states that this causes a commit of any uncommitted data which is read. That is not correct and does not make sense. Uncommitted data is only committed by an explicit commit operation.)
[Non-Repeatable Read vs Phantom Read][4]
[1]:
[To see links please register here]
[2]:
[To see links please register here]
[3]:
[To see links please register here]
[4]:
[To see links please register here]
.