-----Original Message----- > From: Robert Cummings [mailto:robert@xxxxxxxxxxxxx] > Sent: Wednesday, July 07, 2010 10:28 PM > To: Paul M Foster > Cc: php-general@xxxxxxxxxxxxx > Subject: Re: Multiple Access Question > > Paul M Foster wrote: > > On Wed, Jul 07, 2010 at 11:28:56PM -0400, Robert Cummings wrote: > > > >> Paul M Foster wrote: > > > > <snip> > > > >>>> @Paul, > >>>> > >>>> The OPs question is about concurrency on the record itself. How to > >>>> avoid two users accessing the same record and potentially damaging > >>>> each others changes > >>>> > >>>> My approach is the same as Rob's. Flag it locked and let the second > >>>> user gets a read only copy > >>> I can't think of a way to do this using MySQL or PostgreSQL. And one > >>> of the biggest issues with the solution you suggest is the user who > >>> opens a record for writing and then goes out for coffee. Everyone's > >>> locked out of the record (for writes) until they come back and finish. > >>> > >>> Okay, to solve that, we start a timer. But when the locker's time is > >>> up, how do we let the locker know they're not allowed to store > >>> whatever edits they've made? And how do we fix it so that those > >>> locked out are now unlocked? Plus, they're probably in a queue, so > >>> we really only let one of them know that they can now make edits. > >>> > >>> Since this is a PHP list, I assume we're talking about a web interface. > >>> So how do we do all this back end jockeying? Javascript is about the > >>> only way. But every time you fire off one of these javascript > >>> dealies, it has to be on its own timer so that it can let the user > >>> know that the original locker is gone and now the golden ticket is > >>> yours. It essentially has to sleep and ping, sleep and ping. > >>> Actually, it's more like a spinlock. But a spinlock would eat CPU > >>> for every user, if it was running on the server. So it would have to > >>> be running on the client, and "ping" the server every once in a while. > >>> > >>> Then you'd have to figure out some kind of messaging infrastrucure > >>> for the DBMS, so that it would quickly answer "pings" without tying > >>> up a lot of CPU cycles. It would have to be something outside the > >>> normal query infrastructure. > >>> > >>> When you actually get into this, it's an incredibly complex > >>> solution. I vote instead for allowing edits to be queued, log > >>> changes to the database. If there is a true contention problem, you > >>> can look at the journal and see who made what edits in what order > >>> and resolve the situation. > >>> > >>> The best analogy I can think of is when using a DVCS like git, and > >>> trying to merge changes where two people have edited the same area > >>> of a file. Ultimately, git throws up its hands and asks a human to > >>> resolve the situation. > >>> > >>> Bottom line: I've heard about concurrency problems since I started > >>> using databases, and I've never heard of a foolproof solution for > >>> them that wasn't incredibly complex. And I don't think I've ever > >>> seen a solution in actual practice. > >>> > >>> If I'm wrong, someone show me where it's been viably solved and how. > >> I think you're overthinking the issue. The timer handles the issue of > >> holding onto a lock for too long. > > > > That's why I suggested it. > > > >> As for a write queue... don't bother. > >> If a user finds that another user has a lock then tell them when it > >> expires. They can come back and try for the lock on their own. You > >> can set up AJAX polling to see if the lock has been removed and > >> indicate this to the user (if they've bothered to wait on the page) > >> but this is optional. > > > > That's why I suggested it. > > > > Yes, we could just tell users "come back later" if they wanted to edit > > a locked page. I was just imagining a 100% complete > > wipe-your-butt-for-you solution. > > > >> Queuing edits is not a good solution. > > > > And yet, it appears to adequate for the DBMSes I'm familiar with. > > > >> Imagine document X: > >> > >> UserA requests X > >> UserB requests X > >> UserC requests X > >> UserD requests X > >> > >> UserA modifies X and saves X.1 > >> UserB modifies X and saves X.2 > >> UserC modifies X and saves X.3 > >> UserD modifies X and saves X.4 > >> > >> In this scenario all the work done by UserA, UserB, and UserC is > >> clobbered by the submission by UserD. This can be resolved via > >> merging such as used by versioning systems, > > > > ... if automatic merging can be done in a particular case. But there's > > a non-zero probability that a merge will require human intervention. > > Yes of course, without version/merging or some type of write-locks, > > there is potential contention. > > > >> but this makes less sense in a high > >> traffic collaborative content system such as a wiki. In the lock > >> scenario we have the following: > >> > >> UserA requests X > >> UserA modifies X and saves X.1 > >> > >> UserB requests X.1 > >> UserB modifies X.1 and saves X.2 > >> > >> UserC requests X.2 > >> UserC modifies X.2 and saves X.3 > >> > >> UserD requests X.3 > >> UserD modifies X.3 and saves X.4 > > > > ... assuming UserB waits until UserA stores his edits, UserC waits > > until UserB stores his edits, etc. The above assumes locking, and > > probably versioning and merging. > > No, not at all. Each user can only edit the version last saved. It only assumes > locking. > > > But a wiki is not a DBMS. And perhaps the OP was talking about a wiki. > > In which case, all this may be moot. I just checked, and Wikipedia > > does not lock pages under edit. They do versioning, but their > > "locking" is on the honor system. For a discourteous user, this would allow > contention. > > I don't know if other wikis perform locking. I doubt it, but I could > > be wrong. (Note: Wikipedia *will* lock a page from *all* edits when > > there is continued controversy about a given article.) > > I knew I should have double checked Wikipedia before I wrote that :) Indeed, > Wikipedia does use an automatic merge. > > >> At each write step the previous work is appropriately integrated. > >> This is the desired functionality for a collaborative document such > >> as a Wiki. In the case of source code, once generally expects a much > >> smaller number of editors or that editors are working on very > >> different areas of the source file and so conflict resolution is less > >> common due to automatic merge by the version control system. > > > > Agreed. > > > > Again, though, I'd like to see a *working* example of the above, > > particularly in the context of a DBMS. > > I'm not sure what context the OP had intended, for whatever reason I > assumed content editing. I agree, this wouldn't work well at all in a DBMS > since there would need to be multiple locks in place on all data points > affected by a modification. > > > Back in my FoxPro days, we used semaphores on each record, but it was > > a very complicated system to program with. For add-on libraries (like > > CodeBase) which accessed xBase files, they used OS-based file locking. > > Again, clumsy and error-prone. (This was one of the perpetual problems > > for a database system which was originally built as a single-user > > system. SQLite has similar problems. It write-locks, but such locks > > aren't reliable under either Windows or NFS environments, according to > > the documentation.) > > Directory creation is atomic on any modern OS I can think of and I believe the > atomicity is preserved over NFS. As such, you can use a directory to facilitate > locks over NFS. > > > There's another subtle point about DBMSes. Doing a SELECT over a > > table(s) doesn't indicate to the DBMS that a write will occur later on > > that same data. In fact, writes may occur on different fields in the > > same record "concurrently" without issue. Contention is really only a > > problem when two users try to edit the same *field* at the same time. > > And as far as I know, DBMSes like PostgreSQL and MySQL simply queue > > writes, allowing the kind of contention you're talking about. > > PostgreSQL replaces the *whole* record with updated data upon any > writes to it. > > (Actually, they mark the old record for deletion and *add* an updated > > record.) I don't know about MySQL. > > > > But maybe I'm overthinking it. ;-} > > Well when considering DBMSes you are right. The updates are queued (if the > client isn't awaiting a return code) and they are applied one after the other > clobbering as necessary :) One would assume before the data goes to the > DBMS that it had been appropriately handled with respect to contextual > contention resolution. > > Cheers, > Rob. > > -- Any modern decent RDBMS should be able to do row locking. IE, only 1 update to that row is possible. But given the OP's question, I'd say this would be his scenario. John & Jane coincidentally look at the same record. But John gets distracted for a second while Jane updates the same record. When John goes to update it, it may override the already updated record made by Jane. I think the OP wants to know if there's a way for John to know if there's been changes to data since last seen as in this case. You can implement something similar to ASP.NET's 'optimistic concurrency update'. Meaning you'll have to cached the original data values somewhere. Compare the 'keys' (not just PK, but some other important values/indexes too), if the values are exactly the same, then continue with the update. If the data has changed, give a prompt to John, in this case, that data has changed since last viewed and show the changed data and possibly made by whom and probably give John the choice of override that same data with his update. IMHO, this would be ideal for many situations including where the users are in different remote locations. :) Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
