Lost Update (Magic xpa 3.x)
If this new transaction mechanism solves the problem of concurrency, then we should all use and keep our transactions to a minimum. Wait a minute; there is a problem here. Fred is standing in front of the clerk who is on the telephone. At some stage, the clerk is going to get off the telephone and complete the transaction he started. Now, he completes the transaction but Wilma has already done so! In other words, Wilma has already updated the balance. The amounts that he started off with are not the current amounts! This common problem is sometimes referred to as a “lost update”.
What should be done here? Do we want the bank clerk to receive a message, saying that the data has already been updated? Or do we want the clerk’s transaction to complete?
How can we overcome the problem that we just raised, the issue of the “lost update”? Let us look at our problem again. While Fred is trying to draw money from the Savings account, Wilma has already updated the amount. The way that an update normally works is that before an update is performed, the original data is checked. What does this mean? Let us assume that the couple had $100 in the Savings account, of which they wanted to transfer only $50. After the transaction, they would have $50 in the balance. But let us confuse the issue slightly and say that Wilma realized that she only needed to withdraw $40.
Let us look at the underlying SQL commands of Fred and Wilma:
|
Wilma: UPDATE savings SET balance = 60 WHERE id=300
|
|
Fred: UPDATE savings SET balance = 50 WHERE id=300
|
If Wilma performs her transaction before Fred completes his; but Fred does complete the transaction; then the balance of the Savings account is $50. Wilma’s transaction is lost – “lost update”.
How can this be circumvented and controlled? Identify Modified Row to the rescue!
Let us try and understand why we reached this situation. The reason is that in the WHERE clause, we used WHERE id=300. This is the record’s position or unique identifier. The position depends on the RDBMS. Some RDBMSs such as Oracle work with a rowid, others such as MSSQL will use the shortest unique index such as the account number. But, by using id=300 in the WHERE clause, there is not sufficient information to know that the record has been updated. More information is required.
|
Identify Modified Row
|
|
This data source property appears in the Data Source Properties, the Main Source Properties, and the Link Properties. It has the following options:
-
Position
-
Position and selected fields
-
Position and updated fields
-
As Table (only in the Main Source and Link properties)
The default for this property is “Position and updated fields”.
Note: This property is only enabled for Deferred or Nested Deferred transaction tasks, or to the Within Active Trans transaction mode, which evaluates to the deferred transaction mode in runtime.
|
Let us look at how this property can help us or hinder us. In the example, we have already seen the use of the “Position” option of this property. The effect that this may have on our scenario is the “lost update” problem. This option enables the user to update the data even though someone else has already updated it without regards to the other update. You may be asking yourself in what situation you may want to do this. Once again, let us return to our happy couple Fred and Wilma. Suppose that they had been checking their bank account details and realized that the bank had the wrong zip code for their home address. Oops! They decide that while they are transferring the money from account to account, they will also ask the clerk to change the zip code. Now in our scenario in which both Fred and Wilma are performing the same transactions, it is not really important who enters the updated zip code, since the data remains the same.
Let us look at the second option of the Identify Modified Row, Position and Selected Fields. This option will add all the fields that were selected in the program to the resulting WHERE clause. Now what will that mean for Fred and Wilma? Well, let us assume that the program that is being used to display the balance and to enable the transfer from the Savings account only displays the following information to the clerk:
-
Account Number
-
Account Balance
It also shows a non-database field for the amount to transfer and another for the receiving account (see the figure below).
Now, the resulting WHERE clause would have both the Account Number and the Account Balance added to it.
Let us look at the underlying SQL commands of Fred and Wilma (remember that they had $100 in the Savings account before the transfer):
Wilma: UPDATE savings SET balance = 60 WHERE id=300 and balance = 100
Fred: UPDATE savings SET balance = 50 WHERE id=300 and balance = 100
What would happen here? Well, Fred’s transaction would begin but the clerk’s phone call is holding it up. Whilst he is on the phone, Wilma makes her transaction and decreases the balance to $60. Now when Fred tries to complete the transaction, the balance is no longer $100 and so it fails. Data integrity is upheld. This is a very comprehensive way of checking for an update. In the example that we used, it seems to have been the ideal solution; but is it? Well let us take the same program from before and add the account details. These details are used to show who the account belongs to. So know we have the following information being displayed:
-
Account Number
-
Name
-
Last Name
-
Address
-
City
-
Zip Code
-
Telephone Number
-
Account Balance
Now let us look at Wilma’s SQL command again:
UPDATE savings SET balance = 60 WHERE id=300 AND first_name = “Fred and Wilma” AND surname = “Smith” AND address = “10 Upping Avenue” AND city = “Londera” AND zip = 65232 AND telnum = “555-2055” AND balance = 100
Quite a mouthful! If another user updates any of the above data, the transaction will fail. This is very useful but is it useful for all cases? Let us look at our scenario again. Remember that our happy couple wanted to change their zip code. It appears as 65232, but it should be 65233! Now, while Wilma was doing the transfer, Fred was updating the zip code to 65233. What will happen if we use the Position and Selected Fields option? The transaction would fail because “zip = 65232” is no longer true.
What do we do here? Welcome to the third option, Position and Updated Fields. This option will only add the updated fields to the WHERE clause. So in our case, let us look at the underlying SQL command:
|
UPDATE savings SET balance = 60 WHERE id=300 and balance = 100
|
It is the same as the program with only two fields in it. The advantage here is that Fred can update the zip code without interfering with Wilma’s transaction. Where would this fail? This would fail if both members of the family tried to update the same field at the same time.
