需求:
用户反复点击按钮,可能造成重复提交。
可以限制用户,30s内只能提交1次.
这种问题还可以衍生为,每个用户每天只能提交三次,等等.

MySQL
create table t (username varchar(10) ,ts timestamp default current_timestamp);

大柴30s内只能提交一次.

insert into t (username)
select '大柴' from dual
where
(
select if(count(*)<=0,1,0) from t
where username='大柴'
and ts>now()-interval '30' second for update
)=1;

或者

insert into t (username)
select '大柴' from dual
where not exists
(
select 1 from t where
username='大柴' and
ts>now()-interval '30' second for update
);

大柴每天只能提交三次

insert into t (username)
select '大柴' from dual
where
(
select if(count(*)<=2,1,0) from t
where username='大柴'
and ts>date_format(now(),'%Y-%m-%d 00:00:00') for update
)=1;

Oracle实现类似需求,还有更多的方式.
比如,小柴每天只能提交三次.但是由于没有使用锁,并发情况下,会产生多插入的情况.

create table t (username varchar2(10),ts date default sysdate);
insert into
(
select username from t where username='小柴' and
(select count(*) from t where username='小柴')<=2 and
ts>trunc(sysdate)
with check option
) values('小柴');

或者

MERGE INTO t t1
USING (
SELECT COUNT(*) AS c
FROM t
WHERE username = '小柴'
AND ts > trunc(SYSDATE)
) t2 ON (t2.c > 2)
WHEN NOT MATCHED THEN INSERT(USERNAME) VALUES ('小柴');

Oracle这两种方式,都不能处理并发的问题.大并发情况下,可能会多Insert记录.

ITPub博客

数据库层防止重复提交

最新文章