 |
|
|
 | Transaction Isolation |  | Transactions and Savepoints | START TRANSACTION Statement |  |
Combining the two different synchronization mechanisms of various client requests - explicit lock placing and setting transaction isolations - may result in client blocking or even an error.
Let one client lock the Z record for updating and another client calls a query, that has to test the Z record values.
If the other client has the SERIALIZABLE level of transaction isolation set, then the query result creation is postponed until the first client unlocks the Z record or until the time limit for lock waiting expires. The request results in an error in the second case.
If the query has a structure that allows postponed creation, then the error occurs when the values of the Z record are checked.
Why is the first client blocking the second? The first client has the privilege to change the contents of Z record anytime thanks to the placed lock. The other client requires (by setting the SERIALIZABLE isolation level) that the server ensures that the repeated call of the same query gives always the same result. Because the Z record values are used for evaluating the query, the server cannot ensure this. Therefore it cannot allow giving the query result to the second client as long as the first client still has the lock placed. The outcome of this situation is waiting or error, depending on the runtime parameters.
Demonstration of this problem:
We have a table STRTAB created by calling the CREATE TABLE STRTAB (S CHAR(12)) statement. The table has one column containing a character string, without indexes. Let the table contain at least one record.
Lock program model:
... lock_table_for_update(StrTab); open_modal_window; unlock_table_for_update(StrTab); ...
Select program model:
...
set_transaction_isolation(3);
open_cursor('select * from Strtab where s<''zzzzz''')
count_records_in_cursor;
close_cursor;
...
The first client executes the Lock program and leaves it waiting with the modal window displayed. The second client then executes the Select program. This program gets into a state where it waits for a lock (state 3 in the client overview on the server).
When the modal window is closed, the first client continues its work and deletes the lock, then the second client finishes the cursor creation and no error occurs.
If the first client doesn't delete the lock in the time limit, then the second client gets the NOT_LOCKED (136) error.
The mutual client blocking won't occur, if the second client will use the READ COMMITED level of transaction isolation set.
| Transaction Isolation | | Transactions and Savepoints | START TRANSACTION Statement | |