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|
It is possible to take out a manual lock on the cube using the following syntax in an MDX window.
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
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.
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).
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)
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