MySQL Forums
Forum List  »  Newbie

Re: Table Row Locking [SOLVED]
Posted by: Andrew Germishuys
Date: January 13, 2012 11:38AM

Hi Rick

Thank you very much for the input. All the data and info I've gathered from these forums, the MySQL documentation, and Peter's reply above got me thinking, and I have actually begun implimenting something similar to what you have suggested.

Once I had that working properly, I was hoping to post my solution here in the hopes of helping someone else.

I am implimenting the locking at application level, including not only a timestamp, but aldo a way to determine which other user has 'checked out' the data. Data for this application will be 'owned' by one user, and they can allow other persons edit rights to that data.

So, when going to do an edit, a check can be made to see if there is a timestamp, see when that time stamp was added, and determine who, if anyone, is currently working on that data. Data editing will be collaborative, so it would also be a great way to check when, or if a person is busy with an edit.

MyISM will do a table lock when inserting / updating data. This lock would take fractions of a second (and in the case of this data, I'd be surprised if the data size totaled more than 100K). Holding a lock on a table for longer, especially seeing as the front end of the app will be open to anyone to view, is not acceptable. Editing could take several minutes, or longer, a person could cancel an edit, etc.

Others accessing the 'read only' areas of the app shouldn't be able to notice any time locks.

InnoDB has row locking, which is great, but not relevant to the scope of this app.

By having the locking at app level, this allows for pseudo row locking, and leaves the fulltext search of MyISM open for use, if deemed necessary.

Thank you both again for the very valuable information, and I am very happy to say that it seems that I've managed to find a solution.

Kind regards :)

Options: ReplyQuote


Subject
Written By
Posted
January 11, 2012 03:27PM
January 11, 2012 04:44PM
January 11, 2012 05:08PM
January 13, 2012 09:53AM
Re: Table Row Locking [SOLVED]
January 13, 2012 11:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.