1

I need to lock a full table in shared mode. If I execute something like "select 1 from (select * from mytable lock in share mode) t where 1=2", will the query optimizer remove the nested subquery and basically make the query no-op? In my simple experiment, it doesn't (this is what I need). I'm wondering if it's expected by design bahavour and not just an accident. Any other alternatives to lock a full table? Note: I cannot use "LOCK TABLE READ" beause it seems it cannot be promoted into WRITE lock without releasing it first.

MySQL version: 5.1.50 and 5.5.27.

Maxim Mazeev
  • 11
  • 1
  • How about a transaction? How about going back to myisam? Or SELECT FOR UPDATE? Can you give more info on why you're locking the entire table? – Alain Collins Sep 17 '12 at 17:14
  • The problem is to purge a table (delete records based on some condition) and it's required to do some pre-processing to calculate the condition. I'm thinking to START TRANSACTION, lock the table in shared mode so at least other readers are not blocked, perform "calculation", execute DELETE FROM WHERE... and COMMIT. – Maxim Mazeev Sep 17 '12 at 19:47

0 Answers0