Oracle英文版培訓課件之Implement Streams：les16

DataConflictsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DistinguishbetweenthedifferenttypesofdataconflictsDescribehowconflictsaredetectedListmethodsforavoidingdataconflictsWhatIsaReplicationConflict?Whentwoormoretransactionsatdifferentdatabasesupdatethesameshareddata,aconflictcanoccur.AnerrorisgeneratedbytheapplyengineiftherowwithintheDMLLCR:DoesnotexistatthedestinationsiteHasabeforeimageforacolumnfromthesourcesitethatdoesnotmatchthecurrentcolumnimageatthedestinationsiteWouldviolateaconstraintifimplementedatthedestinationsiteConflictExample:ReservationSystemUPDATEseatsSETticket_id='1QA872'WHEREseat_id='12A'ANDflight=934;UPDATEseatsSETticket_id='4HW127'WHEREseat_id='12A'ANDflight=934;SITE1SITE2?ErrorQueueUPDATEemployeesSETmanager_id=108WHEREemployee_id=115;UPDATEemployeesSETdepartment_id=10WHEREemployee_id=115;SITE1SITE2?TypesofDataConflictsUpdateDeleteUniquenessForeignkeyIdentifyingRowsinConflictStreamsidentifiesarowbyusing:Primarykey(defaultDesignatedalternatekeySubstitutekeywillbeusedfirst,ifdefined.Primarykeyservesasidentifiercolumn.SpecifyingSubstituteKeyColumnsSQL>CONNECTstrmadmin/pwd@SITE2SQL>BEGIN2DBMS_APPLY_ADM.SET_KEY_COLUMNS(3object_name=>'hr.employees',4column_list=>'first_name,last_name,5hire_date');6END;7/SQL>CONNECTsys/pwd@SITE1ASSYSDBASQL>ALTERTABLEhr.employeesADD2SUPPLEMENTALLOGGROUPlog_emp_supkey3(first_name,last_name,hire_date)ALWAYS;DetectingConflictsAnapplyprocessdetects:AnupdateconflictiftheoldcolumnvaluesinanLCRdonotmatchthecurrentcolumnvaluesforthesamerowinthetableAuniquenessconflictifauniquenessconstraintviolationoccurswhenapplyinganLCRAdeleteconflictwhenarowwiththesamekeycolumnvaluesascontainedinarowLCRcannotbefoundinthedestinationtableAforeign-keyconflictifaforeign-keyconstraintviolationoccurswhenapplyinganLCRDataConsistencyandConvergenceSharedDataSourcesAvoidconflictsthroughwork-flow,applicationcode,securitymechanisms,anddatapartitioningResolveconflictswithhandlersandproceduresSingleDatabaseConstraintsTriggersProceduralobjectsApplicationcodeRemediesSharedDataSourcesGlobal

data

convergenceSingleDatabaseData

integrityLocalconsistencyChallengesConflictAvoidanceand

ResolutionFoundationSoundandrobustlogicaldesignWell-documentedobjectdependenciesWell-documentedapplicationlogicClearlydefinedapplicationandmoduleboundariesClearlydefinedupdaterightsandresponsibilitiesWell-understoodworkflowconceptConflictResolutionResponsibilitiesStreams,DBA,programmer,userResolvingconflictsStreamsDetectingDMLconflictsDBA,programmer,userAvoidingconflictsResponsiblePartyGoalAvoidingConflictsbyAssigning

DataOwnershipPrimarysiteownershipRow-levelsubsettingColumn-levelsubsettingDynamicsiteownershipWorkflowTokenpassingSharedownershipPrimarySiteOwnershipTable

XTable

XTable

XTable

XRowsubsettingColumn

subsettingSite1Site2Site2Site1DynamicSiteOwnership:

WorkflowMethod1621631645791001632cust-noorder-nostatusOrderstableShippingsite1621631645791001632cust-noorder-nostatusOrderstableBillingsite5791001632cust-noorder-nostatusOrderstableOrderentrysite162163164shippablebillableopenordershippablebillableopenordershippablebillableopenorderSharedOwnershipOrdersHQOrdersCustomerorderentryOrdersCustomerorderentryAvoidingSpecificTypesofConflictsUniquenessconflictsDeleteconflictsUpdateconflictsAvoidingUniquenessConflictsUsesite-specific,primary-keycomponents:Usesequencesinidentificationkeys:Concatenategloballyuniquevalue(forexample,DBname)toalocalsequenceCreatenonoverlappingindividualsequencesat

eachsourcesiteforuseinidentificationkeysSQL>SELECTsys_guid()"UID"FROMdual;

UID

------------------

48D4F888045E11D49A67000000000000AvoidingDeleteConflictsDonotdeleterowsinasharedownershipdatamodel.Useacolumntomarkrowsfordeletion.Purgemarkedrowsonaperiodicbasis.SuppressingConflictDetectionWill

BillyWilliam15NicknameNameIDUPDATEix.attendeesSETnickname='Billy'WHEREid=15;Will

BillWilliam15NicknameNameIDUPDATEix.attendeesSETnickname='Bill'WHEREid=15;SITE2SITE1SuppressingConflictDetectionTospecifythatconflictdetectionshouldnotbeperformedfortheNICKNAMEcolumn,usethefollowingcode:BEGINDBMS_APPLY_ADM.COMPARE_OLD_VALUES(object_name=>'IX.ATTENDEES',column_list=>'NICKNAME',operation=>'*',compare=>false);END;/CascadingDeleteOperationsDelete

cascadeDelete

cascadeSite1Site2StreamsDELETEFROMHR.EMPLOYEES@SITE1.NETWHEREemployee_id=176;Job_

HistoryEmployeesJob_

HistoryEmployeesSummaryInthislesson,youshouldhavelearnedhowto:DistinguishbetweenthedifferenttypesofdataconflictsDescribehowconflictsaredetectedListmethodsforavoidingd