Re: Table Row Locking [SOLVED]
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 :)