Archive

Posts Tagged ‘SSAS Analysis Serivces 2008 2008R2 Lock Type’

SSAS Lock Types

September 11, 2011 Leave a comment

As it usually quite difficult to identify what the various lock types are in Analysis Services and I always seem to forget, below are the lock types and id’s so that in the event of a deadlock, it is possible to identify what locks are present.

Lock Type Lock Name
2 Read – For processing
4 Write – For processing
8 CommitShared
16 CommitExclusive

 

It is possible to take out a manual lock on the cube using the following syntax in an MDX window.

image

 

If you run the begin tran GO and the lock statement, this will take out a CommitShared Lock on the adventure works database.  If you at this point execute the MDX

select *
  from $system.discover_locks

Then you can see that there is a lock_type 8 taken as expected and a lock status of 1 meaning it has been granted.

image

If you now exectue a processing command for say a partition in another window it is then possible to see all of the locks that have been taken out as it will eventually try and get a lock at the server level (more info over here at Andrew Calvett’s blog).

image

The above screenshot (which might be difficult to see) shows that we are now waiting for a CommitExclusive lock at the database level which on the line below we can see has been granted out the lock that we took out explicitly. (You can also see the server/instance level lock in the window above also so that the master.mvp file can be updated).

You now have a choice to COMMIT TRAN or ROLLBACK TRAN which allows the processing in the other window to complete (or rollback)

 

Chris

There is more info on deadlocking over at John Lam’s blog which goes into detail on deadlocking in Analysis Services.

Also another reference to Andrew Calvett’s blog

Follow

Get every new post delivered to your Inbox.

Join 86 other followers