一个多线程运行Sql的插入数据
set @str=N'insert into ulook(uname,cname,addtime) SELECT a.uname,'''+@cname +''',GETDATE()
FROM per as a WHERE CHARINDEX('',''+LTRIM(id)+'','', '','+@perid+','')>0
AND NOT EXISTS(SELECT 1 FROM ulook WHERE uname = a.uname AND cname = '''+@cname+''')'
exec sp_executesql @str
可是最后还是有重复的数据插入,怎么回事?
set @str=N'insert into ulook(uname,cname,addtime) SELECT a.uname,'''+@cname +''',GETDATE()
FROM per as a WHERE CHARINDEX('',''+LTRIM(id)+'','', '','+@perid+','')>0
AND NOT EXISTS(SELECT 1 FROM ulook WHERE uname = a.uname AND cname = '''+@cname+''')'
exec sp_executesql @str
可是最后还是有重复的数据插入,怎么回事?
只能在数据库级进行约束了,对uname,cname进行唯一性约束。
或者你可以试一下 狠点
select 的时候给来个表锁
set @str=N'insert into ulook(uname,cname,addtime) SELECT a.uname,'''+@cname +''',GETDATE()
FROM per with (xlock, tablock) as a WHERE CHARINDEX('',''+LTRIM(id)+'','', '','+@perid+','')>0
AND NOT EXISTS(SELECT 1 FROM ulook WHERE uname = a.uname AND cname = '''+@cname+''')'
exec sp_executesql @str
select 1 from ulook with (tablockx) where 1<2
你的语句
commit tran
begin tran
select 1 from ulook with (tablockx) where 1>2
set @str=N'insert into ulook(uname,cname,addtime) SELECT a.uname,'''+@cname +''',GETDATE()
FROM per as a WHERE CHARINDEX('',''+LTRIM(id)+'','', '','+@perid+','')>0
AND NOT EXISTS(SELECT 1 FROM ulook WHERE uname = a.uname AND cname = '''+@cname+''')'
exec sp_executesql @str
commit tran
set @str= 'select * FROM dbo.per where dbo.per.id in ('+@perid+')'
exec sp_executesql @str
在程序中无法显示出记录
set @str= 'select * FROM dbo.per where dbo.per.id in ('+@perid+')'
exec sp_executesql @str无法返回数据
select 1 from ulook with (tablockx) where 1>2
set @str=N'insert into ulook(uname,cname,addtime) SELECT a.uname,'''+@cname +''',GETDATE()
FROM per as a WHERE CHARINDEX('',''+LTRIM(id)+'','', '','+@perid+','')>0
AND NOT EXISTS(SELECT 1 FROM ulook WHERE uname = a.uname AND cname = '''+@cname+''')'
exec sp_executesql @str
set @str= 'select * FROM dbo.per where dbo.per.id in ('+@perid+')'
exec sp_executesql @strcommit tran
没有仔细看你的业务,其实一个插入的话,可以不用tran的
如果有大量的业务,我才用tran,否则单独一条更新没有用
会不会是返回了第一个记录集(空集)
你是用ado调用的吧,我记得ado有一个方法是用Recordset的NextRecordset 你可以试试