and the deadlocks are back...

  • Sure:

    CREATE TABLE [dbo].[SmsJob](

    [JobID] [uniqueidentifier] NOT NULL,

    [Name] [nvarchar](50) NOT NULL,

    [OwnerAppName] [nvarchar](50) NOT NULL,

    [OwnerAppVersion] [nvarchar](50) NOT NULL,

    [Pri] [int] NOT NULL,

    [Status] [int] NOT NULL,

    [Size] [int] NOT NULL,

    [MessagesSent] [int] NOT NULL,

    [MessagesCompleted] [int] NOT NULL,

    [PackagedAt] [smalldatetime] NOT NULL,

    [SubmittedAt] [smalldatetime] NOT NULL,

    [CompletedAt] [smalldatetime] NULL,

    [LastUpdatedAt] [smalldatetime] NOT NULL,

    CONSTRAINT [PK_SmsJob] PRIMARY KEY CLUSTERED

    (

    [JobID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • No indexes other than the primary key?

    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
  • and one non clustered index

    CREATE NONCLUSTERED INDEX [IX_SmsJob_PriorityStatus] ON [dbo].[SmsJob]

    (

    [Status] ASC,

    [Pri] ASC

    )

    INCLUDE ( [Size],

    [MessagesCompleted]) 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, FILLFACTOR = 80) ON [PRIMARY]

    GO

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Sapen (1/4/2012)


    ok. The problem is I cant modify the EF generated scripts since they are generated dynamically. I meant if these scripts are hardcoded with the isolation level as serializable and I want to exclude that from the scripts.

    If the isolation level is being set to serializable is the scripts and you can't change the scripts, then you are stuck with the current situation.

  • Yes. I am currently stuck and had a discussion with the developers on the change in isolation level.

    Thanks Gail and Michael.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Hey Guys..I have a quick question here..Looking at my deadlock graph and the isolationlevel being serializable which query is using the isolation level as serializable? the update or select? per my assumption the isolation levels are not considered for selects. So it shd be the update statement thats using the hardcoded isolation level (serializable) instead of using the current database setting for isolation level which is set to RCSI. Below is my deadlock info:

    deadlock-list

    deadlock victim=process3ed3948

    process-list

    process id=process3ed3948 taskpriority=0 logused=0 waitresource=KEY: 6:72057594055229440 (c85b14958736) waittime=4753 ownerId=267795423 transactionname=user_transaction lasttranstarted=2012-01-04T14:02:25.960 XDES=0x4fcb4b950 lockMode=S schedulerid=6 kpid=156 status=suspended spid=159 sbid=2 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-01-04T14:02:25.963 lastbatchcompleted=2012-01-04T14:02:25.960 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=6580 loginname=PCSetadmin isolationlevel=serializable (4) xactid=267795423 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    executionStack

    frame procname=adhoc line=1 stmtstart=102 sqlhandle=0x02000000ee5f9a347525532fdabd8edeaedf298318000da3

    SELECT

    [Project2].[JobID] AS [JobID],

    [Project2].[Name] AS [Name],

    [Project2].[OwnerAppName] AS [OwnerAppName],

    [Project2].[OwnerAppVersion] AS [OwnerAppVersion],

    [Project2].[Pri] AS [Pri],

    [Project2].[Status] AS [Status],

    [Project2].[Size] AS [Size],

    [Project2].[MessagesSent] AS [MessagesSent],

    [Project2].[MessagesCompleted] AS [MessagesCompleted],

    [Project2].[PackagedAt] AS [PackagedAt]

    FROM ( SELECT

    [Extent1].[JobID] AS [JobID],

    [Extent1].[Name] AS [Name],

    [Extent1].[OwnerAppVersion] AS [OwnerAppVersion],

    [Extent1].[SubmittedAt] AS [SubmittedAt]

    [Ext

    frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

    Unknown

    Inputbuf

    Message

    (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7))SELECT

    [Project2].[JobID] AS [JobID],

    [Project2].[Name] AS [Name],

    [Project2].[OwnerAppName] AS [OwnerAppName],

    [Project2].[OwnerAppVersion] AS [OwnerAppVersion],

    [Project2].[Pri] AS [Pri],

    [Project2].[Status] AS [Status],

    [Project2].[Size] AS [Size],

    [Project2].[MessagesSent] AS [MessagesSent],

    [Project2].[MessagesCompleted] AS [MessagesCompleted],

    [Project2].[PackagedAt] AS [PackagedAt]

    FROM ( SELECT

    [Extent1].[JobID] AS [JobID],

    [Extent1].[Name] AS [Name],

    [Extent1].[OwnerAppVersion] AS [OwnerAppVersion],

    [Extent1].[SubmittedAt] AS [SubmittedAt]

    [Ext

    process id=process2d1990bc8 taskpriority=0 logused=312 waitresource=KEY: 6:72057594050248704 (c2eb06fba112) waittime=4753 ownerId=267795422 transactionname=user_transaction lasttranstarted=2012-01-04T14:02:25.957 XDES=0x2e508d950 lockMode=X schedulerid=4 kpid=3032 status=suspended spid=139 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-01-04T14:02:25.963 lastbatchcompleted=2012-01-04T14:02:25.960 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=6580 loginname=PCSetadmin isolationlevel=serializable (4) xactid=267795422 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    executionStack

    frame procname=adhoc line=1 stmtstart=144 sqlhandle=0x02000000d0e338368562522edeee18412fca2f10e520f002

    update [dbo].[SmsJob]

    set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2

    where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))

    frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

    unknown

    inputbuf

    (@0 int,@1 int,@2 datetime2(7),@3 uniqueidentifier,@4 int,@5 int,@6 int)update [dbo].[SmsJob]

    set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2

    where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))

    keylock hobtid=72057594055229440 dbid=6 objectname=PPMOnlineMsgEngine.dbo.SmsJob indexname=PK_SmsJob id=lock25b2e5280 mode=X associatedObjectId=72057594055229440

    resource-list

    owner-list

    owner id=process2d1990bc8 mode=X

    waiter-list

    waiter id=process3ed3948 mode=S requestType=wait

    keylock hobtid=72057594050248704 dbid=6 objectname=PPMOnlineMsgEngine.dbo.SmsJob indexname=IX_SmsJob_PriorityStatus id=lock660787200 mode=RangeS-S associatedObjectId=72057594050248704

    owner-list

    owner id=process3ed3948 mode=RangeS-S

    waiter-list

    waiter id=process2d1990bc8 mode=X requestType=wait

    Thanks

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Can you please advise.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Sapen (1/6/2012)


    Looking at my deadlock graph and the isolationlevel being serializable which query is using the isolation level as serializable? the update or select?

    Both.

    per my assumption the isolation levels are not considered for selects.

    Your assumption is not only incorrect, it's a complete reversal of the truth. The isolation levels (other than serialisable) only affect selects and the duration that the shared locks are held. Serialisable mostly affects selects, but also (I think) updates and maybe deletes

    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
  • Oops you are right...I was off track for sometime...I changed the isolation level to RCSI not only to prevent the concurrency issues but also to prevent dirty reads.

    Thanks Gail

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 9 posts - 16 through 23 (of 23 total)

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