SERIALIZABLE mode in middle of transactiongreenspun.com : LUSENET : DBAzine : One Thread
Can I set the TRANSACTION MODE ISOLATION LEVEL TO SERIALIZABLE only in a SELECT statement in a TRANSACTION and then RELEASE IT ?
(I mean that the WHOLE transaction is standard and only a certain statement within it is SEIALIZABLE) ?
-- Eli Leiba (email@example.com), February 17, 2001
It depends on the DBMS you are using. I know that DB2 supports setting the ISOLATION level on a statement by statement basis by using the WITH clause on your SELECT statement. For DB2, the WITH clause supports the following ISOLATION levels:
* CS - Cursor stability * UR - Uncommitted read (dirty read) * RR - Repeatable read * RR KEEP UPDATE LOCKS - Repeatable read but keep update locks * RS - Read stability * RS KEEP UPDATE LOCKS - Read stability keep but update locks
For DB2, the ISOLATION level does not apply to declared temporary tables because no locks are acquired on those. The default ISOLATION level for each SQL statement in the program is set by the ISOLATION parameter of the BIND command when you bind the program DBRM into a package or plan.
So, for a package bound with ISOLATION(CS) you could issue a statement in the program such as:
SELECT NAME FROM CUSTOMER WHERE CUST_ID = '100' WITH RR;
Then all the other SQL would use cursor stability isolation and only this one statement would use repeatable read.
Anyone out there know the answer for other DBMS platforms?
-- Craig S. Mullins (Craig_Mullins@BMC.com), February 23, 2001.