Home Forums SQL Server 2014 Development - SQL Server 2014 Stored proc Timing out halfway through - rolling back without transaction RE: Stored proc Timing out halfway through - rolling back without transaction

  • July 7, 2015 at 1:43 pm

    #1810800

    Casper101 (7/7/2015)

    Hi,

    We have a high volume database with 1000's of users and 1000's of procs. Our application enforces a 20 second timeout on all connections.

    We can't adjust the 20 seconds - this is a business rule.

    It sometimes happens that a proc does not complete within 20 seconds and then times out halfway though. This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.

    We can't put the code in a TRANSACTION in order to roll back when a time out occurs, because this causes exclusive locks on the tables.

    So I guess my question is:

    Is it possible to undo/rollback all the code in a proc when a timeout occurs - without using a TRANSACTION?

    And if a TRANSACTION is the only way - how do I avoid the exclusive lock and blocks?

    Thanks,

    I don't think there's a way to get a rollback to occur without a transaction, but I'm not the expert on that. However, it seems to me that the more important problem is being ignored - that of overall system capacity. I'd first be looking at the "heavy hitters" among the procs that time out, and seeing what might be causing the run-time to be so high. If the queries are as good as they can be, then overall system capacity may be the issue. What kind of hardware is the server running on? Are the disks local or SAN-based? If there's a SAN, what do the I/O statistics look like? What kind of waits are you seeing? Is there blocking going on? Sometimes, a common thread appears after answering these kinds of questions. The solution could be as simple as a decent RAM upgrade, or it might be more complex, and require a combination of query tuning, additional RAM, table partitioning, or other performance improvement techniques. Maybe the server is just plain "out of gas", so to speak, and it's time for a bigger box. As your question only addresses a small part of the problem, without providing any other context, the answer is "it depends". Care to elaborate?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)