Multi-threaded, Multi-session Application with heavy executions lead to Crashing

[amitgujrathi]

I have a Delphi 7 multi-threaded & multi-session application with heavy insertions into 9i Database at the rate of 20/25 messages/inserts per second using a stored procedure call which is part of a Oracle Package. The application crashes after certain time of the exections like 200 messages/inserts or so. If I comment all the code related to the insertions my application works great even at a speed of 50 msgs/sec.

I'm totally frustated with the complete scene, I've been trying to fix this issue from last 2 weeks, but no luck yet.

It would be great if some-one can come up with any idea. If required I would put my code here, but since its a big code of about 20 thousand line (including code for 2 components used in the application, wrriten by me only, amounting to approx 10 thousand lines).

Here is the complete Scenario:

My Sessions & Queries are created & destroyed during run-time only on initialization of each thread.

All threads have their own independent session & no threads interfere with the other threads session.

I do have Datamodule (which is running on the Main Thread), which is the actual manager of all the threads & indirectly all sessions. The Main-thread has multiple Thread Safe Object/string Lists, which are used by the individual threads for all kinds of communications between these threads & is the only link between them.

Every thread has insertions done via stored procedure calls & simultaneously uses some select statements queries to fetch records. I do have an Oracle Event in one of the thread which listens to Alerts on one of the session in that session itself.

Any of you guys help would be highly appreciated.

Thanks & Regards
Amit

------------------
Amit Gujrathi
Sr. Software Developer
Worldco LLC
110 Wall Street, New York, NY 10005
Phone: 212-601-6311
eMail: agujrathi@wldc.com

 

[Marco Kalter]

Can you let me know the following information?

1. Your exact Direct Oracle Access version from the Oracle > Info menu.

2. The exact Oracle Client and Server version.

3. The error message of the crash.

------------------
Marco Kalter
Allround Automations

 

[amitgujrathi]

Here are the answers to your question:
1. Your exact Direct Oracle Access version from the Oracle > Info menu.
>> 3.4.6.4

2. The exact Oracle Client and Server version.
>>Oracle Client & Server 9i

3. The error message of the crash.
I don't come across any errors, thats the Main issue, I would get any errors I would start looking in that direction, but to my disappointment, before I could catch the error, the application crashes.

Thanks for your help !!

------------------
Amit Gujrathi
Sr. Software Developer
Worldco LLC
110 Wall Street, New York, NY 10005
Phone: 212-601-6311
eMail: agujrathi@wldc.com

 

[Marco Kalter]

Do you mean that the application just "disappears" without an exception? Do you know whan this happens (e.g. connect, query.execute, fetch, and so on)?

Have you set TOracleSession.ThreadSafe to True? If not, can you try this to make sure that you are not running into some thread safety issue?

One last thing: can you verify if this problem also exists on a different Oracle version, for example 8i or 9i release 2?

------------------
Marco Kalter
Allround Automations

 

[amitgujrathi]

Marco,

Thanks for your replies so far & actually I'm sorry for all the trouble.

The issue is resolved now, It actually had nothing to do with DOA components. It was a silly mistake on my part, I was doing the triggering of insertions/executions of the the items/records from my Threaded Object/String List twice resulting into the crash ultimately.

Thanks for all your help & Keep the good work going. DOA components are the best Direct Oracle Access components I have come across so far.

Thanks Again !!
Amit

------------------
Amit Gujrathi
Sr. Software Developer
Worldco LLC
110 Wall Street, New York, NY 10005
Phone: 212-601-6311
eMail: agujrathi@wldc.com