MySQL - LOCK IN SHARE MODE for EXISTS query

Question

I am starting a transaction and I need to check that a record exists, I don't need any values, but I need it to exist for the duration of the transaction for everything to remain valid.

It's VERY unlikely of course that someone will delete at more or less the same time someone else tries to do what this transaction happens, but as with all things concurrent 'very unlikely' isn't good enough.

I don't want to experiment either - I want to know for certain it'll work. Will something like:

SELECT EXISTS(SELECT * FROM tbl1 WHERE ... LOCK IN SHARE MODE);

Work as I expect it to?

For

I would imagine that LOCK IN SHARE MODE is simply implemented as a flag at the parsing stage, telling MySQL to acquire certain locks, thus this isn't a special case.

Against

MySQL treats EXISTS differently to a normal SELECT for obvious reasons. As per it's query optimiser it often reads constants before it starts running the query. An over-eager optimisation could defeat this.

I'm leaning towards 'for' but I want to be certain.

Answer 1

There are multiple type of locks in Mysql. LOCK IN SHARE MODE in a shared mode lock on any rows that are read. So you need to pay attention to your query, how many rows are read (look also at examined column when doing explain).

The isolation level will also affect behaviors of certain locks. I strongly advise to let Mysql take care of the locks. When an update needs to occur it is usually the first come first serve. make sure your Exist query is fast (best is to query on the PK).

If exists (select 1 from tbl1 WHERE ) Then
 perform your update
Else 
Select "record does not exists" 
End if;