2025甲骨文全球AI大会：Next-Gen Transactions in Oracle AI Database 26ai

Ajit

Mylavarapu,VP,Transactions&AppDevTechnologiesSamrat

Roy,Sr.Manager,DatabaseEngineeringTirthankarLahiri,SVP,MissonCriticalDataandAITechnologiesNext-GenTransactionsOracle

is

a

leader

inOLTPTransactions

UseCaseshttps://www.gartner.com/doc/reprints?id=1-2KLGMTIL&ct=250324&st=sb

3ScalabilityChangeTrackingConcurrency2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld4...built

into

the

Oracle

Converged

Database5Oracle

CloudWorldCopyright©

2024,Oracle

and/or

its

affiliatesScalabilityChangeTrackingConcurrency2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld6Lock-Free

ReservationsEliminatehotspotsonfrequentlyupdateddata23•

Hot

spots

are

created

due

to•Many

concurrent

sessions

modifying

small

number

of

rows•Long

running

transactions

holding

locks•Transactions

requiring

user

interactionExample:Inventory

for

popular

items

in

an

online

storeLock-FreeReservations:Background2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld8Approaches

to

address

hot

spotsOptimisticapproach•CheckforavailabilityofQOH(QuantityonHand)withaSELECTUPDATE

InventorySETQOH

=QOH

-

:required_amountWHERE

item_name

=

“Kitchen

Towels”AND

QOH

>=

:required_amount;SELECT

1

FROM

InventoryWHERE

item_name

=

“Kitchen

Towels”ANDQOH

>=

:required_amount;•CheckQOHandperform

theactualupdate

toQOHattransactioncommit9OracleCloudWorldCopyright©2024,Oracleand/oritsaffiliatesDrawbacksoftheOptimisticApproachUnderHeavyLoad•Updates

may

not

go

through

at

commit

time•May

not

have

enough

QOH,

if

over

promised(double/over

booking)!•

Abort

the

transaction

at

the

end•Wasted

work(esp.a

long

running

transaction

may

have

done

lot

of

work)•Abort

entails

additional

work

to

rollback

all

the

changes

performed

by

the

transaction•Leads

to

system

thrashing10Oracle

CloudWorld

Copyright©

2024,

Oracle

and/or

its

affiliatesNext-Gen

Solution:Lockfree

Reservations•Introduce

RESERVABLE

column

property

for

aggregate

types

along

with

optionalCHECK

constraint

specification•

Journalon-goingreservations•

Consult

the

journal

to

allow/reject

new

reservations

based

on

constraint

satisfactionon

the

reservable

column

and

pending

reservations•Rejected

reservation

requests

lead

to

failing

the

update

statement•Acquire

the

locks

only

during

transaction

commit

when

the

actual

updates

areperformed11OracleCloudWorldCopyright©2024,Oracleand/oritsaffiliatesOKOKNO[100,

100][100,50][100,20][70,20][70,20]UpdateStatementfails(Rollback)[20,20]T1UPDATEcol=col-

50COMMIT1007020MechanismColumn

in

the

table

Transactionsmarked

RESERVABLET2UPDATEcol=col-

30COMMITT3UPDATEcol=col-

50EscrowJournal[Committed,Available]check

constraint:col>0Copyright©2024,Oracleand/oritsaffiliatesOracleCloudWorld12CREATE

TABLE

Accounts(ID

number

Primary

Key,Balancenumber

RESERVABLE

CONSTRAINTMinInventoryCHECK(Balance

>=0),varchar2(10));ALTERTABLEAccountsADD

(BalancenumberRESERVABLECONSTRAINTMinInventory

CHECK

(Balance>=

100));ALTERTABLEAccountsMODIFY

(Balance

NOTRESERVABLE);New

column

property

Optional

checkfor

numeric

data

constraintAltertabletoadd/modifyRESERVABLEcolumnLockfreeReservationSyntaxNew

RESERVABLEColumn

Property2024,Oracleand/orits

affiliatesCopyright

©Name13LockfreeReservationsPerformance50X

8XTransactionthroughputincreaseonTPC-CTransactionthroughput

increase

on

updates2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld14ScalabilityChangeTrackingConcurrency15Oracle

CloudWorld

Copyright

©

2024,

Oracle

and/or

its

affiliatesGreater

Transaction

Flexibility

and

Connection

Pool

ScalingSessionlessTransactions231.Update

the

audit

table

to

log

the

operation2.

Lock

the

SOURCE

account

and

DESTINATION

account

rows3.Select

balance

and

other

details

from

SOURCE

account4.Select

balance

and

other

details

from

