Essbase: Управление транзакциями COMMITTED/UNCOMMITTED ACCESS

Практическое применение имеет только один режим работы – Uncommited Access, так как Commited Access создает слишком много блокировок и переводит систему в режим не пригодный для параллельного запуска БП. Но это означает, что одновременно два потока могут обращаться к одному блоку данных для чтения и его изменения.

Для расчета верхнего предела кол-ва блоков, при достижении которого будет происходит синхронизации с  жестким диском можно воспользоваться след. формулой   из расчета того , что 10  Mb является оптимальной величиной для репликации.

  • Commit Blocks = 10,000,000 / (Block Size * Compress Ratio)

При такой величине достигается баланс между дефрагментацией файла данных и скоростью расчета.


Committed access is used to provide a high level of data consistency.  Committed Access accomplishes this in two ways:

1)     Committed Access locks blocks until a transaction is completely finished, and committed. Under committed access the commit happens at the end of the entire transaction.  This maintains data integrity throughout a transaction – i.e. while a calc under committed access is running, other users can not alter the blocks used in the calc until the calc is finished.

2)     Provides transaction rollback in case of server crashes, or transaction being aborted.


Committed Access locks blocks until a transaction is completely finished, and committed.

There are two types of access to a block – read access and write access.  Write access allows the thread to read and write back to the block. Read access only allows a read from the block. Locks on a block deny or limit access to the block.  A read lock allows other threads to read the block, but not modify it, while a write lock denies other threads access of any kind to that block.  If the default setting of Pre-image Access for Committed Access is used, then other threads have read access to the image of the data block, before it was locked for write access by a transaction. If Pre-Image access is not used, then other threads have to wait for the write lock on the block to be released in order to gain read access to the block or time-out depending on the Wait

A thread that is running under committed access issues short-term read locks and long-term write locks. The long term write locks are issued for blocks that will be modified by the transaction.  This means that only that thread can access these blocks until the entire transaction is finished, and locks released as the transaction is committed[SP1] .  Short –term read locks are put on blocks that are read but not written to by the transaction.  These locks are released as soon as the block as been read.

Locks ensure that data involved in a calculation remains unaltered until the end of each transaction. Pre-image Access allows other users to continue reading the Database during a calculation.

Memory concerns:

v  If your data cache is too small to hold the number of blocks specified in your commit settings, then blocks will be written to disk before the transaction is committed[SP2] as so sa as soon as the caches become full.  This allows other blocks to come in and be worked on.

v  Each lock uses memory (each lock uses around 80+ bytes to track through a calculation), and these locks are held in memory until the transaction has been completed. This memory usage can present problems for models with large number of blocks as there is a limit to the addressable memory space per processes and eventually large models may /will hit this limit.

Committed Access provides transaction rollback in case of server crashes, or transactions being aborted.

Committed Access ensures that if there is a server crash, all transactions running at the time of the crash are aborted and rolled back to their starting point. To do this Essbase retains redundant (duplicate data) for each committed access transaction.  To accommodate this, allow disk space for double the size of your database.


Uncommitted access does not ensure the same data integrity as Committed access, but uses less memory.

Under uncommitted access, two threads can modify the same block. Which means that one thread could modify a block as part of a calculation, and before that calculation completed, a second thread could come and overwrite the value in the block.  This can lead to situations where data looks incorrect, as it has been modified mid calculation.

Uncommitted access uses only short-term write locks, so the outcome of concurrent transactions is less predictable. A lock on a block is acquired, then the block is written and the transaction committed, then the lock is released.

When using uncommitted access, you can set the number of blocks that are modified before a synchronization point occurs (Commit Blocks) and you can set the number of rows to data load before a synchronization point occurs (Commit Rows). When a synchronization point occurs, the blocks that have been changed are written and committed to disk, locks released and the caches are ready for new blocks.

As in Committed Access, if the data cache is too small to hold the number of blocks specified in the commit settings, then blocks will be written to disk before the transaction is committed as soon as the caches are full.  This allows other blocks to come in and be worked on.  If a transaction is aborted any blocks that were written to disk stay written, there is no rollback.

Memory Concerns.

Uncommitted access does not keep any locks, this eliminates the need for memory to be allocated for tracking locks. This avoids memory issues for larger models running under Committed Access.

When is Committed Access used? When is Uncommitted Access used?

By default Transaction Isolation Level (Committed vs. Uncommitted Access) is set to Uncommitted for each database.  This means that by default, dataloads, calculations, reports etc are run under uncommitted access. This setting can be changed under Database | Settings | Transaction | Isolation Level

The Excel Add-in and Grid API use committed access. These will use committed access even if the databases Isolation level is set to uncommitted access.

What is the difference between Uncommitted Access with Commit Block 0, and Committed Access?

Both Uncommitted Access | Commit Block 0 and Committed Access commit the entire transaction at the very end.  Both will write blocks, but not commit the transaction, if the caches get full.  The difference between the two is that with Committed Access, blocks are kept locked, these locks are tracked, and there is rollback information so if something goes wrong the transaction rolls back to the beginning.

Under Commit Block 0, Locks aren’t tracked, which can cause data consistency problems, and the transaction  can not rollback.  Commit Block 0 uses less memory than Committed Access because there aren’t locks to track.

[SP1] to preserve the sequence……….transaction work is done, locks are released as part of committing the Transaction.

[SP2]before the transaction is committed.