Database Deadlocks

[brandonbollin]

Working a new gig these days and they're getting a TON of database deadlocks. They're using, "with(nolock)" on every SQL query as far as I can see. That said, I still don't have PROD access so I can't totally verify that this is true. Still, on everything I've seen, they have the nolock in place so I wouldn't think they'd be seeing this many deadlocks. Has anyone seen an environment getting a lot of these? How did you fix it?

Here's a sample error message from the IDM job log:

Running pass failed with unhandled exception - {err_classname=ToSAPIdentity, err_category=ERROR, 
err_passname=AssignABAPprivs, err_where=runToPass, 
err_exception=com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 310) was deadlocked

[former_member2987 · Accepted solution]

So many possibilities, so little time...

Few things I can think of to check / consider?

Multiple dispatchers on the same server?

Are they using (nolock) in queries?

Take a look at some of Chris Krabsetsve's blog posts.  Tons of stuff there as I recall.

Also take a look at IdM Thoughtplace: Some thoughts on database locking in Oracle and Microsoft SQL Server  I think there could be some stuff there too.

Matt

PS also check: IdM Thoughtplace: Calling all Dispatchers

[brandonbollin · Accepted solution]

Thanks Matt. I'll get to reading these materials. Side note, I miss Chris. Anyone ever hear from him anymore?

[former_member2987 · Accepted solution]

Yes, I keep in touch with him, he's an old friend   I'll pass along your regards.

RDS is the Rapid Deployment Solution, an add from SAP to promote quicker implementation of IDM. It includes some pre-project materials and some frameworks.

There's a new one coming out for IDM 8 soon.

Matt

[brandonbollin · Accepted solution]

How would I look at our existing MMC tree and identify if it's RDS or standard framework that's installed?

[former_member2987 · Accepted solution]

RDS is a separate group of tasks.  Odds are, you don't have it there.

[brandonbollin · Accepted solution]

Oh yea... I remember now. The task group actually has RDS in the titles. Sorry. Been awhile since I saw an environment with RDS. Thanks. Once I get access to the PROD MMC in this environment, some of my silly questions will stop. 

[jaisuryan]

Hi Brandon,

Are they using RDS package?

We also had these deadlocks in provisioning framework tasks even though all our custom tasks and jobs had "with(no lock)" hint. SAP advised us to replace the RDS package with standard framework.

Kind regards,

Jai

[brandonbollin]

RDS Package? I have to say that I've never heard of that but again, I'm new here so maybe they are. I'll ask the lead tech and see. In the meantime, can you explain what RDS package is?