is it possible with optimistic concurrency ensure this case?

Question

I have a table that has a long column that is a GroupCode. I can have groups of products, so to get all the product of a group I just get all the products which GroupCode is the same.

I can change a product from one group to another, and if I change a product from a group, I want that all the products of the group change to the new group.

If I use optimistic concurrency, it could happen this:

One user wants to change a product from a group, so he gets all the products with the same groupCode. Set the new new groupCode to all this products.

A second user add a new product to the group. But the first user doesn't have this product because he got all the products before the second user add the new product.

So at the end, a new product has a wrong GroupCode, because the code is not correct because all the products of the group was change to the new group. So I would have a group with only one product, and it wouldn't be correct.

With pessimistic concurrency, the first use get all the products of the group, block all the products.

The second user try to add a new product to the group, to do that, first try to get one of the products of the group as reference product, but how it is blocked by the first user, the second user has to wait.

The first user changes all the products to the new group and unblock all the products.

The second user get the reference product, that has the new groupCode, so the new product is added to the correct group.

In summary, I want that when I change a product from one group to another, I want to change all the products of the group, and avoid that a new product belongs to the old group.

Is it possible to solve this case with optimistic concurrency? Or I have to use pessimistic concurrency?

Answer 1

I honestly don't see the issue here. If you want to implement it as OCC, you should just follow the OCC phases.

1. User A gets all records which belong to group ABC
2. User B gets a reference to Record1, which belongs to ABC at the moment
3. User A moves Record1 to group XYZ
4. User B wants to add a new record to the group to which Record1 belongs. So just before inserting the record, get the group of Record, which is now XYZ

This is assuming that you go with the 'referential record' approach. If your screen (or whatever) just lists the currently available groups, and meanwhile one of those groups becomes empty (because you have moved all records to another group), there is no way of telling if that's a concurrency issue or it is working as expected. In such case, you should normalize your database and split the categories into a separate table, so that at least the user gets an error that the group no longer exists.