How to deliberately lock a MySQL row such that even SELECT will return an error?

Question

I'm trying to use MySQL row locking to basically emulate a MuteEx on a row. Lets say that my table has 2 columns, an id and a text field, and three entries (1,a) (2,b) and (3,c). SELECT * FROM table; would return these results. I can lock a specific row the normal way.

START TRANSACTION;
BEGIN;
SELECT * FROM table WHERE id = '2' FOR UPDATE;

However, if from a second connection I were to SELECT * from table. It would return all 3 results. Is there a way for row level locking to basically prevent any SELECT from seeing/using a row that is locked? Basically I'm trying to prevent anyone from using the row that is currently being used/manipulated, or even viewing the row as its data (since it's being used/manipulated) can't be trusted to be accurate at the time of a SELECT.

Answer 1

If you set the transaction isolation level to SERIALIZABLE, InnoDB wil implicity append LOCK IN SHARE MODE to all SELECT statements.

This mode conflicts with the locks placed by SELECT FOR UPDATE and the SELECTs will lock.

Note, though, that InnoDB may lock more rows than satisfy the WHERE condition. This is because it locks all rows scanned, not only those matched.

Say, you have an index on col1 and this query:

SELECT  *
FROM    mytable
WHERE   col1 = 1
        AND col2 = 2
FOR UPDATE

uses this index.

This will lock all records with col1 = 1, even those with col2 <> 2

Answer 2

You need a LOCK IN SHARE MODE. Using it with SELECT guarantees no one else is locking any rows with FOR UPDATE.

e.g.

Client A does SELECT * FROM table WHERE type=2 FOR UPDATE

Client B does SELECT * FROM table LOCK IN SHARE MODE and hangs here

Client A writes/INSERTs/UPDATEs something then does a COMMIT

Client B now un-freezes and resumes processing