存储过程同时插入两条相同数据 插入时间完全相同

ALTER PROC [dbo].[Insert]
    @Tid Int
AS
BEGIN

	IF NOT EXISTS(SELECT 1 FROM Table WHERE TId = @Tid)
	BEGIN
		INSERT INTO Table (
                                                         INSERTDATE,
                                                         TID
                                                     )
                                    VALUES (
                                                        GETDATE(),
                                                         @Tid
                                                     );
         END

END

TID是其他表的主键，主要为了避免重复插入的

这是什么情况。。。

是代码的问题？

求解

36 个解决方案

#1

检查表上是否有触发器

#2

改了一下表名，代码没问题，触发器的可能性比较大

--CREATE TABLE test (INSERTDATE datetime,TID int )
create PROC [dbo].[Insert]
    @Tid Int
AS
BEGIN
 
    IF NOT EXISTS(SELECT 1 FROM test WHERE TId = @Tid)
    BEGIN
        INSERT INTO test (
                                                         INSERTDATE,
                                                         TID
                                                     )
                                    VALUES (
                                                        GETDATE(),
                                                         @Tid
                                                     );
         END
 
END



[Insert] 1
SELECT * FROM test

/*
INSERTDATE              TID
----------------------- -----------
2014-05-27 10:20:13.350 1

*/

#3

链接池+高并发？

#4

可能调用的代码有bug，连调了两次

#5

同时插入两条相同数据 插入时间完全相同    这不符合正常逻辑吗！！

#6

如果要避免数据重复，建议INSERTDATE,TID联合作唯一索引

#7

引用 2 楼 DBA_Huangzj 的回复:

改了一下表名，代码没问题，触发器的可能性比较大

--CREATE TABLE test (INSERTDATE datetime,TID int )
create PROC [dbo].[Insert]
    @Tid Int
AS
BEGIN
 
    IF NOT EXISTS(SELECT 1 FROM test WHERE TId = @Tid)
    BEGIN
        INSERT INTO test (
                                                         INSERTDATE,
                                                         TID
                                                     )
                                    VALUES (
                                                        GETDATE(),
                                                         @Tid
                                                     );
         END
 
END



[Insert] 1
SELECT * FROM test

/*
INSERTDATE              TID
----------------------- -----------
2014-05-27 10:20:13.350 1

*/

我也试过好几次 都没有问题 但是每周都会出现这种情况 
检查过了 没有触发器

#8

个别情况下的重复，有可能是并发执行引起的

放在事务中，是否存在判断时候加上锁提示
 IF NOT EXISTS(SELECT 1 FROM test   WITH(xlock,rowlock) WHERE TId = @Tid)

#9

引用 3 楼 xdashewan 的回复:

链接池+高并发？

不懂。。

#10

引用 4 楼 hujiiori 的回复:

可能调用的代码有bug，连调了两次

我重新调试了一遍 无法重复提交 。
这种情况偶尔会出现

#11

那可能是并发导致的，你的库并发很高吗？

#12

引用 6 楼 rockyljt 的回复:

如果要避免数据重复，建议INSERTDATE,TID联合作唯一索引

其实再把TID做唯一索引就好 可是重复的时候还会报错啊

#13

引用 11 楼 DBA_Huangzj 的回复:

那可能是并发导致的，你的库并发很高吗？

并发的确很高 

#14

引用 8 楼 x_wy46 的回复:

个别情况下的重复，有可能是并发执行引起的

放在事务中，是否存在判断时候加上锁提示
 IF NOT EXISTS(SELECT 1 FROM test   WITH(xlock,rowlock) WHERE TId = @Tid)

请问 “WITH(xlock,rowlock) ”是起什么作用的？

#15

引用 14 楼 linkinqieerxi 的回复:

Quote: 引用 8 楼 x_wy46 的回复:

个别情况下的重复，有可能是并发执行引起的

放在事务中，是否存在判断时候加上锁提示
 IF NOT EXISTS(SELECT 1 FROM test   WITH(xlock,rowlock) WHERE TId = @Tid)

请问 “WITH(xlock,rowlock) ”是起什么作用的？

锁住行

#16

用这个试试，为了编译成功，我把你的table换成test了

ALTER PROC [dbo].[Insert] @Tid INT
AS
    BEGIN
        BEGIN
            INSERT  INTO test
                    ( INSERTDATE ,
                      TID
                    )
                    SELECT  GETDATE() ,
                            @Tid
                    FROM    test
                    WHERE   NOT EXISTS ( SELECT 1
                                         FROM   test
                                         WHERE  TId = @Tid )
        END

    END

#17

引用 12 楼 linkinqieerxi 的回复:

Quote: 引用 6 楼 rockyljt 的回复:

如果要避免数据重复，建议INSERTDATE,TID联合作唯一索引

其实再把TID做唯一索引就好 可是重复的时候还会报错啊

那你的存储过程或触发器写得有问题了，若批量插入数据时将出错了

#18

引用 15 楼 DBA_Huangzj 的回复:

Quote: 引用 14 楼 linkinqieerxi 的回复:

Quote: 引用 8 楼 x_wy46 的回复:

个别情况下的重复，有可能是并发执行引起的

放在事务中，是否存在判断时候加上锁提示
 IF NOT EXISTS(SELECT 1 FROM test   WITH(xlock,rowlock) WHERE TId = @Tid)

请问 “WITH(xlock,rowlock) ”是起什么作用的？

锁住行

这样有作用吗？

我刚刚想到一种情况 
对应的程序有一个批量插入的功能 就是后台遍历插入 会比较慢 正在执行遍历插入时候重复点击批量插入 出现了这种情况 ？

#19

ALTER PROC [dbo].[Insert]
    @Tid Int
AS
BEGIN
		INSERT INTO [Table] (INSERTDATE,)
        SELECT @Tid,GETDATE() FROM [TABLE] WHERE TId <> @Tid
END

#20

不带有select的insert语句不能用hints。也就是单纯的Insert into values不能用with 这些。你的这个说法，应该不会造成连时间都相同的数据（假设你的时间是系统生成而不是你手动指定的）。多个人同时点insert还有点可能

#21

引用 16 楼 DBA_Huangzj 的回复:

用这个试试，为了编译成功，我把你的table换成test了

ALTER PROC [dbo].[Insert] @Tid INT
AS
    BEGIN
        BEGIN
            INSERT  INTO test
                    ( INSERTDATE ,
                      TID
                    )
                    SELECT  GETDATE() ,
                            @Tid
                    FROM    test
                    WHERE   NOT EXISTS ( SELECT 1
                                         FROM   test
                                         WHERE  TId = @Tid )
        END

    END

哦
请问你写的这种方法和我原来的那种有什么区别吗？

#22

把判断直接写在insert过程中，算一步，你那个写法算2步

#23

引用 22 楼 DBA_Huangzj 的回复:

把判断直接写在insert过程中，算一步，你那个写法算2步

但如果楼主是高并发的情况下，这样会导致数据丢失的吧，高并发产生的数据不一定都是重复无用数据

#24

引用 23 楼 xdashewan 的回复:

Quote: 引用 22 楼 DBA_Huangzj 的回复:

把判断直接写在insert过程中，算一步，你那个写法算2步

但如果楼主是高并发的情况下，这样会导致数据丢失的吧，高并发产生的数据不一定都是重复无用数据

不会丢失，只把不存在的数据插入，逻辑来说和你的一样，只是插入的过程直接判断

#25

引用 5 楼 duanzi_peng 的回复:

同时插入两条相同数据 插入时间完全相同    这不符合正常逻辑吗！！

那个时间精度才到毫秒，一个毫秒内很多条数据完全是有可能的

#26

引用 24 楼 DBA_Huangzj 的回复:

Quote: 引用 23 楼 xdashewan 的回复:

Quote: 引用 22 楼 DBA_Huangzj 的回复:

把判断直接写在insert过程中，算一步，你那个写法算2步

但如果楼主是高并发的情况下，这样会导致数据丢失的吧，高并发产生的数据不一定都是重复无用数据

不会丢失，只把不存在的数据插入，逻辑来说和你的一样，只是插入的过程直接判断

我是这样测试的，如下，写了一个存储过程，用sqlquerystress来开循环和线程

create table t
(
	id int,
	Createdate datetime
)
create  index index_1 on t(id,createdate)

alter proc test_p
@i int
as
begin
	begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit
end


--执行调用方式
declare @i int
set @i=cast(  rand()*100000 as int)
exec test_p @i	



然后用
select COUNT(1),id,Createdate from t
group by id,Createdate
having(COUNT(1))>1

来判断是否存在重复数据

select不做任何提示的时候，1000循环*30线程重复的很多，就不说了

当select中用(xlock,rowlock)锁提示的时候，竟然还有极个别重复的
用tablockx就没有了

当然你的那种方式也是没有问题的，我稍微改了一下，加了个top 1，如下，要不然是死循环

alter proc test_p
@i int
as
begin
   INSERT  INTO t
     ( id, Createdate )
   SELECT top 1 @i, GETDATE() FROM    test
      WHERE   NOT EXISTS ( SELECT 1 FROM   t WHERE  id = @i )
                                         
end



还请版主指教，为啥
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit

这种方式是不行的，还是锁不住？
我在手动测试的时候，第一个窗口
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=12345)
		begin 
			insert into t values (12345,GETDATE());
		end
不提交，同样的脚本，第二个窗口中就被阻塞了，按道理也是没问题的啊

#27

1、我是记得只有tablockx才能锁得住。
2、 http://technet.microsoft.com/zh-cn/library/ms187373.aspx中有一句：获取行级别锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能对索引键而不是实际的数据行采用锁。

引用 26 楼 x_wy46 的回复:

Quote: 引用 24 楼 DBA_Huangzj 的回复:

Quote: 引用 23 楼 xdashewan 的回复:

Quote: 引用 22 楼 DBA_Huangzj 的回复:

把判断直接写在insert过程中，算一步，你那个写法算2步

但如果楼主是高并发的情况下，这样会导致数据丢失的吧，高并发产生的数据不一定都是重复无用数据

不会丢失，只把不存在的数据插入，逻辑来说和你的一样，只是插入的过程直接判断

我是这样测试的，如下，写了一个存储过程，用sqlquerystress来开循环和线程

create table t
(
	id int,
	Createdate datetime
)
create  index index_1 on t(id,createdate)

alter proc test_p
@i int
as
begin
	begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit
end


--执行调用方式
declare @i int
set @i=cast(  rand()*100000 as int)
exec test_p @i	



然后用
select COUNT(1),id,Createdate from t
group by id,Createdate
having(COUNT(1))>1

来判断是否存在重复数据

select不做任何提示的时候，1000循环*30线程重复的很多，就不说了

当select中用(xlock,rowlock)锁提示的时候，竟然还有极个别重复的
用tablockx就没有了

当然你的那种方式也是没有问题的，我稍微改了一下，加了个top 1，如下，要不然是死循环

alter proc test_p
@i int
as
begin
   INSERT  INTO t
     ( id, Createdate )
   SELECT top 1 @i, GETDATE() FROM    test
      WHERE   NOT EXISTS ( SELECT 1 FROM   t WHERE  id = @i )
                                         
end



还请版主指教，为啥
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit

这种方式是不行的，还是锁不住？
我在手动测试的时候，第一个窗口
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=12345)
		begin 
			insert into t values (12345,GETDATE());
		end
不提交，同样的脚本，第二个窗口中就被阻塞了，按道理也是没问题的啊

#28

--查了一下，说是在锁定键上建立唯一索引（约束）xlock,rowlock才有效

--我测了一下，好像也是的，如下修改存储过程，为了防止还有重复的，UI上也反馈不出来，特意加了个异常的处理，
--如果异常，则记录下来，反反复复测试了即便，2000次循环*30线程，
--结果是预期的，没有重复的
--不知道能否说明这个问题：锁定键上建立唯一索引（约束）xlock,rowlock才有效？

--锁这块一直没弄清楚，我的理解应该分两类吧，范围和锁定方式，
--像tablock，rowlock这些事范围，xlock，updlock这些事锁定方式
--不知道对不对？

drop index index_1 on t


create unique index index_1 on t(id,createdate)


truncate table t


create table logmsg(id int,msg nvarchar(100))

alter proc test_p
@i int
as
begin
	begin try
		begin tran
			if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
			begin 
				insert into t values (@i,GETDATE());
			end
		commit
	end try
	begin catch
		insert into logmsg values (@i,'数据重复异常');
	end catch
end



select COUNT(1),id,Createdate from t
group by id,Createdate
having(COUNT(1))>1


select * from logmsg

引用 27 楼 DBA_Huangzj 的回复:

1、我是记得只有tablockx才能锁得住。
2、 http://technet.microsoft.com/zh-cn/library/ms187373.aspx中有一句：获取行级别锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能对索引键而不是实际的数据行采用锁。

Quote: 引用 26 楼 x_wy46 的回复:

Quote: 引用 24 楼 DBA_Huangzj 的回复:

Quote: 引用 23 楼 xdashewan 的回复:

