Handle exception in multithread with NonBlocking

[Freezer_86]

Hello,
Can someone help me with my problem, some solution of advice?
My real application is Rest Service with connection to Oracle DB. Service must support many client connections, so it’s must handle all requests in separated threads.
My working thread got own TOraSession. When it’s get client request – it’s takes some pl/sql block (it’s can be anything, configurated by DB admin).
This thread must to:
1. Init and start execution of some pl/sql block
2. Wait until pl/sql block will be done or some amount of time (timeout)

• If was timeout – break execution and send special error to client.

• If was exception – send text of exception to client.

• If was executed fine – get value of out params and send it to client.

I tried to do this with TOraQuery, TOraSQL and TOraScript. When I run it in default mode – all fine, but I can’t set timeout for execution. Timeout it’s a critical thing, because pl/sql block can hang for a long time from some reason (like locked object or something else). When I try to run all in NoBlockingMode – all works fine, BUT:
1. TOraScript, got OnError, but can’t run in NoBlockingMode
2. TOraQuery, got OnPostError, OnUpdateError (which does not call when you use pl\sql). When I try to handle exception with TOraSession.OnError – it’s run in MainThread of application, so starts classic "race condition". TOraQuery.Executing is false, but real Exception can be not processed yet.
3. TOraSQL – got very useful TOraSQL.WaitExecuting, but handling exceptions is same as for TOraQuery.

My source was like this:

Code: Select all

try
  qr.NonBlocking := true;
  qr.Session.OnError := OnSessionError;
  FPostError := nil;
  try
	xStartExecTime := Now();

	qr.ExecSQL();

	while qr.Executing do
	begin
	  FLastUsed := now;
	  Sleep(2);

	  if SecondsBetween(Now(), xStartExecTime) > C_WAIT_SECONDS then
	  begin
		qr.BreakExec;

		while qr.Executing do
		begin
		  Sleep(2);
		end { while };

		raise Exception.Create('qr.ExecSQL too long! > ' +
		  C_WAIT_SECONDS.ToString);
	  end { if };
	end { while };

	if Assigned(FPostError) then
	  raise Exception.Create(FPostError.Message);

  finally
	qr.Session.OnError := nil;
  end { try..finally };
except
  on E: Exception do
  begin
	raise Exception.Create(E.Message)
  end { on };
end { try };

I prepared some small application with demontrating of this problem. It's must work on any Oracle DB.
https://drive.google.com/open?id=1psR0X ... CU3vGBBqIK

[Freezer_86]

As we can see on a screenshot, if we run pl/sql block in a same thread – we have all exceptions. When we run in NonBlocking – my thread things that all was run successful.
How I see solution of this problem:
Odac already have handling of Exceptions which occurs in a separated thread, so if we additional write exception in a some property (like TOraSQL.RuntimeException), developers can easy check - was executing successful or not.

Maybe there is other way to solve it.

[MaximG]

You can use timeouts with our components: https://www.devart.com/odac/docs/devart ... meout.htm
You can implement the desired behavior by creating your own thread without using the NonBlocking mode, which exception handling doesn't comply with the business rules of your project.

[Freezer_86]

You can use timeouts with our components: https://www.devart.com/odac/docs/devart ... meout.htm
You can implement the desired behavior by creating your own thread without using the NonBlocking mode, which exception handling doesn't comply with the business rules of your project.

Ohh.. Thank you for advice. I'm currently use ODAC 10.3.9, there is no property "CommandTimeout". I'll renew my license and try to do like that.
Thank you again.

[MaximG]

Please don't hesitate to contact us with questions concerning ODAC usage.