sql Server 大量数据批量插入 并发 死锁 情况怎么解决啊 这个存储过程能否优化
以下 是存储过程,
其中 Tweeter,Tweets,TweetsCount都存在大量数据，最低千万级的
加了 nolock不行，大家还有什么好的解决办法啊
ALTER PROC [dbo].[TweetsAddOrUpdate]@WebSiteId int,@TweeterId bigint,@WebTweetsId nvarchar(50),@SourceId bigint,@WebSourceId nvarchar(50),@Content nvarchar(4000),@CutWordStatus bit,@OriginalText nvarchar(4000),@ForwardCount int,@CommentCount int,@TweeterPostType nvarchar(100),@TweeterPostTime datetime,@TweetsType int,@Location nvarchar(50),@UpdateTime datetime,@TweetsImportance int,@ProvinceName nvarchar(50),@CityName nvarchar(50)asSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTION declare @ProvinceId int declare @cityId int select @ProvinceId=CityId from City(nolock) where Name=@ProvinceName select @cityId=CityId from City(nolock) where Name=@CityName and ParentId=@ProvinceId DECLARE @TweetsId bigint IF not exists (select TweetsId from Tweets(nolock) where WebTweetsId=@WebTweetsId and WebSiteId=@WebSiteId) begin INSERT INTO [Tweets] ([WebSiteId],[TweeterId],[WebTweetsId],[SourceId] ,[WebSourceId],[Content],[OriginalText] ,[ForwardCount],[CommentCount],[TweeterPostType] ,[TweeterPostTime],[TweetsType],[Location] ,[UpdateTime],TweetsImportance,ProvinceId,CityId) VALUES (@WebSiteId,@TweeterId,@WebTweetsId,@SourceId ,@WebSourceId,@Content,@OriginalText ,@ForwardCount,@CommentCount,@TweeterPostType ,@TweeterPostTime,@TweetsType,@Location ,getDate(),@TweetsImportance,@ProvinceId,@cityId) SET @TweetsId=@@identity end else begin select @TweetsId=TweetsId from Tweets(nolock) where WebTweetsId=@WebTweetsId and WebSiteId=@WebSiteId update Tweets set ForwardCount=@ForwardCount,CommentCount=@CommentCount, UpdateTime=getDate(),TweetsImportance=@TweetsImportance where TweetsId=@TweetsId end update Tweets set SourceId=@TweetsId where WebSourceId=@WebTweetsId if exists (select TweetsId from TweetsCounts(nolock) where TweetsId=@TweetsId and CONVERT(nvarchar(10),UpdateTime,112) =cONVERT(nvarchar(10),GETDATE(),112) ) begin update TweetsCounts set ForwardCount=@ForwardCount,CommentCount=@CommentCount where CONVERT(nvarchar(10),UpdateTime,112) =cONVERT(nvarchar(10),GETDATE(),112) and TweetsId=@TweetsId end else begin INSERT INTO [TweetsCounts] ([TweetsId] ,[ForwardCount] ,[CommentCount] ,[UpdateTime]) VALUES (@TweetsId,@ForwardCount,@CommentCount,GETDATE()) end select @TweetsId COMMIT TRANSACTION
