Postgres Concurrency : What could go wrong (part 2)

#postgres #sql

One month and everything is fine... unless?

After your last misadventure with Bob, you learn to be careful with Isolation in your transaction. Times flies, and you have been in charge of several small reports. And now you are in order of a much more critical and strategic report for The Coffee Company.

Nonetheless, paranormal things are happening in the company. One of your colleagues told you that he found ghosts and needs help chasing them.

Ghost in the Database Company

You discussed this with him, and he told you he had been charged for providing the coffee above a unit price of 15 dollars.

But when he generated his reports, there was a coffee the same where the condition caused two different results sets. He asked this someone else, but this colleague told him that there was maybe some Ghost reading or writing data in the database, so he was a bit scared.

Since you are more familiar with the database now and the isolation level, you offered him help so you can figure this out.

First, you asked him to show you the query for the report he has to create.

BEGIN
SELECT * FROM coffees WHERE unit_price < 15.0; 
...// Another query without updating
SELECT * FROM coffees WHERE unit_price < 15.0; 
COMMIT;

BEGIN
 coffee_id |        name         | unit_price |  country   
-----------+---------------------+------------+------------
         1 | Black Honey         |       14.4 | Costa Rica
         3 | Las Fincas Granadas |       13.3 | Costa Rica
(2 rows)
...
 coffee_id |        name         | unit_price |  country   
-----------+---------------------+------------+------------
         1 | Black Honey         |       14.4 | Costa Rica
         3 | Las Fincas Granadas |       13.3 | Costa Rica
         2 | Ambela Hakala       |       14.4 | Ethiopia
(3 rows)

COMMIT

You understand that you and your colleague are experiencing an isolation issue. But this time, it is spooky: this is the Ghost Read issue.

The Ghost Read issue is a concurrency issue where the same where clause does not produce the same results set in a duplicate transaction.

Postgres solution

Since you have experimented with that, you decided to dive deep into the Postgres documentation to see how this concurrency issue can be solved.

You have finally discovered that in Postgres, the Read Repeatable level protects against both Ghost Read and Unrepeatable Read.

To fix the issue of your colleague, you must put the isolation level to READ REPEATABLE like your precedent issue with UNREPEATABLE READ.

START TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM coffees WHERE unit_price < 15.0; 
...// Another query without updating
SELECT * FROM coffees WHERE unit_price > 15.0; 
COMMIT;

BEGIN
 coffee_id |        name         | unit_price |  country   
-----------+---------------------+------------+------------
         1 | Black Honey         |       14.4 | Costa Rica
         3 | Las Fincas Granadas |       13.3 | Costa Rica
(2 rows)
...
 coffee_id |        name         | unit_price |  country   
-----------+---------------------+------------+------------
         1 | Black Honey         |       14.4 | Costa Rica
         3 | Las Fincas Granadas |       13.3 | Costa Rica
(3 rows)

COMMIT

Not so frightening

As you saw, we can encounter paranormal issues with concurrency in RDBMS. This is not so spooky, but we must be careful about isolation in our database because protection and isolation level can be low in several cases.

Postgres's isolation strategy is not necessarily the same in another RDBMS. Indeed READ REPEATABLE enables you to fight against GHOST READ in Postgres but be careful it could not be the case in another one RDBMS.

Isolation, Concurrency, and consistency are challenging issues, and you often must experiment with them to understand them well. You will likely face another problem with isolation because of some results with another colleague's update are weird and he might need your help.