Nested Transaction

, , ,
1

Hi,

I have the following question, how can i do nested transaction in natural using DB2, ie, consider the following pseudocode.

START TRANSACTION A

SELECT TTTTT
INSERT XXXX
UPDATE YYYY

—GET A COUNTER----

    SELECT W FROM Y WHERE I = L
    SET W = W + 1
    UPDATE Y

-----END GET A COUNTER—

…
MORE SELECTS
MORE UPDATES, ETC

END TRANSACTION A

the "GET A COUNTER " SUB-PROGRAM should be able to do a nested transaction, because i have many programs calling this SUB-PROGRAM
and we’re getting a lot of locks (error -911 resource not available) from DB2. we changed the lock mode on the tablespace to “row-lock”.

any hints or workarounds ?

TIA

2

The problem with your GET A COUNTER subprogram is that a lock will be held on that table until you commit your transaction. Since you have additional selects and updates after you get the counter, the lock will be held on the counter table throughout all the rest delaying any access by other processes. That is probally what is causing your -911s.

What are you using the counter for and could it be replaced with an IDENTITY column on the tables?

Also, you might want to get the counter in its own transaction before your TRANSACTION A. Like so:

START TRANSACTION X

SELECT W FROM Y WHERE I = L
SET W = W + 1
UPDATE Y

END TRANSACTION X

START TRANSACTION A

SELECT TTTTT
INSERT XXXX
UPDATE YYYY

…
MORE SELECTS
MORE UPDATES, ETC

END TRANSACTION A

The only problem with this is if you need to back out TRANSACTION A, you will have waisted the counter that you got in TRANSACTION X.

But, if you can replace the counter with an identity column, that would probably be the best solution since DB2 doesn’t lock anything to generate a new identity value.