SQL reconnect procedures

[Bob Sneidar]

I was getting disconnected from On-Rev within an hour of inactivity, and all my database calls failed when the connection was closed by the host. Also, certain network outages or problems can cause managed switches to reset, notably excessive broadcasts, and when this happens, the SQL connection is reset. Same result. I have experienced this first hand in our accounting application when someone had installed some mesh wireless in bridge mode, and we found out later that this cause a low level packet storm that was wreaking havoc with our managed switches. 

I'm not sure how you could rollback a transaction if the connection had been reset and the server did it automatically. I guess what I am asking is how do SQL servers deal with connection failures in the middle of a transaction. Does it rollback or commit? If commit, that would be bad if the transaction were not complete. If rollback, than attempting to restore the connection and continue with the transaction could be equally bad. 

Bob


On Oct 9, 2012, at 12:44 PM, stephen barncard wrote:

> well "COMMIT/ROLLBACK" should handle the possibility of data loss.
> 
> As far as "connections" I've found that there is little time difference
> between being 'always connected' and making a connection open and close per
> transaction, unless one is hitting it repeatedly for a single result (as I
> had to to for a certain database system a few years ago). A greater time
> lag is introduced when getting the returned data than the time required to
> connect. This is assuming that the DNS is cached; the first fetch will take
> longer.
> 
> On Tue, Oct 9, 2012 at 11:23 AM, Bob Sneidar <bobs at twft.com> wrote:
> 
>> I've been pondering what the ramifications to sql session disconnects are.
>> I have seen in other "professionally developed" applications, like our
>> accounting software used here, that if the user gets disconnected for
>> anything longer than a few seconds, the software completely bails out
>> through a series of errors that I have to abort to get the app to quit. Not
>> very graceful. I want to make my software more robust.
>> 
>> So I am wondering what happens when there is a transaction in effect, and
>> there is an unexpected disconnect. Will reconnecting restore the
>> transaction state or is it flushed after the sql timeout? If I can
>> reconnect and the transaction is still in effect, well and good, but if I
>> proceed as though the transaction is still in effect and it is not, bad
>> things could conceivably happen.
>> 
>> Is completely bailing out the best approach after all?
>> 
>> Bob
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> 
> 
> 
> -- 
> 
> 
> 
> Stephen Barncard
> San Francisco Ca. USA
> 
> more about sqb  <http://www.google.com/profiles/sbarncar>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode