7 Replies Latest reply: Aug 6, 2011 9:30 AM by 343896 RSS

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

      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?
          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,
          • 2. Re: Is Changing the triggers used for fast refresh a bad idea?
            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?
              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
              • 4. Re: Is Changing the triggers used for fast refresh a bad idea?
                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?
                  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
                  • 6. Re: Is Changing the triggers used for fast refresh a bad idea?
                    So far we have seen the deadlock on the <TableName>u trigger of 3 tables in our production environment:


                    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!

                    • 7. Re: Is Changing the triggers used for fast refresh a bad idea?
                      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