存储过程中的MySQL InnoDB表锁定

Question

我不明白为什么MySQL不允许在存储过程中进行表锁定。

我的存储过程中有以下SQL语句：

 -- 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;

所以我必须锁定 comment 表，以防止任何写入操作，因为这可能会导致第一个 SQL 语句选中的行数与实际更新的行数不同。

- Agent Coop

---

在您的SELECT查询上使用LOCK IN SHARE MODE（不确定是否适用于count(*)）。。顺便说一句，如果INSERT运行在锁定记录上并且更新时间很长，您将/可以获得事务超时。 - Raymond Nijland

Answer 1

先生，您的代码中可以使用ROW_COUNT()函数代替SELECT count(*)。

根据文档：http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

如果最后一条语句是UPDATE、DELETE或INSERT，则ROW_COUNT()返回被更改、删除或插入的行数。对于其他语句，该值可能没有意义。

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;

通过这种方式，就不需要锁定表格，语句之间的行数不会发生变化，而且整个事务的速度也更快。

一些注释:
在查询中，user_id 列存在歧义：

where u.user_id = user_id;

更新命令会更新整个表而不是属于一个用户的行。
在过程中不要使用与表中列名相同的变量名，最简单的方法是在变量名前加上一些前缀以避免歧义，例如：

where u.user_id = var_user_id;

- krokodilko