Instance Ownership Issues

Dec 10, 2009 at 11:52 AM

Hi there, I'm having a couple of issues when running multiple runtimes off the same database.

First:

RetrieveInstanceState has a ref_cursor parameter, p_state.  With a code path (p_owner_id is not null, and the update failed to update because of lock) the procedure returns without opening anything in the ref cursor, which was triggering "ORA-24338: statement handle not executed" for me. To fix it, I just opened the cursor with no output, as the calling classes are already checking the ownership result.

So, I changed:

 

IF SQL%ROWCOUNT = 0 THEN
  GetOwnershipResult(p_INSTANCE_ID, p_CURRENT_OWNER_ID, p_RESULT);
  RETURN;
END IF;

for:

IF SQL%ROWCOUNT = 0 THEN
  GetOwnershipResult(p_INSTANCE_ID, p_CURRENT_OWNER_ID, p_RESULT);
  OPEN p_STATE FOR
    SELECT STATE
    FROM INSTANCE_STATE
    WHERE 1=0;
                         
    RETURN;
END IF;

I'm still having some issues with ownership. On this fragment (also on InsertInstanteState):

				-- it does exist, update the existing instance
IF p_UNLOCKED = 0
THEN
UPDATE INSTANCE_STATE
SET
STATE = p_STATE
,STATUS = p_STATUS
,UNLOCKED = p_UNLOCKED
,BLOCKED = p_BLOCKED
,INFO = p_INFO
,MODIFIED = l_NOW
,OWNER_ID = p_OWNER_ID
,OWNED_UNTIL = p_OWNED_UNTIL
,NEXT_TIMER = p_NEXT_TIMER
WHERE
INSTANCE_ID = p_INSTANCE_ID
AND (
(OWNER_ID = p_OWNER_ID AND OWNED_UNTIL >= l_NOW)
OR (OWNER_ID IS NULL AND OWNED_UNTIL IS NULL)
);

Shoudn't it also consider that owner_id is not null and owner_until is <=l_now? So it can update a row that was owned by someone else but whose ownership expired? Nowadays I'm getting an Ownership exception I wasn't expecting (I have a Onwership timeout of like, 15 seconds).