DeadLock while adding a record in table if i have one non-clustered index for 100 users concurrent load at a time

Question

DeadLock while adding a record in table if i have one non-clustered index for 100 users concurrent load at a time

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
June 28, 2010 at 7:52 am

#220547

I have following table in a db. If I create non clustered index on any of following columns:
ProgramId
StartDateTimeOnClient
EndDateTimeOnClient
and put some load of 100 concurrent users on an SP which simply add single record per user in this table generates DeadLocks and the test for 100 users load fails by blocking each transaction.
I have to use these basic Indexes above for generating reports and it wont give any way to do so.
CREATE TABLE [dbo].[ProgramTracking](
[Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SessionId] [nvarchar](50) NULL,
[ProgramId] [bigint] NOT NULL,
[ProgramTitle] [nvarchar](550) NULL,
[ProgramVersion] [nvarchar](10) NULL,
[TenantId] [int] NULL,
[TenantName] [nvarchar](50) NULL,
[CategoryId] [int] NULL,
[CategoryName] [nvarchar](50) NULL,
[EstimatedDuration] [int] NULL,
[Copyrights] [nvarchar](50) NULL,
[LicenceInformation] [nvarchar](1000) NULL,
[TargetIndustry] [nvarchar](500) NULL,
[TargetAudience] [nvarchar](500) NULL,
[ServerTimeZone] [nvarchar](50) NULL,
[StartDateTimeOnServer] [datetime] NULL,
[EndDateTimeOnServer] [datetime] NULL,
[ClientTimeZone] [nvarchar](50) NULL,
[StartDateTimeOnClient] [datetime] NULL,
[EndDateTimeOnClient] [datetime] NULL,
[IsCompleted] [bit] NULL,
[AddedDateTime] [datetime] NULL,
[UserId] [bigint] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Size] [bigint] NULL,
[LiveSessionId] [int] NULL,
[IsLive] [bit] NULL,
[LiveSessionProfileId] [int] NULL,
[LiveSessionTitle] [nvarchar](512) NULL,
[IsPublicInternally] [bit] NULL,
[IsFeatured] [bit] NULL,
[IsPublic] [bit] NULL,
[IsAcquired] [bit] NULL,
[IsPublishedOn] [nchar](20) NULL,
[LiveSessionScheduledStartTime] [datetime] NULL,
[LiveSessionScheduledEndTime] [datetime] NULL,
[EmailAddress] [nvarchar](400) NULL,
[LoginId] [nvarchar](200) NULL,
[Roles] [nvarchar](200) NULL,
[Groups] [nvarchar](max) NULL,
[LiveProgramViewerType] [tinyint] NULL,
[TransactionId] [nvarchar](500) NULL,
[LicenseId] [bigint] NULL,
CONSTRAINT [PK_ProgramTracking] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProgramTracking] WITH CHECK ADD CONSTRAINT [FK_ProgramTracking_ProgramMetaInfo] FOREIGN KEY([ProgramId])
REFERENCES [dbo].[ProgramMetaInfo] ([Id])
GO
ALTER TABLE [dbo].[ProgramTracking] CHECK CONSTRAINT [FK_ProgramTracking_ProgramMetaInfo]
GO
The deadlock graph caught with the insert statement of this table only.
Please help me what is the solution?
Shamshad Ali.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post the deadlock graph

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 2

Shamshad Ali

SSCarpal Tunnel

Points: 4822

June 28, 2010 at 8:03 am

#1185751

.rar file attached.

Thanks for helping

Shamshad Ali.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

Please post the complete code of the procedure called VTLSDBLoadTest.dbo.SaveTrackingRecord

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 4

Here the SP code

Alter PROCEDURE [dbo].[SaveTrackingRecord]

(

@XmlDocument xml,

@CheckFlag int ,

@TrackingId bigint OUTPUT

)

AS

Begin

SET NOCOUNT ON;

DECLARE @DocHandle int

DECLARE @ProgramTrackingId bigint

SET @TrackingId = 0

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

IF(@CheckFlag = 0)

Begin

Declare

@SessionId nvarchar(50),

@ProgramId int,

@ProgramTitle nvarchar(550),

