What are the considerations when using SERLIALIZABLE over READ COMMITTED isolation level in Oracle

isolation-leveloracle

What are the considerations when using SERIALIZABLE isolation level over the default READ COMMITTED in an Oracle database?

Given the concurrency system used within Oracle (using transaction logs to determine the state of the database at a point in time), I understand that using a SERIALIZABLE transaction does not decrease the availability of the database over READ COMMITTED isolation.

One consideration is that there must be enough space allocated for undo segments to allow the database to store enough history to accommodate the implied extra time required to support transaction-level over statement-level read consistency. If there is not enough undo data, there is a risk of error ORA-01555 Snapshot Too Old (although this is still an risk with a READ COMMITTED query).

One point that I am not clear on is if there are any performance implications of serializable isolation. Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?

Finally, as I am talking about a read-only transaction the assumption is that there is no risk of a ORA-08177: Cannot serialize access for this transaction from writing to data which has since been updated.

Best Answer

Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?

Example:

You have a table getting one insert every minute.

If a SERIALIZABLE transaction started at 4:00pm, and queried that table at 4:15pm, then it will have to read at least 15 undo records to return to the state of the table when your transaction started. If there had been no activity on the table, then there would be no additional workload.

Ultimately it would depend on how long the serializable transaction last, and what changes are made to the data it looks at during that time.

I'd add that it is rare to see SERIALIZABLE used. The same mechanisms support DBMS_FLASHBACK with the advantage that you can specify the exact time for which you want to view the data (eg Closing balance at 5pm).

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.

This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Best Answer

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

Postgresql – Read Committed Isolation Level

Related Question