I want to create a T-SQL stored procedure that gets passed the primary key of a parent record plus some field values for a new child record. The procedure needs to accomplish the following:

1. Increment the value of a counter field in the parent record.
2. Add a new record to the child table.
3. Store both the parent ID *and* the updated counter value from the parent record into the child.
4. Store the identical timestamp in both parent and child.
5. ROLLBACK if all of this cannot be done and generate an error.

The code needs to be scalable and anticipate the remote possibility of two users trying to add a child to the same parent at the same time. Is the best way to first SELECT the parent using UPDLOCK, then issue an UPDATE, then INSERT into the child?:

BEGIN TRAN
SELECT @oldCounter = Counter FROM Parent (UPDLOCK)
WHERE Parent_ID = @ParentID
UPDATE Parent SET Counter = (@oldCounter + 1)
WHERE Parent_ID = @ParentID
INSERT Child ( Parent_ID, Counter )
VALUES ( @ParentID, @oldCounter + 1)
END TRAN

Or am I better off using a CURSOR for the parent? Is the locking scheme of either of these better than the other for performance?

I was also contemplating an approach of using optimistic locking by removing the UPDLOCK hint and including the previous Counter value as an additional WHERE clause parameter (to make sure nobody else incremented the counter since I read it). This seems like it would cause less contention, but I'm not sure how I would recover from a failure -- possibly try n times in a loop and then return an error.

If anyone knows any good T-SQL references on these types of strategies, I would love to get ahold of one. I've read through several books, but they don't seem to get very deep into topics like this.

TIA,

-- Randy