How Can I Determine Magic xpa Behavior When Several Users Are Modifying the Same Row? (Magic xpa 2.x)
First of all, Magic xpa will behave differently regarding modified rows depending on the kind of transaction handling you are doing.
Physical transactions are handled by the database engine, while Deferred transactions are handled first by Magic xpa and then committed to the database in a separate step.
If you are using Physical Transactions, then how the row is identified depends on the underlying DBMS settings.
First, Magic xpa has to identify that, in fact, the row is being modified. The way this is handled depends on the Identify Modified Row property in the Data Source. There are three different settings, and they have three very different results.
Let’s take a look at what happens if two users, User A and User B, are updating the same row in an SQL table. Both fetch the same row for update. Both then update the row, but User A writes the record before User B does.
-
Position means that Magic xpa will only look at the unique ID of the row that is modified. So User A’s record is written, then User B’s record overwrites the changes. Only User B’s changes are saved, and no error is raised.
-
Position and Updated Fields means that Magic xpa will look at the unique ID of the row, and also what field was updated. So if User A and User B actually updated different fields, then both sets of changes would be saved and there would be no error. But if they both updated the same field, then an error would be raised for User B and his changes would not be saved.
-
Position and Selected Fields means that Magic xpa will look at the Unique ID, and also all the fields that were selected in the tasks involved, whether or not those fields were updated. So if User A and User B had the same fields selected in their tasks, User B would get an error and his changes wouldn’t be saved.
-
As Table means that the Identify modified row property will be inherited from the Data source definition.
The Effect of Update Style
You can also use the Update Style property to minimize the problems of users updating the same numeric data.
Suppose you have two shopping cart programs running. Program A reads that there are 94 items in stock: so does Program B. Then Program A sells 2 items, and writes 92 to the field. Program B sells 5 items, and writes 89 to the field. The field is now 89, which is incorrect.
However, if the column was defined in the Data Source repository with Update Style->Differential, then it will be updated differently.
Here you can see that the programs work just as they did before. But instead of moving 92 or 89 into the field, the field is decremented by the difference between the value read and the value to write. So instead of writing “92”, Program A causes the field to be decremented by 2. And program B, instead of writing “89”, subtracts 5. There was no change to the coding of Program A or Program B; the change was to the Data source column property.
Difference between Differential Update and Incremental Update
Note that Differential Update is quite different from Incremental Update. Whereas Differential Update is a property of a Data source column, Incremental Update is a property of the Update Operation. When Incremental Update is used in a task, the actual update operation is coded differently. Normally you would have to code explicit logic to handle adding, modifying, or deleting totals when working with subrecords, similar to that shown below.
However, with Incremental update, this is all handled automatically in Record Suffix. The programmer just indicates the amount that needs to be added or deleted:
In this example, when a record is created, Item_Qty will be added to Total Items. When a record is deleted, Item_Qty will be deleted from Total Items. But if Item_Qty is changed, Total Items will be changed by the amount that Item_Qty was changed. Incremental update is a sort of “smart total” for summing child records.