DEST

account5.

Runbusiness

checks

on

SOURCE6.

Run

business

checks

on

DESTINATION7.

Update

SOURCE

account8.Update

DESTINATION

account9.

CommitSessionlessTransactions:Background•Mission-critical

applications

often

perform

complex

OLTP•InteractivetransactionsallowapplicationlogictobeexecutedbetweendatabasecallsAsimplifiedbalancetransfertransactionDatabaseMidTierBusiness

LogicDatabase2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld17Interactive

Transactions

Result

In

Database

Inefficiencies•The

connection

and

session

are

held

for

a

long

time

until

transactioncommits•Connection

might

have

very

low

utilization

because

much

of

the

time

canbe

spent

in"application

think

time"•Connection

cannot

be

used

by

other

transactions2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld18XATransactionsAreSometimesUsedToAddressThisProblem•Use

XA

transactions

to

suspend

the

branch

after

a

unit

of

work•Requires

the

use

of

a

mid-tier

XA

Transaction

Coordinator•Complex

with

many

moving

parts•XA

failures

are

difficult

to

debug

and

can

result

in

system-wide

outages19Oracle

CloudWorld

Copyright©

2024,

Oracle

and/or

its

affiliatesNext-genSolution:SessionlessTransactions•Sessionless

transactions

can

be

suspended

after

performing

some

work•Suspended

transactions

can

be

resumed•The

database

keeps

the

row

locks,so

applications

do

not

need

to

validatepreviousstatements•No

need

to

coordinate

an

XA

transaction•Connection

and

sessionresources

can

be

released

between

callsto

database2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld207.Update

SOURCE

account8.Update

DESTINATION

account9.

Commit1.Update

audit

table

to

log

the

operation2.

LocktheSOURCE

and

DESTINATION

account

rows3.Select

balance

and

other

details

from

SOURCE

account4.Select

balance

and

other

details

from

DEST

account5.

Run

business

checks6.

Run

business

checksSession2ResumeSessionless

Transaction...CommitSession

1StartSessionlessTransaction...SuspendSessionscanbe

used

byothertransactionsafterthetx

is

suspendedSessionlessTransactions21Oracle

CloudWorld

Copyright

©

2024,Oracle

and/or

its

affiliatesSessionlessTransactionsAdvantages•WithSessionlessTransactions,OracleDatabaseitselfmanagesasingletransaction

across

RAC•

No

need

for

XA

protocol•No

need

to

coordinate2-Phase

Commit•No

need

for

complex

recovery

mechanism•

NoneedforaTM•Allows

transactions

and

sessions

to

bemultiplexed22Oracle

CloudWorld

Copyright

©

2024,Oracle

and/or

its

affiliates•

Suspend

transaction

on

Connection

1•

Returnconnectiontopool•

Resume

transaction

on

connection2DatabaseInstancesConnectionConnection

1Connection2ClientApplicationPoolSessionlessTransactions:Example23Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesSessionlessTransactions:Example24Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesSessionlessTransactions:Example25Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesSessionlessTransactions:Example26Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesSessionlessTransactions:Example27Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesSessionlessTransactions:Example28Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesSessionlessTransactions:Example29Oracle

CloudWorld

Copyright©

2024,Oracle

and/or

its

affiliatesScalabilityChangeTrackingConcurrency2024,Oracleand/orits

affiliatesCopyright

©OracleCloudWorld38NotificationsComingSoonDatabaseNativeChangeDataCapture26Notifications:Background•Therearemanybusinessuse-casesthat

benefitfrom

knowingwhatdata

has

changed

since

a

certaintimestampandtheexacttimestampofthosechanges.•

This

data

can

be

used

for:•Incrementally

refreshing

a

midtiercache•Anyeventprocessingbasedondata

changes

such

as

sending

an

email•Triggeringaworkflowbasedonthechange

orevent40Notifications:Features•For

each

change

to

a

tracked

table,the

notification

feature

provides:•

Keys

that

changed•ExactSCN/timestampofthetransactioncommit•

Opcode•Anyotheroptionalcolumnsfromtherow•

OLDand

NEWvalues•

SYS_CONTEXTnamespaces•Autooruser-controlled

purging

policy41CREATEDIRECTIVE

emp_dirFOR

empNOTIFYONINSERT

UPDATE

DELETEUSINGNOTIFICATIONTABLEnotify_table_empTABLESPACE

emp_tbsNOTIFICATIONVIEWnotify_view_empWITHLOGGEDCOLUMNS

(empno,empname,

job,

mgr)

KEY

(empno)INCLUDINGOLDVALUESCONTEXTNAMESPACEUSERENVATTRIBUTESsid,

