SERIALIZABLE mode in middle of transaction

greenspun.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 (elileiba@netvision.net.il), February 17, 2001

Answers

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?

Good luck!

-- Craig S. Mullins (Craig_Mullins@BMC.com), February 23, 2001.


Moderation questions? read the FAQ