0

I'm trying to resolve a thread blocking issue in an API that calls an Oracle DBMS's dbms_snapshot.refresh stored procedure. It is very long running in this case.

The existing code has what you might expect:

await cmd.ExecuteNonQueryAsync();

If this were a T-SQL DBMS, the command object would have a cmd.BeginExecuteNonQuery() method that would fire off the stored procedure without waiting for a return value. In ODP.Net's OracleCommand equivalent, this method does not exist.

How can I make the same call, but without the application parking and waiting for a return value?

Note: The DB is handled by a separate department; this must be a c# solution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CDove
  • 1,940
  • 10
  • 19
  • This is Oracle solution - see if you can use it (talk to another department?). Write a stored procedure which will call `dbms_snapshot.refresh`. Then you'd just **schedule** (using DBMS_SCHEDULER built-in package) newly written procedure and it would do the refreshing *in the background* and you won't have to wait for anything. – Littlefoot Jul 12 '23 at 11:44
  • Unfortunately, that isn't an option. This is something that is a temporary measure until we complete migrating from an old application to a new one, and the DBAs won't approve temporary changes. Thus why I said I needed a C# solution. – CDove Jul 12 '23 at 11:48
  • I see. Unfortunately, I don't speak C#. I hope someone else will be able to assist. – Littlefoot Jul 12 '23 at 11:49
  • Open a new thread with a dedicated connection and execute it in background. – Wernfried Domscheit Jul 12 '23 at 11:51
  • @WernfriedDomscheit can you provide that as an answer with example? – CDove Jul 12 '23 at 12:06
  • You can use `Task.Run()` to run your method in the background without having to manually handle threads. – Panagiotis Kanavos Jul 12 '23 at 12:08
  • Which ODP.NET version are you using? In the past, Oracle's provider faked asynchronous operations. There's no Github repo to check now but a [conference sample from 2022](https://github.com/oracle/dotnet-db-samples/tree/f5bd7cf6d7d868a049bf5339aa1bd2f9f067cc32/session-demos/2022/oracle-cloudworld) showcases async operations. Given that `async/await` were 10 years old by that point, that demo probably showed a *new* capability – Panagiotis Kanavos Jul 12 '23 at 12:14
  • It's several years ago, when I learned it in a training but I never used it in practice. But you should find many examples in the internet doing it. [Class Task](https://learn.microsoft.com/en-us/dotnet/api/system.threading.tasks.task?view=net-7.0) might be a good starting point. – Wernfried Domscheit Jul 12 '23 at 12:15
  • What's the *actual* problem with `await cmd.ExecuteNonQueryAsync();`? `await` doesn't block the application nor does it cause anything to run in the background. It awaits *without blocking* for the asynchronous operation to complete, releasing the current thread to do other work. When the async operation completes, it resumes execution in the next statement. If you use a recent ODP.NET version with *proper* async operations, `await cmd.ExecuteNonQueryAsync();` won't block anything. If the method is fake though, it will block until completion and return the result wrapped in a completed `Task` – Panagiotis Kanavos Jul 12 '23 at 12:22

1 Answers1

0

Compared to the "Begin..." call, you could just remove the await

cmd.ExecuteNonQueryAsync();

That is the equivalent of the "Begin..." pattern call. However, neither is actually "fire & forget", because if you close the database connection and cancel the transaction, I guess the database will stop and roll back. So, you still need to "fire & wait", you are just free to do so asynchronously.

nvoigt
  • 75,013
  • 26
  • 93
  • 142