@ProgramVersion nvarchar(10),

@TenantId int,

@TenantName nvarchar(50),

@CategoryId int,

@CategoryName nvarchar(50),

@EstimatedDuration smallint,

@Copyrights nvarchar(50),

@LicenceInformation nvarchar(1000),

@TargetIndustry nvarchar(500),

@TargetAudience nvarchar(500),

@ServerTimeZone nvarchar(50),

@StartDateTimeOnServer datetime,

@EndDateTimeONServer datetime,

@ClientTimeZone nvarchar(50),

@StartDateTimeOnClient datetime,

@EndDateTimeONClient datetime,

@IsCompleted bit,

@AddedDateTime datetime,

@userid int,

@FirstName nvarchar(50),

@Lastname nvarchar(50),

@Size int,

@LiveSessionId int,

@LiveSessionTitle nvarchar(512),

@IsLive bit,

@LiveSessionProfileId int,

@IsPublic bit,

@IsPublicInternally bit,

@IsFeatured bit,

@IsAcquired bit,

@IsPublishedOn varchar(40),

@LiveSessionScheduledStartTime datetime,

@LiveSessionScheduledEndTime datetime,

@EmailAddress varchar(400),

@LoginId varchar(200),

@Roles varchar(200),

@Groups varchar(1000),

@LiveProgramViewerType tinyint,

@TransactionId nvarchar(500),

@LicenseId int,

@CampaignIds varchar(500)

SELECT

@SessionId = SessionId

, @ProgramId = ProgramId

, @ProgramTitle = ProgramTitle

, @ProgramVersion = ProgramVersion

, @TenantId = TenantId

, @TenantName = TenantName

, @CategoryId = CategoryId

, @CategoryName = CategoryName

, @EstimatedDuration = EstimatedDuration

, @Copyrights = Copyrights

, @LicenceInformation = LicenceInformation

, @TargetIndustry = TargetIndustry

, @TargetAudience = TargetAudience

, @ServerTimeZone = ServerTimeZone

, @StartDateTimeOnServer = (CASE StartDateTimeOnServer when '1/1/1900' then null else StartDateTimeOnServer end)

, @EndDateTimeONServer = (CASE EndDateTimeONServer when '1/1/1900' then null else EndDateTimeONServer end)

, @ClientTimeZone = ClientTimeZone

, @StartDateTimeOnClient = (CASE StartDateTimeOnClient when '1/1/1900' then null else StartDateTimeOnClient end)

, @EndDateTimeONClient = (CASE EndDateTimeONClient when '1/1/1900' then null else EndDateTimeONClient end)

, @IsCompleted = IsCompleted

, @AddedDateTime = AddedDateTime

, @userid = UserId

, @FirstName = FirstName

, @Lastname = Lastname

, @Size = [Size]

, @LiveSessionId = LiveSessionId

, @LiveSessionTitle = LiveSessionTitle

, @IsLive = IsLive

, @LiveSessionProfileId = LiveSessionProfileId

, @IsPublic = IsPublic

, @IsPublicInternally = IsPublicInternally

, @IsFeatured = IsFeatured

, @IsAcquired = IsAcquired

, @IsPublishedOn = IsPublishedOn

, @LiveSessionScheduledStartTime = (Case LiveSessionScheduledStartTime when '1/1/1900' then null else LiveSessionScheduledStartTime end)

, @LiveSessionScheduledEndTime = (Case LiveSessionScheduledEndTime when '1/1/1900' then null else LiveSessionScheduledEndTime end)

, @EmailAddress = EmailAddress

, @LoginId = LoginId

, @Roles = Roles

, @Groups = Groups

, @LiveProgramViewerType = LiveProgramViewerType

, @TransactionId = TransactionId

, @LicenseId = LicenseId

, @CampaignIds = CampaignIds

