Record Locks, how to implement?

[999Dom999]

I have created a product database which will now be used by many people. The problem is if 2 people are editing a record then one will lose information when the second submits.

The main problem with locking records is if some edits then closes the page it remains locked.

I have searched around and here is the best solution I have found, please look at second post from the bottom.

http://www.dmcinsights.com/phorum/read.php?13,30233,30260

This gives a user a 5 minute window to edit a record if they close it then its free to edit after 5 mins is up.

The main problem is if Bob works on a record then goes for a coffee, then after 5 mins Jane gets the lock and changes the record. Then Bob comes back with his coffee and hits submit. Jane still has a few minutes left on her lock time, so when Bob submits he gets a lock message saying Jane has the lock and he is denied access to the edit page and loses his work.

This must be quite a common dilemma for web based record locking and hopefully someone here can shed some light on a full proof way of overcoming this?

 

[jpadie]

there's no fool proof way of enforcing record locks in a stateless environment. you should just ensure that you impose joined up business rules and the code to enforce those business rules.

one way might be to flag that a record has been amended between an edit request and a submit request and allow the submitting user to confirm the second edit.

 

[DonQuichote]

Especially with PHP, you have a proeblem: HTTP is a stateless protocol. This means that nobody is logged in to the server, and hardly anybody uses the "log out" function in a website. And even that function does usually not log you out.

Note that for a lot of data, this is not a problem. Assignments are usually only made to one employee, for instance.

An example of where it can go wrong is a helpdesk system. Questions appear in a list on multiple sessions, and only one session can "take" the question.

To overcome this problem, such a question usually has a field "handled by" with an employee ID (foreign key) in the database. This field is initally NULL.

To take the question for employee number 5, issue a query like:

UPDATE Questions SET HandledBy=5 WHERE QuestionNumber='1001' AND HandledBy IS NULL

Off course, if another colleague did take the question before you, this statement will update nothing. Similarly, your SELECT statement should check if the question is assigned to the right employee and issue a message ("the question is already taken") if there are no records in the result or if the result set contains the wrong employeeID.

In short, about the only thing that does not leave locks is to check before storing. If the record were an address, for instance, you could check if all the unchanged fields had the same value in your update.

But do so only in cases where records can be changed by more than one person. Usually, the application flow makes this impossible for quite a lot of data.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[999Dom999]

The problem I have is part of this database is a product development sheet of information and there is a team of people all working on the same product making many changes. To keep track of changes when you submit it compares the POST data against the database data and emails the team involved the changes. I guess the only way would be allow the second form to submit as you said jpadie, warning them its changed and to check your email for the changes. This is a bit of a messy way to do things.

 

[jpadie]

is it really a messy way to do things?

as an alternatives et of business rules:

when someone loads an edit form impose a lock on related records.
do not allow the record to be edited by someone else whilst the record is locked.
add a request mechanism for users wishing to access the record for editing
have the edit form interact with the server using ajax to check for access requests. if there is an access request flash a dialog to the editor saying that unless he actively refuses the request the lock will be removed in 2 minutes and the page redirected to a static view of the record.

the 2 minutes could, of course, be 30 seconds or whatever.

 

[feherke]

Hi

I would think to a timeless alternative :
[ul]
[li]when generating the edit [tt]form[/tt][ul]
[li]calculate a single checksum from all fields of the record[/li]
[li]put that checksum in a [tt]hidden[/tt] [tt]input[/tt] of the [tt]form[/tt][/li][/ul][/li]
[li]when receiving submitted data from the edit [tt]form[/tt][ul]
[li]calculate a single checksum from all fields of the record[/li]
[li]compare the calculated checksum with the submitted checksum, if they differ, somebody else modified the record in meantime[/li][/ul][/li]
[/ul]
Advantage : no predefined lock expiration time.
Disadvantage : no way to warn second and further users about the first one already editing.
Further possibility : calculate separate checksum of each field and check them separately. If the second user modified different fields then the first one, then you can update only the changed ones.

Just a theory, may be completely useless in you situation.

Feherke.

http://rootshell.be/~feherke/

 

