MySQL: How to lock tables and start a transaction?

Question

TL;DR - MySQL doesn't let you lock a table and use a transaction at the same time. Is there any way around this?

I have a MySQL table I am using to cache some data from a (slow) external system. The data is used to display web pages (written in PHP.) Every once in a while, when the cached data is deemed too old, one of the web connections should trigger an update of the cached data.

There are three issues I have to deal with:

• Other clients will try to read the cache data while I am updating it
• Multiple clients may decide the cache data is too old and try to update it at the same time
• The PHP instance doing the work may be terminated unexpectedly at any time, and the data should not be corrupted

I can solve the first and last issues by using a transaction, so clients will be able to read the old data until the transaction is committed, when they will immediately see the new data. Any problems will simply cause the transaction to be rolled back.

I can solve the second problem by locking the tables, so that only one process gets a chance to perform the update. By the time any other processes get the lock they will realise they have been beaten to the punch and don't need to update anything.

This means I need to both lock the table and start a transaction. According to the MySQL manual, this is not possible. Starting a transaction releases the locks, and locking a table commits any active transaction.

Is there a way around this, or is there another way entirely to achieve my goal?

Answer 1

This means I need to both lock the table and start a transaction

This is how you can do it:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

For more info, see mysql doc