FROM OPENXML(@DocHandle,'//ProgramTracking') WITH ([SessionId] [nvarchar](50), [ProgramId] [int], [ProgramTitle] [nvarchar](550), [ProgramVersion] [nvarchar](10), [TenantId] [int], [TenantName] [nvarchar](50),

[CategoryId] [int], [CategoryName] [nvarchar](50), [EstimatedDuration] [smallint], [Copyrights] [nvarchar](50), [LicenceInformation] [nvarchar](1000),

[TargetIndustry] [nvarchar](500), [TargetAudience] [nvarchar](500), [ServerTimeZone] [nvarchar](50), [StartDateTimeOnServer] [datetime], [EndDateTimeONServer] [datetime],

[ClientTimeZone] [nvarchar](50), [StartDateTimeOnClient] [datetime], [EndDateTimeONClient] [datetime], [IsCompleted] [bit], [AddedDateTime] [datetime],

[UserId] [int], [FirstName] [nvarchar](50), [Lastname] [nvarchar](50), [Size] [int], [LiveSessionId] [int], [LiveSessionTitle] [nvarchar](512), [IsLive] [bit],

[LiveSessionProfileId] [int], [IsPublic] [bit], [IsPublicInternally] [bit], [IsFeatured] [bit], [IsAcquired] [bit], [IsPublishedOn] [varchar](40), [LiveSessionScheduledStartTime] [datetime],

[LiveSessionScheduledEndTime] [datetime], [EmailAddress] [varchar](400), [LoginId] [varchar](200), [Roles] [varchar](200), [Groups] [varchar](1000), [LiveProgramViewerType] [tinyint],

[TransactionId] [nvarchar](500), [LicenseId] [int], CampaignIds varchar(500))

-- select * from @TempXML

INSERT INTO ProgramTracking

(

[SessionId]

,[ProgramId]

,[ProgramTitle]

,[ProgramVersion]

,[TenantId]

,[TenantName]

,[CategoryId]

,[CategoryName]

,[EstimatedDuration]

,[Copyrights]

,[LicenceInformation]

,[TargetIndustry]

,[TargetAudience]

,[ServerTimeZone]

,[StartDateTimeOnServer]

,[EndDateTimeOnServer]

,[ClientTimeZone]

,[StartDateTimeOnClient]

,[EndDateTimeOnClient]

,[IsCompleted]

,[AddedDateTime]

,[UserId]

,[FirstName]

,[LastName]

,[Size]

,[LiveSessionId]

,[LiveSessionTitle]

,[IsLive]

,[LiveSessionProfileId]

,[IsPublic]

,[IsPublicInternally]

,[IsFeatured]

,[IsAcquired]

,[IsPublishedOn]

,[LiveSessionScheduledStartTime]

,[LiveSessionScheduledEndTime]

,[EmailAddress]

,[LoginId]

,[Roles]

,[Groups]

,[LiveProgramViewerType]

,[TransactionId]

,[LicenseId]

)

values (

@SessionId

, @ProgramId

, @ProgramTitle

, @ProgramVersion

, @TenantId

, @TenantName

, @CategoryId

, @CategoryName

, @EstimatedDuration

, @Copyrights

, @LicenceInformation

, @TargetIndustry

, @TargetAudience

, @ServerTimeZone

, @StartDateTimeOnServer

, @EndDateTimeONServer

, @ClientTimeZone

, @StartDateTimeOnClient

, @EndDateTimeONClient

, @IsCompleted

, @AddedDateTime

, @userid

, @FirstName

, @Lastname

, @Size

, @LiveSessionId

, @LiveSessionTitle

, @IsLive

, @LiveSessionProfileId

, @IsPublic

, @IsPublicInternally

, @IsFeatured

, @IsAcquired

, @IsPublishedOn

, @LiveSessionScheduledStartTime

, @LiveSessionScheduledEndTime

, @EmailAddress

, @LoginId

, @Roles

, @Groups

, @LiveProgramViewerType

, @TransactionId

, @LicenseId )

-- FROM OPENXML(@DocHandle,'//ProgramTracking') WITH ProgramTracking

SET @ProgramTrackingId = SCOPE_IDENTITY()

SET @TrackingId = @ProgramTrackingId

INSERT INTO ProgramContentTracking

SELECT @SessionId, @ProgramTrackingId, ProgramContentId, @TenantId , @TenantName,

@userid, @FirstName, @LastName, Url, Title, AddedDateTime , [Size] , IsPrimaryStream

