How to set transactions timeout? RRS feed

  • Question

  • Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

    The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued.  If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

    Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked.  This really backs things up.

    I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout.  I.E. this just makes those that are being blocked timeout faster.  But it is the blockING process that I want rolledback sooner.  It doesn't get selected as the deadlock victim because it has all the resources it needs.  It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity.  The command timeout doesn't seem to have an affect, because the command itself completes.

    I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable.  I then used activity monitor on the server to view the locks being held.  I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

    In the production environment, users will be losing connectivity very often.  I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app.  Right now that is not an option.  I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

    Thanks in advance.
    Thursday, May 31, 2007 1:01 AM


All replies