1

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?

0

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;
| improve this answer | |
  • I don't need to lock the tables, but to avoid locks being held on tables from which I only read data during the update. – watery Mar 10 '17 at 20:25
  • I'm making the assumption that you have MyISAM tables based on the behavior you are outlining. The MyISAM engine works on a table lock basis (no mechanism for row level locking). My answer at least lets you have a read lock on one of the tables if this is the case. Please post your DDL (create table statements) and sample data to support a case study if you are using any other engine. – RMathis Mar 10 '17 at 20:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.