FROM OPENXML(@DocHandle,'//ProgramContentTracking') WITH (ProgramContentId bigint, Url varchar(1000), Title nvarchar(1100),

AddedDateTime datetime, Size bigint, IsPrimaryStream bit)

--DECLARE @ProgramId bigint

--DECLARE @CampaignIds varchar(500)

-- SELECT @CampaignIds = CampaignIds, @ProgramId = ProgramId from @TempXML

-- FROM OPENXML(@DocHandle,'//ProgramTracking') WITH (CampaignIds varchar(500) , ProgramId bigint)

IF(@CampaignIds != '0')

Begin

Set @CampaignIds = @CampaignIds + ':'

Declare @Pos1 Int

Declare @pos2 Int

-- Start from first character

Set @Pos1=1

Set @Pos2=1

While @Pos1<Len(@CampaignIds)

Begin

Set @Pos1 = CharIndex(':',@CampaignIds,@Pos1)

Insert Into ProgramCampaignTracking (CampaignId, ProgramId, ProgramTrackingId)

values (Cast(Substring(@CampaignIds,@Pos2,@Pos1-@Pos2) As bigint), @ProgramId, @ProgramTrackingId)

Set @Pos2=@Pos1+1

Set @Pos1 = @Pos1+1 End

End

ELSE IF(@CheckFlag = 1 Or @CheckFlag = 3)

BEGIN

SET @TrackingId = 0

INSERT INTO ProgramTrackingDetail

SELECT ProgramTrackingId = ProgramTrackingId, ProgramTrackingEventsId, ProgramTrackingEventsName , Id

FROM OPENXML (@DocHandle,'//ProgramTracking//Events',2)

WITH

(

Id int '@Id',

ProgramTrackingId bigint '@ProgramTrackingId' ,

ProgramTrackingEventsId int '@ProgramTrackingEventsId',

ProgramTrackingEventsName nvarchar(50) '@ProgramTrackingEventsName'

)

INSERT into ProgramTrackingSubDetail

SELECT ProgramTrackingDetail.Id , ProgramTrackingParametersId, ProgramTrackingParametersName,ProgramTrackingParametersValue

FROM

(

SELECT * FROM OPENXML (@DocHandle,'//ProgramTracking//Parameters',2)

WITH

(

EventId int '@EventId',

ProgramTrackingId bigint '@ProgramTrackingId' ,

ProgramTrackingParametersId int '@ProgramTrackingParametersId',

ProgramTrackingParametersName nvarchar(50) '@ProgramTrackingParametersName',

ProgramTrackingParametersValue nvarchar(50) '@ProgramTrackingParametersValue'

)

) As TrackingDetail

inner join ProgramTrackingDetail with (nolock) on ProgramTrackingDetail.ProgramTrackingId = TrackingDetail.ProgramTrackingId

and ProgramTrackingDetail.TrackingEventSequenceId = TrackingDetail.EventId

INSERT INTO ProgramContentTrackingDetail

Select ProgramContentTracking.Id, ProgramTrackingEventsId,ProgramTrackingEventsName,TrackingEventSequenceId

FROM

(

SELECT * FROM OPENXML (@DocHandle,'//ProgramContentTracking//Events',2)

WITH

(

TrackingEventSequenceId int '@Id',

ProgramTrackingId bigint '@ProgramTrackingId' ,

ProgramContentId int '../@ProgramContentId',

ProgramTrackingEventsId int '@ProgramTrackingEventsId',

ProgramTrackingEventsName nvarchar(50) '@ProgramTrackingEventsName'

)

) AS ContentTrackingDetail

INNER JOIN ProgramContentTracking with (nolock) on ProgramContentTracking.ProgramTrackingId = ContentTrackingDetail.ProgramTrackingId

and ProgramContentTracking.ProgramContentId = ContentTrackingDetail.ProgramContentId

INSERT INTO ProgramContentTrackingSubDetail

SELECT ProgramContentTrackingDetail.Id , ProgramTrackingParametersId, ProgramTrackingParametersName,ProgramTrackingParametersValue

FROM

