Concurrency erro when deleteing or updating multiple primary key fields
The classic example of a lost update involves reading data into a user form, for subsequent modification.For example, an employee loads into a user form, data pertaining to a particular customer, in order to update their address.However, as usual in this book all the details that are not relevant to this simple example are omitted. table, reporting a problem with the TPS report This is a very important ticket, so two developers – let’s call them Arne and Brian – immediately start troubleshooting.
He runs the report for Ohio and gets the same problem so, from his onscreen view of the ticket, which he opened before Arne made his update, Brian updates the Listing 4: The SQL that was issued by Brian’s bug tracking form The changes saved by Arne were completely lost.
Clearly, our bug tracking system is susceptible to lost updates, and so has a big problem.
Meanwhile, an automated process updates the same row, assigning to the customer a new status, such as “loyal customer”.
If the employee submits the address update and the application updates the whole row, rather than the column that was changed, then the customer could be rest back to their old status, and the effect of the automated process will be lost.
Aside from having a more intelligent logic attached to the form, so only the column modified is updated in the database (we’ll discuss this in more detail shortly), there are essentially two concurrency control approaches that we can use in order to avoid such ‘lost updates’: Optimistic approach: even though we have selected a row, other sessions can modify it, but we optimistically assume that this will not happen.
When the selected row is updated, we have logic in place that will test to see if the row has been modified by someone else, since it was queried.
Lost modifications can occur when multiple connections modify the same row of data.
For example, one connection (A) reads a row of data, with the intent to update that row, based on certain criteria.
In cases where only automated processes involved, where we have quick transactions attempting to (almost) simultaneously change the same rows of data, we are also in risk of lost updates, and are likely to adopt a pessimistic approach to concurrency control, in order to avoid them.