This content has been marked as final. Show 7 replies
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.1 person found this helpful
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,
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
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.1 person found this helpful
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.
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?
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?
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!
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 email@example.com.