mysql 行锁定需知:
1、表的存储引擎需为InnoDB
2、为查询的条件创建索引
3、for update 关键字
4、行锁定查询需要放置在事物中
例:
Begin;
select `id_sale` from `red_sale` where `id_sale` = 1 for update;
commit;
或者:
set autocommit=0; select `id_sale` from `red_sale` where `id_sale` = 1 for update;
释放 set autocommit=1;
存储过程:
例子:
/****创建存储过程***/
DELIMITER $$ /***将DELIMITER 暂转换成$$,以便数据库将存储过程编译完成后,再执行***/
DROP PROCEDURE IF EXISTS `wbs`.`p_grad_red`$$ /***如果存在则删除这个存储过程***/
CREATE DEFINER = 'root'@'localhost' PROCEDURE `wbs`.`p_grad_red`(in saleId int, in gradId int, in repeatGrad int, out detailId int)
BEGIN
DECLARE num INT; /***定义变量****/
START TRANSACTION; /**事物开启***/
select id_sale from red_sale where id_sale=saleId for update;
/***判断是否允许同个红包活动内,同一个人重复抢**/
IF repeatGrad THEN
SET num=0;
ELSE
select count(1) into num from red_detail where id_sale=saleId and id_grad=gradId;
END IF;
/****判断这个人是否已抢过红包***/
IF num > 0 THEN
SET detailId=-1;
ELSE
SET detailId=(select id_detail from red_detail where id_sale=saleId and id_grad=0 order by id_detail asc LIMIT 0,1);
IF detailId > 0 THEN
update red_detail set id_grad = gradId , grad_time=now() where id_detail=detailId;
ELSE
SET detailId=0;
END IF;
END IF;
select detailId;
COMMIT;
END$$
DELIMITER ;
/*** 回调存储过程 param1:红包ID, param2:抢红包用户ID, param3:是否允许在一个红包活动中同一个账号抢多次(1:允许,0:不允许), param4: 返回结果 return{0:该红包已被抢空, -1:该用户已抢过一次不可重复抢} ***/ call p_grad_red(1,2,1,@resultId);
select @resultId;
//mysql 表锁定与解除锁
查看当前被锁的表
show OPEN TABLES where In_use > 0;
// 查看数据库中表的状态,是否被锁;
SHOW PROCESSLIST;
//杀掉被锁的表
kill 2726;