Thursday, November 13, 2014

Rollback to Savepoint Does Not Release Locks

I read that rolling back to a savepoint releases locks. This sounded reasonable so I decided to check it out in an Oracle 11.2 database. I logged in as user John (in blue) and noted my SID for future reference. Then I created a table, inserted a row, committed the change and created a savepoint. Finally I updated the row but did not commit the change, thus setting up a lock:
 
SQL> conn john/smith
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       683
 
SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
 
SQL> savepoint sp1
  2  /
 
Savepoint created.
 
SQL> update tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         2
 
SQL>
 
I logged into a new session as user Fred (in red), noted my SID again and tried to update the same table. This did nothing, as you might expect:
 
SQL> conn fred/bloggs
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
         5
 
SQL> update john.tab1 set col1 = 3
  2  /
 
I returned to John’s session and checked that I could see the lock in the DBA_WAITERS view. Then I rolled back to the savepoint and checked that the original value had reappeared in the table (it had). However, the lock was still shown in DBA_WAITERS:
 
SQL> show user
USER is "JOHN"
SQL> select holding_session, waiting_session
  2  from dba_waiters
  3  /
 
HOLDING_SESSION WAITING_SESSION
--------------- ---------------
            683               5
 
SQL> rollback to savepoint sp1
  2  /
 
Rollback complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
 
SQL> select holding_session, waiting_session
  2  from dba_waiters
  3  /
 
HOLDING_SESSION WAITING_SESSION
--------------- ---------------
            683               5
 
SQL>
 
I guessed that Oracle must still be holding some kind of lock between the two transactions. There was clearly no lock on the data any more as Donald was able to start a new session (in green) and update it with no problems:
 
SQL> conn donald/duck
Connected.
SQL> update john.tab1 set col1 = 4
  2  /
 
1 row updated.
 
SQL> select * from john.tab1
  2  /
 
      COL1
----------
         4
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>
 
User John finished his transaction and the lock disappeared:
 
SQL> show user
USER is "JOHN"
SQL> commit
  2  /
 
Commit complete.
 
SQL> select holding_session, waiting_session
  2  from dba_waiters
  3  /
 
no rows selected
 
SQL>
 
… and this allowed Fred’s earlier update to finish:
 
SQL> conn fred/bloggs
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
         5
 
SQL> update john.tab1 set col1 = 3
  2  /
 
1 row updated.
 
SQL>

2 comments:

Anonymous said...

You don't see the second session 'unblock', that is because the second session is blocked on the first session - NOT on the row the first session locked, but on the first session itself.

Jeffrey Kemp said...

Good to know. Reproduced in 11.2.0.3.

It's true, the row lock is released (allowing Donald to update the row), but Oracle blocks Fred on John's session (because dba_waiters is not fine-grained enough to pick up which row locks were released by the rollback to savepoint).

Of course, if John were to do a full rollback, the session is unlocked immediately.