Re: Help with Locking Issue
This is hugely irritating, and I guess it may be a version-dependent thing, but I can't get the exact match for the quoted locks by setting up three tables in the manner described. The locking information varies between 8.1.7.3 and 9.0.1.3, but I can't get a PK/FK issue to behave (mis-behave ?) properly. Various failures include: a)Seeing a lock on the parent table b)Not seeing the TX mode 4 (share) lock at all c)See a TM mode 5 (share row exclusive) request on the child. (combined with a held mode 3 (row exclusive). Which version of (exact) Oracle is this, and have you figured out the exact pattern of actions that make the problem appear. My tables are: P1 (id_p1 number primary key ...) P2 (id_p2 number primary key ...) C(id_p1 references P1, id_p2 references P2, primary key (id_p1,id_p2) ...) This seems to describe your scenario - two foreign keys, but only one of them index by virtue of the composite primary key. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 18 April 2002 19:45 I had already checked that, but since you asked, I double checked. The primary key on the FIXED_ASSET_ACTIV table has two columns - both are foreign keys from other tables. I created a separate index for the 2nd column in the PK. This fixed my problem! So, the foreign key was indexed, it just wasn't indexed correctly. Thanks, Jay [EMAIL PROTECTED] 04/18/02 01:17PM Jay, do you have any unindexed foreign keys on those tables? If so, Oracle Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Help with Locking Issue
I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Help with Locking Issue
Jay It looks like the application is locking the FIXED_ASSET_ACTIV exclusive. Is the application written in Visual Basic and they are issuing a SELECT FOR UPDATE clause? The developers here loved the clause even if it wasn't the table they were updating. It sounds reasonableI'm doing an update so there fore the data selected to get the row from a different table updated must be SELECT FOR UPDATE NOT! . It took a while but they stopped that practice and things run smoother now. Ron ROr mª¿ªm [EMAIL PROTECTED] 04/18/02 12:58PM I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Help with Locking Issue
I had already checked that, but since you asked, I double checked. The primary key on the FIXED_ASSET_ACTIV table has two columns - both are foreign keys from other tables. I created a separate index for the 2nd column in the PK. This fixed my problem! So, the foreign key was indexed, it just wasn't indexed correctly. Thanks, Jay [EMAIL PROTECTED] 04/18/02 01:17PM Jay, do you have any unindexed foreign keys on those tables? If so, Oracle will take out a lock on any transaction involving the parent or child, IIRC. HTH, Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Jay Hostetter [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: Help with Locking Issue I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Jay Hostetter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Help with Locking Issue
Cool! Happy querying...
Scott Shafer
San Antonio, TX
210-581-6217
-Original Message-
From: Jay Hostetter [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, April 18, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Help with Locking Issue
I had already checked that, but since you asked, I double checked. The
primary key on the FIXED_ASSET_ACTIV table has two columns - both are
foreign keys from other tables. I created a separate index for the 2nd
column in the PK. This fixed my problem!
So, the foreign key was indexed, it just wasn't indexed correctly.
Thanks,
Jay
[EMAIL PROTECTED] 04/18/02 01:17PM
Jay, do you have any unindexed foreign keys on those tables? If so,
Oracle
will take out a lock on any transaction involving the parent or child,
IIRC.
HTH,
Scott Shafer
San Antonio, TX
210-581-6217
-Original Message-
From: Jay Hostetter [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, April 18, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Subject:Help with Locking Issue
I have been spending most of my morning trying to resolve a locking
issue. I think I could me missing the forest for the trees. This is
what
happens: a user kicks off two identical jobs from two different PCs.
Each
of these jobs is doing the same thing, but against different rows of
data
(they are processing work orders in our system, but each job is
processing a different work order).
One session will wait until the other session completes. I am trying
to
figure out what they are waiting on. At first I assumed a locked
record,
but I don't think that is the case. I did quite a bit of research on
MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might
be
too small, but that didn't seem to help either.
Here is the output from the query in note 1020047.6.
Sess Op Sys OBJ NAME or
ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req
Mode
-- - -- ---
---
12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl
12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive
14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl
14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share
14 KEN468 Batch BATCHTrans-65597 TX Exclusive
So session 14 is waiting for a share lock. Session 12 has an exclusive
lock that is blocking session 14. How do I find out what session 12 has
locked that is needed by session 14?
Thanks,
Jay
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Jay Hostetter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Help with Locking Issue
Any bitmap indexes on the table ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 18 April 2002 19:15 I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Help with Locking Issue
Jay , Thanks for the feedback. One to store in the memory back when you inherit projects. Ron [EMAIL PROTECTED] 04/18/02 02:53PM I had already checked that, but since you asked, I double checked. The primary key on the FIXED_ASSET_ACTIV table has two columns - both are foreign keys from other tables. I created a separate index for the 2nd column in the PK. This fixed my problem! So, the foreign key was indexed, it just wasn't indexed correctly. Thanks, Jay [EMAIL PROTECTED] 04/18/02 01:17PM Jay, do you have any unindexed foreign keys on those tables? If so, Oracle will take out a lock on any transaction involving the parent or child, IIRC. HTH, Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Jay Hostetter [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Subject: Help with Locking Issue I have been spending most of my morning trying to resolve a locking issue. I think I could me missing the forest for the trees. This is what happens: a user kicks off two identical jobs from two different PCs. Each of these jobs is doing the same thing, but against different rows of data (they are processing work orders in our system, but each job is processing a different work order). One session will wait until the other session completes. I am trying to figure out what they are waiting on. At first I assumed a locked record, but I don't think that is the case. I did quite a bit of research on MetaLink. I even rebuilt the table in case INITRANS and PCTFREE might be too small, but that didn't seem to help either. Here is the output from the query in note 1020047.6. Sess Op Sys OBJ NAME or ID USERNAME User IDTERMINAL TRANS_ID TY Lock Mode Req Mode -- - -- --- --- 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive 14 KEN468 Batch BATCHFIXED_ASSET_ACTIV TM Row Excl 14 KEN468 Batch BATCHTrans-196694 TX --Waiting-- Share 14 KEN468 Batch BATCHTrans-65597 TX Exclusive So session 14 is waiting for a share lock. Session 12 has an exclusive lock that is blocking session 14. How do I find out what session 12 has locked that is needed by session 14? Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Jay Hostetter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
