Deadlock Troubleshooting in SQL Server Analysis Services ( SSAS )

  • [アーティクル]

A deadlock is a special type of situation wherein two or more transactions are waiting for the other to finish, and thus neither ever does and no one can proceed. Deadlocks can be often considered as normal in a high concurrent update environment. When a deadlock is detected, some systems (such as SQL Server) will choose the least expensive process to rollback to resolve the deadlock.

Deadlocks not only happen in OLTP systems but can also happen in an OLAP environment. If you suspect you are having some deadlock issues with SQL Analysis Services, you can follow the example below to trouble shoot the problem.

The example in the blog here is taken out from a SSAS 2005 server, but the same concept and steps should be applicable in SSAS 2008 as well.

The best way to trouble shoot a deadlock in SSAS is to capture the whole scenario within a SQL profiler trace and then analyze the trace offline.

By default, the “Deadlock” event is not selected. You will need to explicitly select the Deadlock event under Locks within the Trace Properties (see screen shot #1 below)

 

Screen shot #1

image

Additional to the Deadlock Event, please make sure that the “Command Begin” and “Command End” events are also included. You can add other events you are interested in.

(If you need detail information on how to use SQL Server Profiler to Monitor Analysis Services, please check out the BOL: https://msdn.microsoft.com/en-us/library/ms174946(SQL.90).aspx)

Once you have the trace captured, you can open up the trace offline with the profiler. The first thing to look for is the “Deadlock” under the EventClass (see screen shot #2). If you see one, the deadlock event usually is followed by the following error

Transaction errors: Aborting transaction on session <SPID>. Transaction errors: The lock operation ended unsuccessfully because of deadlock.

Screen shot #2

clip_image002

The deadlock graph is captured in the TextData column of the deadlock event (see screen shot #3). It is easier to look at the whole description by coping the TextData and paste it on to an XMLA windows.

 

Screen shot #3

clip_image003

Here is an example deadlock graph:

<DeadlockGraph>

  <VICTIM>

    <LOCK_TRANSACTION_ID>0DA3B625-2632-4BC8-BF42-8BD04C0F40E5</LOCK_TRANSACTION_ID>

    <SPID>84947</SPID>

  </VICTIM>

  <LOCKS>

    <Lock>

      <LOCK_OBJECT_ID>

        <Object>

          <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

          <CubeID>SQL FoodMart</CubeID>

          <MeasureGroupID>Internet Sales</MeasureGroupID>

        </Object>

      </LOCK_OBJECT_ID>

      <LOCK_ID>2C768E8D-90B3-410A-B0B9-808A99787203</LOCK_ID>

      <LOCK_TRANSACTION_ID>0DA3B625-2632-4BC8-BF42-8BD04C0F40E5</LOCK_TRANSACTION_ID>

      <SPID>84947</SPID>

      <LOCK_TYPE>2</LOCK_TYPE>

      <LOCK_STATUS>1</LOCK_STATUS>

    </Lock>

    <Lock>

      <LOCK_OBJECT_ID>

        <Object>

          <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

          <CubeID>SQL FoodMart</CubeID>

          <MeasureGroupID>Retail Sales</MeasureGroupID>

        </Object>

      </LOCK_OBJECT_ID>

      <LOCK_ID>3FE9E9BF-5341-4269-989F-9A7E8FA2166E</LOCK_ID>

      <LOCK_TRANSACTION_ID>0DA3B625-2632-4BC8-BF42-8BD04C0F40E5</LOCK_TRANSACTION_ID>

      <SPID>84947</SPID>

      <LOCK_TYPE>2</LOCK_TYPE>

      <LOCK_STATUS>0</LOCK_STATUS>

    </Lock>

    <Lock>

      <LOCK_OBJECT_ID>

        <Object>

          <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

          <DataSourceViewID>SQL Foodmart DW</DataSourceViewID>

        </Object>

      </LOCK_OBJECT_ID>

      <LOCK_ID>FD240063-75BD-4348-9E7D-F2B8903E27E1</LOCK_ID>

      <LOCK_TRANSACTION_ID>94406B70-4078-4B35-8AFF-0120AF3BF8A7</LOCK_TRANSACTION_ID>

      <SPID>84858</SPID>

      <LOCK_TYPE>2</LOCK_TYPE>

      <LOCK_STATUS>1</LOCK_STATUS>

    </Lock>

    <Lock>

      <LOCK_OBJECT_ID>

        <Object>

          <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

          <CubeID>SQL FoodMart</CubeID>

          <MeasureGroupID>Internet Sales</MeasureGroupID>

        </Object>

      </LOCK_OBJECT_ID>

      <LOCK_ID>B8177860-45F0-40EE-AD0F-A2867C1851B4</LOCK_ID>

      <LOCK_TRANSACTION_ID>94406B70-4078-4B35-8AFF-0120AF3BF8A7</LOCK_TRANSACTION_ID>

      <SPID>84858</SPID>

      <LOCK_TYPE>4</LOCK_TYPE>

      <LOCK_STATUS>0</LOCK_STATUS>

    </Lock>

    <Lock>

      <LOCK_OBJECT_ID>

        <Object>

          <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

          <CubeID>SQL FoodMart</CubeID>

          <MeasureGroupID>Retail Sales</MeasureGroupID>

        </Object>

      </LOCK_OBJECT_ID>

      <LOCK_ID>94F8C292-41D2-43B1-B9BC-929B4FD953E7</LOCK_ID>

      <LOCK_TRANSACTION_ID>94406B70-4078-4B35-8AFF-0120AF3BF8A7</LOCK_TRANSACTION_ID>

      <SPID>84858</SPID>

      <LOCK_TYPE>4</LOCK_TYPE>

      <LOCK_STATUS>1</LOCK_STATUS>

    </Lock>

  </LOCKS>

</DeadlockGraph>

Before we go on, let look at some of the items highlighted above.

“LOCK_STATUS” is the status of a lock. It is either 0 or 1. 0 means “waiting" and 1 means “granted"

The possible LOCK TYPES of a lock are:

 

 

 

 

 

 

 

LOCK_READ

0x0000002

read - for processing

 

 

LOCK_WRITE

0x0000004

write - for processing

 

 

LOCK_COMMIT_READ

0x0000008

commit - shared

 

 

LOCK_COMMIT_WRITE

0x0000010

commit - exclusive

 

 

 

 

 

 

Two SPID are involved in the deadlock example. They are <SPID>84947</SPID> and <SPID>84858</SPID>.

Now, with the information about different lock types and lock status, we can paint a better picture.

(A) Process 84947 is holding a read lock on “Internet Sales” but it is waiting on a read lock on “Retail Sales” before it can proceed.

(B) Process 84858 is holding a write lock on “Retail Sales” but it is waiting on a write lock on “Internet Sales” before it can proceed

A is waiting on B and B is waiting on A. This is a classic deadlock situation.

To understand why we are getting to this stage, we need to find out what commands are running by A and B at the time of the deadlock.

We know that the SPID are 84947 and 84858. We can use the numbers within the profiler to filter out the commands that are issued by these specific SPID (see screen shot #4). We are interested in the commands found immediate before the deadlock error.

 

Screen shot #4

clip_image004

In the scenario here, we found the following:

Process 84947 is processing a Partition under measure group “Retail Sales” at the time of deadlock.

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">

  <ErrorConfiguration xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">

    <KeyErrorLimit>-1</KeyErrorLimit>

  </ErrorConfiguration>

  <Parallel>

    <Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">

      <Object>

        <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

        <CubeID>SQL FoodMart</CubeID>

        <MeasureGroupID>Retail Sales</MeasureGroupID>

        <PartitionID>Partition Y2008</PartitionID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel>

</Batch>

And process 84858 is processing a list of dimension objects.

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">

  <Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2">

    <Object>

      <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

      <DimensionID>Products</DimensionID>

    </Object>

    <Type>ProcessUpdate</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  </Process>

  <Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2">

    <Object>

      <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

      <DimensionID>DiscountCard Point Level</DimensionID>

    </Object>

    <Type>ProcessUpdate</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  </Process>

  <Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2">

    <Object>

      <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

      <DimensionID>Customers</DimensionID>

    </Object>

    <Type>ProcessUpdate</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  </Process>

  <Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2">

    <Object>

      <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

      <DimensionID>DiscountCard Award Points</DimensionID>

    </Object>

    <Type>ProcessUpdate</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  </Process>

  <Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2">

    <Object>

      <DatabaseID>SQL FoodMart_2008_12_29</DatabaseID>

      <DimensionID>Stores</DimensionID>

    </Object>

    <Type>ProcessUpdate</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  </Process>

</Batch>

 

Now we know that although the product supports parallel processing multiple partitions, the product does not support parallel processing dimensions mixing with partitions in two separate transactions. Here we can see parallel processing dimensions and partitions can potential run into a deadlock situation. To avoid something similar happen again, we should process all the dimension objects first prior moving on to partition processing.

C S John Lam | Premier Field Engineer - SQL Analysis Services

Comments

  • Anonymous
    November 19, 2009
    This is great information, but what if I am using auto MOLAP?  I don't have control of what's processing when then.

  • Anonymous
    March 22, 2011
    Here are this and some other articles on Analysis Services Locks: <a href="ssas-wiki.com/.../Articles ssas-wiki.com/.../Articles