[IPGuru]

How about ading a time stamp to the record that is updated on every write.

read the time stamp when the record is displaid
check the time stamp has not changed before writing the record
if the time stamp has changed perform some validation (ask user to confirm any differnces

this should enable sequences such as :-

user A gets form
User B gets form
User B submits form OK
User A submits form - gets warning of changes

&

User A gets for
User B gets form
User A Submits form - OK
User B submits form - gets warning of changes.

Ajax could also be used to give the user (almost) realtime alerts of changed data if req.

 

[DonQuichote]

The problem I have is part of this database is a product development sheet of information and there is a team of people all working on the same product making many changes.

Stop right there. Try to forget any technical solutions for now.

What should the system do in such circumstances? I can imagine a few things:

[ul]
[li]Show the user that somebody else has opened part of the sheet so they can sit together[/li]
[li]Block access once you opened a (part of) a sheet for editing[/li]
[li]Try to merge the data[/li]
[/ul]
The first case assumes that the crew members know each other and can sit together, which may not be the case for an internet application. If it is an Intranet application, it may very well be an option. This informing the user stategy puts every responsibility with the users. And the users should know best.

The second is a lock strategy. Lock strategies are a nuisance to the user and often require extra management for "waste" locks, but the advantage is that you can guarantee separate actions.

The third may be the most dangerous one, but is used successfully in a lot of source code control systems. A product sheet may not be that different from a piece of source code. If automatic merging is not possible, the user committing the conflicting data is usually asked to merge the data by hand. Note that this works great with text, and lousy with binary data. The PEAR repository has "diffing" and merge code as far as I know.

And there are even more strategies. Like the source code control systems, you can give every user his own copy of the data and merge it or have it approved (this is quite a heavy solution, but it is found in a lot of content management systems).

In short, you or your client have to decide what the strategy is.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ingresman]

Sounds an interesting problem. Have a search for the term optimistic locking. This has been alluded to in other responses and can work well if used well, especially in online systems. As stated previously, in a web type application you are connected to the web server (and by extension the database) for a very short period of time (if your design is ok !!). Competing updates do need protecting from each other while executing and the normal MYSQL locks should suffice. This is known as currency control.
What optimistic locking does is the mark a record as potentially being updated. It works best in scenarios where it is unlikely that more than one person would be updating a particular record at the same time. For example it would be unlikely that a customer would be having his address changed at exactly the same time by more than one operator.
When you create a table in the optimistic world you add a column which acts as a version. This can be a number of a time stamp. I prefer numbers but it you want to implement, a time based lock, a time stamp will be ok.
When you read a record from a table you also read the current version e.g.

 select id,name,version from customer where name  "fred".

When you ready to update the record you issue an update like

update customer set name="newname",version=version+1 where id=@id = "fred" AND version = @previous_version_from_select.

If the update fails with no such row you know either the record has been physically deleted by someone or the version column has been updated by someone else. If the update works ok you have negotiated the optimistic lock and have updated the version number.
Now if you did fail the update you have one of server options. You can re-retrieve the data and ask the operator to perform the update again or you could fail the transaction or you simply overwrite the last update (not nice). If you have lots of random updates you shouldn't see a concurrency failure, if you see a few concurrency failures well that's maybe ok but if you are getting loads that's not good and optimistic locking is probably not for this application and you should have spotted this from the start before you implemented.
I don't know exactly what your trying to do but if your database is made up of many table types with the different people working on different rows you might get away with it. If you have many people working on (say) the same document you will probably have to go down the route of checking out the document (like a version control system) and keeping it locked for ever or until it gets checked back in. (of course an admin should be able to unlock it). If this is the scenario it would be ok to keep somethig checked out for ever as you might take longer that 10 mins to do your change.
This is just some general wordage to add to what everybody else has said, it would be useful to have some explanation of the internals of your app.

 

[999Dom999]

Thanks so much to everyone for all your input and suggestions, its really good to see other people's perspective and ideas. This has given me a lot to think about. A mixture of these suggestions might be the answer. I will post back here with what I'm going ahead with.