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.
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:
Applying exclusive locks on rows, pages, or tables being changed. It holds those locks until the end of the transaction.
Applying shared locks on rows, pages, or tables being searched. It holds those locks until the end of the transaction.
Using range locks or infinity key locks for certain queries on data-only-locked tables.
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.
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.
