Lock-free Reservations Presentation 2024

GUOB Tech Day 2024 presentation

1. Oracle 23ai Lock-free Reservations

2. Lock-Free Reservations Rethink everything you think you know about row locking Tércio Costa

3. Tércio Costa Cloud Transf & Migration Assoc Manager Accenture Enkitec Group 17/08/2024 Linktree : 3 ace.oracle.com

4. Oracle Certifications Oracle GoldenGate 12c Certified Implementation Specialist Oracle Certified Professional Oracle Database 19c: Performance Management and Tuning Oracle Cloud Database 2023 Migration and Integration Certified Professional Oracle Autonomous Database Cloud 2023 Certified Professional Oracle Cloud Data Management 2023 Certified Foundations Associate Oracle Base Database Services 2023 Certified Professional Oracle Database Cloud Administrator 2023 Certified Professional Oracle Cloud Infrastructure 2023 Certified Architect Associate Oracle Cloud Infrastructure 2023 Certified Foundations Associate Oracle Cloud Infrastructure 2022 Certified Foundations Associate Oracle Autonomous Database Cloud 2021 Certified Specialist Oracle Cloud Infrastructure Foundations 2021 Certified Associate Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administrator Oracle Certified Expert, Oracle Database 12c Maximum Availability Oracle Certified Expert, Oracle Database 12c: Data Guard Administrator Oracle Cloud Infrastructure Foundations 2020 Certified Associate Oracle Database Administration 2019 Certified Professional Oracle Cloud Infrastructure 2019 Certified Architect Associate Oracle Autonomous Database Cloud 2019 Certified Specialist Oracle Database 12c Administrator Certified Associate Oracle Database PL/SQL Developer Certified Professional Oracle Database SQL Certified Expert 4 ace.oracle.com

5. 3 membership tiers Connect: @ oracleace Facebook.com / OracleACEs [email protected] 400+ technical experts helping peers globally The Oracle ACE Program recognizes and rewards community members for their technical and community contributions to the Oracle community Nominate yourself or someone you know: ace.oracle.com/nominate For more details on Oracle ACE Program: ace.oracle.com

6. Learn more & connect with the Oracle ACE Program 6 Program Details Nomination ACEs in Action Blog Twitter Facebook Linkedin Email ace.oracle.com ace.oracle.com /nominate blogs.oracle.com /ace @ oracleace The Oracle ACE Program bit.ly / OracleACEs [email protected]

7. Lock-free Reservations # Oracle 23ai 7

8. How locking works so far UPDATE hr.employee SET salary =15000 WHERE employee_id =101; 1 row updated . Rollback ; Without lock-free reservations, since forever 8 UPDATE hr.employee SET salary =30000 WHERE employee_id =101; ... 1 row updated .

9. How locking works so far Bad for quantity columns , like account balance, inventory , ticketing and etc. Bad for long running querys Low concurrency Impact performance and reduce user experience and throughput Downsides 9

10. Lock-Free Reservations Oracle 23ai New Feature 10

11. Lock-free Reservations UPDATE hr.employee SET salary =15000 WHERE employee_id =101; 1 row updated . Without lock-free reservations, since forever 11 UPDATE hr.employee SET salary =30000 WHERE employee_id =101; 1 row updated . commit ;

12. Lock-free Reservations When a transaction issues an update operation on a reservable column, the reservable update is placed as a lock-free reservation in a reservation journal The transaction update does not lock the row (that has the reservable update) but indicates its intention to modify (add or subtract) the reservable column in the row by a delta amount. The modification amount is reserved and promised so that the transaction may proceed without waiting Defers the actual update to reservable column until the commit time to improve concurrency 12 How it works

13. Lock-free Reservations CREATE TABLE Account( ID NUMBER PRIMARY KEY, Name VARCHAR2(10), Balance NUMBER reservable CONSTRAINT minimum_balance CHECK (Balance >= 50) ); Creates an associated reservation journal table, deferred segment creation enabled. SYS_RESERVJRNL_< object_number_of_base_table >; 13 Lock-free Reservable Column

14. Journal Table 14 Column Name Description ORA_SAGA_ID$ Saga ID of the transaction (0 for non-saga transactions) ORA_TXN_ID$ Transaction ID of the transaction (containing usn , slot, seq) ORA_STATUS$ Status of the Txn ID, with values: {ACTIVE, COMMITTED, COMPENSATED} ORA_STMT_TYPE$ DML statement type {UPDATE} ID Primary Key column of user table BALANCE_OP Reservable column operation with operations having ‘+’ or ‘-’ for replenishment or consumption BALANCE_RESERVED Reservable column reserved and is the amount reserved from the reservable column

15. Views 15 View Name Column *_TAB_COLUMNS reservable_column *_TABLES has_reservable_column

16. ? 16

17. Thank You