ACQUIRE

When an analytic workspace is attached in multiwriter mode, the ACQUIRE command acquires and (optionally) resynchronizes the specified objects so that their changes can be updated and committed.

Note:

You cannot acquire an object dimensioned by a composite when that composite has another composite as one of its base dimensions.

Syntax

ACQUIRE {acquired_noresync_objects] | RESYNC resync_objects [WAIT] } - [CONSISTENT WITH consistency_objects [WAIT]]

Arguments

acquired_noresync_objects

A list of one or more variables, relations, valuesets, or dimension names, separated by commas, that you want to access in read/write mode without resynchronizing. Acquiring objects in this manner preserves all read-only changes made to the objects. You can update variables and dimensions acquired in this manner using an UPDATE statement.

RESYNC

Specifies acquisition in read/write mode of the latest generation of the specified objects with all private changes discarded.

resync_objects [WAIT]

A list of one or more variables, relations, valuesets, or dimension names, separated by commas, that you want to access in read/write mode and resynchronize.

When you do not specify WAIT, the ACQUIRE statement fails when another user has acquired any of the objects in resync_objects in read/write mode. When you specify WAIT, Oracle OLAP waits until all objects in resync_objects it can be acquired in read/write mode or the wait times out.

CONSISTENT WITH

Specifies that additional objects are to be accessible in read-only mode.the behavior of the ACQUIRE statement when a specified object is already acquired by another user and resynchronizes the specified objects when the ACQUIRE statement succeeds.

consistency_objects [WAIT]

A list of one or more a list of one or more variables, relations, valuesets, or dimension names, separated by commas, that you want to acquire in read-only mode.

When you do not specify WAIT, the ACQUIRE statement fails when any of the objects in the consistency_objects are acquired in read/write mode by another user. When you specify the WAIT keyword, Oracle OLAP waits to execute the ACQUIRE statement until none of the objects in consistency_objects are acquired in read/write mode by another user or until the wait times out.

Notes

Understanding Consistency

To some extent you can think of an ACQUIRE statement with a CONSISTENT WITH phrase as a combination of ACQUIRE and RELEASE statements.

ACQUIRE [avar...] RESYNC [rvar ...] cvar ... [WAIT]
RELEASE cvar ...

The difference is that an ACQUIRE CONSISTENT WITH statement succeeds even when the user does not have sufficient permissions to acquire cvar variables.

Failure and Error-Handling

All of the clauses in the ACQUIRE statement must succeed or the statement fails. Consequently, either all of the requested objects are acquired or none of them are acquired.

Once you have acquired an object in read-only mode, you can still acquire it in read/write mode. However, once another user has acquired an object in read-only mode, you cannot acquire the same object in read/write mode until the other user releases the object. When a specified object has been acquired by another user or when your read-only generation for a specified object is not the latest generation for the object, the ACQUIRE statement fails.

Also, it can take a long time for the ACQUIRE statement to complete when you specify WAIT for either the RESYNC or CONSISTENT phrase. During the wait, some variables in the acquisition lists may be released while others may have been acquired. It is even possible for a deadlock to occur which causes the ACQUIRE statement to fail with a timeout error.

To avoid problems caused by deadlock, be thoughtful about the order in which you code ACQUIRE and RELEASE statements and include appropriate error handling routines.

Examples

Example 6-15 Acquiring, Updating, and Releasing Objects

A classic use of multiwriter attachment mode is to allow two users to modify two different objects in the same analytic workspace. For example, assume that an analytic workspace has two variables: actuals and budget. Assume also that one user (user A) wants to modify actuals, while another user (user B) wants to modify budget. In this case, after attaching the analytic workspace in the multiwriter mode, each user acquires the desired variable, performs the desired modification, updates, commits the changes, and then, either detaches the workspace or releases the acquired variable.

User A executes the following statements.

AW ATTACH myworkspace MULTI
ACQUIRE actuals
... make modifications
UPDATE MULTI actuals
COMMIT
RELEASE actuals
AW DETACH myworkspace

While, at the same time, User B executes the following statements.

AW ATTACH myworkspace MULTI
ACQUIRE budget
... make modifications
UPDATE MULTI budget
COMMIT
RELEASE budget
AW DETACH myworkspace

Example 6-16 Acquiring and Resynchronizing Objects

Assume that two users (named B1 and B2) both need to make what-if changes to budget and possibly modify their parts of budget when they like the results of the what-if changes. Neither user knows if anyone else will need to access budget at the same time that they are or if they will need to make any permanent changes to budget. Consequently, they do not want to block anyone while they are performing what-if changes.

In this case, both users perform their what-if computation after attaching the analytic workspace in the multiwriter mode but without acquiring budget. When they later decide to make their what-if changes permanent, they try to acquire budget in unresynchronized mode. When the acquire succeeds, they update budget and commit the changes. The following OLAP DML statements show this scenario.

AW ATTACH myworkspace MULTI
...perform what-if computations
ACQUIRE budget
...maybe make some additional final changes
UPDATE MULTI budget
COMMIT
RELEASE budget
AW DETACH myworkspace

However, when the first acquire does not succeed, however, the users try again to acquire budget in resynchronized mode (possibly requesting a wait). When the resynchronized acquisition succeeds, they re-create the changes (since some relevant numbers might have changed) and then proceed to update and commit their analytic workspace. The following OLAP DML statements show this scenario.

AW ATTACH myworkspace MULTI
... perform what-if computations
ACQUIRE budget
...maybe make some additional final changes
UPDATE MULTI budget
COMMIT
RELEASE budget
AW DETACH myworkspace
AW ATTACH myworkspace MULTI
...perform what-if computations 
ACQUIRE budget --> failed
ACQUIRE RESYNC budget WAIT
...determine that the changes are still needed
...make changes to make permanent
UPDATE MULTI budget
COMMIT
RELEASE budget
AW DETACH myworkspace

Example 6-17 Acquiring Objects While Keeping Consistency

Sometimes you must keep some objects consistent with each other, which requires special care in multiwriter mode.

Assume that two users (User B1 and User B2) both need to modify budget, that budget must be kept consistent with investment, and that another user (User I) needs to modify investment. In this scenario, even though none of the users needs to modify both budget and investment, they all must ensure that when they acquire either budget or investment that no one else has either budget or investment already acquired. To achieve this effect, each user must issue an ACQUIRE statement with the CONSISTENT WITH phrase as shown in the following example code. Note that all of the users must be aware that the objects listed in the CONSISTENT phrase may be resynchronized by the ACQUIRE statement, if needed.

For example, User B1 could issue the following OLAP DML statements.

AW ATTACH myworkspace MULTI
... perform what-if computations
ACQUIRE budget CONSISTENT WITH investment
... maybe make some additional final changes
UPDATE MULTI budget
COMMIT
RELEASE budget, investment
AW DETACH myworkspace

User B2 could issue the following OLAP DML statements.

AW ATTACH myworkspace MULTI
... perform what-if computations 
ACQUIRE budget CONSISTENT WITH investment --> failed
ACQUIRE RESYNC budget CONSISTENT WITH investment WAIT
... determine that the changes are still needed
... make changes to make permanent
UPDATE MULTI budget
COMMIT
RELEASE budget, investment
AW DETACH myworkspace

User I could issue the following OLAP DML statements.

AW ATTACH myworkspace MULTI
ACQUIRE investment CONSISTENT WITH budget --> failed
ACQUIRE RESYNC investment CONSISTENT WITH budget WAIT
... make changes to investment
UPDATE MULTI investment
COMMIT
RELEASE budget, investment
AW DETACH myworkspace