MySQL难点

select ... for update将会锁住任何其他connection的select for update, update, insert。当然还要放入到transaction中。

CREATE DEFINER=`qfu`@`%` PROCEDURE `updateIndex`(IN batchNum INT)
BEGIN

  DROP TEMPORARY TABLE IF EXISTS temp;
  create temporary table temp(id int);
  START TRANSACTION;
  insert into temp
  select id from Dow_Jones_Index2 where isUpdated=0 limit batchNum for update;
  update Dow_Jones_Index2 set isUpdated=1 where id in (select id from temp);
  commit;
  select * from Dow_Jones_Index2 where id in (select id from temp);
  DROP TEMPORARY TABLE IF EXISTS temp;  
END

临时表生命期是session内，如果数据库连接断了，临时表也会消失。相当于mssql中的#temp

---
explain select * from Student where id=5;
可以解释是否用了index。
Using where: 没用到index
Using index: index含有所有信息不需要再查表。The difference is that "Using index" doesn't need a lookup from the index to the table, while "Using index condition" sometimes has to.
Using index condition: 需要再查表

clear cache for performance tuning (workbench)
RESET QUERY CACHE;

ref:
select for update