sql2012 运行事务发生死锁

qq_35636618 2016-11-08 12:20:20

匹量更新数据：
public void BulkMerge(IEnumerable<SessionInfoEntity> Entities, string CinemaCode,
DateTime StartDate, DateTime EndDate, int UserId)
{
if (StartDate < DateTime.Now)
{
StartDate = DateTime.Now;
}
EndDate = EndDate.AddDays(1);

using (var connection = DbConnectionFactory.OpenSqlConnection())
{
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.MergeSession";
cmd.Parameters.AddWithValue("@sessions", Entities.ToList().ToDataTable());
cmd.Parameters.AddWithValue("@CinemaCode", CinemaCode);
cmd.Parameters.AddWithValue("@StartTime", StartDate);
cmd.Parameters.AddWithValue("@EndTime", EndDate);
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.ExecuteNonQuery();
}
}

sql存储过程:
ALTER PROCEDURE [dbo].[MergeSession]
@sessions SessionInfoTemp READONLY,
@CinemaCode nvarchar(10),
@StartTime datetime,
@EndTime datetime,
@UserId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
MERGE SessionInfo with(serializable) AS ts
USING
@sessions AS ns
ON ts.CCode = ns.CCode and ts.SCode = ns.SCode and ts.StartTime = ns.StartTime and ts.UserID = ns.UserID
WHEN NOT MATCHED BY TARGET THEN
INSERT( [CCode], [SCode], [ScreenCode], [StartTime], [FilmCode], [FilmName], [Duration],
[Language], [UpdateTime], [StandardPrice], [LowestPrice], [IsAvalible], [PlaythroughFlag],
[Dimensional], [Sequence], [UserID], [DingXinUpdateTime], [ListingPrice], [FeatureNo])
VALUES(ns.[CCode], ns.[SCode], ns.[ScreenCode], ns.[StartTime], ns.[FilmCode], ns.[FilmName], ns.[Duration],
ns.[Language], ns.[UpdateTime], ns.[StandardPrice], ns.[LowestPrice], ns.[IsAvalible], ns.[PlaythroughFlag],
ns.[Dimensional], ns.[Sequence], ns.[UserID], ns.[DingXinUpdateTime], ns.[ListingPrice], ns.[FeatureNo])
WHEN NOT MATCHED BY SOURCE AND ts.CCode = @CinemaCode AND ts.UserID = @UserId AND ts.StartTime > @StartTime AND ts.StartTime < @EndTime THEN
DELETE
WHEN MATCHED THEN
UPDATE SET ts.[CCode] = ns.[CCode], ts.[SCode] = ns.[SCode], ts.[ScreenCode] = ns.[ScreenCode],
ts.[StartTime] = ns.[StartTime], ts.[FilmCode] = ns.[FilmCode], ts.[FilmName] = ns.[FilmName], ts.[Duration] = ns.[Duration],
ts.[Language] = ns.[Language], ts.[UpdateTime] = ns.[UpdateTime], ts.[StandardPrice] = ns.[StandardPrice],
ts.[LowestPrice] = ns.[LowestPrice],ts.[IsAvalible] = ns.[IsAvalible],ts.[PlaythroughFlag] = ns.[PlaythroughFlag],
ts.[Dimensional] = ns.[Dimensional],ts.[Sequence] = ns.[Sequence],ts.[UserID] = ns.[UserID],
ts.[DingXinUpdateTime] = ns.[DingXinUpdateTime],ts.[ListingPrice] = ns.[ListingPrice],ts.[FeatureNo] = ns.[FeatureNo];
END

结果：