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:

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.