Any one seen an extension, or cant give me some code - or a url so that i can make sure that only one person can edit at record at one time…
I’ve got various controllers all updateing the same table in the database, but i know at some point, theres going to be the situation where two people open and update the same record…
Perhaps its something like setting a values in a column named ‘lock’ to 1 if the record is opened… but then i’ll ask how to auto change a field automatically, and then change it back if the use cancels or closes the browser…
you add ‘last_modified_at’ datetime column in table and every time you fetch the row to edit you also fetch this timestamp. when updating you check if this timestamp did not change (noone else modified). If timestamp did not change - you can safely update record, if it did change - you show an error that someone else changed the record same time.
this is simpe, but stable solution. other locking mechanizms need some garbage collector to remove locks after some time when user opened record to edit, but then closed browser windows without removing locks…
While your solution is better than nothing note that it doesn’t guarantee data integrity: in the (small) time it takes in your final checking of the “last modified on” till the actual save (when you found that no one has modified it) your record could be updated. I hope that you don’t say that this is negligible situation. Eventually you’ll hit it, sooner or later, and as soon as you do your data integrity goes out the window.
I don’t have a full solution for that yet.
DB (write) locking can be a solution but one needs to read the fine letters on the usage of locking for the specific DB server (and for the version of the server…). As noted, locks can remain if the process aborted without cleaning (and this is possibly even further complicated if your database connection is using “persistent connections” but I’m not 100% sure on this - needs checking). DB locking also degrades the performance on the DB and thus should be used sparingly.
Still looking for an “application wide” solution for that. This can be an extension, simple behavior, a piece of code that can be reused - whatever. I’m not a Yii expert yet
Continuing my last post - if the update is conditioned to be done only if the value of the "last modified on" column is a given one, all in an atomic manner (meaning update and condition check), then its probably a safe solution.
This is, I think, described and prescribed in this forum post. I haven’t put “conditions” yet on AR API calls but if they are producing a single SQL statement then I guess its fine. Will check this… .
Meaning, the suggested code in the forum post linked above works as advertised. I haven’t check the logs to see what query exactly the updateByPk is being translated to but I guess eventually is does something like:[sql]UPDATE myTable set lock_version=4 where pk_col=111 and lock_version=3[/sql] and this is one atomic SQL statement in which the update is dependent on existing value.
I guess that until I get wiser, this is more or less the way I’m going to implement safe records update/delete in Yii.
