+1  A: 

Why not use InnoDB and get the same effect without manual table locks?

Also, what are you protecting against? Consider two users (Bill and Steve):

  1. Bill loads record 1234
  2. Steve loads record 1234
  3. Steve changes record 1234 and submits
  4. Bill waits a bit, then updates the stale record 1234 and submits. These changes clobber Bill's.

Table locking doesn't offer any higher data integrity than the native MyISAM table locking. MyISAM will natively lock the table files when required to stop data corruption.

In fact, the reason to use InnoDB over MyISAM is that it will do row locking instead of table locking. It also supports transactions. Multiple updates to different records won't block each other and complex updates to multiple records will block until the transaction is complete.

You need to consider the chance that two updates to the same record will happen at the same time for your application. If it's likely, table/row locking doesn't block the second update, it only postpones it until the first update completes.

EDIT

From what I remember, MyISAM has a special behavior for inserts. It doesn't need to lock the table at all for an insert as it's just appending to the end of the table. That may not be true for tables with unique indexes or non-autoincrement primary keys.

Gary Richardson
hmm, interesting...so I haven't solved the problem...I'm aware of the limitations to the MyISAM tables, but unfortunately the host I'm using doesn't support InnoDB...Yeah I could install all of that stuff on my home box...php, mysql, etc...which I probably should so I can test it out for real.Thanks for the insight!
leeand00
...also the application doesn't really do any updates, it just does inserts and reads (it's a very simple application...) hopefully that means it will be okay...
leeand00
I do a select before I do an insert because one of the tables doesn't auto increment (it has a composite key of two fields); Thus you have to do a select on the table before inserting a new record to find out what the maximum id is for one of the keyed fields. The other part of the composite key is a foreign key also.
leeand00
well, you could always turn off your table locking code and run the jmeter test to validate the same results..
Gary Richardson