To Lock or not to Lock... What are the implications?

[jasperx]

I have a mySQL -php application set up to service a small group who are located in several different cities using a variety of platforms. The application is hosted by a G4 Mac running OS 10.2.8 We have been very pleased with the performance of the set up. The database/application manages contact and booking information for approx. 20,000 businesses. There is a small chance that without locking our users could get into problems with dirty reads and lost updates. We have begun reading up on row level locking in innoDb type tables but so far do not really understand how they work and are concerned that a user who pulled up a view of a business record and wandered off to lunch could leave everyone else unable to access the data for that business. The other concern we have is that we are backing our database up with a nifty little shareware program called mySQLBackup which we would like to continue using after the conversion to innoDB.... I think this program uses mysqldump under the hood. Anyone feel like climbing on a soapbox about using locking?

 

[ingresman]

You have to lock really unless you can gaurentee that no recrod clashes will happen. The gone to lunch thing was a curse of 4gl years ago which gave releation databases a bad name, should have given programmers a bad name really!
Web apps being stateless have less of the problems but if you implement a user written lock code system (e.g. optimistic locking) you might get the same effect and get users hung up. You need to make a policy decision around the length of time an update can hold the lock before it is considerd stale and will be made available for use by other people. When you read up about optimistic locking you will see that it is up to the code that holds the lock to sort out the fact that data may have been changed by other people. The key here is to make sure that the optimistic protocol you use keeps every consistent. Search for "optimitic locking" in google. If any one wants I'll write a paper up on optimistic locking for mysql and address the fiddly issues as I get to them.

Regards

Kevin

 

[tshot]

yes i would like to read yr paper on optimistic locking




----------------
ur feedback is a very welcome desire

 

[ingresman]

I shall knock out a paper and post a reference here

 

[jasperx]

Thanks for the advice to look up optimistic locking on Google.... the light finally went on in my head....I can "see" how this might work. What I have not noticed before is the use of "optimistic" terminology in the MySQL documentation for InnoDB tables.... is this how InnoDB works? And where can I get my hands on a reference to the syntax I need to add to my application to implement the locks? I have a copy of the O'Reilly text on Web Database Applications but have not found it to be helpful.

And I very much look forward to your paper ingresman...