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>

Tuesday, November 04, 2014

DBMS_SYSTEM.KCFRMS

I read about this in the book advertised at the end of this post. It was tested on Oracle 11.2. V$SESSION_EVENT holds similar information to V$SYSTEM_EVENT but it is broken down by session (only currently logged in sessions appear - there is no history). There is a MAX_WAIT column which shows the maximum time a session has had to wait for a particular event.There is no timestamp on this so you cannot tell when the longest wait took place. However, if you have a session which is about to start another step in a process, you can zeroise MAX_WAIT so you know the maximum wait time in that step once it has finished. This zeroises all MAX_WAIT values for all events in all sessions. It also resets MAXIORTM and MAXIOWTM in V$FILESTAT. I decided to give it a try. First I checked the current values:

SQL> conn / as sysdba
Connected.
SQL> select sum(max_wait) from v$session_event
  2  /
 
SUM(MAX_WAIT)
-------------
      1323729
 
SQL> select sum(maxiortm), sum(maxiowtm)
  2  from v$filestat
  3  /
 
SUM(MAXIORTM) SUM(MAXIOWTM)
------------- -------------
         2972          8171
 
SQL>

Then I ran the command to zeroise them:

SQL> exec dbms_system.kcfrms();
 
PL/SQL procedure successfully completed.
 
SQL>

Finally I checked the figures again:

SQL> select sum(max_wait) from v$session_event
  2  /
 
SUM(MAX_WAIT)
-------------
            0
 
SQL> select sum(maxiortm), sum(maxiowtm)
  2  from v$filestat
  3  /
 
SUM(MAXIORTM) SUM(MAXIOWTM)
------------- -------------
            0             0
 
SQL>