Situation:
Table LOCKTEST with unique field TESTID.

From one session (for example SQL+) insert a record with TESTID=33 without commit.

Then from Delphi application with thread having a TOracleQuery do the same (insert record with TESTID=33). Then this thread hangs until the other session is committed.

If you don't commit the other thread and call OracleSession.LogOff in the hanging thread the application wil crash without any exception! The last debug line is a call to 'OCISessionEnd'.

Calling BreakExecution first doesn't make a difference.

Any ideas?

If TOracleSession.ThreadSafe = False in a multi-threaded application, you must be careful when you access the same session from different threads. Simultaneous access can lead to unpredictable results.

If a thread is waiting for a lock, then you do not have much control. Like you said, BreakExecution does not help (the Oracle Server does not respond in case of a lock). You can only wait until the lock is released.

It's not the same session. The thread has its own TOracleSession. And what if the execution takes on for ever?? There must be a possibility to stop the execution.

I just use the thread to be able to access the oraclesession. If it is hanging in main thread, the application doesn't react at all.

So one session in a thread in a delphi application and the other session is through Oracle's SQL+.

The Oracle session in the thread has ThreadSafe=false because it isn't used by more threads.

I wrote a testproject:

Test Project

You must set the connection-info for the database and create the table as described in the top of unit1.pas.

You can add the record from SQL+ or with the button 'Add Record' that adds a record to the testtable. Then press the execute button, which inserts the same record in the same table with another session from a thread.

You cannot disconnect a session in the main thread that is currently executing in a background thread. All you can do is call TOracleSession.BreakExecution, and if that doesn't help, you have no real control over the session until execution finishes.

OK, but why does my application crash when I call logoff??? It doesn't even show an exception in Delphi!

This is caused by the Oracle Call Interface DLL (oci.dll). Like I said, you cannot access a session from 2 different threads if ThreadSafe is false, this will lead to unpredictable results.

Did you try my test project????
It uses 2 different sessions!!!
Then ThreadSafe isn't relevant according to your help:

Quote: "Multi-threaded, multi-session database access. No special properties need to be set, and session management is clear."

What am I missing??

I didn't notice that it uses multiple sessions. What exactly should I do in your application to reproduce the problem?

You can reproduce it the way I described above...

Quote: "You can add the record from SQL+ or with the button 'Add Record' that adds a record to the testtable. Then press the execute button, which inserts the same record in the same table with another session from a thread."

So first press the button 'Add Record' and then press the other button. Make sure the table is empty.

This does not immediately cause a problem. If I try to close the application I get an ORA-00001 error because the session is logged off and the transaction is committed.

My mistake. The timer is not enabled, when running the thread. Adjusted test-project:

Test Project

Just tried it with the newest evaluation version (4.0.4) and pressing the second button makes the application hang. Using our version (3.4.4) the application exits without an exception.

Can you please look at it?

Thanks.

Can you please look at it?
Our database server is 'randomly' crashing, which isn't very convenient for connected clients...

I will check it out tomorrow.

I ran the demo, and it does indeed hang. This is because your timer event attempts to disconnect the session that is currently executing in the background thread. Like I said, this will lead to unpredictable results if ThreadSafe = False, and to a hangup if ThreadSafe = True. The LogOff must wait until the thread is finished executing the query.

Putting the timer in the thread makes no difference... But maybe it's not really 'in' the thread. Aside from this: how can I check whether a session (a query or whatever) is still busy?

The timer event will always occur in the main thread, it does not matter where you create it.

To see if a session is busy, you would need to create your own administration. You could also simply check if your thread is suspended. Only then is it safe to access the session from the main thread.

Hi Marco,

this was the reason why i asked for such a property some time ago
Any chance to get it someone ?

Greetings
Jens

I realized that when I wrote my previous reply . It's still on the to-do list.

Hi Marco,

In a reply you posted on 15 March, 2004 14:42 (Topic: Application crash without exception on LogOff when busy ) :

What did you want to say exactly by "The timer event will always occur in the main thread, it does not matter where you create it."

Does it means a TTimer.timer procedure where I execute a TOraclequery with Session1 in Thread1 can cause a hang in principal thread working itself with its own session?

Greetings
CdeV

A TTimer.OnTimer event is generated by Windows, and occurs within the main thread of the application (where all windows events occur). If you access a session from this event, you can interfere with other threads accessing the same session. It cannot interfere with other application functions from the main thread that access this session.

OK. Thanks Marco.

In fact, I tried to use Timers created with each of my sessions to insure the session is always connected. :p
So, is there a possibility to periodically refresh all sessions (with or without timers). The goal is to prevent each session to be disconnected after the user's profile defined parameter "Idle time" (60 minutes in this case and cannot be changed ).

If you set TOracleSession.ThreadSafe to True for all sessions, you can still use a Timer to call TOracleSession.CheckConnection(True) for each session. This will be blocked by simultaneous access from the threads, but it will work okay.

The CheckConnection function will keep the session alive by preventing too much idle time, and will (attempt to) reconnect in case of fatal other failures, such as a killed session, a database that was shutdown and restarted, network failures, and so on.