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.
