Record am storing looks like this [CATEGORY, TYPE, VALUE, ID]… Primary key is on first three fields [CATEGORY, TYPE, VALUE]… I also have an index on ID field. I have a business use case where an ID can have only one VALUE for some TPYE’s. For ex, lets say an ID can have only one VALUE for type T1 then this is invalid [C1,T1,V1,ID1] , [C1,T1,V2,ID1] since ID1 will have two values [V1,V2] for type T1.
How can i ensure when concurrent writes happen only one of them gets stored and other is rejected. What i am doing today is, after inserting the row as part of transaction TX1, in a new transaction TX2 i do a read with causal read risky option to see if something else got inserted. If so i rollback the current transaction TX1 else i commit it. Will this work? If two instances are performing this concurrently is it possible for each tx to not know about the other tx’s commit and end up with both the records in DB?