Some thoughts on database locking in Oracle and Microsoft SQL Server

Deadlocks are the bane of those of us responsible for designing and maintaining any type of database system. I’ve written about these before on the dispatcher level. However this time around, I’d like to discuss them a little further “down” so to speak, at the database level. Also in talking to various people about this topic I've found that it’s potentially the most divisive question since “Tastes good vs. Less filling”

Database deadlocks are much like application ones, typically come when two processes are trying to access the same database row at the same time. Most often this is when the system is trying to read and write to the row at the same time. A nice explanation can be found here. What we essentially wind up with is the database equivalent of a traffic jam where no one can move. It’s interesting to note that both Oracle and Microsoft SQL server handle these locking scenarios differently. I’m not going to go into DB2 at the moment but will address it if there is sufficient demand.

When dealing with SQL Server, management of locks is handled through the use of the “Hint” called No Lock. According to MSDN:

Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query. (Source)

When NOLOCK is used this is the same as using READUNCOMMITTED which some of you might have be familiar with if you did the NetWeaver portion of the IDM install when setting up the data source. Using this option keeps the SQL Server database engine from issuing locks. The big issue here is that one runs the risk of having dirty (old) data in the database operations. Be careful when using NOLOCK for this reason. Even though the SAP Provisioning Framework makes extensive use of the NOLOCK functionality, they regression test the heck out of the configuration. Make sure you do, too misuse of NOLOCK can lead to bad things happening in the Identity Store database.

There is also a piece of SQL Server functionality referred to as Snapshot Isolation which appears to work as a NOLOCK writ large where database snapshots are held in the TEMPDB for processing (source) This functionality was recommended by a DBA I worked with on a project some time ago. The functionality was tested in DEV and then rolled to the customer’s PRODUCTION instance.

Oracle is a little different in the way that it approaches locking in that the system has more internal management of conflicts through use of rollback logs forcing data to be committed before writes can occur and thus deadlocks occur much less often (Source) This means that there is no similar NOLOCK functionality in the Oracle Database System.

One final thing to consider with database deadlocks is how the database is being accessed, regardless of the database being used.  It is considered a best practice in SAP IDM to use To Identity Store passes as opposed to uIS_SetValue whenever possible (Source)

At the end of the day, I don’t know that I can really tell you to employ these mechanisms or not. In general we do know that it’s better not to have deadlocks than to have them and to do what you can to achieve this goal. In general, if you are going to use these techniques, do make sure you are doing so in concert with your DBA team and after careful testing. I have seen Microsoft SQL Server’s Snapshot Isolation work well in a busy productive environment, but I will not recommend its universal adoption as I can’t tell you how well it will work in your environment. I will however recommend that you look into it with your DBA team if you are experiencing Deadlocks in SQL Server.

Dispatcher Errors

Recently when working on a new QA system based on a copy of the PROD database when I encountered an error that I had never seen before when starting up the first dispatcher. I highlighted it below:

Running MxDispatcher_d1.
[21.09.2011 18:37:19-539] - Initialized log for com.sap.idm.ic.services.api.MXMCApi. Log level is Debug
MxDispatcher version: 7.10.5.2 Built: 07.06.2011 16:20:24 (c) Copyright 2008 SAP AG. All rights reserved.
Java VM: Sun Microsystems Inc.   Version: 1.5.0_22
Java home: C:\Program Files (x86)\Java\jdk1.5.0_22\jre
Java lib/ext: C:\Program Files (x86)\Java\jdk1.5.0_22\jre\lib\ext
CLASSPATH: d:\sap\idm\Java\mxdispatcher.jar;d:\sap\idm\Java\mxmcapi.jar;D:\jdbc2.0\sqljdbc_2.0\enu\sqljdbc.jar;
[21.09.2011 18:37:19-557] - MxDispatcher:Reading prop files
[21.09.2011 18:37:19-557] - MxDispatcher:Loading driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
[21.09.2011 18:37:19-639] - MxDispatcher:Creating connection to : jdbc:sqlserver://NWIDMSBX:1433;databasename=mxmc_db;user=mxmc_rt;password=********
[21.09.2011 18:37:21-369] - MxDispatcher:Reading main MxDispatcher configuration ...
[21.09.2011 18:37:21-593] - MxDispatcher:Dispatcher configuration d1 not found
[21.09.2011 18:37:21-594] - MxDispatcher:Error reading main MxDispatcher configuration ...
[21.09.2011 18:37:21-594] - The first config load failed:Dispatcher configuration d1 not found

I went through all of the normal dispatcher configuration checks, JAVA configuration, drivers, and database configuration. Everything looked OK, My ODBC checks were ok, and I knew that I was contacting the database server and that the ports were open. One suspicious thing was the extremely long length of the dispatcher name, however was not the the cause.

