Avoid dirty/phantom reads while selecting from database

Question

I have two tables A and B.

My transactions are like this:

• Read -> read from table A
• Write -> write in table B, write in table A

I want to avoid dirty/phantom reads since I have multiple nodes making request to same database.

Here is an example:

1. Transaction 1 - Update is happening on table B
2. Transaction 2 - Read is happening on table A
3. Transaction 1 - Update is happening on table A
4. Transaction 2 - Completed
5. Transaction 1 - Rollback

Now Transaction 2 client has dirty data. How should I avoid this?

Answer 1

If your database is not logged, there is nothing you can do. By choosing an unlogged database, those who set it up decided this sort of issue was not a problem. The only way to fix the problem here is change the database mode to logged, but that is not something you do casually on a whim — there are lots of ramifications to the change.

Assuming your database is logged — it doesn't matter here whether it is buffered logging or unbuffered logging or (mostly) a MODE ANSI database — then unless you set DIRTY READ isolation, you are running using at least COMMITTED READ isolation (it will be Informix's REPEATABLE READ level, standard SQL's SERIALIZABLE level, if the database is MODE ANSI).

If you want to ensure that data rows do not change after a transaction has read them, you need to run at a higher isolation — REPEATABLE READ. (See SET ISOLATION in the manual for the details. (Beware of the nomenclature for SET TRANSACTION; there's a section of the manual about Comparing SET ISOLATION and SET TRANSACTION and related sections.) The downside to using SET ISOLATION TO REPEATABLE READ (or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE) is that the extra locks needed reduce concurrency — but give you the best guarantees about the state of the database.