locking data for individual users

nozom

this is just a thought in the pipeline rather than anything i'm working on but..

set the scene:

The system is a mutliuser Database system for a small firm with up to 50 people on the system.

2 Users are about to edit the same bit of data at the same time. what i want to happen is.

USER A finds the bit of data and clicks on 'edit' this then opens up the data in a text field so they can make the changes. USER B comes along 30 seconds later and clicks on edit, however can't get in because USER A is currently making changes.

AS soon as user A clicks 'submit' the data becomes editable again.

I know of one way round involves setting a field in the table to locked when the user opens the edit page and 'unlocked' when the user hits submit. HOWEVER, it could go wrong if the browser was closed or a computer crash or back is hit on the browser etc. Leaving the bit of data locked.

Is it possible to do? Did i make sense?

Cheers,
nozom

matt_4013

Makes complete sense. I'm working on the same thing myself at the moment, though it is for a bit more than 50 users. That's really all I can think of doing, a field that defines if it's locked, as well as a field defines who actually locked it. That way if some malfunction does occur, such as a crash, the person using it could come back in and unlock it, but nobody else could. It was so much easier on an AS/400 when it just did it itself 😉

nozom

what would be good is some system that keeps the connection alive every 30 seconds or so, supplying a timestamp to mysql, then if a computer does crash a cron job could run every minute(or whatever) that checks for locked fields and when they were last locked, if it's over 1 minute ago, it unlocks the data.

or, like you said, add the ID of the user who locked it and if that person goes offline it unlocks, again using cronjobs.

matt_4013

yeah but then it wont be portable to servers where you can't implement something like that, and that's one of my requirements 🙁

Gemini1706

Hmm, interesting..
I thought MySQL (or databases in general) do this automatically!!!

That was my impressions: the database initiates an internal session whenever a field is under edit, and locks it, then releases the session automatically and unlocks the field after edit.. The session itself keeps monitoring who locked it, when, and if it is about time to acdept changes and unlock, or just rollback to undo..

nozom

i think thats correct but the field is only under edit for the micro seconds it takes to actually make the changes in mysql.

I would like to know if there is a way to keep it locked for a period between edit field being clicked in the browser and submit being hit after the user has made all their changes. This could take several minutes depeding on the size of the field/'s to edit.

cheers,
nozom