mySQL locking for simple multiple selects

Question

I have the following simple two-selects sequence within a stored procedure:

SELECT * FROM mytable WHERE <conditions>;
SELECT id FROM mytable ORDER BY id DESC LIMIT 1;

where id is the primary key of mytable.

The overall purpose is to select some rows from mytable (including the newest ones - ie the ones with the heighest id values) and then, with the second select, get the id of the last/newest row in the same table.

Question: how do I ensure that no new records gets inserted between the first and the second select?

I was going to use:

TRANSACTION
select <1..> FOR UPDATE;
select <2..>;
COMMIT

but will that always work and is there a better way?

Or can do something different to achieve the goal of getting the heighest id value from the first select without having the second select?

Answer 1

When you haven't changed your isolation level, you are using the default of REPEATABLE READ which already solves this problem.

To check which isolation level you're using you can execute the following query:

SELECT @@global.tx_isolation, @@session.tx_isolation;

The global value is the default value for newly created sessions. The sessions value is of course the value you are currently using.

You can set it explicitly for your session with

SET SESSION tx_isolation='REPEATABLE-READ';

With REPEATABLE-READ you can simply do this:

START TRANSACTION;
SELECT * FROM mytable WHERE <conditions>;
SELECT id FROM mytable ORDER BY id DESC LIMIT 1;
COMMIT;

When another session inserts rows into the table during your 2 selects, this row won't show up inside your transaction.

I posted more details about isolation levels in my answer here.

If you want to combine the two queries into one, you could do this:

SELECT m.*, s.maxid 
FROM mytable m 
CROSS JOIN (SELECT MAX(id) AS maxid FROM mytable) s 
WHERE <conditions>;