Two processes running same time

Last Post 15 Jul 2008 10:47 AM by SwePeso. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
TRACEYSQL
New Member
New Member

--
13 Jul 2008 07:00 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 without no deadlock and the 2nd 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.

What do you think the best method is to actually see this.
I looked at profiler and i see lock released and lock acquired that was rather hard to follow along with.
Also trying to determine what type of lock the being place used Page, Record.

Trying to pint point that the software is placing what type of lock on this range of records and whether they are using the wrong lock and not doing this per the records required.

2nd process runs and it can't do this as the first is locking - not sure if page, entire table thats what i need to find out.


Any suggestions how to record all.

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.
TRACEYSQL
New Member
New Member

--
13 Jul 2008 10:14 AM
Thanks i take a look - but deadlock not any good as it not always deadlocking but just stopping until one process is completely finished.

----
On a 2nd note..........i managed to trace everything...
Now this is the first time i have actually taking the commands and now i want to replay them in query analyzer

One small example:

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, DELTEK.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' etc but having hard job getting
the values out.

How to just run it without having to spend so much time guessing all values - some data i know but not ever value.

TRACEYSQL
New Member
New Member

--
13 Jul 2008 10:16 AM
Do you think that aba_lockinfo is better than the ms one sp_blocker_pss80 i used this in SQL 2000 i am guessing works ok for SQL 2005.

I take a look at the aba_lockinfo too.
SQLUSA
New Member
New Member

--
14 Jul 2008 05:30 AM
quote:

Originally posted by: TRACEYSQL
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 without no deadlock and the 2nd has to wait until first finishes even when the data is different.



Generally if deadlock happens infrequently just rerun the terminated job.

However, since "financial" processing involved, I would either reengineer or schedule them so they don't interfere with each other. As already noted in this thread, SQL Server 2005 deadlock graph is an excellent tool to resolve deadlocks.

The 2nd waiting for 1st to finish condition very likely indicates table lock by the 1st.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/



SwePeso
New Member
New Member

--
15 Jul 2008 01:17 AM
If you are sure the two processes do not touch the same records,
try to incorporate the ROWLOCK table hint.

Then locks are placed only on the current record(s), not the entire page where the record is stored.
This will allow you to run the two processes without locks.

SwePeso
New Member
New Member

--
15 Jul 2008 10:47 AM
I thought PAGE LOCK were default in SQL Server?
SwePeso
New Member
New Member

--
21 Jul 2008 01:09 AM
The closest information I can find is this
http://msdn.microsoft.com/en-us/lib...87373.aspx

But it is not conclusive to what you are saying. Do you have some links for me to read?

I also read about lock escalation here
http://blogs.msdn.com/sqlserverstor...ation.aspx

but it says the limit for lock escalation starts at about 5000 locks, which are clearly more than 20 locks required from OP.


Acceptable Use Policy
---