What we actually found was that the server names were not correct after all. The ODBC connection was pointed to the correct server, but the Java runtime connection was to the wrong server. Nothing like the confusion in moving configurations from one environment to another!  After ensuring once again, that I had the correct configuration, I regenerated the dispatcher scripts and all was fine.

So the cause of this error is when there is a connection string mismatch if you should see this in the future.

More from the JAR

An ugly issue came up not too long ago on my project.  We were seeing the error messages referencing an mxmc_admin based connection string as mentioned in Too Much in the JAR

So, I said to myself, I know how to deal with this, and proceeded to show off my knowledge by going to the MMC Console, selecting Tools/Option and selected the JAVA tab and found… nothing wrong. Only one extension present, JDBC driver JAR was right.  Felt the virtual pie in the face.

So we started looking.  I did insist that the root of the issue was a JAVA conflict and no one on the team had any real reason to doubt me.

Eventually we found the issue, and it was indeed related to JAVA.  It seemed that there were multiple JDBC drivers installed and like the JARs, this can be a bad thing.

There were two SQL Server drivers specified. It turns out one was for SQL Server 2000 and one for SQL Server 2005.  For reference here are the drivers:

2005: com.microsoft.sqlserver.jdbc.SQLServerDriver
2000: com.microsoft.jdbc.sqlserver.SQLServerDriver

Hope this helps you next time you get a conflict!

The Tao of IDM

The best soldier does not attack. The superior fighter succeeds without violence. The greatest conqueror wins without struggle. The most successful manager leads without dictating. This is intelligent non aggressiveness. This is called the mastery of men. 

So why would I lead an Identity Management blog entry with a quote from the Tao Te Ching? Basically it sums up a recent issue I had in my current project.


As a part of this project, I am helping to get a young engineer familiar with IDM.  Working together we needed to create a query that would return only specific types of users for an IDM export Job.  I explained the basic process for executing the export and watched him work on various queries to return the correct users, while advising him about database structures and useful techniques. As an elaborate query began to take shape it was starting to look way too complicated.  I started thinking that there had to be a better way to accomplish our task.


Then I remembered that since we were doing a "To Database" task we could specify the Identity Store as the source and used the built in editor to build the correct query.  It took seconds to build and we quickly checked the query by doing a copy/paste to Microsoft SQL Server.  It worked perfectly and we were up and running.


Here's an example of the query that we created:

So what's the takeaway on this?  Look to see what the system can do rather than build something from the outside. At the very least, use the tools to build the query and then customize it (just remember that using an external query editor on the edited query make using the built in tool not work). 


And here's how easy it was to generate the query:

There's no need to reinvent the wheel

Account Logins and Anonymous Access

For a process that is supposed to be all about promoting access, NW IDM certainly has enough ways to prohibit access to the system.

We recently enabled Anonymous Password reset and had a user that just could not authenticate. No matter what they entered they kept getting access denied when they answered their authentication questions.

As a test we had them reset their authentication answers to a single character.  We even tried clearing all the answers by dropping their MX_AUTHQ_00x entries (Set MX_AUTHQ_001 --> {D}, and so on) which had been the previous extreme solution to these problems.

Finally one of the other engineers on the project noticed that MX_FAILEDRECOVER was currently at 4 for the user with 3 being the limit in the system. We then executed our unlock IC user task which resets this value.  The user was then able to authenticate.

Kind of an interesting situation, since the "user" was logging in anonymously, the system was still checking this value before they could reset their password. Nice to see that SAP IDM is on duty and guarding the points of entry to the system.  Time for this user to call the help desk!

Troubleshooting "To passes"

Now on to a different troubleshooting tip.

Sometimes when executing a "To Pass" you'll have an error in writing to that destination be it a database or a directory service.  When writing to a database, you might encounter an error saying something like "the table cannot be created" or the dreaded LDAP 49, "Unwilling to perform"

Basically, what's going on here is that there's a problem writing to the database or the directory service, so you should check a couple of basic things:

1. Is the data format in the destination actually supported? In regards to a database, just because the destination grid says tinyint, this does not mean your Oracle database back end supports it (or smallint on the Microsoft SQL side for that matter)  Always double check this first.

2. Try disabling all of the destination attributes except for the first one and run the task again.  If it works, enable the second destination attribute and keep on with it, leaving attributes that work enabled and ones that don't work disabled. Don't forget that the first line in the destination grid refers to an key, so if this isn't working, make sure that the value must be unique and properly formatted for your destination in terms of type and format.

You can then look back at the disabled attributes and see what works and what does not.  More likely than not there's a formatting issue going on or something in an attached script. When working with Directory Services in a "To LDAP" pass, I've also found it helpful to change the output type to LDIF as shown below.

After this is done the results of the pass will be sent to a text file, which is sometimes easier to review, just don't forget to change it back when you're done!

Good luck and  feel free to post your own favorite troubleshooting tips as comments!!