Re: Table Locking Problem

In article <1eRK4.3629$E4.7429_at_newsfeeds.bigpond.com>,   "Graeme Farmer" <g_farmer_at_halas.com.au> wrote:
> I had a situation recently where a user issued the statment
> select * from <table_name>
> for update;
>
> Without the where clause, this statement proceeded to select all
 data,
> return it to standard output and lock the records of one of our
 largest
> tables.
>
> This process was taking too long to wait then issue the rollback
> statement so I killed the user session assuming that PMON would kick
 in and
> tidy up the mess (including removing the table locks). This did not
 appear
> to happen so in Server Manager I issued the command:
> ordebug wakeup <pid of PMON>
>
> This also appeared to do nothing. Time was moving on (or it seems
 that
> way when irate staff are hounding you) so I tried to shut down the
 database
> using shutdown immediate. All users had logged off by this stage but
 the
> database would not shut down. After about 20 minutes (with no disk/cpu
> activity, AT ALL) I figured the database was not going to shut down
 so I
> forced it down (for lack of a better euphemism!).
>
> I restarted the instance, instance recovery took place and the
 database
> opened. The table, however, was still locked. After a few minutes of
> restarting the instance the lock was released and everything was back
 to
> normal.
>
> Was I just being impatient? Would the locked table have been
 released?
> If so why was it taking so long (or did it just seem to take long!!).
 Is
> there any other way to release locks, or to halt a user session that
 is
> issuing locks midstride and reverse it's action safely?
>
> Thanks and regards,
> Graeme Farmer,
> AIX System Administrator/Oracle DBA.
>
> The opinions expressed here are my own. Or so I'm told.
>

When you kill a process that is holding locks Oracle will often use that same process to perform the rollback. You can tell if this is happening by looking at the sid, serial#, and status for the process. Where the OS shadow process still exists then a status of 'killed' if the serial# changes every 30 seconds or so then I believe Oracle is using the process to perform the rollback. If the process is gone then pmon is doing the work. The rollback will probably take as long or maybe slightly longer than the process took to get the locks in the first place in order to roll them back. You were probably too quick on the bounce.

When you bounced your database you expected Oracle to perform instance recovery and when the database opened that the locks would have been removed. But the current versions of Oracle open and allow users in before all recovery operations complete. I expect pmon was in the process of cleaning up the locks but it may even be possible that Oracle used delayed block cleanout on the affected table so no clean up was performed until user processes attemped to access the data and found that clean up was needed and did it themselves.

I usually start my databases in restricted mode to keep customers off the system until I pin and load large packages and verify its status.

Rule 1 - Don't Panic
Rule 2 - Don't Rush, look and analyze key v$ tables, alert log entries
Rule 3 - Don't stop the db until you are sure it will probably restart

We have encounted problems in the past where because the db was up and running we could take action, but had we stopped it then we would have not been able to start.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.