Isolation Level 3, serializable reads

Level 3 prevents phantoms. These occur when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert, delete, or update statement). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows. In Table 10-10, transaction T9, operating at isolation level 1, sees a phantom row in the second query.

Table 10-10: Phantoms in transactions

T9

Event Sequence

T10

begin transaction

select * from account
where acct_number < 25







select * from account
where acct_number < 25

commit transaction

T9 and T10 start. T9 queries a certain set of rows. T10 inserts a row that meets the criteria for the query in T9. T10 ends. T9 makes the same query and gets a new row. T9 ends.

begin transaction




insert into account
(acct_number, balance)
values (19, 500)

commit transaction

If transaction T10 inserts rows into the table that satisfy T9’s search condition after the T9 executes the first select, subsequent reads by T9 using the same query result in a different set of rows.

Adaptive Server prevents phantoms by:

Holding the shared locks allows Adaptive Server to maintain the consistency of the results at isolation level 3. However, holding the shared lock until the transaction ends decreases Adaptive Server’s concurrency by preventing other transactions from getting their exclusive locks on the data.

Compare the phantom, shown in Table 10-10, with the same transaction executed at isolation level 3, as shown in Table 10-11.

Table 10-11: Avoiding phantoms in transactions

T11

Event Sequence

T12

begin transaction

select * from 
account holdlock
where acct_number < 25





select * from 
account holdlock
where acct_number < 25

commit transaction

T11 and T12 start. T11 queries account and holds acquired shared locks. T12 tries to insert row but must wait until T11 releases its locks. T11 makes same query and gets same results. T11 ends and releases its shared locks. T12 gets its exclusive lock, inserts new row, and ends.

begin transaction





insert into account
(acct_number, balance)
values (19, 500)








commit transaction

In transaction T11, Adaptive Server applies shared page locks (if an index exists on the acct_number argument) or a shared table lock (if no index exists) and holds the locks until the end of T11. The insert in T12 cannot get its exclusive lock until T11 releases its shared locks. If T11 is a long transaction, T12 (and other transactions) may wait for longer periods of time. As a result, you should use level 3 only when required.