Pitfalls of monitoring MySQL table activity with stored routines

A friend of mine needed a quick method to monitor a table that was frequently updated. There were several procedures writing to the table several times per second. He needed a quick way of determining how many updates per second the table was getting. The table has only one row, which includes a counter that is increased at each operation. Therefore, the simple plan was: Let's get the counter from the table; Allow N seconds to pass; Get the counter again; The difference between the second counter and the first counter, divided by the number of seconds gives the updates per second. The plan makes sense, and if you run the above commands manually, you get what you want.

However, my friend wanted the update ratio to be a single operation, say like:

SELECT update_ratio();

He went to make a simple function, following the four steps described above.

delimiter //
drop function if exists update_ratio //
create function update_ratio()
RETURNS INT 
begin
     declare sleep_wait int default 5;
     declare start int default 1;
     declare finish int default 1;

     set start = (select counter from mytable);
     do sleep(sleep_wait);
     set finish = (select counter from mytable);
     return (finish-start)/sleep_wait;
end $$
delimiter ;

It seems OK. The function runs without errors, but it always returns zero.
Mystery! Running the statements manually gives always a sensible result. Using triggers to monitor the table shows that indeed it is updated many times per second, but the function returns always zero.
More puzzling is the fact that if we convert the function to a procedure, it gives the wanted result.

The solution to the mystery is found in the MySQL online manual

A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log.

In other words, it means that all tables referenced in a stored functions are locked when the function starts. Therefore the external procedures that were updating the table will have to wait until the function's end before updating. When the function reads from the table, it gets always the same record counter, because no updates were happening in the meantime. That's why the second read is the same as the first one, and the result is zero.

What should you do then?
One option is to convert the function into a procedure:

delimiter //
drop procedure if exists show_update_ratio //
create procedure show_update_ratio()
begin
     declare sleep_wait int default 5;
     declare start int default 1;
     declare finish int default 1;
    
     select counter into start from mytable;
     do sleep(sleep_wait);
     select counter into finish from mytable;
     SET @UPS := (finish-start)/sleep_wait;
end //

That gets the job done. If you want to get the result into a variable, you can do it with two statements.

call show_update_ratio();
select @UPS;

If you don't change the last SET into a SELECT and just display the value.

Another option is using several SQL commands from your application. Also in this case, make sure that you are NOT wrapping this code inside a transaction, or you will get the same result in both queries

# WRONG!
     set autocommit=0;
     BEGIN;
     select counter into @start from mytable;
     set @start = start;
     do sleep(5);
     select counter into @finish from mytable;
     select (@finish - @start) / 5 as UPS;

If you go for this solution (or even the stored procedure), make sure that you are either using autocommit, or commit after each query if you must use a transaction.