SQL Server Performance

Avoid long transactions

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Lattice, Jun 19, 2009.

  1. Lattice New Member

    Actually I'm trying to fix a process that modifies a lot of records (according to many business requeriments) in two of the principal tables of the whole system. Those two tables most be modified in secuence order (imagine a tables Parent and a Child)
    This process execute the updates tasks in just one begin transaction and commit transaction (creating a long transaction)
    The problem is that it causes that other process have timeout errors an even deadlocks errors.
    I´m thinking in modify the procees so the update task will be performed over a Parent -Children at a time. EveryTime that I modified the data set I'll be opening and commiting a transaction.
    But exists an extra requeriment.
    All the updates most be finished or the process most be aborted.
    is it recommendable that before any update I take a snapshot of the data and just in case than I need to undo the changes I can use it?
    Thanks for your help.

  2. satya Moderator

    Welcome to the forums.
    What kind of isolation level settings you have defined?
    As you say this is the long transaction causing the issue, why not you fix it by performing the transaction in smaller chunks. Or is it not possible to perform such an action?
  3. Lattice New Member

    Yes, I'll break this process in small ones. For each little process will be a transaccion to prevent data inconsistency. But my requriment says that if just one of those little process fails, all the previos process must be aborted.
    So. is the best choice than before the process begin i take a copy of the data, and in the case that some of the little process fails I could reset the previos data (all data commited in previos subprocess)?
    We're using the "Read Commited" isolation level.
    Thank for your help.

Share This Page