January 4, 2012 at 2:18 pm
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
January 4, 2012 at 2:25 pm
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
January 4, 2012 at 2:27 pm
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
January 4, 2012 at 6:23 pm
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.
January 4, 2012 at 6:41 pm
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
January 6, 2012 at 8:20 am
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
January 6, 2012 at 8:21 am
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
January 6, 2012 at 8:37 am
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
January 6, 2012 at 8:47 am
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