(

SELECT * FROM OPENXML (@DocHandle,'//ProgramContentTracking//Parameters',2)

WITH

(

EventId int '@EventId',

ProgramTrackingParametersId int '@ProgramTrackingParametersId',

ProgramTrackingId bigint '../@ProgramTrackingId' ,

ProgramTrackingParametersName nvarchar(50) '@ProgramTrackingParametersName',

ProgramTrackingParametersValue nvarchar(50) '@ProgramTrackingParametersValue'

)

) As TrackingDetail

inner join ProgramContentTracking with (nolock) on ProgramContentTracking.ProgramTrackingId = TrackingDetail.ProgramTrackingId

inner join ProgramContentTrackingDetail with (nolock) on ProgramContentTrackingDetail.ProgramContentTrackingId = ProgramContentTracking.Id

and ProgramContentTrackingDetail.TrackingEventSequenceId = TrackingDetail.EventId

IF(@CheckFlag = 3)

BEGIN

--Declare @EndDateTimeONServer datetime

--Declare @EndDateTimeONClient datetime

--Declare @EstimatedDuration int

--Declare @IsCompleted bit

--Declare @LicenseId bigint

--Declare @userid bigint

--Declare @IsLive bit

Declare @ProgramId2 bigint

SELECT @EndDateTimeONServer = EndDateTimeONServer , @EndDateTimeONClient = EndDateTimeONClient ,

@IsCompleted = IsCompleted , @ProgramTrackingId = ProgramTrackingId , @EstimatedDuration = EstimatedDuration ,

@LicenseId = LicenseId , @userid = UserId , @IsLive = IsLive

FROM OPENXML(@DocHandle,'//ProgramTracking') WITH

(

EndDateTimeONServer datetime '@EndDateTimeOnServer' ,

EndDateTimeONClient datetime '@EndDateTimeOnClient' ,

EstimatedDuration int '@EstimatedDuration' ,

IsCompleted bit '@IsCompleted' ,

LicenseId bigint '@LicenseId' ,

UserId bigint '@UserId' ,

IsLive bit '@IsLive' ,

ProgramTrackingId bigint '@Id'

)

IF(@IsCompleted = 1)

Begin

Update ProgramTracking

Set EndDateTimeONServer = @EndDateTimeONServer ,

EndDateTimeONClient = @EndDateTimeONClient ,

IsCompleted = @IsCompleted ,

EstimatedDuration = @EstimatedDuration

Where Id = @ProgramTrackingId

IF (@IsLive = 0 and @LicenseId > 0)

Begin

exec [UpdateTotalViews] @LicenseId , @userid

End

Else

Begin

Update ProgramTracking

Set EndDateTimeONServer = @EndDateTimeONServer ,

EndDateTimeONClient = @EndDateTimeONClient ,

EstimatedDuration = @EstimatedDuration

Where Id = @ProgramTrackingId

End

END

EXEC sp_xml_removedocument @DocHandle

End

GO

How do I call this SP with parameters:

declare @p1 xml

