13TransactionsInSQL

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
Isolation levels
Phenomena
Isolation levels versus phenomena
Setting isolation levels in ANSI SQL

Transaction processing in ANSI SQL is based on the different definitions of correctness from view and conflict serialziability. The correctness levels in transaction processing in ANSI SQL are based on the concepts of phenomena.

We start from a "dirty read phenomenon". A dirty read phenomenon happens when a transaction reads uncommitted date created by a transcation that fails later on.

In the present example Transaction 2 starts processing with SELECT statement:

SELECT budget

FROM DEPARTMENT

WHERE name = 'SALES';

The result is 2000

Then Transaction 1 increases a value of budget by 1000 for Sales department:

UPDATE DEPARTMENT

SET BUDGET = BUDGET + 1000

WHERE NAME = 'Sales';

Then Transaction 2 process the same SELECT statement as before:

SELECT budget

FROM DEPARTMENT

WHERE name = 'SALES';

The result is 3000.

Finally Transaction 1 performs rollback. Hence the current value of budget for Sales department is 2000. A Transaction 2 is left with an incorrect value 3000. It is because a Transaction 2 was allowed to read uncommitted data.

Dirty read phenomenon

A transaction reads uncommitted data created by a transaction that fails later on


  Transaction 1                 Transaction 2

                                SELECT budget
                                FROM DEPARTMENT
                                WHERE name = 'SALES';

UPDATE DEPARTMENT
SET BUDGET = BUDGET + 1000
WHERE NAME = 'Sales';

                                SELECT budget
                                FROM DEPARTMENT
                                WHERE name = 'SALES';

  ROLLBACK;

???

Reading only committed data


  Transaction 1                 Transaction 2

                                SELECT budget
                                FROM DEPARTMENT
                                WHERE name = 'SALES';

UPDATE DEPARTMENT
SET BUDGET = BUDGET + 1000
WHERE NAME = 'Sales';

                                SELECT budget
                                FROM DEPARTMENT
                                WHERE name = 'SALES';

  ROLLBACK;

Transaction Processing in ANSI SQL

The next pehonomenon is a "non-repeatable read" phenomenon. In such case a transaction reads a data item many times and sometimes a value is different.

Like before Transaction 2 starts from reading a budget of Sales department. A result is 2000.

Next, Transaction 1 updates a budget of Sales department to 3000 and it commits an update. It means that now Transaction 1 is successfully completed and its results can be disclosed to other transactions with a danger of reading dirty data.

Transaction 2 reads again a budget of Sales department and this time a result is 3000.

It means that another transcation updated a budget and successfully committed itself. Now, Transaction 2 has a dilemma: do we proghress with 3000 or do we check a values of budget of Sales department again ? Maybe in the mean time another transaction changed the value and it successfully committed itself.

If the first value has been already used for the computations by Transaction 2 then using a different value later on makes the results of computations inconsistent.

Non-repeatable read phenomenon

A transaction reads the same data item few times and sometimes a data item has a different value


  Transaction 1                 Transaction 2

                                SELECT budget
                                FROM DEPARTMENT
                                WHERE name = 'SALES';

UPDATE DEPARTMENT
SET BUDGET = BUDGET + 1000
WHERE NAME = 'Sales';

COMMIT;

                                SELECT budget
                                FROM DEPARTMENT
                                WHERE name = 'SALES';

???

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
Isolation levels
Phenomena
Isolation levels versus phenomena
Setting isolation levels in ANSI SQL

The phenomenon is called "phantom phenomenon". A transaction counts several time the total number of rows in the table and sometimes the total number of rows is different.

In the present example, Transaction 2 starts from counting the total number of rows in a relational table DEPARTMENT. The total number of rows is 20.

Next, Transaction 1 delete a row with information about Sales department and commits the deletion.

Transaction 2 counts the total number of rows again and a resuilt is different from the previous one.

A "phantom phenomenon" is differemnt from "non-repeatable read" phenomenon. It applies only to DELETE and INSERT operations perfromed by Transaction 1. UPDATE operation performed by Transaction 1 would never change the total number of rows in a relational table. It means that elimination of phantom phenomenon does not need any control of UPDATE statements perfromed by the transactions as UPDATE statements are not able to change the total number of rows in a relational table.

Phantom phenomenon

A transaction counts several times the total number of rows in the same table and sometimes the total number of rows is different


  Transaction 1                 Transaction 2

                                SELECT count(*)
                                FROM DEPARTMENT

DELETE DEPARTMENT
WHERE NAME = 'Sales';

