ThatJeffSmith

Oracle SQL Developer and the Dreaded Connection Busy Message

So you’re minding your own business, doing your work, and you click in your favorite Oracle Database IDE. Do you get what you asked for? No! Instead, you are rewarded for your troubles with this puzzling and frustrating message.

What the heck?!?

What the heck?!?

Abort? Try again?

Busy?

The Long Answer

When you connect to a database in SQL Developer you are actually making two connections. We use one connection to run background queries in the the worksheet. This makes the code assist features work.

The other connection is used to do EVERYTHING else. The only exception is when you open an UNSHARED worksheet. It’s called ‘unshared’ because we open another dedicated connection just for that worksheet.

So, if you see this message in SQL Developer, it means your request has timed out while waiting for the main connection to become available.

For example: when you ask to see a table’s data in a table editor, we first ask to lock the JDBC connection so we can run our ‘SELECT * FROM…’ query. If the JDBC comes back and says, sorry we’re busy, we try again. We give up after a certain amount of time and give you the error message.

At that point you get to:

  • Try again – ask for the lock to run the query again
  • Abort – give up

Now, I realize this explanation has only given you more questions to ask, so let’s go through those now.

But I’m not doing anything, how could the connection be busy
What’s probably happening is the columns page query is still technically executing when you go to click on the ‘Data’ page in a view or table editor. Or, something you’re doing somewhere else in the tool is still using the main connection.

Still don’t think you’re doing anything? Do you have any reports (or Monitor Sessions) on auto-refresh? Or maybe you’ve gotten cute and installed/created a ‘keep alive’ extension to thwart connection-idle disconnects? Oh yeah…

In earlier versions of SQL Developer you might run into this error more frequently because of poor session management – that’s on us. But in version 4, you should VERY rarely see this message.

In fact I’ve been spending the entire morning trying to get this message to pop-up and I failed.

Even if I click ‘Abort’ the data comes back anyway?!?
The dance of the ‘are you busy’ and the ‘yes we are’ is colliding with the timeout perfectly just so that the dialog is triggered as we’re able to run the query for you. If you see this frequently, it means the delay setting is just a hair-low for you.

How do I tell SQL Developer to give it another second or so to try before giving up?

-Dsqldev.obtainlockwaittime=[X] where x = seconds

The default is 1 second. If you add this line to your sqldeveloper.conf file in the bin directory, you can tell SQL Developer to give your connection another second or three before it should ‘give up.’

Can’t SQL Developer just ‘be smarter?’
Yes. We’re talking now about having connection speed measured and having the delay period dynamically set on a connection when we can tell that response time is going to ‘suck.’

The Short Answer

Dude, this is a blog. I don’t do short answers here. That’s what Twitter is for :)

It's self-documenting ;)

It’s self-documenting ;)