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

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?

Answer 2

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 3

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.