This discussion is archived
8 Replies Latest reply: Jun 11, 2013 1:46 AM by Stefan Jager RSS

Long Transactions Feasibility

bmarbury Newbie
Currently Being Moderated
Our application has a new use case for transactions that require inserts/updates to approximately 20 - 30 tables to be "saved" and not completely committed to the database or rolled back until specific external user events occur. The events that would trigger the commit or rollback can occur days, weeks, or months after the transaction is initiated and the changes are saved.

The transactions require records from all live/production tables (existing unchanged records), including non-versioned tables.

We don't want users of the "live" tables to view the transaction changes until they are committed/merged.

We want each long transaction to have it's own discreet unit of work so only the changes made for a single transaction are committed or rolled back together. We do not want changes from other transactions to be included during a commit or rollback (i.e. merge or refresh).

The application can have many (hundreds) of these long transactions pending commit/rollback at any given time.

Some of the tables contain a few million records.

Workspace Manager looks like a potential solution for these transactions. The documentation explicitly states that WM can be used to implement long transactions.

However, from my limited knowledge, it looks like each transaction in our new use case would require it's own workspace.

Is this assertion correct? (Discreet long transactions would have to be isolated in their own workspaces, hundreds in our case, in order to be committed/rolled back together without including changes from other transactions)

Or, can multiple long transactions with discreet units of work (i.e. long savepoints) be implemented in one workspace, and be merged into the live workspace separately?

If the assertion above is correct, is it possible and advisable to design a use case that could create hundreds of separate workspaces in the database?

