7 Replies Latest reply on Aug 6, 2011 2:30 PM by 343896

    Is Changing the triggers used for fast refresh a bad idea?

    880538
      We used to have all users using disconnected clients and having an oracle lite database on each client. We are moving to a web based solution, so now we have half of the user base still on the Oracle lite clients, and half on the web updating the same schema directly. For the most part everything is good. We are seeing deadlocks on some updates though. In thinking about things, we will know if the record is being updated from the web and were considering changing the TABLENAMEu triggers to not update the mobileadmin.C$all_sid_logged_tables to show that it is dirty for that change as well as prevent updates to the cvr$ table. We will still allow inserts to have the dirty flag set as that is not causing issues. It is just the updates that will have the updates to the cvr$ and C$all_sid_logged_tables blocked.

      The users of our system will either be on the web or on the disconnected client, and not allowed to use both. So the clients won't need to get any updates.

      Just wondering if anyone has done this? Or just what people would think of altering these mobile server triggers? What other ramifications might this approach have?

      We would only change these on a few of our tables, so it wouldn't be a sweeping change. This is on Oracle Lite 10g by the way.
        • 1. Re: Is Changing the triggers used for fast refresh a bad idea?
          vasileios
          it depends on your business if you are 100% sure that that is what you need then go ahead. you simply have to make sure that the clients will not try to update a row which might has been already updated by the web or else you might get an error.

          btw i remember that in 10g there used to be 3 triggers for each table but now in 10gr3 with the latest patches there is only one trigger for all delete-insert-update. so keep that in mind if you patch/upgrade yours in the future,
          1 person found this helpful
          • 2. Re: Is Changing the triggers used for fast refresh a bad idea?
            880538
            Thanks for your input. We have data subsetting in place by using a sales rep number. And a sales rep is either on the web or is disconnected, so there will never be an instance of that record in a disconnected client updating that record. If for some reason a salesrep moves back to the disconnected client, I plan on running a script that would update all of his data before we set him up in mobile server again and do an initial sync. That would increase the version number for all of the records and make sure that data would get into the queues during MGP Compose

            We do have the three triggers, I planned on leaving the insert trigger alone, and letting that do its work as normal. I planned on leaving the delete alone as well so that it would delete things out. I would just change the update trigger to prevent the deadlocks that are occurring. Or would you recommend changing all of them so absolutely no versions of these records get into these mobile server tables?

            Edited by: user7751282 on Aug 5, 2011 8:51 AM
            • 3. Re: Is Changing the triggers used for fast refresh a bad idea?
              343896
              Consider changing your heaviest hit tables to Queue Based refresh. The problem with heavy transaction tables is that they can cause blocking locks with MGP and Sync if they are updated at high intervals because of the hit the C$ALL_SID_LOGGED tables. The Queue Based refresh removes the use of triggers but you will have to put in your own sync logic... ie date stamp, or another mechanism. You also won't have the use of the CVR$ table. This is just for composing data that gets downloaded to the client though. Uploads and Updates are pretty straight forward.

              You can also use dependency hits for tables that are affected by other tables. So, lets say you have a table that maps users to regions and you change the data in this mapping table, but want data in region to get composed as well, you would put a dependency hit on the region tables based on the mapping table.

              Greg Rekounas
              www.rekounas.org
              1 person found this helpful
              • 4. Re: Is Changing the triggers used for fast refresh a bad idea?
                880538
                This is the last year that we will be having Oracle Lite as part of our architecture (all users will be web based next summer). changing to a queue based refresh would be too much of a change considering we are at end of life with olite. We just need to make it through this year and then we will be removing these tables from the mobile app and thus the triggers will go away. That is why I am considering the bandaid of adding logic in the triggers to ignore changes from the web.

                Would you foresee any issues of not propogating changes through the mobile server C$ALL_SID_LOGGED and CVR$ tables, if no disconnected client would need to receive those changes?
                • 5. Re: Is Changing the triggers used for fast refresh a bad idea?
                  343896
                  Queue based probably wouldn't take you that long if you already have some type of auditing on your table(s).

                  Do you know what tables are causing you the grief?

                  Greg Rekounas
                  www.rekounas.org
                  • 6. Re: Is Changing the triggers used for fast refresh a bad idea?
                    880538
                    So far we have seen the deadlock on the <TableName>u trigger of 3 tables in our production environment:

                    JDOE_IOS
                    JDOE_IO_ITEMS
                    JDOE_IOS_SERVER

                    So it is either the C$ALL_SID_LOGGED table or the CVR$ table corresponding to the table above.

                    A DBA is looking into whatever logs there are to track down the exact culprit. I am the lead web developer on this application and am just doing some legwork of possible solutions once we track down the exact culprit.

                    Since I am not an Oracle developer, I would need to work with someone to implement queue based refresh. These tables have audit fields on them, but there is nothing on the tables that sets them (application doing the inserts/updates are responsible for setting them). I would be worried about manual scripts being run that forget to set the audit info and I am not confident that the disconnected app has the audit info being set with every operation. We could add defaults to the table, but that wouldn't help the disconnected clients either. To me, there would be a risk of missing updates?

                    With changing the refresh type - would the oracle lite clients be at risk of a full refresh when we change over? I would be worried about clients losing unsynced data, etc

                    Thanks for taking the time to chat about this, by the way!

                    Ben
                    • 7. Re: Is Changing the triggers used for fast refresh a bad idea?
                      343896
                      I would recommend some things, but not knowing what your sync logic is for your application it could be risky and I wouldn't guarantee success.

                      Basically, changing the snap shots at this point, would cause a complete refresh for those clients. If there was a change to the triggers not to touch the C$ALL_SID_LOGGED tables, and then to have a some sort of mechanism update the table at some interval, that might get around your issues. But as I say, I haven't used that logic and you wouldn't get support from oracle if you run into issues.

                      I do provide consulting service and development services for something like this... my contact information is greg@rekounas.org.

                      Greg Rekounas
                      www.rekounas.org