Hi all!

I would have a question regarding serialized updates. We have a table as follows:

CREATE TABLE MyTable ( ID INT NOT NULL, Data INT NOT NULL )
INSERT INTO MyTable VALUES ( 1, 1 )

There are two types of transactions for the MyTable table. Writer transactions and reader transactions. The writer transactions are very short and they only update values in the table as follows:

UPDATE MyTable
SET Data = Data + 1
WHERE ID = 1
RETURNING Data;

I.e. each writer increments a value by one and determines the incremented value.

The reader transactions read values from the MyTable table. They use optimistic concurrency by using the REPEATABLEREAD isolation transaction.

Application requirements:

R1) Writers cannot block readers (and readers cannot block readers)
R2) Each writer must update and get a unique value

How to fulfil both of these requirements? By definition, readers utilizing row versioning are not blocked by other readers and writers. So, I think R1 is already fulfilled. What about R2? Can we issue updates in a way that in case of N concurrent writers, each updates and gets a unique value, and the value in the table is incremented by N? Does my update already guarantee this if run under READCOMMITTED? And if it doesn't, how to change it and what isolation level to use?

Thanks in advance!

Timo Partanen