Introduction

When two or more people read and write rows to the same table at the same time, you can get inconsistent results. This tutorial discusses these and how to handle them in Oracle Database.

It uses the following table to highlight the issues:

select * from toys;

Autonomous Transactions

Read consistency issues can happen when you have two sessions accessing the same data. But LiveSQL doesn't allow you to have two interacting sessions.

Luckily there's a workaround: autonomous transactions.

These are transactions that take place inside another.

You do this with the autonomous_transaction pragma. Place it in the declaration section of your PL/SQL:

declare
  pragma autonomous_transaction;

The transaction must complete within the block. That is, commit or rollback. This is independent from commits or rollbacks in the parent transaction.

For example, in the following the parent transaction inserts a row for Baby Turtle. At the end it removes it with a rollback. But between these the nested autonomous transaction adds a row for Blue Dinosaur. And commits it. So the final rollback removes Baby Turtle, but not Blue Dinosaur:

insert into toys values ( 'Baby Turtle', 7.95 );

declare
  pragma autonomous_transaction;
begin
  insert into toys values ( 'Blue Dinosaur', 15.95 );
  commit;
end;
/

select * from toys;
rollback;
select * from toys;

Note. It's rare you'll use autonomous transactions in real code. The major use-case for these is logging errors. Here you want to save the exception to a table. But may need to rollback the parent transaction. So you need an autonomous transaction. For almost all other uses they are the Wrong Method.

Read Phenomena

The SQL standard defines three read phenomena; issues that can occur when many people read and write to the same rows. These are:

• Dirty reads
• Non-repeatable (or fuzzy) reads
• Phantom reads

These cause the following issues:

Dirty Reads

A dirty read is when you see uncommitted rows in another transaction. There is no guarantee the other transaction will commit. So when these are possible, you could return data that was never saved to the database!

Dirty reads are impossible in Oracle Database. You can only view uncommitted rows in your own transaction.

Non-repeatable (Fuzzy) Reads

A non-repeatable read is when selecting the same row twice returns different results. This happens when someone else updates the row between queries. For example, after the first query in transaction 1, transaction 2 changes the shape of the row. So the second query sees the new value:

insert into bricks ( 
  colour, shape 
) values ( 
  'red', 'cube' 
);
commit;

select shape from bricks 
where  colour = 'red';

SHAPE
cube

update bricks 
set    shape = 'pyramid'; 
commit;

select shape from bricks 
where  colour = 'red';

SHAPE
pyramid

The SQL standard also allows for fuzzy reads in a single query. This can cause problems when swapping values for two rows. If the other session updates these part way through your query, you can double count values. In this example, the bricks table has 20,000 rows, with a 50:50 split between red and blue.

The first transaction counts how many rows there are of each colour. Halfway through the second query it has counted 5,000 rows of each. At this point, another transaction updates the colour of all the rows to red.

In databases without statement-level consistency, the query could see these values immediately. This leads it to count the remaining 10,000 rows as red. So the query returns 5,000 blue rows and 15,000 red. A total that never existed in the table!

select colour, count(*)
from   bricks
group by colour;

COLOUR COUNT(*)
blue     10,000
red      10,000

select colour, count(*)
from   bricks
group by colour;

update bricks 
set    colour = 'red'; 
commit;

COLOUR COUNT(*)
blue      5,000
red      15,000

Luckily Oracle Database always has statement-level consistency. So fuzzy reads are impossible in one query.

Phantom Reads

A phantom read is a special case of fuzzy reads. This happens when another session inserts or deletes rows that match the where clause of your query. So repeated queries can return different rows:

insert into bricks ( 
  colour, shape 
) values ( 
  'red', 'cube' 
);
commit;

select shape from bricks 
where  colour = 'red';

SHAPE
cube

insert into bricks ( colour, shape ) 
values ( 'red', 'pyramid' );
commit;

select shape from bricks 
where  colour = 'red';

SHAPE
cube
pyramid

As with fuzzy reads, these are impossible in a single statement in Oracle Database.

Isolation Levels

To help you manage which read problems you're exposed to, the SQL standard defines four isolation levels. These state which phenomena are possible, as shown by this table:

Oracle Database supports these as follows:

Read Uncommitted

As you can't have dirty reads in Oracle Database, this isolation level is not relevant and not implemented.

Read Committed

This is the default mode for Oracle Database. Using read committed, you have statement-level consistency. This means that each DML command (select, insert, update, or delete) can see all the data saved before it begins. Any changes saved by other sessions after it starts are hidden.