session_user(10)

ENABLE;NotificationDirectives42Copyright

©

2025,

Oracle

and/orits

affiliates

|

Confidential:

Internal/Restricted/Highly

RestrictedSamrat

RoySr.

Engineering

Manager,

Database

Platform,

PayPalCustomer

SpotlightAt

PayPal,

we

put

people

at

the

center

of

everything

we

do

.Mission&VisionWearerevolutionizingcommerceglobally.

With

thismission

as

ourNorthStar,

we

are

aligned

around

onecentralvision:

tomakesendingandreceivingmoney,selling,andshoppingsimple,personalized,

andsecure.PayPalplatformincludesBraintree,

Venmo,Xoom,HoneyandPayPalZettle

Users’paymentcardsarevaulted

Alltransactionsfollowingdefaultcard

change,should

pick

up

newcard

Users’balancecanshow

upwith

incorrectamountifnotconsistent

Trust

and

Brand

impact

BadUserexperience

Conversion

failure

at

checkout

Lag

aware

routing

Providesread-after-writeconsistency

Scale

reads

with

externalconsistency

Out-of-the-boxexternal

consistencyThank

YouSamrat

RoySr

EM,

Database

Platform,

PayPalAutonomousData

TimehouseAcloudserviceforrecordingandmanaginghistorical

data,fortemporalanalytics,auditingandcompliance.ComingSoon26ManyEnterpriseUseCasesforTimehouse•

Pointin

timereporting:Analyzedataatdifferentpointsin

time•Example–Runananalyticqueryasoflastquarter•Pointintimesnapshots:Createpoint-in-timesnapshotsoftables,schemasanddatabasesforsharing•

Temporalanalytics:Combinecurrentdatawithhistoricaldatawithin

thesamequery•Example-Compare

thenumberofemployeesundereachmanagerfrom

thepreviousyear

tocurrentyear•Repairlogicallycorruptdata:Repairusererrors(e.g.accidentalchanges)usingcapturedhistoricaldata•Datawarehousepipelines:IncrementallyrefreshDWfromOLTPdatabases•

MLandAI:Historicaldataisavaluable

traininginputforMLmodelsandforbuildingMLfeatures•RegulatoryandComplianceAuditing55Existing

Solutions:User

Implemented

Application-Level

Time

Travel•Inefficient:Usageoftriggersaddssignificantperformanceoverhead

(dueto

PL/SQL)•Incorrect:DatainconsistenciesbecausetimestampatthetimeofDML

is

recorded

(this

is

notthe

committimestamp)•Inconvenient:Appsneedtomergerowsfrom

basetableswithrowsfromthe

historytable•Insufficient:Does

not

work

across

schema

evolution•Insecure:Solutions

do

not

work

with

security

features

like

VPD,

RAS,

Data

Vault,

OLS,

etc.56Autonomous

Time

House:High

Level

OverviewAutonomousTimehouseisacompletelytransparent,fullymanagedservicethatfeatures:•

Automatic

change

history

recording•Dataretentionruleenforcement,

basedon

user

defined

policies•Temporalqueriesonhistoricaldata...WithnoimpacttoworkloadsontheoperationaldatabaseAlltemporalProcessingisoffloadedtoTimeHouse

DatabasesSELECTAVG(sal)FROMempASOFTIMESTAMPSYSDATE-365Operational

Databases

(Customer

Tenancy)

Time

House

Databases

(Service

Tenancy)PrimaryWorkload57

Copyright©

2023,Oracle

and/or

its

affiliatesATHATHUsingTimehouse

|Examples•Selectanemployee'ssalaryfromthepreviousyear-

SELECTsalaryFROMemployeeASOF

TIMESTAMP

<timestamp>WHERE

empid

=

:1;•Selectallversionsofsalarybetween2timestamps-

SELECTsalaryFROMemployeeVERSIONSBETWEEN

<start>AND

<end>WHERE

empid

=

:1•Bulkexportrowsthatchangedsinceyesterdaytoawarehouse-

SELECT*FROMorders

CHANGEDSINCE

TIMESTAMP

<start>;58UsingTemporalAnalysisForBusinessIntelligenceandAuditingExampleofcomparingnumberofemployeesundereachmanagerfrom

thepreviousyearWITH

newDataAS

(SELECTmanager_name,COUNT(*)ASnew_countFROMemployeesGROUPBY

manager_name),currentcountofemployeesundereach

managerCopyright©2020Oracle

and/or

its

affiliates.59WITH

newDataAS

(SELECTmanager_name,COUNT(*)ASnew_c