![]() ORA-04020: deadlock detected while trying to lock object HOSCDPAYTEST.NP_PAYROLL_PROCESS The following information may aid in finding This should not be reported to Oracle Support. Of concurrent statements which can cause a deadlock. ![]() The design of an application or from issuing a set This deadlock is usually due to user errors in Still i could not realize the scanario.Ī deadlock among DDL and parse locks is detected. After this execution, locking on a record has been occuring frequently, it may be and index lock as you explained earlier. We ran a stored procedure to process the payroll through a forms6i application. ***************** dump of cursor xsc=1a7681c **********************Īnd that should help you ID where the problem is (you'll see the BR_NO and be able to identify the ROWS being modified) Value=5 <<<<<= the bind variable valuesīind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=0 offset=24 <<<<<= the bind variable values It isn't a row lock issue - rather, an index key collision that is happening.įurther down in the trace file, you should be seeing something like:įrm: - Comment - Size Seg Offīind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=48 offset=0 So, basically, you have two sessions doing this update (or a similar update) and they are bumping into each other with a unique index. That is when this trace file will be produced: Session 1 will block and then one of the sessions will get the dead lock. Next, session 1 tries to update 2 to 6 - that'll be another conflict with session 2, unique key violation POSSIBLE at this point. Session 2 will then update 4 to 5 - this'll be a conflict, unique key violation POSSIBLE at this point. Session 2 will then update 3 to 6 (no conflict, but 6 is "locked" in the index) Here, session 1 will get the row updated from 1 to 5 - 5 will be "locked" in the index. The locks are NOT on a row - they are due to unique conflicts. I'm going to hypothesize that there is a unique index on some (at least one) of the columns being updated. UPDATE INV_MST SET PV_NO=:b1,PV_DT=SYSDATE,ACCT_YR_MTH=:b2,ACCT_DT=SYSDATE,PASSĭT=SYSDATE,INV_STATUS='03' WHERE BR_NO=:b3 In following case how to find out objects involved in deadlock. You are updating more then one row in RA_INTERFACE_LINES_ALL but must be getting them in a different order so one session updated 1 row, another row 5 (for example) then the first session tried to update row 5 (blocks) and the second session tried to update row 1 (deadlock) Tells us this is data object 5173 - its in HEX. There are various apis in the dbms_rowid pacakge to get all of the information you select to_number( '00001435', 'xxxxxxxxxxx' ) from dual Shows that given a rowid from dual, I can find the object number and hence the object name. You use the dbms_rowid package to extract this info, for select rowid from select dbms_rowid.rowid_object( 'AAAADCAABAAAAHQAAA' ) from select object_name from all_objects where data_object_id = 194 ![]() Session 125: obj - rowid = 00001435 - AAEUluACwAAAY83AAB Session 62: obj - rowid = 00001435 - AAEUluADmAAAswlAAA Resource Name process session holds waits process session holds waits Information may aid in determining the deadlock: It is aĭeadlock due to user error in the design of an application The following deadlock is not an ORACLE error. ![]() UPDATE RA_INTERFACE_LINES_ALL SET LINE_GDF_ATTRIBUTE1=:b1,LINE_GDF_ATTRIBUTE2=DECODE(:b2, NULL ,DECODE(:b3, NULL ,:b4,:b3),LINE_GDF_ATTRIBUTE2),LINE_GDF_ATTRIBUTE3=DECODE(:b6, NULL ,:b7,:b6),LINE_GDF_ATTRIBUTE4=DECODE(:b9, NULL ,DECODE(:b10, NULL ,:b11,:b10),LINE_GDF_ATTRIBUTE4),LINE_GDF_ATTRIBUTE5=DECODE(:b13, NULL ,DECODE(:b14, NULL ,:b15,:b14),LINE_GDF_ATTRIBUTE5),LINE_GDF_ATTRIBUTE6=DECODE(:b17, NULL ,DECODE(:b18, NULL ,:b19,:b18),LINE_GDF_ATTRIBUTE6),LINE_GDF_ATTRIBUTE7=DECODE(:b21, NULL ,:b22,LINE_GDF_ATTRIBUTE7),HEADER_GDF_ATTR_CATEGORY=DECODE(:b23, NULL ,'JL.BR.ARXTWMAI.Additional Info',HEADER_GDF_ATTR_CATEGORY),LINE_GDF_ATTR_CATEGORY=DECODE(:b24, NULL ,'JL.BR.ARXTWMAI.Additional Info',LINE_GDF_ATTR_CATEGORY) WHERE ROWID = :b25 Would please elaborate on the whole output if possible ?Īnd how can prevent or at least avoid this situation ? How can I understand which two rows where involved in the following deadlock, which appears in this excerpt of a tracefile ?Įspecially in the last two lines, Oracle seems to convey this information, but I was not able to locate the object or the rowids. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |