Broad updates cause concurrency problems

AR performs broad updates - that is, when you save an AR record, every field in that record is overwritten.

This can cause concurrency problems.

Let’s say the user pulls up his user profile form - and at about the same time, an administrator pulls up a form that displays the same attributes plus some that are only accessible to administrators.

If the user submits his changes before the administrator does, the user’s changes are overwritten and lost - and the other way around.

Note that I’m not talking about milliseconds here - certainly those updates where a record is loaded, changed, and saved back directly can result in potential problems, but you would probably work around those specifically in an area of an application where data integrity is extremely important.

I’m talking about the interim between the time you pull up the form until you submit it. We could be talking anywhere from 1 to 20 minutes here, it all depends. So there’s actually a fair chance that this will happen.

Unfortunately, there is no simple way to work around that. My first thought was that AR should only update those attributes that have actually been changed. But in the example above, if the administrator submits his form last, on the server-side, that model will reload and attributes will be applied, and they will appear to have changed, simply because they no longer match what the user just committed to the database.

The only way I can think of that would work, is to put a hidden field in the form, containing checksums for each of the current attribute values. That way, when the form is submitted, you can compare the submitted checksum against the checksum of the submitted value, to see if the user actually changed the values on the form. If they weren’t changed, don’t apply the attribute values.

Maybe this is something to be implemented with an extension rather than a framework feature?

On the other hand, this could be generally useful to anyone who runs into these concurrency problems…

If you want 100% data consistency in this case you can use SELECT … LOCK IN SHARE MODE.

You can save fields you need by passing array of attributes for save method: http://www.yiiframework.com/doc/api/CActiveRecord#save-detail.

Not saving not changed values or merging transactions is not a desired behavior in many cases. Fine for an extension but, I think, it’s not for core Yii.

I agree, it’s probably not for core.

Could you give an example of when saving unchanged values would be required or necessary?

One problem here is IMO that detection of an attribute change would create overhead you have to carry around all the time - even thought in most cases you don’t need it.

mindplay

Same concurrency problem. Data entered by user A is OK, data entered by user B is OK but if we merge A and B it can be invalid. A shop manager can raise a price based on quantity while another manager can change quantity. It’s not obvious, what’s better. Wrong price, correct quantity or wrong quantity and correct price.

Only reasonable solution (???) would be to cache the original record and before saving to check if the original record data is unchanged in the database… if it’s unchanged then data is saved… if it’s not… then there should be some way of displaying the changes so that the user can decide if he wants to save the data he entered or to cancel the update because the data has changed while he was editing the data…

but this would be a big overhead in memory (duplicate data) and in time (reading the data for checking) so it should be used only in some applications like accounting

Would be nice if Yii would have some option that when enabled would keep the original record data (disabled by default)… so that we can access the original data by something like $model->attribute->original even if $model->attribute has some new value

@mdomba

AFAIK there’s already an extension which does exactly that.

I haven’t put this into work, but your solution could be something like this:

In your table add a field named ‘lastUpdate’.

Add a beforeSave() handler to your AR model:

  • use SQL to lock the record to be updated

  • check if the lastUpdate field of the AR model matches the lastUpdate field in the db

  • if they don’t match set an appropriate error message and return false

  • otherwise set the lastUpdate field in the AR model to the current datetime, save and return true.

As this creates some overhead to the save() you may want to implement a lightweight check to see if a record is changed.

You could do that in the __set() method by checking if the attribute to be set is in the database table. If it is, set a ‘isChanged’ flag.

Good point - I hadn’t thought of that.

Maybe there are too many variations or edge cases for these situations to be handled in a generic way…