oracle

Why Does ODP.NET Have Async Methods If They're Not Asynchronous?

The Oracle Client (ODP.NET) that we are using to build a major web project put us in a really stupid bind last week, ultimately causing me to waste two days in search of something that wasn't really going to work in the first place.

I'm on something of an async/await binge with this project, since in C# and ASP.NET it's really easy to implement and possibly gives you great scalability benefits. So, one of the first things I did was to make all our ORACLE accessing code asynchronous. There's a particular section of the code which needs to call a bunch of stored procedures in a row, and I figured that since the procedures don't really rely on each other, we'd benefit quite a bit by making them asynchronous.

All of this was going swimmingly, until this error started popping up:

"Connection must be open for this operation"

Not being a total expert on this, I would still expect that, given an asynchronous execution, each async call would have its own connection and so this error should never happen. And yet, there it was, repeatedly being logged into our exception logger.

So, to the Google I went, and eventually came across this StackOverflow post saying that the Oracle Managed Driver does not support asynchronous operations.

Yes it does! I thought. It's got all these async methods! They wouldn't name them "async" unless they were actually asynchronous! (Apparently I've never been on the internet before.)

In fact, the Oracle Data Provider for .NET does contain a few asynchronous methods:

  • ExecuteNonQueryAsync()
  • ExecuteReaderAsync()
  • ExecuteScalarAsync()

But the StackOverflow post says that these methods are not actually async, they are synchronous. So what's going on?

Browsing the features list for ODP.NET, I can't find any reference to asynchronous methods, the Task or Task<T> classes, or even the await keyword. In fact, I can't find any documentation at all that says these async methods need to exist, and yet they do. The question is: why?

Microsoft's own documentation says the following:

"In Visual Studio 2012 and the .NET Framework 4.5, any method that is attributed with the async keyword (Async in Visual Basic) is considered an asynchronous method..."

But it never actually specifies that the method has to actually be asynchronous! Rather, only that the method should return a Task or Task<T>, and even that is only a convention!

All the above is to say: technically, ORACLE did nothing wrong by suffixing these method names with "async", since in fact they do return a Task or a Task<T>. In fact, they might even be useful since they can be asynchronous with other, non-ORACLE-accessing methods. But that wasn't my scenario, and how was I supposed to know about this in advance?

The question becomes: since the methods themselves are not actually executed asynchronously, are they of any use?

Short answer? Nope. At least not for my situation. If anything, it's more harmful than helpful.

As far as I can tell, the only reason the asynchronous versions of these methods exist is to conform to some mythical interface mentioned in the comments of this StackOverflow answer. Which just begs the question: why does the interface need these method declarations? Ultimately I decided to get out of the rabbit hole before digging myself too deep and remove all my asynchronous code that used ODP.NET.

So now, in addition to the two days I spent researching this and implementing it for ORACLE calls, I now have to spend at least half a day removing it. All because ORACLE made some "asynchronous" methods that aren't actually asynchronous. Thanks ORACLE!

To anyone else out there who has to use ODP.NET: don't bother with the asynchronous methods unless you really know what you are doing. They're a lot more trouble than they are worth.

And, if you do really know what you are doing, tell me in the comments what situations these bloody things might actually be useful!

Happy Coding!

Ancient ORACLE, Modern IIS, and a Failure to RTFM

My coworkers Rajit and Dave and I have been spending an inordinate amount of time over the last few days looking for a really annoying error in one of our apps. The exception message reads like this:

Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.

Yep, that's right, we're using ORACLE as our database. I assure you, this is not by choice. We've known for a while that our version of ORACLE is so old that it can remember when it played with two sticks and a rock (and was happy, dammit). Problem was, we assumed that it had to be causing this issue, though we ultimately determined that it wasn't responsible. Even better, in the process of squashing this bug we discovered that someone had probably found the fix for this issue already and utterly failed to inform us about it.

Sun Temple With Ancient Artwork, used under license

Here's the situation: the app we're building is a rewrite of a much-loved internal app for our company, using a more modern tech stack: MVC5, Dapper, etc, all of which connect to an ORACLE database which, for reasons too convoluted to discuss here, we cannot refactor or remove. On our local machines and on our "Beta" server (which is where we have our QA department do their tests), the app works fine. It connects to the ORACLE database, runs queries, returns data, the works. But when we move the app to our staging server (which runs IIS 7 and connects to production databases to allow us to do sanity-checks before deployment) the app completely loses its mind and throws the above error.

Rajit, Dave, and I figured that the issue had to be either something to do with the app pool settings or some stupid coding mistake having to do with the ancient ORACLE DLLs. So we and the server admins started poring over the app's code and the IIS setup, scanning for some simple clue as to how to drive out the insanity that ancient ORACLE and modern IIS had inflicted upon us.

And we found nothing. At least, nothing obvious.

So, in desperation, we resorted to turning things off to see if that fixed the problem. That lead to us ultimately finding, after a day and half of searching, the very setting that had been causing all of the strife we'd been experiencing this project. This bug was so subtle, so innocuous, that none of us thought to look there. In fact, all of us presumed that it couldn't even be the issue. We prematurely concluded that the newest, shiniest thing (IIS 7) was less likely to be the cause of the problem than primitive, creaky ORACLE.

See where this is going?

After much frustration, we eventually located the cause of the bug; it was a setting in the IIS app pool called "Enable 32-bit Applications". In other words, the source of all of our grief for 36 hours was this thing:

A screenshot of an app pool's settings dialog in IIS, with

Here, let me make that a little more obvious.

A screenshot of an app pool's settings dialog in IIS, with

Perhaps I should explain.

ORACLE cares about the bitness of the machine it is running on, meaning whether said machine runs a 32-bit or 64-bit operating system. The 32-bit DLLs for ORACLE do not work on a 64-bit OS, and vice-versa. We figured that we were in the clear, though, since the production server's OS that was running our app is 64-bit and the app itself is using the 64-bit ORACLE DLLs.

(As a side note, ORACLE caring about bitness was the reason I wrote Using 64-bit IIS Express in Visual Studio, since everyone on my team needed to flip that switch in order to be able to debug the app locally.)

Did you catch what the description of that "Enable 32-bit Applications" setting actually said, though?

If set to true for an application pool on a 64-bit operating system, the worker process(es) serving the application pool will be in WOW64 (Windows on Windows64) mode. Processes in WOW64 mode are 32-bit processes that load only 32-bit applications.

Well, crap. Maybe if we'd slowed down a bit and RTFM we'd have saved ourselves some headache.

This was, obviously, reproducible in both the "Beta" and production environments. The reason that our app didn't immediately fail on our "Beta" server was that, on said server, the application was in its own app pool with "Enable 32-bit Applications" disabled. Apparently some idiot already ran into this issue months ago and forgot to tell us about it, seeing as we couldn't come up with another reason why the app pool was set up this way (and, y'know, the server logs told us such). Regardless, once we set up the application on production in its own app pool with the setting disabled, it worked like a charm.

So, yeah, I hate this stupid-ancient version of ORACLE we're forced to us (even though it wasn't the cause of the problem). It's old, it's slow, it requires us to care about whether the server it's running on has a 32-bit or 64-bit architecture. It's tacky and I hate it.

But mostly, I hate that there is someone out there, in our own company, who took the time to read the friggin manual, figure this nightmare out, and then proceed to not leave a note to inform us devs that this dumb little IIS setting would be the root cause of this infuriating bug. If you've solved a problem that others might encounter, and the solution was non-obvious, can't you leave a comment, a sign, a carrier pigeon, something to tell the people that come after you about it? Is it really that difficult to do?

That guy? Yeah, I hate that guy the most.

Of course, that guy is me. So say the server logs. Take that, me!

Using 64-bit IIS Express in Visual Studio

Our organization, like many other medium-to-large companies, uses a central ORACLE database for most of our company-specific data. By itself this is not an issue (according to management), but problems arise from two things:

  • We are almost exclusively a Microsoft shop.
  • The version of ORACLE we are using is very old (8+ years).

Our need to interface with ORACLE is what drove me and my team to develop a mapping system that allowed us to map ASP.NET DataTables to strongly-typed objects. Now, however, we're running into a different set of problems.

Like most other shops (I believe), our servers run a 64-bit architecture. What I didn't realize until I started working with ORACLE about a year ago is that ORACLE actually cares about the architecture of the machine it is running on; the x86 version of ORACLE's DLL will not run on an x64 machine and vice versa.

This is problem because Visual Studio is a 32-bit application. When you start debugging a web site or application in VS, it spins up a 32-bit instance of IIS Express to host the process in. But ORACLE's 64-bit DLL won't run in this environment, so running and debugging on our local machines was something of an annoyance.

Before Visual Studio 2013 there was no easy way to run an app as a 64-bit process, so what we were doing was running locally using the 32-bit ORACLE DLL and then at deployment replacing that DLL with the 64-bit equivalent. These extra steps, though, were often loss in the shuffle of deploying an app, so it was quite a relief to us when VS 2013 was able to spin up the 64-bit version of IIS Express so that we could not have to bother with switching the ORACLE DLLs around.

In VS 2013, here's how you enable targeting a 64-bit version of IIS Express. First, go to Tools -> Options, then in the resulting window expand Projects and Solutions to find Web Projects. Click on Web Projects to open this dialog:

The Web Projects dialog, showing the

See that check box? If that is checked, running projects locally will cause Visual Studio to spin up a 64-bit instance of IIS Express, thereby creating a 64-bit process that the ORACLE DLL can execute within.

It's just that easy! Let me know if you found any other tricks for dealing with ORACLE in ASP.NET applications in the comments.

Happy Coding!