set @p1=convert(xml,N'<Tracking><ProgramTracking SessionId="2kf3smmhhlxmm2m3d524ru45" ProgramId="4166" CampaignIds="0" ProgramTitle="Gum disease" ProgramVersion="4" TenantId="1"

TenantName="SWW" CategoryId="1966" CategoryName="Music" EstimatedDuration="786" Copyrights="" LicenceInformation="" TargetIndustry="" TargetAudience="" ServerTimeZone="5"

StartDateTimeOnServer="2010/06/23 19:17:22" EndDateTimeOnServer="" ClientTimeZone="5" StartDateTimeOnClient="2010/06/23 19:17:29" EndDateTimeOnClient="" AddedDateTime="2010/06/23 19:17:22"

UserId="1" FirstName="Guest" LastName="Guest" IsCompleted="False" Size="0" LiveSessionProfileId="0" LiveSessionId="0" IsLive="False" IsPublic="False" IsPublicInternally="False"

IsFeatured="True" IsAcquired="False" IsPublishedOn="Market" EmailAddress="" LoginId="Guest" Roles="Guest" LiveSessionScheduledStartTime="" LiveSessionScheduledEndTime="" LiveSessionTitle=""

LiveProgramViewerType="" Groups="" TransactionId="" LicenseId="0"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129274" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1"

TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest" Title="Gum Disease, Diagnosis And Treatment (Health Gum Disease)3.wmv"

Url="http://ltapp.softech.us/UploadedFiles/188/903/1903/uid_1903_Gum Disease, Diagnosis And Treatment (Health Gum Disease)3.wmv" AddedDateTime="2010/06/23 19:17:22" Size="0"

IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129270" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest"

LastName="Guest" Title="Welcome" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0" IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129271"

SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest" Title="Gum Disease" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0"

IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129272" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest"

Title="Flap Sugery" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0" IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129703" SessionId="2kf3smmhhlxmm2m3d524ru45"

TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest" Title="Completion at 100%" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0" IsPrimaryStream="False"/>

<ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129704" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest" Title="Completion at 75%" Url=""

AddedDateTime="2010/06/23 19:17:22" Size="0" IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="129705" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1"

TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest" Title="Completion at 50%" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0" IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0"

ProgramContentId="129706" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest" Title="Completion at 25%" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0"

IsPrimaryStream="False"/><ProgramContentTracking ProgramTrackingId="0" ProgramContentId="0" SessionId="2kf3smmhhlxmm2m3d524ru45" TenantId="1" TenantName="SWW" UserId="1" FirstName="Guest" LastName="Guest"

Title="Closed Caption" Url="" AddedDateTime="2010/06/23 19:17:22" Size="0" IsPrimaryStream="False"/></Tracking>')

declare @p2 bigint

set @p2=16501

exec SaveTrackingRecord @XmlDocument=@p1,@TrackingId=@p2 output,@CheckFlag =0

select @p2

go

I have other tables in this SP that are also insreting records, but when we start load, it starts deadlock on the first INSERT query. The all data is basically an xml and it is passed from a WCF service.

Please feel free to ask any other question if you have missing any information.

Shamshad Ali.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

I really find it very disturbing that a number of concurrent single-row inserts can cause deadlock. Maybe someone else can explain why this is happening.

To fix the problem I think it is enough to explicitly apply an exclusive tablock to the insert statement.

Like this:

INSERT INTO ProgramTracking WITH (TABLOCKX)

...

Of course this will also affect the possible concurrency so it is definitely not an optimal solution, but it should at least solve your deadlock problem.

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 6

I don't think that HINT will solve my problem, its locking at the first insert statement <Master table>, when it returns the ID then it will be possible to insert in the detail table. Hint would be case when it does skip to next INSERT statement <detail table> and failure on second insertion. May be I am wrong.

I might also suspect the table desing structure, lots of same case issues with other tables. I suspect it should be simple as I am taking incharge and consultancy to solve the performance issue with such a DB which has 200 tables with 16 GB of data.

We are providing this db for customers and they will be using it, the Load test is what we are using to see how much app and db can accept load and calculate its limitations on a given hardware.

We have a target of 1000 users but its failing on 100 users.

We are using a dedicated SQL Server 2008 64 bit Enterprise Edition, 8 GB RAM on Physical Server with Core 2 Quad CPU Q9650 3 GHz 3 GHz. The app server is with same configuration on another server.

We are using VS Team System <another power full physical machine for test controller> and Rig Env. to test load. that is very short story to explain if someone has questions in their mind.

Shamshad Ali.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 7

Shamshad Ali (6/29/2010)
I don't think that HINT

???

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 8

I am talking about "WITH (TABLOCKX)" - These are called HINT -

Shamshad Ali.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

I'll check the indexes and queries this evening, if I have the time, see if there's anything you can improve. The vast majority of deadlocks can be resolved completely by optimising code and/or tuning indexes.

Adding a TablockX hint will almost certainly remove deadlocks, with the downside of completely eliminating concurrent access. Not usually a good option unless you're running batch jobs and know that there will only be one connection at a time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Stefan_G SSCertifiable Points: 6609 More actions · Answer 10

Shamshad Ali (6/29/2010)
I don't think that HINT will solve my problem, its locking at the first insert statement <Master table>, when it returns the ID then it will be possible to insert in the detail table. Hint would be case when it does skip to next INSERT statement <detail table> and failure on second insertion. May be I am wrong.
I might also suspect the table desing structure, lots of same case issues with other tables. I suspect it should be simple as I am taking incharge and consultancy to solve the performance issue with such a DB which has 200 tables with 16 GB of data.

Well, I am pretty sure that the hint will avoid this particular deadlock.

It is possible that if you add this hint you will get another deadlock somewhere else, but it is also possible that everything will work fine.

The main problem with adding the hint is that it will cause a performance problem. To find out how severe that problem is you need to test (which is what you are doing anyway).

As I said, I do not understand the root cause for this deadlock. I thought SQL server would protect itself from any possible deadlocks in cases like this.

Someone else might be able to to explain what is really happening.

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 11

Thanks for your comments back again. I respect all who are taking interest and helping me out. I really need to solve this issue.

Shamshad Ali.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 12

I have tried to reproduce your problem.

I am able to get deadlocks using the following script:

-- Create a test table with an index

use tempdb

go

if object_id('deadtest') is not null drop table deadtest

go

create table deadtest (

id int identity(1,1) primary key clustered,

uid uniqueidentifier,

filler char(300)

)

create index ix1 on deadtest(uid) WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)