Quote: 引用 22 楼 DBA_Huangzj 的回复:

把判断直接写在insert过程中，算一步，你那个写法算2步

但如果楼主是高并发的情况下，这样会导致数据丢失的吧，高并发产生的数据不一定都是重复无用数据

不会丢失，只把不存在的数据插入，逻辑来说和你的一样，只是插入的过程直接判断

我是这样测试的，如下，写了一个存储过程，用sqlquerystress来开循环和线程

create table t
(
	id int,
	Createdate datetime
)
create  index index_1 on t(id,createdate)

alter proc test_p
@i int
as
begin
	begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit
end


--执行调用方式
declare @i int
set @i=cast(  rand()*100000 as int)
exec test_p @i	



然后用
select COUNT(1),id,Createdate from t
group by id,Createdate
having(COUNT(1))>1

来判断是否存在重复数据

select不做任何提示的时候，1000循环*30线程重复的很多，就不说了

当select中用(xlock,rowlock)锁提示的时候，竟然还有极个别重复的
用tablockx就没有了

当然你的那种方式也是没有问题的，我稍微改了一下，加了个top 1，如下，要不然是死循环

alter proc test_p
@i int
as
begin
   INSERT  INTO t
     ( id, Createdate )
   SELECT top 1 @i, GETDATE() FROM    test
      WHERE   NOT EXISTS ( SELECT 1 FROM   t WHERE  id = @i )
                                         
end



还请版主指教，为啥
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit

这种方式是不行的，还是锁不住？
我在手动测试的时候，第一个窗口
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=12345)
		begin 
			insert into t values (12345,GETDATE());
		end
不提交，同样的脚本，第二个窗口中就被阻塞了，按道理也是没问题的啊

#29

我个人认为，讨论锁的问题，首先要确定隔离级别，不同的隔离级别对锁的表现不同，虽然排它锁那些是一样的，但是不同的级别对锁的范围和持续时间都有不同的影响，比如可序列化，就有可能加范围锁。你可以看到刚才那篇文档：
ROWLOCK
指定通常采用页锁或表锁时，采用行锁。 在从 SNAPSHOT 隔离级别操作的事务中指定时，除非将 ROWLOCK 与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合，否则不会取得行锁。都是要说明在什么隔离级别下的行为。既然你已经有测试结果，大胆相信也是可以的，我也不敢说熟，每次都要打开MSDN或者Books online来看才放心。

#30

忘了引用

引用 28 楼 x_wy46 的回复:

--查了一下，说是在锁定键上建立唯一索引（约束）xlock,rowlock才有效

--我测了一下，好像也是的，如下修改存储过程，为了防止还有重复的，UI上也反馈不出来，特意加了个异常的处理，
--如果异常，则记录下来，反反复复测试了即便，2000次循环*30线程，
--结果是预期的，没有重复的
--不知道能否说明这个问题：锁定键上建立唯一索引（约束）xlock,rowlock才有效？

--锁这块一直没弄清楚，我的理解应该分两类吧，范围和锁定方式，
--像tablock，rowlock这些事范围，xlock，updlock这些事锁定方式
--不知道对不对？

drop index index_1 on t


create unique index index_1 on t(id,createdate)


truncate table t


create table logmsg(id int,msg nvarchar(100))

alter proc test_p
@i int
as
begin
	begin try
		begin tran
			if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
			begin 
				insert into t values (@i,GETDATE());
			end
		commit
	end try
	begin catch
		insert into logmsg values (@i,'数据重复异常');
	end catch
end



select COUNT(1),id,Createdate from t
group by id,Createdate
having(COUNT(1))>1


select * from logmsg

Quote: 引用 27 楼 DBA_Huangzj 的回复:

1、我是记得只有tablockx才能锁得住。
2、 http://technet.microsoft.com/zh-cn/library/ms187373.aspx中有一句：获取行级别锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能对索引键而不是实际的数据行采用锁。

Quote: 引用 26 楼 x_wy46 的回复:

Quote: 引用 24 楼 DBA_Huangzj 的回复:

Quote: 引用 23 楼 xdashewan 的回复:

Quote: 引用 22 楼 DBA_Huangzj 的回复:

把判断直接写在insert过程中，算一步，你那个写法算2步

但如果楼主是高并发的情况下，这样会导致数据丢失的吧，高并发产生的数据不一定都是重复无用数据

不会丢失，只把不存在的数据插入，逻辑来说和你的一样，只是插入的过程直接判断

