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>