1. Isolation levels
Great work on that last exercise! Now let's look look at isolation levels and how transactions are affected by concurrency.
2. Concurrency
In most systems, there are tons of database operations going on at the same time from many different connections and users. Handling the coordination of all these operations is an example of concurrency. Handling concurrency does not mean that everything works perfectly; it has a unique set of problems. To work around these problems, it uses rule sets known as isolation levels to make it easier to reason about outcomes. Let's learn more about some of the issues concurrency causes in databases.
3. Dirty Reads
First, we have dirty reads. These occur when a transaction reads data written by a concurrent uncommitted transaction. For example, If we have two transactions, transaction 1 which is going to reduce the value of the RCON2237 field by 10% and another named transaction 2 which will reduce the same field by 35%. Let's say these two transactions start at roughly the same time, but transaction 2 performs the 10% reduction first, and Transaction 1 reads the resulting value before performing its reduction, that is a dirty read.
4. Nonrepeatable Read
When a transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the first read), this is called a nonrepeatable read.
Let's see this in action. Here we have two transactions, one that is going to read the data twice and one that is updating it. Transaction 2 performs its reduction before Transaction 1 reads all the records again, and Transaction 1 reads the new values, which are different than the first read of the records.
5. Phantom Read
A phantom read is when a transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction. As you can see here, we have two transactions, much like the last example, except this time, we are using a where clause to filter the data we get back. If transaction 2 finishes between the two statements of transaction 1, we will get a new record in the second select statement even though the filter was the same.
6. Serialization Anomaly
Finally, a serialization anomaly can occur when the order in which you run transactions changes the resulting value stored in the database. In this example, we have two transactions, which would result in a different result being written in the database depending on the order in which the transactions completed.
7. Isolation levels
Databases provide different levels of protection from these issues called isolation levels. There are four prevalent isolation levels in databases. read uncommitted, read committed, repeatable read, and serializable.
Let's look at a table of isolation levels and the protections they provide.
Read uncommitted doesn't protect from any of the four major concurrency issues in most databases; however, in PostgreSQL, it protects you from dirty reads.
This protection makes it the same as the read committed isolation level in most databases, as shown here.
Read committed is the default isolation level in PostgreSQL.
Repeatable read protects you from dirty read, nonrepeatable read, and in PostgreSQL phantom read issues. Finally, we have serializable, which protects you from everything.
8. Affects of isolation levels
Here is a transaction that uses an isolation level. You might notice that I used start transaction instead of begin this time. Start and begin are synonyms in PostgreSQL; however, I prefer to use start whenever I declare an isolation level to differentiate even more between a transaction without a specific isolation level and a transaction with a specific isolation level. Next, we are going to read the same data twice; however, a statement will execute between our two statements adding one more lemon drop cookie to the table. What do you think the results of these two statements will be assuming there are five lemon drop records in the database? After the first statement executes, we get five back. When the second statement runs, we get a six! These results indicate a phantom read concurrency issue. However, depending on what your application needs, this may be what you want to happen. Let's assume that it is not okay for our transaction to have this occur and try a different isolation level.
9. Affects of isolation levels
In this example, we are going to try the most protective of the isolation levels serializable, and run the same two statements with the same operation in the middle. Remember serializable protects from all four of the significant concurrency issues. What will be the results of our two statements now assuming there are five lemon drops in the database? The first one will get five, and so will the second one. It's important to remember that the trade off for all the protections of serializable is speed. Adding that much protection requires transactions to wait for other transactions to complete, and then run slower due to all the safeguards.
10. Let's practice!
Now let's go work on some transactions with the read uncommitted and serializable isolation levels.