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

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.

---

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

---

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

---

Actually, the row data can be trusted even while you are manipulating it.

If you start a transaction from one connection, other connections will not see any of your changes until you commit the transaction.

---

I don't know whether there is a native, locking specific mechanism to do that, but my first instict would be to give the table a status column (e.g. locked) and setting that to 1 when locking the row. A select sensitive of this would then always add a WHERE locked != '1' condition to any query.

On a side note, I don't know what you are doing, but isn't this a task that should be done a level or two above the database engine?