Updating Numeric Data (Magic xpa 3.x)
Let us look at our scenario to show the problem and the solution. Remember our scenario in which Wilma wanted to update the balance? We updated the balance with a specific value i.e. 60 ($100 minus the $40 withdrawn by Wilma). This is referred to as an absolute value. The balance field was updated by a constant value.
Remember the resulting SQL command:
|
UPDATE savings SET balance = 60 WHERE id=300
|
The balance was updated with 60. Now let us look at this differently. We seem to have forgotten about the other part of this transaction – the Checking account. Let us assume that there was also $100 in this account before Wilma started the transfer. So now let us look at both sides of the transfer:
|
UPDATE savings SET balance = 60 WHERE id=300
UPDATE checking SET balance = 140 WHERE id=600
|
The balance of the Checking account has been increased with a constant value of $40. This is the amount that was transferred from the Savings account. In our little story we seem to have neglected the greengrocer who wants to cash Wilma’s check for $30. So here the SQL command would be:
|
UPDATE checking SET balance = 70 WHERE id=600
|
But wait a minute, both of these transactions running at the same time will cause a problem – “Lost Update”. What should we do in order to maximize concurrency?
Let us welcome a new property: Update Style
|
Numeric Update Style
|
|
This property is only valid for a Numeric field and is defined in the data source’s column or in the Column Properties sheet of the data source. It has the following options:
The default of this property is Absolute.
|
How can this property assist our dilemma? When the Update Style property is defined as Differential, then instead of an absolute value being set, i.e. a constant value, the difference is sent instead. This may be better understood with our example. Let us take Wilma’s update and the greengrocer’s update and look at them again.
|
Wilma: UPDATE checking SET balance = 140 WHERE id=600
Grocer: UPDATE checking SET balance = 70 WHERE id=600
|
Now let us send these transactions as a Differential update style:
|
Wilma: UPDATE checking SET balance = balance + 40 WHERE id=600
Grocer: UPDATE checking SET balance = balance - 30 WHERE id=600
|
So what is the end result here? It is not important in this case which operation is performed first, the end result will be that there is $110 in the Checking account! This is the correct amount that should be in the database.
