4 Replies Latest reply: May 24, 2010 6:03 PM by NoelKhan RSS

    ? How can additional updates be performed during a MergeWorkspace ?

    NoelKhan
      All,

      . . . .I've been trying to perform updates during a MergeWorkspace and would appreciate guidance from the community.

      Environment*

      . . . .*System*: Oracle 10g R2 version-enabled spatial data-warehouse, Windows Server 2003.

      . . . .*Organization*: BEFORE UPDATE Trigger on Schema1.Table1. Trigger calls procedures in Schema3, which modify data in Schema2.Table2. Trigger events already set to WORKSPACE_MERGE_WO_REMOVE. Trigger owner is User3 ( _~_ Schema3).

      . . . .*Workspaces*: LIVE > Project > Revision. All workspaces owned by User4 ( _~_ Schema4). End-user application that connects to database uses User4's credentials to perform all locks, updates, merges, etc.

      . . . .*Workflow*: (1) Supervisor creates Project workspace and assigns to end-user. Project is unfrozen. (2) End-user creates Revision workspace (unfrozen), performs work and relinquishes control back to supervisor. Revision now frozen READ_ONLY. (3) Upon review/acceptance, supervisor Merges Revision into Project. (4) If no other work needs to be performed, supervisor Merges Project Into LIVE, during which time the Revision is frozen with NO_ACCESS.
      . . . .At each MergeWorkspace, Trigger fires and calls procedures in Schema3. Procedure evaluates whether the Revision is being Merged into Project OR the Project is being Merged into LIVE. Ideally (read, design requirement) is the procedure continues IFF the Project is being Merged into LIVE. The procedure then attempts to update Schema2.Table2.

      . . . .*Execution Environments*: (1) SQL Developer [debug mode] (2) SQL+ [MergeWorkspace] (3) End-user application.

      What I've tried_

      . . . .(1) rtfm
      . . . .(2) stfw
      . . . .(3) experimentation: (A) Compromise solution: End-user application (using User4's credentials) initiates a MergeWorkspace on Revision. BEFORE UPDATE Trigger fires and calls procedures in Schema3 (using User3 credentials). Procedure changes context from LIVE > Project > Revision. Procedure updates Schema2.Table2 _LT* , commit. Procedure changes context to LIVE and exits. Note if the Project workspace is removed, then the update on the _LT table is discarded/reverted.
      . . . .(B) While Project workspace is being merged, created another workspace and performed update on Schema2.Table2. Strangely, conflicts detected on Schema1.Table1 upon merging temporary workspace. Conflict resolution performed and version of record in Schema1.Table1 with CHILD version retained. The operation was successful, but with no changes to LIVE.
      . . . .(c) I've also tried butchered variants of the above. Ultimately unproductive, but which led to the following observations: (a) the parent workspace (even LIVE) appears to be locked during merge operations and (b) updates to data within a workspace requires the same credentials as the original editor (which is why I've repeatedly noted the credentials above. I also tried having my procedures impersonate User4; I created a procedure in Schema4 that ran DML passed to it as a parameter. I noted that this function would execute using User4's credentials. Interesting, but ultimately unavailing for the original purpose.

      . . . .How could one perform an update in the said environment during a Merge operation?

      Regards,
      Noel

      Edited by: Noel Khan on May 18, 2010 4:13 PM
        • 1. Re: ? How can additional updates be performed during a MergeWorkspace ?
          Ben Speckhard-Oracle
          Hi,

          What version of OWM are you using? I tested this within SQL*Plus and everything is working as expected. Also, it wasn't quite clear as to exactly what was happening when you let OWM handle everything. Were you able to verify that the trigger was actually firing? Is there a privilege error, or does something else happen? Also, updating _LT directly is not recommended.

          By default, an exclusive lock is taken on the parent workspace during a merge. This behavior can be changed by using the ROW_LEVEL_LOCKING system parameter. However, in this case it shouldn't make a difference, since the user performing the merge would be the session with the lock and so would continue having the lock necessary to perform the update on the user2 table.

          Ben
          • 2. Re: ? How can additional updates be performed during a MergeWorkspace ?
            NoelKhan
            Ben,

            . . . .[ *Re Version* ] OWM_VERSION = 10.2.0.1.0

            . . . .[ *Re SQL+* ] I've also been more successful at SQL+ executions than SQL-Developer or (worst of all) end-user application executions. I believe the difference between these environments is related to the context in which the MergeWorkspace is called. Although I can't control the context of the end-user application, I can set the context for SQL+ executions and (can simulate the same for) SQL-Developer (by hard-coding GotoWorkspace calls).

            . . . .[ *Re Trigger execution* ] Yes, the trigger is indeed firing. The Trigger calls a procedure which logs every operation to a text file.

            . . . .[ *Re Privileges* ] User3 (who owns the procedure called by Schema1.Trigger) is granted the WM_ADMIN_ROLE and (for good measure) ACCESS_ANY_WORKSPACE, FREEZE_ANY_WORKSPACE and CREATE_ANY_WORKSPACE via GrantSystemPriv.

            . . . .[ *Implementation OWM-1* ] Here, the procedure inherits the context from the caller, i.e., the procedure does not change context on its own. The procedure ultimately performs an update on Schema2.Table2 (through the view, not the _LT table).
            . . . .Assuming: [1] the following workspace tree: LIVE > Project > Revision, [2] MergeWorkspace(Revision) is called from GetWorkspace=Project and [3] the procedure fires upon MergeWorkspace(Revision).
            . . . .Within SQL+, if I set the context to Project and call MergeWorkspace(Revision), the procedure executes without error. Likewise, if I use the end-user application to MergeWorkspace(Revision), it likewise completes without error (again, the end-user application automatically sets the context to Project). However, the procedure's update to Schema2.Table2 seems to have been discarded, i.e., the triggered update is not visible in LIVE. However, the changes made within the Revision workspace, which caused the trigger to run, are seen in LIVE. In summary, the operation was a success, but the tumor was not removed.

            . . . .[ *Implementation OWM-2* ] Within the procedure and without regard to the caller's context - If I set the context to Project, the procedure errors out with ORA-06519: "active autonomous transaction detected and rolled back". Likewise, if I use the end-user application to MergeWorkspace(Revision), it raises the same error. Consistency is good. As you've pointed out, this is due to the lock on the Project workspace while the Revision workspace is being merged. Assuming this "workspace lock" is synonymous to a "workspace freeze", I attempted to Unfreeze(Project), but get ORA-20161 "unable to acquire lock on workspace: Project as the resource is busy". If - within the procedure - I set the context to Revision instead of the Project (because it's locked), I get ORA-20233 "deadlock detected when trying to acquire lock for workspace Revision, session may have open database transactions". As before, I get ORA-20161 if I try to unfreeze the Revision.

            . . . .[ *Implementation OWM-3* ] Regardless of the caller's context, the procedure creates and enters a temporary workspace (isrefreshed=>TRUE, auto_commit=>TRUE). After the procedure performs the update, commits, goes to LIVE, commits again (otherwise, ORA-06519 "active autonomous transaction detected and rolled back") and finally attempts to Merge the temporary workspace. At this point I get ORA-20033 "attempt to MODIFY a row locked by: 'User4 in workspace Project".
            . . . .This is where I tried to impersonate an Oracle user. I created a function in Schema4 that executes dynamic DML. If - within that function - I select from sys_context('USERENV', 'CURRENT_SCHEMA') I get User4, as desired. Ultimately, I still get ORA-20033. Upon debugging the procedure using SQL Developer, it is the update statement that raises an exception regardless of the caller's credentials (actual or impersonated). That about disproves my impersonation approach, but to be sure, could the temporary workspace cause the update to fail (the record being updated is not explicitly locked or otherwise modified within the workspace being merged (Revision) )?

            . . . .Shall I post my code?

            Thanking you,
            Noel
            • 3. Re: ? How can additional updates be performed during a MergeWorkspace ?
              Ben Speckhard-Oracle
              Hi,

              I would suggest to file a SR, and post traces for any errors that you are receiving. That will give a better indication as to where and why these errors would be occurring.

              When merging the Revision workspace, the changes should only be visible from the Project workspace. Only after the Project workspace is merged, would the changes be visible from LIVE. It does does not matter what workspace MergeWorkspace is called from. It will internally change the workspace as necessary. The only restriction is that when using remove_workspace=>true, the session cannot be in the workspace being removed.

              Also, there is a newer version of Workspace Manager, so you might want to try to upgrade to see if it helps. If you do file a SR, the traces would indicate whether or not upgrading would help in this situation.

              Regards,
              Ben
              • 4. Re: ? How can additional updates be performed during a MergeWorkspace ?
                NoelKhan
                Ben,

                . . . .I appreciate the feedback. I'll try upgrading to 11g.

                Thanking you,
                Noel