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
starhuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
is the user explicitely hitting a button "Edit" ?
After that does he/she go home and forget to hit "Save" or "Cancel" ?

a mysql by default is set to repeatable read
http://dev.mysql.com/doc/refman/4.1/en/set-transaction.html

you'll specifally have to look at what level of locking you want
from your Q i'd say row level locking

you can lock a row with "select for update" without changing it
if the user logs off with saving, the row will get released again

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
starhuAuthor Commented:
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
0
jimyXCommented:
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.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Geert GOracle dbaCommented:
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)
0
jimyXCommented:
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?
0
Geert GOracle dbaCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.