Using ROWLOCK in the UPDATE statement won't work. You have to set the isolation level of the entire transaction to REPEATABLE READ or SERIALIZABLE to prevent a lost update.
FYI: There are other (and better imo) methods to perform sequence numbering in SQL Server. What sorts of clients will be using the data?
>Thanks you Keith, but,
>Is it'n necessary to use the clause
UPDATE DocNumber WITH (ROWLOCK)
?
>
>
>
>
>>
>>declare @Next int
>>
>>begin transaction
>> select @Next = invoice + 1 from DocsNumbers
>> update DocsNumbers
>> set invoice = @Next
>>end transaction
>>
>>
>>For more info about SQL locking, see "SET TRANSACTION ISOLATION LEVEL" in the BOL.