COMMIT;

                                SELECT count(*)
                                FROM DEPARTMENT

No phantoms


  Transaction 1                 Transaction 2

                                SELECT count(*)
                                FROM DEPARTMENT

DELETE DEPARTMENT
WHERE NAME = 'Sales';

COMMIT;

                                SELECT count(*)
                                FROM DEPARTMENT

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
Isolation levels
Phenomena
Isolation levels versus phenomena
Setting isolation levels in ANSI SQL

Isolation levels are equivalent to correctness levels of transaction processing in ANSI SQL standard. The standard provides four levels of isolation (correctness ) of concurrent processing of database transactions.

Isolation levels are defined in terms of several possible phenomena or wird hard-to-explain occurrences of operations.

ANSI SQL standard defines the following isolation levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

The lowest correctness level is READ UNCOMITTED isolation level. At READ UNCOMMITTED isolatin level the transactions are not controlled at. The highest correct ness level is SERIALIZABLE level. A serializable isolation level is equivalent to conflict serializability correctness criterion.

Isolation levels are defined in trems of the already presented phenomena:

Dirty read phenomenon

Non-repeatable read phenomenon

Phantom phenomenon

Isolation levels

SQL provides four levels of isolation for database transactions
Isolation levels are equivalent to correctness levels
Isolation levels are defined in terms of several possible phenomena, or weird hard-to-explain occurrences of operations
The following isolation levels are defined in ANSI SQL
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
Isolation levels are defined in the terms of so called phenomena
The following phenomena are considered
- Dirty read phenomenon
- Non-repeatable read phenomenon
- Phantom phenomenon

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
Isolation levels
Phenomena
Isolation levels versus phenomena
Setting isolation levels in ANSI SQL

Phenomena

Dirty read phenomenon
- Read operations may access dirty data, i.e. data written by uncommitted transactions
Non-repeatable read phenomenon
- Different reads by a single transaction to the same data will not be repeatable, i.e. they may return different values
Phantom phenomenon
- A set of rows that transaction reads once might be a different set of rows if the transaction attempts to read them again

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
Isolation levels
Phenomena
Isolation levels versus phenomena
Setting isolation levels in ANSI SQL

At read uncommitted level a transaction may experience dirty read phenomenon, non-repeatable read phenomenon and phantom phenomenon. It measn that execution of a transaction is not controlled at all. all data manipulation operations executed by the other transtactions have a direct impact on data read by a tranbsaction running at read uncommitted level.

At read committed level a transaction may experience non-repeatable read phenomenon and phantom phenomenon. At read committed level a transaction reads only comitted data. It is possible that while a transaction is processed another transcation processes UDATE, DELETE, and INSERT statements on the same data items and its commits its operations. Then due to processing of UPDATE statement by another transaction a transcation may experience non-repetable read. Then due to processing of INSERT and DELETE statement by another transaction a transaction may expereince a phantom phenomenon.

At repeatable read isolation level a transaction may experience only phantom phenomenon. At repeatable read isolation level processing of committed UPDATE statements by another transaction must not be visible to a transaction.

At serializable isolation level a transaction may experience non of the phenomena. At serializable isolation level processing of committed UPDATE, DELETE, and INSERT statements is not visible to a transaction.

Isolation levels versus phenomena

At READ UNCOMMITED isolation level a transaction may exhibit:
- dirty read phenomenon,
- non-repeatable read phenomenon,
- phantom phenomenon
At READ COMMITED isolation level a transaction may exhibit:
- non-repeatable read phenomenon,
- phantom phenomenon
At REPEATABLE READ isolation level a transaction may exhibit:
- phantom phenomenon
At SERIALIZABLE isolation level a transaction may exhibit:
- none of the phenomena

Isolation levels versus phenomena

	      
Level               Dirty Read          Nonrepeatable Read          Phantom
--------------------------------------------------------------------------------	      
READ UNCOMMITTED    Possible            Possible                    Possible

READ COMMITTED      not possible        Possible                    Possible

REPEATABLE READ     not possible        not possible                Possible

SERIALIZABLE        not possible        not possible                not possible

Transaction Processing in ANSI SQL

Outline

Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
Isolation levels
Phenomena
Isolation levels versus phenomena
Setting isolation levels in ANSI SQL

Setting isolation levels in ANSI SQL

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

References

Elmasri R. and Navathe S. B., Fundamentals of Database Systems, Chapter 20.6 Transaction Support in SQL, 7th ed., The Pearson Education Ltd, 2017