CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
user1463644
user1463644

Reputation: 1

Rollback not working from Stored Procedure

I am acquiring lock on row using "select * from table_name where att1 = 'some_value' for update" query using sqldeveloper. And from SP trying to release the lock using rollback on same row in case of exception but rollback is not working from SP. But if i do rollback from sqldeveloper its working fine and releasing the lock.

please guide me if i am doing anything wrong. Here is my stored procedure.

DECLARE
  resource_busy    EXCEPTION;
  resource_busy2   EXCEPTION;
  PRAGMA EXCEPTION_INIT (resource_busy, -30006);
  PRAGMA EXCEPTION_INIT (resource_busy2, -00054);
BEGIN
  counter := 0;

  SELECT COUNT (*)
    INTO counter
    FROM TBLACCOUNT
   WHERE TBLACCOUNT.ACCOUNT_ID = RPAD (ACCT_NUM, 20, ' ');

  IF (counter > 0) THEN
    BEGIN
      SELECT TBLACCOUNT.AVAILABLE_BALANCE, TBLACCOUNT.ACTUAL_BALANCE
        INTO Avail_Bal, Curr_Bal
        FROM TBLACCOUNT
       WHERE TBLACCOUNT.ACCOUNT_ID = RPAD (ACCT_NUM, 20, ' ')
       FOR UPDATE WAIT 1;
    EXCEPTION
      WHEN resource_busy OR resource_busy2
      THEN
        ROLLBACK;                      --This rollback is not working.
        RETURN -2;
    END;
  END IF;
END;

This SP return -2 whenever i acquire lock using select for update but not doing rollback.

Upvotes: 0

Views: 633

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

If you are executing the stored procedure in a different session than the SQL Developer session where you acquired the lock, issuing a rollback will not release the lock. The SQL Developer session (Session A) holds the lock so nothing the session where the stored procedure is being executed (Session B) does can affect that. Only Session A can issue the rollback and release the lock.

If you are executing the stored procedure in the same session as the SQL Developer session where you acquired the lock, the SELECT ... FOR UPDATE statement in the stored procedure will not generate an exception because the current session already holds the lock. That would mean that the stored procedure never enters the EXCEPTION block and never issues the ROLLBACK.

Upvotes: 4

Related Questions