0

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Álvaro García
  • 18,114
  • 30
  • 102
  • 193
  • It seems like you want to perform an operation on the group, not on the products of the group. – hyankov Mar 02 '17 at 18:28
  • But I don't have a group table, I have products that has the same code. So I want to update the column Products.GroupCode, so I want to perform an operation in the procuts: update products set GroupCode = 111 where GroupCode = 000; – Álvaro García Mar 02 '17 at 18:31
  • Please show some sample data. The way you describe it (*a table that has a long column that is a GroupCode* -- why "long"?) doesn't make clear which tables are involved and how they are related. Also it's not clear if, and when, new groups can be started. If a user always updates all products in an *existing* group, optimistic concurrency will do its job aright. – Gert Arnold Mar 02 '17 at 19:58

1 Answers1

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.

hyankov
  • 4,049
  • 1
  • 29
  • 46
  • But in this case, user B gets Record1 with reference ABC, user B get record1 as reference, this product still has ABC as reference. User A changes to XYZ and save the changes, user B set group of new product to ABC because is the group that record1 had when user B get it as reference. The exptected result would be that the new product had the XYZ code. – Álvaro García Mar 02 '17 at 18:53
  • No. The idea is that just before you insert the new record, you refresh (get from the db again) the CURRENT category of the referential product. – hyankov Mar 02 '17 at 18:56
  • You may want something like this - https://msdn.microsoft.com/en-us/library/jj592904(v=vs.113).aspx (see 'Custom resolution of optimistic concurrency exceptions') – hyankov Mar 02 '17 at 19:00
  • But if before to inser the new record i refresh, between this refresh and the insert of the new record, the user A changes the reference product, how the user B doesn't update the reference product, EF will not check the timestamp because the entity is not changed. So there are always a gap between the get the current category and the insert of the new record. – Álvaro García Mar 02 '17 at 19:01
  • Thanks for the example. But this only would work if user B modify the Record1 to make EF to check the timestamp column, but how user B just takes the record1 as reference, it will not check if it is changed by user A, so the result could not be the expected one. – Álvaro García Mar 02 '17 at 19:07