Next-Gen Transactions in Oracle Database 23ai [LRN3353].pdf

《Next-Gen Transactions in Oracle Database 23ai [LRN3353].pdf》由會員分享，可在線閱讀，更多相關《Next-Gen Transactions in Oracle Database 23ai [LRN3353].pdf（54頁珍藏版）》請在三個皮匠報告上搜索。

1、This presentation is intended to outline our general product direction.It is intended for information purposes only,and may not be incorporated into any contract.It is not a commitment to deliver any material,code,or functionality,and should not be relied upon in making purchasing decisions.The deve

2、lopment,release,timing,and pricing of any features or functionality described for Oracles products may change and remains at the sole discretion of Oracle Corporation.Statements in this presentation relating to Oracles future plans,expectations,beliefs,intentions,and prospects are“forward-looking st

3、atements”and are subject to material risks and uncertainties.A detailed discussion of these factors and other risks that affect our business is contained in Oracles Securities and Exchange Commission(SEC)filings,including our most recent reports on Form 10-K and Form 10-Q under the heading“Risk Fact

4、ors.”These filings are available on the SECs website or on Oracles website at http:/ information in this presentation is current as of September 2024 and Oracle undertakes no duty to update any statement in light of new information or future events.Some regulatory certifications or registrations to

5、products or services referenced herein are held by Cerner Corporation.Cerner Corporation is a wholly owned subsidiary of Oracle.Cerner Corporation is an ONC-certified health IT developer and a registered medical device manufacturer in the United States and other jurisdictions worldwide.The materials

6、 in this presentation pertain to Oracle Health,Oracle,Oracle Cerner,and Cerner Enviza which are all wholly owned subsidiaries of Oracle Corporation.Nothing in this presentation should be taken as indicating that any decisions regarding the integration of any EMEA Cerner and/or Enviza entities have b

7、een made where an integration has not already occurred.Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates1Forward-Looking statementsThe following is intended to outline our general product direction.It is intended for information purposes only,and may not be incorporated into any contract

8、.It is not a commitment to deliver any material,code,or functionality,and should not be relied upon in making purchasing decisions.The development,release,timing,and pricing of any features or functionality described for Oracles products may change and remains at the sole discretion of Oracle Corpor

9、ation.The materials in this presentation pertain to Oracle Health,Oracle,Oracle Cerner,and Cerner Enviza which are all wholly owned subsidiaries of Oracle Corporation.Nothing in this presentation should be taken as indicating that any decisions regarding the integration of any EMEA Cerner and/or Env

10、iza entities have been made where an integration has not already occurred.Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates2Safe harbor statementNext-Gen TransactionsAjit Mylavarapu,VP,Transactions&AppDev Technologies,OracleChristian Trieb,Senior Database Administrator,Paragon DataTirtha

11、nkar Lahiri,SVP,Data and In-Memory Technologies,Oracle3Source:Gartner Critical Capabilities for Cloud DBMS for Analytical Use Cases Rick Greenwald,Henry Cook,Xingyu Gu,Aaron Rosenbaum,Ramke Ramakrishnan,Adam Ronthal,8 January 2024Gartner is a registered trademark of Gartner,Inc.and/or its affiliates

12、 in the U.S.and internationally and is used herein with permission.All rights reserved Gartner does not endorse any vendor,product or service depicted in its research publications,and does not advise technology users to select only those vendors with the highest ratings or other designation.Gartner

13、research publications consist of the opinions of Gartners research organization and should not be construed as statements of fact.Gartner disclaims all warranties,expressed or implied,with respect to this research,including any warranties of merchantability or fitness for a particular purpose.Oracle

14、(Autonomous Transaction Processing)scored highest out of 16 vendors in all three use cases in the 2023 Gartner Critical Capabilities for Cloud Database Management Systems for Operational Use CasesSo,where do we go from here?#1Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates5Oracle Cloud

15、World Copyright 2024,Oracle and/or its affiliates6Introducing Next-Generation TransactionsStateless ProtocolsScalabilityResilienceWhat do modern applications want?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates7.along with all the strong ACID guarantees8Oracle CloudWorld Copyright 2024

16、,Oracle and/or its affiliatesStateless ProtocolsScalabilityResilienceWhat do modern applications want?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates9Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates10Sessionless TransactionsTransactions for a stateless worldSessionless Tra

17、nsactions:Background Mission-critical applications often perform complex OLTP Interactive transactions allow application logic to be executed between database callsOracle CloudWorld Copyright 2024,Oracle and/or its affiliates111.Update the audit table to log the operation2.Lock the SOURCE account an

