MySQL locking problem

Question

I have a simple setup of a set of writers and a set of readers working with a MySQL ISAM table. The writers are only inserting rows while the readers are only checking for new rows.

OK, so I know that I don't need a lock in this situation, since I'm not modifying existing rows. However my Writers are accessing one more table that does need a lock. I piece of information seems irrelevant except for the following limitation stated in the MySQL documentation:

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

So to access the table I want to insert rows into, I NEED to lock it, which is causing me performance problems. Any suggestions of how to get around this?

Answer 1

Typically you lock and unlock immediately around the queries which need locking. The documentation is simply stating that for any set of queries run while you have a lock, all tables involved must be locked. You can unlock as soon as you're done and touch any other tables.

Also consider that InnoDB supports row-level locking, which is often preferable to table-locking for performance since other queries on other rows will not be locked out for reading while you're also writing.