It does this using multiversion concurrency control (MVCC). When you update or delete a row, this stores the row's current state in undo. So other transactions can use this undo to view data as it existed in the past.

So none of the read phenomena are possible in a single statement in Oracle Database. Only within a transaction.

Repeatable Read

The intent of repeatable read in the SQL standard is to provide consistent results from a query. But Oracle Database already has this in read committed! So it has no use for this level and doesn't implement it.

If you're using a database without MVCC you may need this mode to get correct results.

Serializable

None of the three read phenomena are possible using serializable. You use this in Oracle Database to get transaction-level consistency. You can only view changes committed in the database at the time your transaction starts. Any changes made by other transactions after this are hidden from your transaction.

Set Transaction Isolation Level

You can change the isolation level for a transaction using the set transaction statement. For example, to set it to read committed, run:

set transaction isolation level read committed;

This must be the first statement in the transaction. If you try and change it part way through, you'll hit an error:

insert into toys values ( 'Purple Ninja', 19.99 );
set transaction isolation level read committed;
rollback;

Read Committed

This is the default mode in Oracle Database. Using this you have statement-level consistency. Each command can view all the changes saved in the database at the time it starts.

The following code starts a read committed transaction. The nested autonomous transaction set the price of all the rows to 1.61. Then adds a row for Baby Turtle. The two queries either side of the commit at the end can see these changes. So they return the same rows:

set transaction isolation level read committed;
select * from toys;

declare 
  pragma autonomous_transaction;
begin
  update toys set price = 1.61;
  insert into toys values ( 'Baby Turtle', 19.99 );  
  commit;
end;
/

select * from toys;
commit;
select * from toys;

Note that the changes must be committed before the query starts. The database hides any changes saved by another transaction while the query runs. This means none of the read phenomena apply to a single query.

Serializable

In serializable, you have transaction-level consistency. This acts as if you are the only user of the database. Changes made by other transactions are hidden from you. Some other databases refers to Oracle Database's implementation of serializable as snapshot isolation.

The following code starts a serializable transaction. The nested transaction updates the prices and adds a row for Purple Ninja.

But this happens after the start of the parent transaction. So the parent can't see the new values. The query after the PL/SQL block returns the same rows as the query at the start. You can only see the updated and inserted rows after the parent transaction ends with a commit.

set transaction isolation level serializable;

select * from toys;

declare 
  pragma autonomous_transaction;
begin
  update toys set price = 2.71;
  insert into toys values ( 'Purple Ninja', 7.95 );  
  commit;
end;
/

select * from toys;
commit;
select * from toys;

Serializable also stops you changing rows modified by other transactions. The nested transaction in the code below set the price of all the rows to 3.14. The parent transaction then tries to update and delete rows. But, because these have changed since it started, they both trigger an ORA-08177 error:

set transaction isolation level serializable;

select * from toys;

declare 
  pragma autonomous_transaction;
begin
  update toys set price = 3.14;
  commit;
end;
/

update toys 
set    price = price * 10
where  toy_name = 'Miss Snuggles';

delete toys 
where  toy_name = 'Baby Turtle';

commit;
select * from toys;

You should consider using serializable when a transaction accesses the same rows many times. And you will have many people running the transaction at the same time.

If you do use serializable, you'll need to consider how to handle ORA-08177 errors. The easiest way is to tell the user. Then get them to try the transaction again. But this is a bad user experience. So you could get your code to try again automatically. But this could lead to further errors!

To choose the best method you need to understand what your data requirements are. Work closely with your end users to figure out the way forward for your application.

But whatever you do, you'll reduce how many people can use your application at the same time. So you may need to make some trade-offs between data consistency and performance.

Read Only

Besides read committed and serializable, Oracle Database offers another transaction mode: read-only.

For queries this works in the same way as serializable. You have transaction-level consistency. Queries return data as it existed at the time the transaction began.

It has the added restriction that you can only run selects. Any attempts to change rows will throw an exception. For example, the first update below throws an ORA-01456:

set transaction read only;

select * from toys;

update toys
set    price = price + 1;

declare 
  pragma autonomous_transaction;
begin
  update toys set price = 99.00;
  commit;
end;
/

select * from toys;
commit;
select * from toys;

This mode can be useful in reporting environments. A set of reports may need to query the same tables many times. But to ensure the results are correct, you must ignore any changes made by other users while the reports run. Users must also only be able to read data. You need to stop all non-select DML.

Setting the transaction mode to read only addresses both of these needs.

As a complement to read-only, you can set a transaction to read-write. You do this with the following statement:

set transaction read write;

This works in the same way as the read committed isolation level. It's the default mode in Oracle Database.