How to avoid row locks on both tables joined in an update? I have little knowledge about MySQL internals, but I'm experiencing that in a query like:

UPDATE t1 
  JOIN t2 ON t1.id = t2.id 
SET t1.a = x, t1.b = y, [, ...]
WHERE [ ... ]


rows from both t1 and t2 are locked (they're blocking other SELECTs), although values from table t2 are only read in, they never change.

Since I usually do updates joining critical tables (that are involved in several queries and whose data is most of the times only read, I must get rid of those locks: is there any way to do that?
Should I rewrite those updates with subselects instead of joins?
You don't say what engine the tables are using (show create table $db.$table_name; ).

If you are using the MyISAM engine, you can manually lock the tables.  This isn't recommended for InnoDB tables because you will be over-riding the row level locking mechanism.

If you lock one table, you need to acquire a lock on all you use within the same lock command.  The command to lock both t1 and t2 for your purposes is...

lock table t1 write, t2 read;


After making your changes, you will want to immediately release the locks...

unlock tables;