Level 102
Personally I use Javascript to mark the fields that have been changed and only submit those
But do they have an example from their framework of choice?
Nov 1, 2021
54
Level 15
Record Locking In Laravel 8 ?
I have had a colleague ask about record locking. Admittedly he comes from major mainframe technology, but to be honest I did not have an answer for him. So any advice how to ensure that two users both accessing the same record at the same time, eg a customer profile, where for example, user one changes the address, then user two changes the phone number. How do I ensure that user two does not overwrite user One's address change??
Many Thanks !
Level 75
@vincej the best source is https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
But locking is rarely needed. Usually a whole block gets locked.
Edit: Usually a lock is done in conjunction with a transaction.
Level 15
My colleague / friend comes from a mainframe / Cobol world, where he argues that unless I can lock a record, I have not produced a multiuser system. He argues that when several people are hitting the same record, then only the last person to hit submit will have their version of the record updated. All those changes to the record from other users will be overwritten. I see his point. I can imagine that there are large users out there with Laravel. It is a legitimate concern, so I need to find a satisfactory answer.
Level 75
@vincej this happens mostly in a client server setting (same office), but can happen if say a husband and wife or two truck dispatchers (just examples) edit something at the same time.
For cases where it can happen set an FOR UPDATE lock and perform a transaction.
In most cases two users won't be on same edit, like this forum, I can only edit my replies.
Level 73
What I think your colleague is talking about is database transactions.
Which means that you do a select for update to prevent any other user from changing the same record. You then make your change and do a commit or a rollback and then the record is again available for other users to edit.
This is a good option to choose when more than one table needs to be updated before the changes is saved in the database. For example during a bank transfer. The amount is removed from one account and then added into another account and only if both the updates are successful are the changes commited. Also while doing this it might be good that no one can make any other changes to those two accounts.
Level 15
Level 102
@vincej I think the question is rather if big companies use mysql as this is all handled by the database, not laravel
Level 73
@vincej It's not that common no but check the manual on the link I gave your and you see how easy it is to implement.
Level 15
@Tray2 Thank you - I have not studied it yet, but that looks encouraging. You see the guy is a consultant now, and he looks for stuff like this. If I don't satisfy his question, my app will be disregarded out of the gate. So, can you tell me, does this feature effectively deliver protection from two users each others update to a record? I don't want to implement huge number of changes if it does not do it. Thanks !!
Level 102
@vincej I believe you only lock it for the duration of the change. So if I save 2 seconds after you, by change overwrites your
Level 15
@Sinnbeck Yes, exactly, and therefore by definition multiple users can overwrite each others amendments, thus in his opinion, such a system is not a multi user system. Ugh!
Level 102
@vincej but how would such a system work? For how long would you expect the record to be locked? I am actually curious
Level 73
@vincej Yes and no. If you do a select for update no other user can make changes to that record until the first user have done a commit or a rollback. There is nothing however to prevent another user from updating it after the previous commit/rollback. The thing to be aware of is that if a user does a select for update and doesn't save or cancel it the recorded will be locked until the session that locks the record is killed.
Level 15
@Sinnbeck I'm not sure, I have never worked in that environment, however, @tray2 makes a very good point, that it could be locked for a long time. I think my colleague wants assurances that my application will not allow two users to overwrite each others amendments. So, putting aside the situation where someone opens a record and then goes home, I would expect that a record would be open for the duration of the transaction. some seconds to perhaps a minute or two. This is not something I have thought about. But I need to figure it out or else my consultant friend will dismiss my application entirely as a single user system.
Level 102
@Tray2 never worked with that so I'm qurious how it would work? How does mysql know what user has made the lock? Would you be forced to have a mysql login for every single user?
Or are we just talking for the duration of the transaction?
Level 15
@Sinnbeck I don't know, however, does the DB need to know who the user was that opened the record? I think not. Only that it is in use by another user. @jlrdw link to MYSQL offers a detailed discussion on the topic. My consultant friend asks a good question, and I am surprised that there is almost no discussions on the topic on laracasts.
Level 102
@vincej as I see it, if you can lock it for a duration, the mysql should be able to identify who did so. If not it cannot know who is allowed to update the record in the end. Only was I can see this working on a database layer is within a single transaction
Level 73
@Sinnbeck That is a very good question, I don't know how that would function but I think that the connection to the database is stored in a session in the database and if that session takes out a lock only that same session can do the commit or rollback. So if you have a edit view it must take out the lock and then the update method needs to commit it to the database. So I'm guessing the persistent database connections for each and every user somehow.
This is mostly old client server methodology where every user was a database user. Not sure how that would function in a modern day web app since it's usually only one database user and several application users.
Level 102
@Tray2 my though exactly! I actually struggle with this issue at work on an old system using mssql. Would be nice to know of a workaround in case I someday rewrite the backend
Level 15
@Sinnbeck Yes, you make a good point, I agree. This transaction locking stuff sounds very complicated and an enormous amount of work. Somehow I need to come up with a reasonable answer for him, even if it means that I will do it, and this is how.
Level 102
For the record, I don't think I have seen any modern system handle this without some sort of js/ajax check if a user is editing a record. I know WordPress does it this way
Level 102
@vincej any chance you can ask him if his solution is multiple database users or if he has another trick (like locking using code)?
Level 15
@Sinnbeck Yes I am speaking with him tomorrow at length. This subject will be for 100% certain part of the discussion. I fear that he will insist that my application is not a true multiuser system.
Level 102
@vincej well if you are able to get an idea of what he expects in more detail, it might end up being solvable
Level 15
@Tray2 Ok, fair enough. Any idea how large php users handle this problem eg Amazon, Magento etc?
Level 15
Level 102
@vincej I would try and find out how others are doing it. If they expect it to be like that then they must have other examples of systems where that is the case. I would then dive into how they work for inspiration
Level 15
@Sinnbeck It appears that no one else on Laracasts has been asking this question. But, yes, for sure I will study the issue this afternoon.
Level 102
One new idea. Use atomic locks on the cache to tell laravel that some ID is locked for now.
https://laravel.com/docs/8.x/cache#managing-locks-across-processes
You can get the lock token and use it updating later
$token = $lock->owner();
Level 15
@Sinnbeck Nice idea, I will look into it. I don't think my app will be condemned for not having it today, but I will need to show a roadmap on how to get there.
Level 75
@vincej Like I said: For cases where it can happen set a FOR UPDATE lock and perform a transaction.
And a transaction is easy, not hard at all. That's all you would need to do. You can do a transaction within laravel.
Edit: If fact here is an example:
https://stackoverflow.com/questions/61985327/how-to-lockforupdate-on-an-existing-eloquent-model
Level 15
@jlrdw You know, I think you were right all along! I just had a look at FOR UPDATE, and indeed, it looks like this would do the trick! I will have to try one and see how well it works. I just looked at a video on transaction from Povilas, and to me, it looks like journaling on steroids.
I find it bizaar that it looks like I am the first person to ask this question on LC. When I get it working, I will come back and give your answer the best reply reward. MANY THANKS!
Level 75
@vincej see my edit also, the laravel example.
1 like
Level 122
In my experience, this problem cannot be solved by transactions or locks.
These are useful to stop simultaneous writes to the same record, but you cannot and should not lock the database between requests.
So, whats the solution? Two possibilities, and probably using both at the same time.
When user 2 requests to edit a record that is already open for edit by user 1, then they can receive a warning and be made to wait. In a web environment this is tricky to manage because a user can knock off for lunch not realising that they have the record for their exclusive use.
The second technique is to store the original values with the form data, and then when posting the data, check that the record still matches the data when it was presented to the user in the edit form. This can be as simple as a hidden last update field in the form. When receiving the form request check that the record has the same updated_at value. Bear in mind though that the record might be updated for other reasons also. The better approach is probably just to worry about the fields that the user wants to change.
Level 15
@Snapey Many Thanks for that ! Great answer. So, if I understand you correctly, I could just store the last "updated_at" value, and if when the form is posted back to the server, you check that the updated_at value has not changed. And if it has changed that means that someone else has amended the record. So, then I need to compare DB record and and my form, and update the DB with my form as mine is newer? This sounds quit complex, I'm scared already! Particularly since I would need to amend dozens and dozens of views and the controllers which go with them. Maybe I have not understood correctly?
Level 75
@vincej all of your Farms are not going to have this problem, as I'm sure customer A cannot edit customer B data.
Taylor has lock example in the docs.
Level 15
@jlrdw Farms? You are right. However, I will be deploying one instance per customer, so there is no risk of that.
The essential problem is to ensure that if a large company calls up my user who themselves might have multiple employees manning phones, and says change my address. Ok. However, then someone from the same "large company" also calls at the same time and speaks to a different person, and says add a new phone number to our company profile, then called #2 data will overwrite caller #1 data.
Level 122
@vincej If you find that the record has been updated whilst the user was deciding what to enter, then you have to go back to them with an error and let them fill the form in again
Level 75
@vincej I found a very detailed article covering locks: https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking
It even has diagrams.
1 like
Level 15
@Snapey So that means user #2 has an error message presented, some JS I guess, and then refresh the page and tell them to re-enter the data they were about to enter. Correct? Sounds good, but does this not require a lot of coding at both view and controller level? Just like to understand and find the easiest / quickest approach.
Level 15
@jlrdw Mnay thanks ! I'll check it out! Gotta walk the dog before it gets too dark and cold -5C (20F) here, snow, wind! Texas must be nice!
Level 75
@vincej 75 degrees.
Level 122
When user 2 requests to edit a record that is already open for edit by user 1, then they can receive a warning and be made to wait.
can be done with Atomic Locks as @sinnbeck mentioned earlier https://laravel.com/docs/8.x/cache#atomic-locks
You would need to decide how long the user is allowed to edit a record since the atomic lock should have a limited lifespan
Atomic locks are managed in code (uses the cache) and has nothing to do with transactions or database row locks.
Level 102
@Snapey the updated_at check is actually a beautiful and simple solution. Love it.
Could just be implemented using validation. Check if the updated_at matches, and if not return a validation error "customer has been changed by another user. Reload page and try again"
I know it isn't a great end user solution, but it is fast to implement and won't do any unnecessary locking
Level 15
@Sinnbeck Looks interesting. I'll study this. Many thanks !!
Level 15
@Sinnbeck @snapey thinking about the validation approach it occurred to me that when user two gets a warning that there have been changes they will have to refresh their page view with the upto date data. Ok. But surely this means they loose all the amendments they made and they will now have to be re-entered. Correct?
Level 122
@vincej unfortunately yes. There could be some clever presentation of the conflict and allow the user to choose which one they want, but it would require a bit of work
Level 102
@vincej but at least the changes wouldn't be gone until they manually refresh, due to laravels old() helper
Level 122
Following all that, just remember (for your smart friend) last save wins - yes, exactly the same as if one user completed their changes and then the other one started editing and saved.
If two people have rights to change a record, they can always be a chance that they are in conflict as to what the record should contain. They could, for instance keep changing it back and forth until one can't be bothered any more. In reality these situations are extremely rare.
Level 15
Very good point - just like in wikipedia!
I have so much to do, between this, and a side project, that I really don't want to be having to re-engineering my whole site. Many thanks !
Level 15
@Snapey Furthermore, this guy comes from a mainframe world. I am never going to be selling into banks or airlines. I am selling into small and medium sized businesses. I am not selling against SAP. And now that I better understand the technical issues better, I accept it is a priority, but it is not for me a top priority. Cheers !
Level 19
@vincej Hey.. just wanted to add my 2 cents to this discussion. I don't know what a mainframe world is supposed to be, actually. Data locking and ensuring consistency happens on many levels and every level has or can have its own strategies. It starts with HDDs or SSDs, it continues in the filesystem, goes on in databases and finally the application.
You should ask yourself if this is really something that you actually need to think about. The lower levels are handled for you. In large scale setups you would tweak those levels or pick technologies according to your needs (should I use zfs? do I need a column-oriented database?). A super simple example of locking at the app level can be seen in Wordpress (this post is currently being edited by X).
As I said.. I don't know what your friend actually means by mainframe world, but I come from an enterprise background where we have around 100k users. In reality we also have thousands of apps and none of those apps have that many simultanous users, let alone users editing the same record. Not even the ubiquitous monster SAP. In my world I have very rarely seen app-level locking, I'm not even sure SAP does it. ;-)
If computer science is your hobby, you can read up on Mutexes and Semaphores.
Level 15
Thanks for your thoughts. They are much appreciated.
I am sure you mean nothing by it, however, computer science is not my hobby. I have been in the software industry since 1985, working for some of the most prominent corporations in the world, commonly in a senior management positions. My last "real job" was running Canadian operations for a publicly quoted International software provider. In my new role as an independent s/w developer, I accept my search for technical responses with humility.
As I said in earlier responses, I am not convinced that record locking is essential for my application. Nevertheless I am going to study the various suggestions from @jlrdw, @sinnbeck, @snapey. And I will include those wikipedia references you have offered.
Many Thanks !! Cheers Vince
Please or to participate in this conversation.