SQL Update error using Transaction

Hi reaching out to you guys for a help on this case since I couldn't narrow down and identify the root cause.

This is the scenario :

I am using a table to maintain sequence numbers that are being used as a primary key. Contains 3 columns wiz.

1.cur_value

2.incr_by_value

3.next_value

Here's my update statement (transaction managed):

       update _seq set
        cur_value = next_value,
        next_value = next_value + incr_by_value,
        @id = cur_value

@id(output)

I ended up in a situation where the current value and the next value are the same thus causing primary key violation. In a normal situation it does work fine. What are possible scenarios where this update statement could fail.

Any thoughts?

via Chebli Mohamed