OraFAQ Forum: Forms » ora-60 Dead Lock

Home » Developer & Programmer » Forms » ora-60 Dead Lock

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora-60 Dead Lock [message #160495]

Sun, 26 February 2006 23:51

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Hi

I am using Oracle Forms 6i.
I have a form where there are three Database blocks.

1. FH_TRANS - (HEADER)
With TRNH_SR_NO as Primary key through sequence Number.

2. FD_TRANS - (DETAILS)
With (TRND_TRNH_SR_NO, TRND_SR_NO) Both Numbers again
as Primary key

3. FD_DB_MR_HEADER - (SUB-DETAILS)
With (DBMR_TRND_TRNH_SR_NO, DBMR_TRNH_SR_NO, DBMR_SR_NO)
as primary key.

Now while data entry for certain rows in FD_TRANS
I need to cpature details which should go to DB_MR_HEADER Table.
After Entering details from here when I have to Navigate back to FD_RANS I use POST.

Now when Multiple Users are trying to access this Same form and do data entry sometimes it Goes into DEADLOCK Situtation.
It gives ora-00060 Error.

I Know its a big shame to come across this error for any developer.
But I am not ABle to get thru what could be wrong here.
And as usual after few minutes the lock is released and i can enter further details.

I know its the POST doing something wrong. But dont know how to stop it from doing so Sad

.
Can someone help me to get over this situation.
Thks
Atul p

Report message to a moderator

Re: ora-60 Dead Lock [message #160520 is a reply to message #160495]

Mon, 27 February 2006 02:26

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't know solution to the problem as it is, but have something else in mind: if your database version allows it, perhaps you might want to consider use of the Global Temporary Tables. Data stored into them are visible ONLY to the user who works with them. Therefore, everyone would see his own records and no locking would be possible. At the end of the transaction, data would be "copied" from the GTT tables to the "original" ones.

Report message to a moderator

Re: ora-60 Dead Lock [message #160550 is a reply to message #160520]

Mon, 27 February 2006 04:21

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Hi Littlefoot

Will wait for some more time.
Expect to get some feedback.
HOwever if nothing turns up then Maybe i might have to go
with your Option.

Thks & rgds
Atul P

Report message to a moderator

Re: ora-60 Dead Lock [message #160672 is a reply to message #160495]

Tue, 28 February 2006 00:24

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Do you have any triggers? What is the code that is in them? Did you use the 'relations' wizard to 'join' your blocks or did you write your own code?

David

Report message to a moderator

Re: ora-60 Dead Lock [message #160714 is a reply to message #160672]

Tue, 28 February 2006 03:17

orausern
Messages: 826
Registered: December 2005

Senior Member

Are the 3 tables referred in your post, having referential constriants? if they do, then are the foreign keys indexed? I have faced one situation, where somehow deadlocks were happending and by indexing the foreign keys we got rid of that.

Also it would be good , if you can identify EXACT scenario that leads to the deadlock..EXACT sql statement being fired etc..(assuming indexing foreign keys do not solve the issue..)

Report message to a moderator

Re: ora-60 Dead Lock [message #160751 is a reply to message #160672]

Tue, 28 February 2006 04:29

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Hi David

I have no triggers at backend.
At front end I have loads of them for Validation etc.
I did use the relation wizard while joining them.

Rgds
Atul P

Report message to a moderator

Re: ora-60 Dead Lock [message #160755 is a reply to message #160714]

Tue, 28 February 2006 04:37

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Hi Nirav

No only the DETAIL and Sub detailed table is referred.
They are joined by JOIN-CONDITION.
Foreign Keys are Primary Keys for respective Tables.

Exact scenario is.
When multipkle Users try to enter Data in the SUB-DETAILEd thats is the third level Table(FD_DB_MR_HEADER) for which the POST is used it goes into Dead-lock.

Rgds
Atul p

[Updated on: Tue, 28 February 2006 04:40]

Report message to a moderator

Re: ora-60 Dead Lock [message #160756 is a reply to message #160714]

Tue, 28 February 2006 04:38

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Report message to a moderator

Re: ora-60 Dead Lock [message #160763 is a reply to message #160756]

Tue, 28 February 2006 05:00

orausern
Messages: 826
Registered: December 2005

Senior Member

so you have foreign keys..are ALL of the FOREIGN keys in the 3 tables involved, indexed?- just make sure that there is NO foreign key that is left unindexed. -at least to rule out the possibility that they are the cause.

Report message to a moderator

Re: ora-60 Dead Lock [message #160768 is a reply to message #160763]

Tue, 28 February 2006 05:14

orausern
Messages: 826
Registered: December 2005

Senior Member

for example u can use this query:

select constraint_name from User_cons_columns where table_name in ('FH_TRANS','FD_TRANS','FD_DB_MR_HEADER' ) and
constraint_name in (select constraint_name from user_constraints where
constraint_type='R') and column_name not in (select column_name from user_ind_columns)
/

This query should NOT RETURN ANY ROWS..if they do then there are unindexed foreign keys..pl. check.

Report message to a moderator

Re: ora-60 Dead Lock [message #160770 is a reply to message #160768]

Tue, 28 February 2006 05:29

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Hi Nirav

The QUery that u send definately does not return any rows.
HOwever I came across something new thats is.
IN the Table User_cons_columns
when I check there is no listing for
table : FL_DB_MR_HEADER

The Syntax to assign the Foregn and Primary key for Table
is as below.
When I run the same in SQL it does say Table altered.

Code is as below:

ALTER TABLE FL_DB_MR_HEADER
	ADD CONSTRAINT PK_DMRH_SR_NO
		PRIMARY KEY(DMRH_SR_NO)
/


ALTER TABLE FL_DB_MR_HEADER
	ADD CONSTRAINT FK_DMRH_TRN_SR_NO
		FOREIGN KEY (DMRH_TRND_SR_NO,DMRH_TRNH_SR_NO) 
			REFERENCES FD_TRANS (TRND_SR_NO,TRND_TRNH_SR_NO)
/

Rgds
Atul P

Report message to a moderator

Re: ora-60 Dead Lock [message #160777 is a reply to message #160770]

Tue, 28 February 2006 05:50

orausern
Messages: 826
Registered: December 2005

Senior Member

Atul,

If it does not return any rows than that it! that proves that our original suspect that "this may be due to unindexed foreign keys" is proven false.

however, I did not ask you to create any primary or foreign keys..so I do not understand why you have added them. Are you the main developer or one having all authority to modify the tables? if not THEN PLEASE ROLL BACK THESE CHANGES....AS IT MAY LEAD TO PROBLEMS. HAVE a discussion with your architect or whoever is responsible for table design before you modify.

So what next? well you need to continue the investigation..as I said earlier..try finding the EXACT SCENARIO..(WITH more details)

what is all the work done by the code at the time when the deadlock ocurrs..list it down -if possible with the detail of all the data that is being entered. Now if any other person repeat this steps , they should get a deadlock..once that is done, try to find out which exact sql statements are begin sent..EXACT statements. that may start giving a clue to the answer.

Report message to a moderator

Re: ora-60 Dead Lock [message #160778 is a reply to message #160777]

Tue, 28 February 2006 05:58

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Nirav

I did not add any Prmary keys.
FOrget about that Part.
I was just worried that why those keys dont reflect in the
User_cons_columns Table.

Exact scenario is.
When multiple Users try to enter Data in the
Table(FD_DB_MR_HEADER) for which the POST is used
it goes into Dead-lock.(Sometimes not all times)

Rgds
Atul P

Report message to a moderator

Re: ora-60 Dead Lock [message #160781 is a reply to message #160778]

Tue, 28 February 2006 06:13

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi Atul,
Thank you about clarification on altering the tables.

About finding of scenario --can you identify exact sql statements that are fired when this post occurs? after finding that, can you from 2 different sql plus sessions, try to insert the same sql statements that were prepared and sent by the application?

IF your findings are correct THEN you will be able to get a deadlock from sql plus also..
so this is one way to investigate further. (this same thing can also be done by setting sql_trace for those sessions and then seeing the trace files)

also it is possible that I missed out some other points/cause-this is AFAIK.

Report message to a moderator

Previous Topic:	FRM-92101 Error
Next Topic:	Create/Write Cursor???

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:48:39 CDT 2024