Isolation Levels (Magic xpa 4.x)

At this stage, we should talk a little bit about the database’s isolation level. We have indirectly touched on the subject throughout this section and now it is time for a definition of sorts.

The isolation level of a process specifies the degree to which the rows that are read and updated by a process are available to other concurrently executing processes.

Quite often there is a situation where one transaction can change a value, and a second transaction can read the changed value before the change has actually been committed or rolled back. This situation is known as a “Dirty Read”.

Let us look at this in the context of our scenario with our beloved couple, Fred and Wilma. As you remember, Fred is in the middle of his transaction of transferring $50 from the Savings account to the Checking account. Remember, there are two sides to this transaction: the decreasing of the Savings account by $50 and the increasing of the Checking account by $50.

Wilma has now checked the Checking account and the $50 is not there (the clerk is on the phone) and when she checks the Savings account, the amount is $0. Wilma checked the Savings account during the course of Fred’s transaction before he completed the transaction.

There may be a problem here if Fred aborts his transaction. All changes will be reverted to their original status. Unfortunately, the second user will not know that Fred has aborted this transaction. In order to know that Fred has aborted the transaction, the data has to be reread. Dirty Reads obviously increase concurrency but reduce consistency.