July 13, 2008 at 7:07 am
A process within our Financials which does some calculations and build tables is run for two separate companies. I have seen the deadlock occur whilst both run at the same time and then i seen the first run and second is run and has to wait until first finishes even when the data is different.
My theory is :
If process A calls for records 1-10
If process B calls for records 11-20 this process is held waiting because the process A has acquired the lock.
So i need to run the profiler trace and see this exactly that process A put the lock and then B comes along and is waiting.
What do you think the best method is to actually see this.
I looked at profiler and i see lock released and lock acquired.
Also trying to determine what type of lock they used Page, Record.
I think the sp_blocker_pss80 will give me all the locks that are occuring for each processes.
Little example:
72 0 16 306100131 2 PAG 1:210 IX GRANT 20466599 00000000-0000-0000-0000-000000000000
71 0 16 306100131 2 PAG 1:210 IS GRANT 20463068 00000000-0000-0000-0000-000000000000
72 0 16 306100131 0 PAG 1:224 IX GRANT 20466599 00000000-0000-0000-0000-000000000000
72 0 16 306100131 0 RID 1:224:0 X GRANT 20466599 00000000-0000-0000-0000-000000000000
I can use dbcc page command.
I see PAGE 1:210 and PAGE 1:224 im assuming same page is locked.
How to see all PAGES in a table.
If anyone got any other way of doing i apprecaite their views.
Any suggestions how to record all.
July 13, 2008 at 10:42 am
It's just me, but I'd forget about spending any further time on that... it's time to start looking at the code and fixing it. You already know that they conflict... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2008 at 11:49 am
That was just example i not done it on the large process - im getting all my eggs ready.
I have to determine this is true first ? that it is doing this type of locks?
One small example:
Got a little profiler as example here
declare @p1 int
set @p1=1073741879
declare @p2 int
set @p2=180150097
declare @p5 int
set @p5=8
declare @p6 int
set @p6=1
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(6),@P2 varchar(10),@P3 varchar(2),
@P4 varchar(10)',N'SELECT S.ORG_SEC_PROF_CD, P.PROF_ORG_SEC_FL
FROM ORG_SEC_GRP_SETUP S, ORG_SEC_PROFILE P WHERE
S.ORG_SEC_GRP_CD = @P1
AND S.ORG_SEC_PROF_CD = P.ORG_SEC_PROF_CD AND S.COMPANY_ID = @P2
AND S.S_MODULE_CD = @P3
AND P.COMPANY_ID = @P4
',@p5 output,@p6 output,@p7 output,'BUSSOL','1','AO','1'
select @p1, @p2, @p5, @p6, @p7
Now i want to copy this and just run in SQL Query with some plans and executions.
How do i get the parameters for the @p1 WHICH give me
1073741879 and this is not the value for ORG_SEC_GRP_CD the value is the BUSSOL
I just want to do
select part where S.ORG_SEC_GRP_CP = 'BUSS0L' i know this value
Is it everything in output BUSSOL = @P1, 1 = @P2, AO = @P3 upt to how many shown.
Sorry never really got this far with the analysis before now i want to....
How would you tackle this case - when someone reports the two processes one is running and the other just sits there - i got to go to lock level correct? and to the profiler and ensure that the first process is locking correct - because why would the 2nd just sit and only run after the first.
July 13, 2008 at 11:55 am
You're looking at the code that SQL Server generated to run some user code... better if you look at the user code. Look for long transactions to begin with...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2008 at 12:08 pm
Looking now - figured out the parameters and what to put in to re run that one single part.
I have to copy the RPC Completed into note pad edit it so it fits on page then paste in
SQL that seems long winded - if i just copy in sql analyzer it does not paste it cause
it too long i guess.
Now to get to the long duration one.
Got showplan on too.........(that whole another learning part )
This is fun part of it - wish i had all day just to do this 🙂
I get there 🙂
July 13, 2008 at 7:20 pm
The one process creates 53 brand new tables with no indexes and every select statement is a table scan does not read any table with a index seek at all.
The main tables it joins back to after build it so called temp tables is doing like + '%' rather than = the column when it just built its temp table so that interesting.
I get that fixed first with indexes first before running two of these together. First things first.
July 14, 2008 at 6:19 am
From the sounds of things, you should also check some of the basic operations in the code like access order and/or join order. You need to make sure that the processes put locks on the tables in the same order, TableA, TableB, TableC. You don't want one locking TableC then TableA while the other locks TableA then TableC. Small things like this frequently lead to deadlocks.
Although, it sounds like the worst part of your problem is the complete lack of indexes. Be judicious where you place the cluster on those tables, you only get one. Make sure you do make a clustered index though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2008 at 8:41 am
Looks like it may be old ADOc code maybe? In any case, I would look for a complete rewrite to avoid row-by-row processing if at all possible. Indexing these 'permanent temp tables' could provide benefit too, assuming they have any reasonable amounts of rows put in them. BTW, missing indexes can often lead to horrible concurrency problems because DML against base tables can lead to table locks of various flavors which will keep everything from accessing them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply