SA 12.0.1.3046, Delphi XE, AnyDAC DB layer

Troubleshooting an issue where I've created a simple locking scheme that is usually handled just fine by the app itself, however I did create an event (Disconnect) that should delete the lock record based on connection ID upon disconnect (the connection ID is part of the lock record, so it is a simple DELETE FROM LOCKTABLE WHERE CONNECTIONID= ? fired from the event upon disconnect).

Somehow we are getting into a state where our app goes down (not quite sure on this one yet) and those connections stay open, and the events never fire. Even a reboot of the workstation that made the original connection has no effect (thought there might be some process holding them open even though I couldn't find any even with Process Explorer (sysutils)).

So, my question is, generally speaking, should I always expect connections to be dropped (no pooling, btw) if the app that made the connection is no longer in memory (regardless of how that came to be)? Can someone explain to me how Sybase knows that it occurred and resets the connections (which is the only behavior I can reproduce...my events fire every time)? Can the server settings -tl and -ti set to zero have anything to do with it?

I do see similar postings referring to undroppable connections, but I'm not quite ready to blame the server until I have a clearer view of what I should expect from an unexpected crash.

asked 22 Aug '14, 17:03

Dan%20Hacker's gravatar image

Dan Hacker
1765513
accept rate: 0%


Having -tl 0 and -ti 0 may certainly be the cause here.

If the application crashes on a Windows machine and that machine still has connectivity with the server, the Windows OS will trigger a WSAECONNRESET and SQL Anywhere will see that as a disconnect.

However, if another OS is in use, or if the machine with the crashing application's host does not have connectivity with the server (e.g. wireless connection dropped, power outage, etc.), the socket will not be cleaned up on the server.

If the socket isn't cleaned up on the server (which is very possible as -tl 0 -ti 0 instructs the server to leave it alone despite liveness and activity absence), then the disconnect event will not fire.

Hope this helps,
Tyson

permanent link

answered 22 Aug '14, 18:23

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

I assumed there would be a difference between an idle connection and a severed connection. If the server settings turn out to be the problem, I'll have to eat my shoe.

(22 Aug '14, 20:28) Dan Hacker
Replies hidden
5

> I assumed there would be a difference between an idle connection and a severed connection

I think you assumed correctly (the "I think" waffle is a symptom of my continued struggle with the Impostor Syndrome :)

The -ti 0 option is OK, and is frequently used to stop the server from dropping idle connection pool connections in the quiet times (for example).

However, the -tl 0 option is almost never OK... it stops the server from checking to see if those idle connections are actually there.

The server doesn't "see crashes"... all it can do is "notice dead air", analagous to a cellphone user determining that a connection has dropped by asking "hello? are you still there?"... liveness checking.

To put it bluntly, everyone who rides a motorcycle will eventually drop it, and everyone who uses -tl 0 will eventually regret it. Combining it with -ti 0 is like leaving the peg down while riding your motorcycle... it just makes things worse.

To put it even more bluntly (for other readers, not you!) default settings for server options should NEVER EVER BE CHANGED without clearly understanding how they work, what they are for, and why you actually need to change them. SQL Anywhere is not Oracle! SQL Anywhere defaults have been carefully chosen.


5 upvotes on a comment... does that mean I'm cured? ...no, that can't be true! :)

(23 Aug '14, 07:04) Breck Carter
Comment Text Removed
2

I have reproduced the issue by pulling the network plug on the client while having -tl set to zero...non-zero settings work just fine.

The only reason we set -tl (and -ti) to zero (over ten years ago, believe it or not) was to allow our users to always have our application running, even through the night and still be waiting for them in the morning. The hands-off approach was mainly due to early use of the Borland Database Engine, but now that we have AnyDAC as our DB layer we should be able to handle these disconnects in a much better way and present the users with a better disconnect experience.

(25 Aug '14, 12:13) Dan Hacker
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×132
×46
×35
×9

question asked: 22 Aug '14, 17:03

question was seen: 680 times

last updated: 25 Aug '14, 16:58