18、d DESTINATION account rows3.Select balance and other details from SOURCE account4.Select balance and other details from DEST account5.Run business checks on SOURCE6.Run business checks on DESTINATION7.Update SOURCE account8.Update DESTINATION account9.CommitA typical interactive transactionInteracti

19、ve Transactions Result In InefficienciesThe connection and session are held for a long time until transaction commitsConnection might have very low utilization because much of the time can be spent running business checksConnection cannot be used by other transactionsOracle CloudWorld Copyright 2024

20、,Oracle and/or its affiliates12XA Transactions Are Sometimes Used To Address This ProblemOracle CloudWorld Copyright 2024,Oracle and/or its affiliates13Use XA transactions to suspend the branch after a unit of workRequires the use of a mid-tier XA Transaction CoordinatorComplex with many moving part

21、sXA failures are difficult to debug and can result in system-wide outagesNext-gen Solution:Sessionless Transactions Sessionless Transactions enable stateless paradigm for applications Sessionless transactions can be suspended after performing a unit of work Suspended transactions can be resumed The

22、database keeps the row locks,so applications do not need to validate previous statements No need to coordinate an XA transaction Connection and session resources can be released between calls to databaseOracle CloudWorld Copyright 2024,Oracle and/or its affiliates15Sessionless TransactionsOracle Clo

23、udWorld Copyright 2024,Oracle and/or its affiliates161.Update audit table to log the operation2.Lock the SOURCE and DESTINATION account rows3.Select balance and other details from SOURCE account4.Select balance and other details from DEST account 5.Run business checks6.Run business checks7.Update SO

24、URCE account8.Update DESTINATION account9.CommitStart Sessionless Transaction.SuspendSession 1Resume Sessionless Transaction.CommitSession 2Sessionless Transactions Advantages Over Transaction Managers With Sessionless Transactions,Oracle Database itself manages a single transaction across RACNo nee

25、d for XA protocolNo need to manage branch information in TMNo need to coordinate 2-Phase CommitNo need for complex recovery mechanismNo need for a TM Allows the sessions and connections to be multiplexedOracle CloudWorld Copyright 2024,Oracle and/or its affiliates17MicroservicesSessionless Transacti

26、onsRAC ClusterClient Applicationtransaction branchConnection PoolSessionless Transactions:ExampleOracle CloudWorld Copyright 2024,Oracle and/or its affiliates18Value-based Concurrency ControlNext generation lock-free concurrency control for stateless applicationsConventional concurrency control is a

27、 poor fit with stateless APIsStateless APIs are used widely due to their scalability and simplicityHTTP GET and PUT are stateless APIsTransactions and locks cannot span completely stateless callsCopyright 2024,Oracle and/or its affiliates22Lock-Free Concurrency Control How it WorksCopyright 2023,Ora

28、cle and/or its affiliates23TirthankarIn the HTTP protocol,an ETAG is a signature or fingerprint for the contents of a web page Oracle uniquely extends HTTP ETAGs into the core database to implement lock-free concurrency controlWhen rows are read from the database,you can choose to also read the ETAG

29、 for the selected data.DatabaseAppRead RowsChange RowsETAGLock-Free Concurrency Control How it WorksCopyright 2023,Oracle and/or its affiliates24TirthankarWhen the modified rows are written back into the database The database verifies that the underlying rows still match the ETAG computed by the rea

30、d If the rows match the ETAG,the rows are atomically updated If not,another user has changed the data and the write operation is rejected The write can be retried using the new data DatabaseAppWrite RowsChange RowsETAGReject if Stored Data ChangedCalled Optimistic Concurrency ControlCopyright 2023,O

31、racle and/or its affiliates25Oracles unique value-basedconcurrency control architectureTirthankarValue-Based Concurrency ControlETAG concurrency control is value-basedConflicting updates are detected by examining the data values themselvesNot by adding locks or versions to the dataCopyright 2023,Ora

32、cle and/or its affiliates26TirthankarValue-Based for Pure Relational DataCopyright 2023,Oracle and/or its affiliates27Tirthankar Great for interactive or mobile applications that directly access tables-Select the ETAG along with data columnsSELECT stid,name,sinfo,SYS_ROW_ETAG(stid,sinfo)FROM student

33、 WHERE name=JILL;Application think time-Validate the ETAG before updating UDPATE student SET sinfo=New DataWHERE name=JILL AND SYS_ROW_ETAG(stid,sinfo)=F4ED.ETAG is also possible across rows!Copyright 2023,Oracle and/or its affiliates28Tirthankar-Select the ETAG along with data columnsSELECT SYS_AGG

