| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Where is the lock?
Sean,
A lock would normally go away when the database is shut down, but if the
problem was in the application (or the users), the lock would probably come
right back again.
Check In v$lock for type "TX" locks with lmode=6 . These are exclusive row
locks caused by updates or select for update, inserts or deletes. If you see
any of these you're on the right path. (The column ctime shows how long the
lock was held in seconds)
The session thats reporting the error might not have any rows in v$lock, because they probably did a select for update nowait. In otherwords they checked to see if the row they wanted to update was locked and because it was, they returned control to the interactive user instead of requesting a lock. You could check the row_wait_obj# in v$session which will have the object id of the table that the select for update was issued against. Sessions that didn't wait will have a value of -1 , sessions that did wait will have the object_id in dba_objects.
Run-away sessions are less common now than they used to be. I guess when you shut down the database, if you stopped the Oracle service (NT) or killed all the oracle processes you would be sure that there aren't any of them around (unless it was coming over a db link?)
Hope this is a bit of help to you.
Chaim
"O'Neill, Sean" <Sean.ONeill_at_organon.ie> on 07/05/2001 11:55:55 AM
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Chaim Katz/Completions/Bombardier)
On of our applications is reporting a problem trying to access on a record in a table. The application is reporting that a transaction is reserved for update. The app in question hung whilst working on the record a few days ago. I'm not convinced that this is an Oracle problem, i.e. a row lock of some sort, as I've had a peek at DB level via v$lock and v_$locked_object, v$access but can't seem to find any locks on the table in question. If it is an Oracle lock of some type how can I identify it?. The database gets shutdown every night for COLD backups but the app prog in question is reporting problem for number of days now. Is it possible that some session might still be haunting around?.
Would appreciate any advice/help on this one folks?.
Sean :)
Rookie Data Base Administrator
[0%] OCP Oracle8i DBA
[0%] OCP Oracle9i DBA
-------------------------------- ------------Organon (Ireland) Ltd.
Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA
"Nobody loves me but my mother... and she could be jivin' too." - BB King
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: Sean.ONeill_at_organon.ie Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Chaim.Katz_at_Completions.Bombardier.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Jul 06 2001 - 10:39:46 CDT
 |
 |