Link to home
Start Free TrialLog in
Avatar of starhu
starhu

asked on

Delphi: lock a record?

Hello,

I use Mysql with MyDac component.

The software is used simultaneously by many users. The customer wants so as if a user navigates to a record (a customer sheet) then he can modify the record, but the second (and 3d etc.) users who click on the same customer would see a message (this record is locked by xy user) and they shouldn't be able to edit it.

When the first user navigates away then another user can edit it.

How can I do this?

Thank you
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of starhu
starhu

ASKER

is the user explicitely hitting a button "Edit" ?
yes
After that does he/she go home and forget to hit "Save" or "Cancel" ?
I told him that, he said that it would never happen

The question is not only the locking but how do I know and *show* to the second user that "this record is locked by John Snow".

Thank you
You should implementing suitable technique. Many options could be implemented here:
Have fields in your DB showing Username, connection ID, Datetime and Timeout for each record.
For instance, when user "John Snow" edits a record, then add the username, connection ID, Datetime and Timeout (seconds) for this user. As the user keeps working on the form increase the timeout (to avoid users who forget to close the Edit Form or who capture it for lengthy time then update the timeout value for a certain number of times only and do not keep the capture lasts forever, or as need be).
When another user tries to edit this record just calculate the Datetime plus Timeout and if the result is greater than the current time then it means "John Snow" is still editing and hasn't finished yet, and you can show who that user is by retrieving the username and the time of capturing the record (it's really helpful to find out who captured the record). And if it's less than the current time then it means the record has timed out and any user can capture it for Editing.

You should have constraints on the time limit for each user who calls the Edit action to enable other users to use the same action as well and to encourage fast release of records. You could implement different action to ensure the Edit action Times out if the user forgot the form open or if the user is taking too long to release. One option is to set the number of times the Timeout field is updated to three times, for instance (do not forget to notify the user of such actions to finish with-in the time limit).

When the first user navigates away then simply clear the timeout fields. And do not worry about leaving them because the time limit will be elapsed and when another user captures this record, new parameters will be set. But it's better to clear them for not to occupy your DB.

MyDac has an interesting property "AutoRefresh" and "AutoRefreshInterval" you could use them along with the event "AfterRefresh" to update the Timeout. And when data is posted clear the timeout fields.

Also additional feature you could implement is adding extra field "UserRequestEdit" to show at least one User who might demand and try to capture the record while it's captured by another user. So while the first user is Editing you could tell him/her that another user "by name" is requesting to edit this record.

PS: I haven't fully implemented all these options but just giving thoughts here.
one thing i wouldn't recommend is writing your own locking mechanism
investigating how the database does it and how you can visualize it is way simpler.

>>After that does he/she go home and forget to hit "Save" or "Cancel" ?
>>I told him that, he said that it would never happen
Does he still believe in Santa Claus ?

You need a realistic person for this, not someone who says "yes" to everything you say without thinking

( I rephrased the "idiot" part)
mlmcc, according to the stipulated requirement in the main question, the asker says:
"users who click on the same customer would see a message (this record is locked by xy user) and they shouldn't be able to edit it."
And again the asker reconfirmed the requirement at this comment:
"The question is not only the locking but how do I know and *show* to the second user that "this record is locked by John Snow"."
I provided a feasible workable solution that can fulfill the asker's demand, here.

Did you see it?
creating your own locking mechanism is like reinventing the wheel
consider looking at when locking problems occur
coming with a solution which locks 1 or multiple users and being able to solve the locking problem should help understand the locking mechanism in mysql

the tuning guide gives a few perspectives on this:
http://dev.mysql.com/doc/refman/5.6/en/locking-issues.html