我是这样测试的，如下，写了一个存储过程，用sqlquerystress来开循环和线程

create table t
(
	id int,
	Createdate datetime
)
create  index index_1 on t(id,createdate)

alter proc test_p
@i int
as
begin
	begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit
end


--执行调用方式
declare @i int
set @i=cast(  rand()*100000 as int)
exec test_p @i	



然后用
select COUNT(1),id,Createdate from t
group by id,Createdate
having(COUNT(1))>1

来判断是否存在重复数据

select不做任何提示的时候，1000循环*30线程重复的很多，就不说了

当select中用(xlock,rowlock)锁提示的时候，竟然还有极个别重复的
用tablockx就没有了

当然你的那种方式也是没有问题的，我稍微改了一下，加了个top 1，如下，要不然是死循环

alter proc test_p
@i int
as
begin
   INSERT  INTO t
     ( id, Createdate )
   SELECT top 1 @i, GETDATE() FROM    test
      WHERE   NOT EXISTS ( SELECT 1 FROM   t WHERE  id = @i )
                                         
end



还请版主指教，为啥
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=@i )
		begin 
			insert into t values (@i,GETDATE());
		end
	commit

这种方式是不行的，还是锁不住？
我在手动测试的时候，第一个窗口
begin tran
		if not exists(select 1 from t with(xlock,rowlock)  where id=12345)
		begin 
			insert into t values (12345,GETDATE());
		end
不提交，同样的脚本，第二个窗口中就被阻塞了，按道理也是没问题的啊

#31

引用 29 楼 DBA_Huangzj 的回复:

我个人认为，讨论锁的问题，首先要确定隔离级别，不同的隔离级别对锁的表现不同，虽然排它锁那些是一样的，但是不同的级别对锁的范围和持续时间都有不同的影响，比如可序列化，就有可能加范围锁。你可以看到刚才那篇文档：
ROWLOCK
指定通常采用页锁或表锁时，采用行锁。 在从 SNAPSHOT 隔离级别操作的事务中指定时，除非将 ROWLOCK 与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合，否则不会取得行锁。都是要说明在什么隔离级别下的行为。既然你已经有测试结果，大胆相信也是可以的，我也不敢说熟，每次都要打开MSDN或者Books online来看才放心。

我找到答案了，稍后分享出来

#32

好奇怪的问题

#33

引用 31 楼 x_wy46 的回复:

Quote: 引用 29 楼 DBA_Huangzj 的回复:

我个人认为，讨论锁的问题，首先要确定隔离级别，不同的隔离级别对锁的表现不同，虽然排它锁那些是一样的，但是不同的级别对锁的范围和持续时间都有不同的影响，比如可序列化，就有可能加范围锁。你可以看到刚才那篇文档：
ROWLOCK
指定通常采用页锁或表锁时，采用行锁。 在从 SNAPSHOT 隔离级别操作的事务中指定时，除非将 ROWLOCK 与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合，否则不会取得行锁。都是要说明在什么隔离级别下的行为。既然你已经有测试结果，大胆相信也是可以的，我也不敢说熟，每次都要打开MSDN或者Books online来看才放心。

我找到答案了，稍后分享出来

请把答案分享出来吧。

#34

引用 33 楼 linkinqieerxi 的回复:

Quote: 引用 31 楼 x_wy46 的回复:

Quote: 引用 29 楼 DBA_Huangzj 的回复:

我个人认为，讨论锁的问题，首先要确定隔离级别，不同的隔离级别对锁的表现不同，虽然排它锁那些是一样的，但是不同的级别对锁的范围和持续时间都有不同的影响，比如可序列化，就有可能加范围锁。你可以看到刚才那篇文档：
ROWLOCK
指定通常采用页锁或表锁时，采用行锁。 在从 SNAPSHOT 隔离级别操作的事务中指定时，除非将 ROWLOCK 与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合，否则不会取得行锁。都是要说明在什么隔离级别下的行为。既然你已经有测试结果，大胆相信也是可以的，我也不敢说熟，每次都要打开MSDN或者Books online来看才放心。

我找到答案了，稍后分享出来

请把答案分享出来吧。

http://bbs.csdn.net/topics/390798474他已经分享出来了

#35

我这边也测试了一下 表中其他列为唯一索引 需要避免重复的列非索引 确实没有再出现重复 学习了 谢谢各位！

#36

这应该是高并发引起的，可是使用唯一索引试试