| Subject | Re: [IBO] How to prevent update conflict in multiuser app? |
|---|---|
| Author | Raymond Kennington |
| Post date | 2002-11-16T09:27:48Z |
kj01135 wrote:
sent to the server) there might be a conflict with any one of the 100 records being
updated. Because you want these done as a batch or none at all, which is not the way I
would do it, it would be necessary to re-issue the entire request.
The updates to counts for each product are independent of each other and do not need to be
in a single transaction. It would be much better in this case to issue updates for each
one separately in a very short transaction for each one.
Suppose there is table with the counts of new products that have been received for the
shelves.
Suppose there is a SP that attempts to add all of those counts to the corresponding total
available in a single transaction for the lot.
If it fails due to any one item of any or the products being sold (and therefore generates
an exception) it is possible for your application to capture the exception and retry
automatically, meaning that it must re-post all the updates.
----
If you were to commit one row of the table for each call to a SP that processes a single
additional count, it might take longer for the manager if no conflicts were to have arisen
using the all-or-none method, but would reduce the chance of clashes and retries by the
manager.
Since you think there would be clashes, I would use the one-by-one approach and mark them
as posted during the update process as part of the transaction, or alternatively, delete
the update count after adding it to the total available as part of the transaction.
--
Raymond Kennington
Programming Solutions
W2W Team B
>When the manager presses the Commit button (or otherwise causes a Commit instruction to be
> Hi Raymond,
>
> Thanks for your quick response.
> Do you mean when the SP is about to commit and it detects the ITEM
sent to the server) there might be a conflict with any one of the 100 records being
updated. Because you want these done as a batch or none at all, which is not the way I
would do it, it would be necessary to re-issue the entire request.
> A's qty is different (after sold), then an exception will be raisedYes. That's what you wanted: all or none.
> and the whole update process need to rollback (ie rollback the
> 100 records)? If this SP is executed again after the rollback, then
> another records (ITEM B) might cause the conflict again.Precisely. That's why updating each of the records as a batch is not a great idea.
The updates to counts for each product are independent of each other and do not need to be
in a single transaction. It would be much better in this case to issue updates for each
one separately in a very short transaction for each one.
>This is what I meant:
> What do you mean that "auto-update the original count that was read
Suppose there is table with the counts of new products that have been received for the
shelves.
Suppose there is a SP that attempts to add all of those counts to the corresponding total
available in a single transaction for the lot.
If it fails due to any one item of any or the products being sold (and therefore generates
an exception) it is possible for your application to capture the exception and retry
automatically, meaning that it must re-post all the updates.
----
If you were to commit one row of the table for each call to a SP that processes a single
additional count, it might take longer for the manager if no conflicts were to have arisen
using the all-or-none method, but would reduce the chance of clashes and retries by the
manager.
Since you think there would be clashes, I would use the one-by-one approach and mark them
as posted during the update process as part of the transaction, or alternatively, delete
the update count after adding it to the total available as part of the transaction.
> and post the additional stock values using the SP again."?Raymond.
>
> Kenneth
--
Raymond Kennington
Programming Solutions
W2W Team B