If anyone has suggestions as to possible other features/technologies/designs that should be considered, it would be much appreciated.
  • 1. Re: Long Transactions Feasibility
    davidp 2 Pro
    Currently Being Moderated
    I'm only just getting into Workspace Manager, and using it for history, not long transactions, but here are my thoughts.

    It does sound like a candidate for Oracle Workspace Manager.
    The table sizes don't matter too much. The transaction sizes and rates are more relevant, and managing conflicting transactions is always difficult for this sort of business requirement - it's a often business issue, not solvable by technology.

    Are the rows also updated in normal short transactions ? If so, that would create new versions of the rows in LIVE when rows are updated after implicit savepoints are created by creation of a new workspace. If the only updates happen via workspaces, millions of live rows that have never changed are reasonably fast to access.

    Hundreds of workspaces sound feasible to me.

    Alternatively DBMS_WM.MergeTable has a where_clause parameter - could that be used to filter the data for the different transactions, perhaps with a non-versioned table recording the ID's of the edited entities
    The WHERE clause (excluding the WHERE keyword) identifying the
    rows to be merged into the parent workspace. Example:
    'department_id = 20'
    Only primary key columns can be specified in the WHERE clause,
    except in a subquery. The subquery can refer to columns that are not
    primary keys, but it cannot refer to a version-enabled table.>
    I expect Ben Spechard will confirm that hundreds of workspaces is no problem, which would mean no need to use the MergeTable where clause stuff.

    Test the locking effects as you merge, but I don't think LIVE suffers from this as much as other workspaces.

    I have only touched the surface of WM so far, so please take this as opinion/guess not fact.
  • 2. Re: Long Transactions Feasibility
    Stefan Jager Journeyer
    Currently Being Moderated
    Haven't done much with WM this last year, but here go my two cents worth:
    bmarbury wrote:
    Our application has a new use case for transactions that require inserts/updates to approximately 20 - 30 tables to be "saved" and not completely committed to the database or rolled back until specific external user events occur. The events that would trigger the commit or rollback can occur days, weeks, or months after the transaction is initiated and the changes are saved.
    That's WM for you right there.
    The transactions require records from all live/production tables (existing unchanged records), including non-versioned tables.
    No problem.
    We don't want users of the "live" tables to view the transaction changes until they are committed/merged.
    Again, that is exactly what WM allows you to do.
    We want each long transaction to have it's own discreet unit of work so only the changes made for a single transaction are committed or rolled back together. We do not want changes from other transactions to be included during a commit or rollback (i.e. merge or refresh).
    Again, you're almost reading the specs of WM here :-)
    The application can have many (hundreds) of these long transactions pending commit/rollback at any given time.
    Some of the tables contain a few million records.
    Not a problem. One of my customers has a database with (roughly) 80 tables, all of which contain (excluding the history!!!) between 5 and 10 million rows. They have approximately 25-50 users working on this database at the same time, usually on multiple projects per user, where each project has it's own workspace. The biggest problem you face here is your hardware and your backups - you need enough space for that but since diskspace is fairly cheap these days that shouldn't be a problem, making your network the biggest bottleneck.
    Also, versioning or unversioning reasonably large tables like these can take some time, but that is only once at initial setup of your database.
    Workspace Manager looks like a potential solution for these transactions. The documentation explicitly states that WM can be used to implement long transactions.
    It is.
    However, from my limited knowledge, it looks like each transaction in our new use case would require it's own workspace.
    Is this assertion correct? (Discreet long transactions would have to be isolated in their own workspaces, hundreds in our case, in order to be committed/rolled back together without including changes from other transactions)
    Or, can multiple long transactions with discreet units of work (i.e. long savepoints) be implemented in one workspace, and be merged into the live workspace separately?
    If the assertion above is correct, is it possible and advisable to design a use case that could create hundreds of separate workspaces in the database?
    Well, this really depends on your business model. I usually try to keep my technical data model as close to the required business data model. So if your business model has large projects with every project containing multiple units of work that need to be committed during the project lifetime, you could create a workspace for each project, then use savepoints to create and commit the individual units of work. From what you describe I do not think this is what you want, so I think in your case I would stick with the workspaces, and look into the use of child-workspaces. This is what the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_intro.htm]WM documentation (par 1.1.2.1) says:
    Documentation says:
    On the other hand, creating a child workspace is convenient for providing an isolated environment in which a complex set of changes can be made, completely removed from the parent workspace (for example, the
    production data). If you want to set up an independent environment for a scenario, and if regular users in the parent workspace do not need access to this scenario's data, you probably want to create a child workspace
    instead of simply creating a savepoint in the parent workspace.
    So I would stick with that.

    Depending on whether you want to save history, that will probably become a bigger problem after a while. If you have a lot of edits you'll accumulate a lot of history as well, which will grow the tables quickly. But using well-thought out partitioning, that should not pose much of a problem for your performance.

    HTH,
    Stefan
  • 3. Re: Long Transactions Feasibility
    Ben Speckhard Pro
    Currently Being Moderated

    Hi,

     

    What you are describing is exactly what Workspace Manager was designed for.  Workspace exists for as long as you want, without any limit on how long the workspace can remain open.  I would recommend sticking with a workspace per project unless you have a business reason to group them.  Workspaces are very lightweight objects, so having hundreds, thousands or more should not be a problem.  They use no storage in a versioned table until the table itself is edited as which the point only the edit is added to the table, not a copy of any additional rows. As mentioned above, you do have the capability to merge a subset of the modified rows by using MergeTable, if you desire.

     

    Your description didn't mention it, but are these projects working on completely distinct sets of rows, or would there be the potential for conflicts?  If there is, I would recommend looking at the documentation that discusses conflict resolution.  Essentially, when resolving a conflict you have the option to resolve to the parent, child, or base/common ancestor row.

     

    If you have any additional questions, just let me know.

     

    Regards,

    Ben

  • 4. Re: Long Transactions Feasibility
    bmarbury Newbie
    Currently Being Moderated

    Thanks for the reply.  This was helpful.

     

    Are the rows also updated in normal short transactions ? If so, that would create new versions of the rows in LIVE when rows are updated after implicit savepoints are created by creation of a new workspace. If the only updates happen via workspaces, millions of live rows that have never changed are reasonably fast to access.

     

    Yes, the rows in the versioned tables would also be updated in normal short transactions in the LIVE workspace.  We would anticipate having to do conflict resolution.  We don't need a history of versions in LIVE so we would probably compress the LIVE workspace frequently.

     

    Using the where clause on a merge is preferrable to creating a child workspace for every transaction.  However, we would probably have some work to do in order to conform to the rules for versioning tables (i.e. primary key requirement).

  • 5. Re: Long Transactions Feasibility
    bmarbury Newbie
    Currently Being Moderated

    Thank you for the reply.

     

    The application domain is OLTP.  So, we don't have the concept of projects and users working on projects.  The application changes are for transactions, not for projects that are versioned or "what if" scenarios.  The nature of the use case is that transaction changes may or may not get committed to the live tables after a long period of time.

     

    Since we don't need to keep a history of versions in the live tables (only pending changes), we would probably compress the tables frequently.  If versioning could be turned off in the live workspace, that would be best.


  • 6. Re: Long Transactions Feasibility
    bmarbury Newbie
    Currently Being Moderated

    Thanks Ben.

     

    See my replies to the other users.  The replies address the fact that our use case is related to transactions not projects (probably a subtle distinction); we would anticipate dealing with conflict resolutions; and if possible, we would prefer merging a subset of modified rows from one child workspace over dynamically creating and managing child workspaces for each transaction.

     

    The biggest concern at this point is the impact on infrastructure and potential impact on performance.  The application is fairly mature (over 20 years), with many lines of code across multiple delivery channels (Web, N-Tier, IVR, Oracle Tools such as Forms).  The DBA's and architects are concerned about the current and future implications of the object changes related to enabling versioned tables (i.e. added restrictions, added maintenance, and impact on our current object change procedures).  We are a large shop and have a lot of controls and processes in place related to change management and compliance.  The performance concern is related to the volume of existing code that would have to be analyzed and possibly modified.

     

    Having said that, WM seems to be an ideal solution for the use case.  All other options considered such as temp tables, db triggers, separate databases or schemas, etc. seem to require far more development effort and have their own drawbacks related to maintenance.

     

    Again, any other option suggestions are welcomed.

  • 7. Re: Long Transactions Feasibility
    davidp 2 Pro
    Currently Being Moderated

    It sounds like you should use one workspace per transaction unless testing proves that is a problem - it gives you what you are after "out of the box: rather than requiring extra home-grown logic.

     

    A couple of caveats that should be in the documentation but aren't:

    Two kinds of SQL do not work on workspace manager versioned tables:

    • MERGE
    • UPDATE of a join (which is the obvious alternative to MERGE) e.g.

    {code}

    update (select d.postcode, s.new_postcode , d.v1, d.v2, d.v3, s.nv1, s.nv2, s.nv3

            from suppliers d join sup_data s on d.supplier = s.supplier)

    set postcode= new_postcode, v1 = nv1, v2 = nv2, v3 = nv3 ;

    {code}

    That means "application SQL statements ... continue to work in the usual way with version-enabled tables" isn't true if you use MERGE or UPDATE joins.

     

    Both of these are because of limitations or problems with handling of INSTEAD OF triggers - Workspace manager uses views with INSTEAD OF triggers. MERGE is a known enhancement request 7829229. The update one may be a bug 16889567 (bug currently in triage)

  • 8. Re: Long Transactions Feasibility
    Stefan Jager Journeyer
    Currently Being Moderated

    bmarbury wrote:

     

    The application changes are for transactions, not for projects that are versioned or "what if" scenarios.  The nature of the use case is that transaction changes may or may not get committed to the live tables after a long period of time.

    OK, that makes things a bit easier, because that's more predictable.

     

    bmarbury wrote:

     

    Since we don't need to keep a history of versions in the live tables (only pending changes), we would probably compress the tables frequently.  If versioning could be turned off in the live workspace, that would be best.

    Not a problem. When adding a tale to WM, it's still called "version-enabling", but you can tell WM that there is no need for history, so it won't keep track of history.

     

    bmarbury wrote:

     

    The biggest concern at this point is the impact on infrastructure and potential impact on performance.  The application is fairly mature (over 20 years), with many lines of code across multiple delivery channels (Web, N-Tier, IVR, Oracle Tools such as Forms).  The DBA's and architects are concerned about the current and future implications of the object changes related to enabling versioned tables (i.e. added restrictions, added maintenance, and impact on our current object change procedures).  We are a large shop and have a lot of controls and processes in place related to change management and compliance.  The performance concern is related to the volume of existing code that would have to be analyzed and possibly modified.

    Understandable. As DavidP already mentioned, current applications that simply access the tables for viewing should not have a problem. In that respect WM is quite transparent. There is however an impact on changing tables, columns and such, that will need to be investigated when moving existing stuff over. You also need to check your object names, since WM adds a little extra to object names the limitation on the length of the object name is a little more strict that normal. And your editing applications need to be made aware of workspaces. Limitations on existing triggers are fairly well documented, so with a bit of studying you should be able to get a fairly well-estimated impact analysis. Best place to start is the documentation: read it carefully and thoroughly (and prefereably have your DBA's and Architects read it too).

     

    And of course if you have questions you can always come back here

     

    HTH,

    Stefan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points