7 Replies Latest reply: Nov 18, 2010 7:49 PM by 788766 RSS

    Commit workspace blocks GotoWorkspace

    679981
      Hi there,

      I noticed that when a workspace is being committed, many other sessions are blocked and waiting at:
      GTM.SetActiveWorkspace('LIVE);

      This is a GeoMedia command that does the following:
      DBMS_WM.GotoWorkspace('LIVE);
      DBMS_WM.SetWoOverwriteON();


      Is this reasonable?

      I remember that committing a workspace will block other session from doing the committing, but if it blocks other users from even going into a workspace as well, is there any way to prevent this?

      Thanks,
      Sharon
        • 1. Re: Commit workspace blocks GotoWorkspace
          Ben Speckhard-Oracle
          Hi Sharon,

          This is expected behavior. During a merge operation, an exclusive lock is typically acquired on the parent workspace. This can be avoided by setting the system parameter ROW_LEVEL_LOCKING to true. This parameter will not have any affect while merging workspaces that are continually refreshed, or that contain changes to valid time enabled tables. Changing this parameter will result in a shared lock being acquired instead, allowing a dbms_wm.GotoWorkspace operation to be executed from another session.

          However, when committing a workspace by specifying remove_workspace=>true, a more stringent lock is acquired while removing the workspace after the rows have been committed, which will block those other sessions while allowing other workspaces to merge into LIVE, but not execute GotoWorkspace.

          So, if you have to do a commit as opposed to not removing the workspace, then it is not going to be currently possibly to prevent the block within the other session.

          Regards,
          Ben
          • 2. Re: Commit workspace blocks GotoWorkspace
            679981
            Thank you Ben!

            Is ROW_LEVEL_LOCKING a new parameter in 11g? We're running 10g. Is there anyway for us to avoid the lock?

            Thanks,
            Sharon
            • 3. Re: Commit workspace blocks GotoWorkspace
              Ben Speckhard-Oracle
              Hi,

              It is in 11g, as well as the latest Workspace Manager patchsets available on Metalink for 10.2, 10.1, and 9.2.

              Regards,
              Ben
              • 4. Re: Commit workspace blocks GotoWorkspace
                679981
                Hi Ben,

                That's great news! Thank you!

                Also, today I monitored the job, 90-95% of the full hour was consumed by committing revisionset (merge workspace to live). We had this bug fix from Oracle to turn off fix control 5483301:OFF. Although I saw so many full table scan during the merge, like 30-40 full table scan per merge, and the job committed 3 times so it took an hour. Yesterday it committed 4 times it took more than 2 hours.

                Any suggestion to speed it up? This is a manual job running everyday, when it takes long it blocks everybody else.

                Many many thanks!

                Sharon
                • 5. Re: Commit workspace blocks GotoWorkspace
                  Ben Speckhard-Oracle
                  Hi Sharon,

                  In terms of performance, I would recommend filing a SR(if one isn't filed already) for this issue, so that the necessary information can be gathered and diagnosed. It is difficult to solve these types of performance issues within a forum setting.

                  Regards,
                  Ben
                  • 6. Re: Commit workspace blocks GotoWorkspace
                    679981
                    Thank you Ben.
                    • 7. Re: Commit workspace blocks GotoWorkspace
                      788766
                      bspeckha wrote:

                      Hi Sharon,



                      This is expected behavior. During a merge operation, an exclusive lock is typically acquired on the parent workspace. This can be avoided by setting the system parameter ROW_LEVEL_LOCKING to true. This parameter will not have any affect while merging workspaces that are continually refreshed, or that contain changes to valid time enabled tables. Changing this <font face="tahoma,verdana,sans-serif" size="1" color="#000">parameter</font> will result in a shared lock being acquired instead, allowing a dbms_wm.GotoWorkspace operation to be executed from another session.



                      However, when committing a workspace by specifying remove_workspace=>true, a more stringent lock is acquired while removing the workspace after the rows have been committed, which will block those other sessions while allowing other workspaces to merge into LIVE, but not execute GotoWorkspace.



                      So, if you have to do a commit as opposed to not removing the workspace, then it is not going to be currently possibly to prevent the block within the other session.



                      Regards,

                      Ben
                      Now I got it, It's helpful to me, Thanks for your sharing! Nice writing.