34、_ETAGS(empsal,emptitle)FROM employeeWHERE dept_id=:1;Application think time-Update department budget only if employee salaries-and titles are unchanged since last readUPDATE dept d SET Budget=:1 WHERE dept_id=:2AND(SELECT SYS_AGG_ETAGS(SYS_ROW_ETAG(empsal,emptitle)FROM employee e WHERE e.dept_id=d.d

35、ept_id )=:3;Stateless ProtocolsScalabilityResilienceWhat do modern applications want?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates29Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates30Lock-Free Reservations Eliminate hotspots on frequently updated dataLock-Free Reservation

36、s:BackgroundHot spots are created due toMany concurrent sessions modifying small number of rows Long running transactions holding locksTransactions requiring user interactionExample:Inventory for popular items in an online storeOracle CloudWorld Copyright 2024,Oracle and/or its affiliates31Performan

37、ce Issues with Hot spotsOracle CloudWorld Copyright 2024,Oracle and/or its affiliates32Traditional locking on hot rows cause:Concurrency bottlenecks Lock waits Deadlocks Too restrictive for“aggregate fields”Fields on which an update operation adds to or subtracts from the quantity Examples:Inventory

38、,Ticketing,etc.T1T2T3LocksDurationApproaches to address hot spotsOptimistic approach Check for availability of QOH(Quantity on Hand)with a SELECT Check QOH and perform the actual update to QOH at transaction commit SELECT 1 FROM InventoryWHERE item_name=“Kitchen Towels”AND QOH=:required_amount;UPDAT

39、E InventorySET QOH=QOH-:required_amountWHERE item_name=“Kitchen Towels”AND QOH=:required_amount;33Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesDrawbacks of the Optimistic Approach Under Heavy LoadUpdates may not go through at commit timeMay not have enough QOH,if over promised(double

40、/over booking)!Abort the transaction at the endWasted work(esp.a long running transaction may have done lot of work)Abort entails additional work to rollback all the changes performed by the transactionLeads to system thrashingOracle CloudWorld Copyright 2024,Oracle and/or its affiliates34Next-Gen S

41、olution:Lockfree ReservationsIntroduce RESERVABLE column property for aggregate types along with optional CHECK constraint specificationJournal on-going reservationsIntroduce columns in a journal table to track reservationsNo lock on base table rows for reservationsConsult the journal to allow/rejec

42、t new reservations based on constraint satisfaction on the reservable column and pending reservationsRejected reservation requests lead to failing the update statementUnlike rollback of an older transaction in the Optimistic ApproachAcquire the locks only during transaction commit when the actual up

43、dates are performed 35Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesMechanism36Transactions Column in the table marked RESERVABLEEscrow JournalCommitted,AvailableT2UPDATE QOH=QOH-30COMMITT1UPDATE QOH=QOH-50T3UPDATE QOH=QOH-50COMMIT100100,50100,2070,2070,20Update Statement fails(Rollba

44、ck)20,207020100,100OKOKNOOracle CloudWorld Copyright 2024,Oracle and/or its affiliatesAssume inventory of 100Lockfree Reservation SyntaxCopyright 2024,Oracle and/or its affiliates 37CREATE TABLE Accounts(ID number Primary Key,Balance number RESERVABLE CONSTRAINT MinInventory CHECK(Balance=0),Name va

45、rchar2(10);New column propertyfor numeric dataOptional checkconstraintALTER TABLE Accounts ADD(Balance number RESERVABLE CONSTRAINT MinInventory CHECK(Balance=100);New RESERVABLE Column PropertyAlter table to add/modify RESERVABLE columnALTER TABLE Accounts MODIFY(Balance NOT RESERVABLE);Transaction

46、 throughput increase on updatesTransaction throughput increase on TPC-CLockfree Reservations PerformanceOracle CloudWorld Copyright 2024,Oracle and/or its affiliates3850X8XStateless ProtocolsScalabilityResilienceWhat do modern applications want?Oracle CloudWorld Copyright 2024,Oracle and/or its affi

47、liates39Priority TransactionsPrioritize transactions based on their importanceGeschftsbereich ORACLEDil.Inf.(FH)Christian TriebSenior DBA,OCS,OCA,OCP,OCE,ITIL certifiedDOAG Database Director,Head of DOAG Database Community,Former Head of DOAG Competence Center Support,BOARD Member,DOAG Vice Chair ww

48、w.doag.orgDegree of Computer ScienceEOUC Board Member,EOUC Vice ChairMember of the ORACLE Database and OEM Beta Test ProgramMember of the OEM Customer Advisory BoardORACLE Cloud Customer Connect Black DiamondSpeaker at User Groups and ORACLE EventsORACLE Database PartnerLab MasterORACLE Proactive Su

49、pport Voice of the CustomerORACLE Community Super EliteORACLE Supprt Community Gold TrophyMy ORACLE Support ExpertORACLE University LaunchPad MasterTwitter:DOAGeV,CTriebhttp:/ CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLEParagon Data GmbH83 employees at headquarters in

50、 Friedrichsdorf(Hessen)and Munich(Bavaria)Turnover of 13 millions Industries:retail,logistics,public sector,pharma,banksFlat hierarchies,open communication,flexibilityComprehensive consulting with a focus on practiceIndividually optimized solutionsOracle PartnerStrong partner networkhttp:/www.parago

51、n-data.de/index.html42Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLESituationBlocking sessionOther transactions can not workDBA:Gets information about slow database Slow or no answer from a transactionDBA:Analyzing timeDBA:Alter system kill sessionUser/Developer:

52、Restart statement 43Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLESolutionBlocking sessionOther transactions can not workAutomatic:Abort blocking transactionBlocking transaction gets rolled back,has to be acknowledged by application before submitting more workLes

53、s work for User/Developer/DBATime for other things to do44Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLERequirementsSetting of waiting timeFor the database45ALTER SYSTEM SET priority_txns_high_wait_target=;ALTER SYSTEM SET priority_txns_medium_wait_target=;Oracle

54、 CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLERequirementsSetting priorityFor the sessionOnly Transactions with lower priority will be killed!Transactions with high priority will never be killedDefault:HIGHDefault:HIGHALTER SESSION SET txn_priority=“”;Oracle CloudWorld

55、 Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLEProcessThe database detects blocking sessionWaiting until priority_txns_high/medium_wait_target is reachedIf the blocker transaction has a lower priority,the blocker session is rolled backRollback acknowledgment through application st

56、atement Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLEMonitoring Priority TransactionsV$TRANSACTION2 new columns showing priority and wait targetsV$SYSSTATSNew stats in AWR reportsAlertsDetails in alert log when any transaction is rolled backOracle CloudWorld Cop

57、yright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLEConclusionGood featureHelpful for better working of the databaseReduce administration timeWhy is it necessary to acknowledge the transaction rollback?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesGeschftsbereich ORACLEThank

58、 YouParagon Data GmbH DOAG(German Oracle User Group)OracleChristian Trieb /Version 1 /11.09.2024DOAG 2024 Conference+ExhibtionTransaction QuarantineDatabase resiliency against errors due to block corruptionsTransaction Recovery BackgroundFailures are a reality in large-scale systemsSome failures res

59、ult in unfinished transactions requiring transaction recoverySHUTDOWN IMMEDIATE/ABORT Transaction recovery recovers transactions without user intervention Recovery applies undo to database blocks to restore previous imageCopyright 2024,Oracle and/or its affiliates53What can go wrong?Recovery can fai

60、l due to block corruptions,lost writes,and other errorsPhysical data corruption of database blocks(ORA-01578,ORA-28304)Logical data corruption(ORA-00600)Memory corruption(ORA-00602,ORA-07445)State Corruptions(ORA-00600)Failure during recovery is a serious problemBut,it should not lead to a system-wi

61、de outage due to cascading failuresResilience is extremely critical in a multi-tenant environment!Copyright 2024,Oracle and/or its affiliates54Next-gen solution:Transaction Quarantine Transactions are automatically quarantined if there is a failure during Transaction RecoveryRecovery process continu

62、es recovering other transactionsWhat happens to the un-recovered transaction?Transaction and error information is published to a view that is queried by AdminAdmin can rectify the underlying error and re-attempt transaction recoveryCopyright 2024,Oracle and/or its affiliates5560451,00000,cannot exec

63、ute the DML because it encountered a quarantined transaction(%s.%s.%s)holding a row lock(TX)required by this statementTransaction Quarantine Manageability Quarantined transactions and errors are loggedAttention Log and Error LogSYS.ALERT_QUE logs the error persistently Alert is exposed in DBA_OUTSTA

64、NDING_ALERTS and DBA_ALERT_HISTORY views DBAs can view all quarantined transactions in the dictionary view and recover them manuallyDBA_QUARANTINED_TRANSACTIONS After recovery is complete,transactions can be removed from this view by issuing a DDLCopyright 2024,Oracle and/or its affiliates56ALTER DATABASE DROP TRANSACTION QUARANTINE ;Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates57Stateless ProtocolsScalabilityResilience