Multi user SSAS writebacks may result to blocks on similar functions and new connections.

  • Article

  Author: Nicholas Dritsas

       

         Reviewers: Akshai Mirchandani, Kevin Cox, Lubor Kollar, Thomas Kejser

Writeback consists of two distinct processes. The first one is an update cube process that updates the current session with the changes. Only the current user sees the changes and he can continue with updates and what/if analysis. The second process is a commit so the changes get committed in the database and all users can see the results.

The challenge is when you have many users issuing writeback commits. A writeback commit requires an SSAS database write lock. If it gets it, the other commit requests (eg. from other writebacks, cube process, alter roles etc) will have to wait. Also, during this time, new connections cannot be made since a new connection requires a read lock of the database.

So, as you can imagine, the queue can get rather long. New connections cannot be made and they will start timing out. Queued writeback commits can take a long time since they will not timeout. To make things more interesting, writeback commit will not enforce the ForceCommitTimeout server setting that is already used by cube process. ForceCommitTimeout kills all the processes, reads or writes, ahead of it to make sure the issued commit completes within a reasonable time.

This is the observed behavior in SQL server today. The SSAS team decided to introduce an enhancement as part of the sql 2008 sp1/cu3.

Under this enhancement, writeback commit will observe the ForceCommitTimeout server setting. So, if it waits more than the set value, typically 30 seconds, it will go ahead and kill any queries or processes ahead of it to ensure it commits within a timely fashion. This is new behavior and applications need to be modified to handle the new error case.

Additionally, SSAS now requests a database commit write lock at a later stage and for a shorter period of time than before. When you enable MOLAP Writeback, SSAS updates both the relational engine that holds the table with the deltas plus SSAS storage with the updates. An SSAS cube transaction wraps around the relational transaction and it will only commit the relational transaction if it is ready to commit the cube transaction. If the MOLAP update fails, then SSAS will rollback its transaction which will also rollback the relational transaction.

During a writeback commit, there are three operations involved:

- Update MOLAP

- Update relational

- Commit

Previously, a Database Commit Write lock was required at the beginning of these three operations. Now, we have introduced a more granular process, such as:

- Temporarily take read commit lock on DB – note that this will not prevent other sessions/queries from executing. Commit Read protects a database from being committed in another transaction. It’s only taken to safely take read/write locks on the affected objects later on.

- Analyze writeback to determine which objects are going to be affected.

- Take write locks on these objects and read locks on their dependent objects. Write locks protect an object while it is being written such that no other writers/readers can affect it for the duration of the transaction.

- The DB read commit lock is then released.

- Now, we do the two update steps (update MOLAP then update relational in a single transaction).

- We enter Commit and only now we take the Database Commit Write lock and finish the Commit operation. Commit Write protects the database from being read/written in another transaction. This is also where the ForceCommitTimeout value is utilized. All the read/write locks are released at commit/rollback time. They’re held till then to prevent other transactions from updating the objects until the commit/rollback says that this transaction is done with them.

With this enhancement we already see customers gaining higher levels of concurrency during writebacks with no or minimum blocking of other processes and connections.