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.
