I've been using Transaction processing (BEGIN TRANSACTION, END TRANSACTION, ROLLBACK) for a while now, but still not sure I fully understand when, why, and how to use it properly. Specifically, why would I use nested transactions? Also, is there a limit to the number of updates within a transaction.

For example, suppose I have a batch of 5000 items to be processed. Each of the items is processed and then marked as "done" by a logical flag. At the end of my processing, I also want to update the batch header record with some information. However, if one item could not be processed, I want to rollback the entire thing, including the update to the batch header. Is it okay to include all 5000 item updates plus the batch header update in a single transaction? BTW, "5000" was an arbitrary number - could by more, could be less.

Thanks,
Alan