Does a pending remote server connection block DROP CONNECTION?

[Breck_Carter]

Here is the scenario: A SQL Anywhere 16.0.0.1915 event makes a connection to a remote server. An EXECUTE IMMEDIATE FORWARD TO statement establishes the actual remote server connection.

Another event detects when the first event has not received control back from the FORWARD TO statement, and this second event runs an EXECUTE IMMEDIATE DROP CONNECTION on the first event's local connection number to stop the waiting.

The DROP CONNECTION statement runs quickly, but it seems the other event's local connection is not actually dropped until after the FORWARD TO returns control. Repeated DROP CONNECTION statements for the same connection number also run quickly... they don't fail, but they don't have any better luck than the first.

In some tests, multiple "the check is in the mail" messages appear in the database console ...

I. 08/24 11:51:32. User "DBA" dropped event connection 1000000033 ("attempt_connection")
I. 08/24 11:51:33. User "DBA" dropped event connection 1000000033 ("attempt_connection")
I. 08/24 11:51:35. User "DBA" dropped event connection 1000000033 ("attempt_connection")

... for those of you who haven't had your morning coffee yet, it is logically impossible to drop the same connection number more than once 🙂

Is that expected behavior? ...I suspect it is, just wanted to be sure.

What else (if anything) blocks DROP CONNECTION?

Some insight into the inner workings of DROP CONNECTION would be appreciated... there's more to it than meets the eye 🙂

[VolkerBarth]

Another event detects when the first event has not received control back from the FORWARD TO statement, ...

Can you tell how the second event does know about the state of the first one - does it interpret some particular values from sa_connInfo() or the like?

[VolkerBarth]

Does it behave differently when you drop the remote connection first (via v16's new DROP REMOTE CONNECTION TO ... CLOSE <connection_id> statement)?

(Or via ALTER SERVER ... CONNECTION CLOSE <connection_id> for older versions?)

[Former Member]

As you surmise correctly, the dropping of a connection will require all remote connections to be dropped as well. And there could be multiple. Heavy use of multiple remote servers and external envirnements could cause some overhead in the clean up.

That is in addition to any local context information related to that connection. (same also goes for each of the remote connections local to their respective db and servers). So global temp table entries, non-transactional objects, local temp tables, temporary procedures, ... etcetc ... might add up to some overhead independant of the remote disconnects.

Is there any obvious contributing factors that remote disconnects might take longer in your enviroment?

[thomas_duemesnil]

Have same symptom from time to time with a remote connection to as Oracle DB. I have tried to drop the event connection, drop remote connection no success. Did somebody find a solution without restarting the server ?