MySQL InnoDB table locking inside stored procedure

Question

I wonder why MySQL does not allow table locking inside a stored procedure.

I have the following SQL statements inside my stored procedure:

 -- Total amount of money for comments
 start transaction;
 select @num_comments := count(*)
 from `comment` c
 where
    c.user_id = user_id and
    c.payment_rejection = 'NO' and
    c.is_recorded = 0;
 update `user` u set account_balance += u.comment_price * @num_comments where u.user_id = user_id;
 update `comment` c set is_recorded = 1 where c.user_id = user_id and c.payment_rejection = 'NO' and c.is_recorded = 0;
 commit;

So I have to lock table comment to prevent any writing to it for it may cause the number of rows selected in the first SQL statement be different from the number of actually updated.

LOCK IN SHARE MODE on your SELECT query (not sure if it works with count(*)).. side note you will/can get transactions timeout if INSERTS run on the locked records and updating takes long... — Raymond Nijland, Oct 27 '13 at 14:10

krokodilko · Answer 1 · 2013-10-28T08:31:18.977

Sir, in your code you can use ROW_COUNT() function instead of SELECT count(*)

According to documentation: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful.

start transaction;

 update `comment` c 
     set is_recorded = 1 
 where c.user_id = v_user_id 
   and c.payment_rejection = 'NO' 
   and c.is_recorded = 0;

 SET @num_comments = row_count();

 update `user` u 
   set account_balance += u.comment_price * @num_comments 
 where u.user_id = v_user_id;

 commit;

In this way there is no need to lock tables, the number of rows cannot change between statements, and a bonus is a higher speed of the whole transaction.

Some comments:
The user_id column is ambiguous in the query:

where u.user_id = user_id;

and the update command updates the whole table instead of rows belong to one user only.
Don't use the same names for variables in the procedure as columns names in the table, the easiest way is to prepend some prefix to variable names to avoid ambiguity, for example:

where u.user_id = var_user_id;

MySQL InnoDB table locking inside stored procedure

1 Answers1