-- run the following concurrently in two different tabs in SSMS

use tempdb

go

set nocount on

declare @i int = 0

while @i < 100000 begin

insert into deadtest (uid) values (newid())

set @i=@i+1

end

But, if I remove the WITH clause from the index or change it to

create index ix1 on deadtest(uid) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I am not able to provoke a deadlock.

Are you 100% sure that you allow row locks on your non-clustered index on this table ?

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 13

My friend, I used following script to create Three non-clustered indexes:

CREATE NONCLUSTERED INDEX [IX_ProgramTracking_ProgramId] ON [dbo].[ProgramTracking]

(

[ProgramId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ProgramTracking_StartDateTimeOnClient] ON [dbo].[ProgramTracking]

(

[StartDateTimeOnClient] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ProgramTracking_EndDateTimeOnClient] ON [dbo].[ProgramTracking]

(

[EndDateTimeOnClient] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Excluding the Clustered index (script already mentioned with CREATE TABLE - on first page), if I create any single non-clustered index, deadlock appears. These script contain both row_Lock ON/OFF incase of INT and datetime fields.

Also the way you are testing is different from mine. You are using a single connection from same machine from Management Studio. We are using some sort of real environment test in which the concurrent users with different connections and different parameters are used in the Load Test. Rig of different machines are participating with Test controller to generate a real scenario of 100 concurrent users attacking on application. This is what making it a real environment scenario to findout the limitations on a given hardware.

Shamshad Ali.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 14

Shamshad Ali (6/29/2010)
My friend, I used following script to create Three non-clustered indexes:
CREATE NONCLUSTERED INDEX [IX_ProgramTracking_ProgramId] ON [dbo].[ProgramTracking]
(
[ProgramId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ProgramTracking_StartDateTimeOnClient] ON [dbo].[ProgramTracking]
(
[StartDateTimeOnClient] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ProgramTracking_EndDateTimeOnClient] ON [dbo].[ProgramTracking]
(
[EndDateTimeOnClient] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Please try changing all your non-clustered indexes to ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

If you still get a deadlock after this change, please post the new deadlock graph. I believe that the kind of deadlock you posted first is caused by not allowing row locks on the IX_ProgramTracking_ProgramId. If you still get a deadlock after the change it is probably caused by something else.

Also the way you are testing is different from mine. You are using a single connection from same machine from Management Studio. We are using some sort of real environment test in which the concurrent users with different connections and different parameters are used in the Load Test. Rig of different machines are participating with Test controller to generate a real scenario of 100 concurrent users attacking on application. This is what making it a real environment scenario to findout the limitations on a given hardware.

Of course your test is different from mine. I dont have access to your full environment so I can not run your real tests.

Note that I am actually using two separate connetions in SSMS just to try to provoke deadlocks. These two connections are issuing insert statements as fast as possible. If there is any chance of deadlock this kind of test will almost for sure be able to trigger it.

Of course it is possible that something else in your test is also provoking a deadlock. That is why I am asking you to make my suggested change to the IX_ProgramTracking_ProgramId index and rerun the test.