locked
Keep the Transaction as short as possible + Sql Serevr RRS feed

  • Question

  • Hello All,

    I read everywhere that Keep the Transaction as short as possible.

    Now suppose there be a stored procedure. It does the following:

    1) Task 1

    2) Task 2

    3) Task 3

    And finally it returns the result.

    To better optimize the proc, should I create three different procedures to avoid deadlocks? 

    If I call these procs from C# code, then obviously the procs should be called from the code behind?

    Thanks in advance. 

    Tuesday, July 19, 2011 12:11 PM

Answers

  • Generally speaking, if all three tasks need to complete before the correct result can be returned, splitting the tasks into separate stored procedures will not reduce the risk of deadlocks. It will more than likely increase the risk as you'd still need to execute all stored procedures in the context of a transaction to ensure that all tasks complete successfully prior to returning the result.

    Executing multiple stored procedures within a transaction is generally more expensive that executing all the required statements within one procedure.

    As per Uri's reply, you need to provide a little more detail.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Tuesday, July 19, 2011 12:30 PM
  • You are correct that it's a good practice to keep transactions short. This will reduce the liklihood of deadlocks and improve concurrency. Also consider than performance will also affect deadlock liklihood and concurrency. Query and index tuning can help ensure only the needed data are touched.

    If you execute all 3 tasks within the same transaction, the deadlock liklihood will be the same regardless of whether the tasks are in a single proc or 3 different procs.  Do you begin transactions in the C# code or in T-SQL?  Is each task in a separate transaction?

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, July 19, 2011 12:29 PM
    Answerer

All replies

  • You have provided too little info about the problem. How does you access the tables what order? What indexes do you have defined on those tables? How much data does it return? What version of SQL Server you are using?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 19, 2011 12:17 PM
  • You are correct that it's a good practice to keep transactions short. This will reduce the liklihood of deadlocks and improve concurrency. Also consider than performance will also affect deadlock liklihood and concurrency. Query and index tuning can help ensure only the needed data are touched.

    If you execute all 3 tasks within the same transaction, the deadlock liklihood will be the same regardless of whether the tasks are in a single proc or 3 different procs.  Do you begin transactions in the C# code or in T-SQL?  Is each task in a separate transaction?

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, July 19, 2011 12:29 PM
    Answerer
  • Generally speaking, if all three tasks need to complete before the correct result can be returned, splitting the tasks into separate stored procedures will not reduce the risk of deadlocks. It will more than likely increase the risk as you'd still need to execute all stored procedures in the context of a transaction to ensure that all tasks complete successfully prior to returning the result.

    Executing multiple stored procedures within a transaction is generally more expensive that executing all the required statements within one procedure.

    As per Uri's reply, you need to provide a little more detail.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Tuesday, July 19, 2011 12:30 PM
  • >I read everywhere that Keep the Transaction as short as possible.


    Don't believe everything you read. :)

    Keep that TRANSACTION as long as it makes sense to. Just try not to keep it longer.

    I like the idea of one SP per task. Sometimes the SP will CALL other SPs.

    Whether to CALL them from C# or from an SP depends on where the "API" layer is. My preference is the database so the network plays no part between the begin and end of what i want it to do.

    Tuesday, July 19, 2011 3:19 PM
    Answerer