首页新闻找找看学习计划

SQL Server数据库插入重复记录的问题

1
悬赏园豆:200 [已解决问题] 解决于 2017-02-20 13:41

存储过程中SQL语句如下:

复制代码
IF NOT EXISTS(SELECT 1 FROM [Relations] WHERE [UserId]=@UserId AND [RelativeUserId]=@RelativeUserId AND IsActive=1)
BEGIN
    BEGIN TRANSACTION
    INSERT INTO [Relations]([UserId], [RelativeUserId]) 
    VALUES (@UserId,@RelativeUserId)
    UPDATE [Users] SET FollowingCount=FollowingCount+1 WHERE UserID=@UserId
    UPDATE [Users] SET FollowerCount=FollowerCount+1 WHERE UserID=@RelativeUserId
    COMMIT TRANSACTION
END
复制代码

上面的SQL语句,如果2次操作的 @UserId 与 @RelativeUserId 相同,并且2次操作发生在1秒内,在数据库服务器负载高的情况下,偶尔会出现插入重复记录的情况,也就是 IF NOT EXISTS 的重复检测没有起作用。

请问如何有效地解决这个问题?

dudu的主页 dudu | 高人七级 | 园豆:39830
提问于:2017-02-16 09:34
< >
分享
最佳答案
2

将上面改造一下比如:INERT INTO.....SELECT ...FROM WHERE NOT EXSITS(SELECT 1 FROM...),这样是否可行?


收获园豆:200
Jeffcky | 老鸟四级 |园豆:2549 | 2017-02-16 09:50

已采用你的这个方法,有待观察

BEGIN TRANSACTION
    INSERT INTO [Relations]([UserId], [RelativeUserId])
    SELECT @UserId, @RelativeUserId
    WHERE NOT EXISTS(SELECT 1 FROM [Relations] WHERE [UserId]=@UserId AND [RelativeUserId]=@RelativeUserId AND IsActive=1)

    UPDATE [Users] SET FollowingCount=FollowingCount+1 WHERE UserID=@UserId        
    UPDATE [Users] SET FollowerCount=FollowerCount+1 WHERE UserID=@RelativeUserId    
COMMIT TRANSACTION
dudu | 园豆:39830 (高人七级) | 2017-02-16 11:12

经过临床验证,该药方确实能根治插入重复记录的问题。

dudu | 园豆:39830 (高人七级) | 2017-02-20 13:39

@dudu: 保证药到病除


Jeffcky | 园豆:2549 (老鸟四级) | 2017-02-20 13:40

@Jeffcky: 汗,在结帖后竟然又出现了重复记录,这个方法能降低重复记录发生的概率,但不能根治。

dudu | 园豆:39830 (高人七级) | 2017-02-20 16:16

@dudu: 进一步的解决方法:在存储过程中加上了 SET TRAN ISOLATION LEVEL READ UNCOMMITTED ,尽可能脏读。让2个几乎同时进行的操作,即使第1个操作的事务还没提交,第2个操作也可以读到第1个操作insert的数据,从而减少重复insert。

read uncommitted:这个隔离级别最低啦,可以读取到一个事务正在处理的数据,但事务还未提交,这种级别的读取叫做脏读。

参考文档:SQL Server中的事务与锁

dudu | 园豆:39830 (高人七级) | 2017-02-20 17:03
其他回答(9)
0

1.with updlock,可能要扩大事务范围

2.针对请求参数做一次hash后分片顺序执行。

Daniel Cai | 园豆:10374 (专家六级) | 2017-02-16 09:50
0
BEGIN TRANSACTION
IF NOT EXISTS(SELECT 1 FROM [Relations] WITH(UPDLOCK,HOLDLOCK) WHERE [UserId]=@UserId AND [RelativeUserId]=@RelativeUserId AND IsActive=1)
BEGIN

    INSERT INTO [Relations]([UserId], [RelativeUserId]) 
    VALUES (@UserId,@RelativeUserId)
    UPDATE [Users] SET FollowingCount=FollowingCount+1 WHERE UserID=@UserId
    UPDATE [Users] SET FollowerCount=FollowerCount+1 WHERE UserID=@RelativeUserId

END
COMMIT TRANSACTION

 

这样试试

Yu | 园豆:12944 (专家六级) | 2017-02-16 09:53
1
BEGIN TRANSACTION
IF NOT EXISTS(SELECT 1 FROM [Relations] WITH(XLOCK,ROWLOCK) WHERE [UserId]=@UserId AND [RelativeUserId]=@RelativeUserId AND IsActive=1)
BEGIN
    INSERT INTO [Relations]([UserId], [RelativeUserId]) 
    VALUES (@UserId,@RelativeUserId)
    UPDATE [Users] SET FollowingCount=FollowingCount+1 WHERE UserID=@UserId
    UPDATE [Users] SET FollowerCount=FollowerCount+1 WHERE UserID=@RelativeUserId
END
COMMIT TRANSACTION

再在查询条件上建索引试试

MrNice | 园豆:3008 (老鸟四级) | 2017-02-16 10:00

已采用你提供的SQL并加上了索引,待观察效果

支持(0) 反对(0) dudu | 园豆:39830 (高人七级) | 2017-02-16 10:12

采用该方法后问题依旧

支持(0) 反对(0) dudu | 园豆:39830 (高人七级) | 2017-02-16 10:56

@dudu: 那估计和我之前遇到的就有些不同了

支持(0) 反对(0) MrNice | 园豆:3008 (老鸟四级) | 2017-02-16 11:01
0

MrNice方法没问题,整个查询和update都在一个事务里面并且加了rowlock

czd890 | 园豆:7982 (大侠五级) | 2017-02-16 10:26
0

话说你们都不知道merge into命令吗..

吴瑞祥 | 园豆:28729 (高人七级) | 2017-02-16 10:32

这好像只有Oracle才有吧

支持(0) 反对(0) MrNice | 园豆:3008 (老鸟四级) | 2017-02-16 10:39

@MrNice: sqlserver也有.就mysql没有.

支持(0) 反对(0) 吴瑞祥 | 园豆:28729 (高人七级) | 2017-02-16 10:49
0

找到一个类似的问题:http://bbs.csdn.net/topics/390798474
采用MrNice的方法需要在查询条件上建立唯一索引


JoiWilliam | 园豆:546 (小虾三级) | 2017-02-16 11:23

唯一约束可以避开这个问题,但现实场景不允许,相同的UserId与RelativeUserId,IsActive允许存在多个false值。

支持(0) 反对(0) dudu | 园豆:39830 (高人七级) | 2017-02-16 11:35
0

一般会进行唯一性约束吧,后面的自然而然就失败了

hobotree | 园豆:206 (菜鸟二级) | 2017-02-16 11:34
0
BEGIN TRANSACTION
IF NOT EXISTS(SELECT 1 FROM [Relations] WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE [UserId]=@UserId AND [RelativeUserId]=@RelativeUserId AND IsActive=1)
BEGIN
    INSERT INTO [Relations]([UserId], [RelativeUserId]) 
    VALUES (@UserId,@RelativeUserId)
    UPDATE [Users] SET FollowingCount=FollowingCount+1 WHERE UserID=@UserId
    UPDATE [Users] SET FollowerCount=FollowerCount+1 WHERE UserID=@RelativeUserId
END
COMMIT TRANSACTION

 

idiotcnblogs | 园豆:202 (菜鸟二级) | 2017-02-17 20:49
0

这问题最后是解决了 还是没解决?

小眼睛老鼠 | 园豆:2774 (老鸟四级) | 2017-05-11 17:46
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册