Technique for multiple users on same datasets

Question

This is more a learning question than coding, but I'm certain it's a common issue for anyone developing administration systems or applications in php/mysql/js etc.

I've developed quite a complex application that lets users upload images, and define hotspots in them with associated actions. The images are stored in a table, and the actions in another, with json data for every action in a text field. It's a magazine style format that is used by a custom reading application. However, like I say, the problem is generic.

Basically, my fear is that if someone is editing the same image and set of actions at the same time, and they both submit changes, or if it was edited by someone else then there's a whole series of structures that potentially will fail on submission.

I don't want to implement a locking system, as the system is very wide ranging (links to other images, etc), and I think it's a bit ugly. I saw this link (MSDN Multi-tenant architecture article) in another question, but it seems a little overwhelming and specialised for sql server.

So - what are the terms for data and system architecture here that I can investigate, or are there some good articles to do with this topic that people can recommend? Specifically for php/web world would be great!

--

I'm still looking for good responses on this question. Found out meanwhile that the general term is 'Concurrency', but technique is the important thing :)

Answer 1

First

ALTER TABLE tablename ADD COLUMN changecount BIGINT NOT NULL DEFAULT 0; 

for all relevant tables. Then whenever you want to submit a change, use not only

UPDATE tablename SET whatever WHERE id=whatever

but

UPDATE tablename SET whatever, changecount=changecount+1 WHERE id=whatever AND changecount=the_changecount_you_remembered_from_loading_the_object

now if a user submits a change, it will update the changecount - another user submitting a change to the same object, but loaded from older state, can be told "another user has just changed blah blah"