Tuesday, May 7, 2024

Transportable Binary XML – modern XML document storage in Oracle Database 23ai

Traditional XML storage in Oracle Database


In 2010, Oracle Database introduced native XML storage capabilities to optimize the storage and usage of XML documents with Oracle Database 11g (11.2.0.2). Oracle's approach for native XMLType storage then was purely document-centric, called Compact Schema-Aware XML (CSX) or Non-transportable Binary XML. This binary format was built on a central token table set approach where every XML document is encoded in binary format, and the keys to encode and decode the binary data are stored in a single, central dictionary. Such a design is excellent for the storage and performance within a single database. There is no duplication of work for encoding and decoding tokens, and there is always just one reference. Since hashing of tokens is one of the most expensive tasks, this approach saves a lot of time. However, as a natural consequence, every operation involving the decoding of binary XML data needs to access the central dictionary.

What is Transportable Binary XML


Although CSX succinctly compresses XMLType data, its use of a central dictionary (token table) and schema registries prevent an easy adoption for distributed and remote architectures, such as sharding, cross containers, remote database links, or even robust import/export capabilities. Any scenario where the central dictionary is not directly accessible imposes a challenge. To overcome these challenges, Oracle Database 23ai introduced Transportable Binary XML (TBX) as a storage option, a variant built on top of CSX but without the dependency of a central dictionary.

TBX is far more flexible and scalable without this dependency, enabling binary stored XML documents to be transportable and usable in distributed environments. Transportable Binary XML is the recommended method for storing XML documents natively in the Oracle Database beginning with Oracle Database 23ai. The following will walk you through how to use Transportable Binary XML and how to migrate earlier legacy binary XML storage formats.

Working with Transportable Binary XML


Beginning with Oracle Database 23ai, Transportable Binary XML is the default XML storage type. You do not need to explicitly specify the storage type of your XML columns. However, if you want to specify TBX explicitly, the DDL looks as follows:

CREATE TABLE tx( ID NUMBER, XMLDOC XMLTYPE) 
XMLTYPE COLUMN XMLDOC STORE AS TRANSPORTABLE BINARY XML;

To validate that a table has an XMLType column stored as TBX, you can just describe the table:

DESCRIBE TX 
Name                         Null?    Type 
------------------          -------- ----------------------------------------- 
ID                                         NUMBER 
XMLDOC                             SYS.XMLTYPE STORAGE TRANSPORTABLE BINARY

Transportable Binary XML provides full data processing compatibility with non-transportable binary XML storage. All XML operators and SQL generally continue to work like before without any behavior changes. Transportable Binary XML only changes how your XML documents are stored in the database and how XML documents are handled internally.

The only change of Transportable Binary XML is lifting existing constraints, enabling binary XML documents in modern architectures like Oracle Sharding.

Transportable Binary XML and Oracle Sharding


Sharding is a database architecture technique used to scale databases by partitioning data across multiple independent servers or instances, each holding a portion of the total data (shards). The shards are all coordinated by one instance (coordinator), where all the information is gathered and presented as a single database.

Transportable Binary XML – modern XML document storage in Oracle Database 23ai

This architecture is based on a shared-nothing architecture, meaning that each shard knows nothing about the other shards. Each shard could also be used as an independent, single database. Transportable binary XML as self-encapsulated, self-decoding/encoding binary storage on table level allows using a native XMLType with Sharding: it enables sending XML documents over the wire to any other database without limits. Transportable Binary XML is the only supported XMLType storage format in Oracle Sharding environments; specifying other XMLType storage types will throw an error.

To create a sharded table with an XMLType column using Transportable Binary XML, the DDL looks as follows:

CREATE SHARDED TABLE SHARDEDTAB
   ( SHDKEY NUMBER NOT NULL, 
     CTRY_CODE VARCHAR2(3) NOT NULL, 
     XMLDOC XMLTYPE, 
     CONSTRAINT SHARD_PK PRIMARY KEY (SHDKEY, CTRY_CODE)
    ) 
PARTITION BY LIST (CTRY_CODE) 
    ( PARTITION P_REG_1 VALUES (‘RG1’) TABLESPACE TBS1, 
      PARTITION P_REG_2 VALUES (‘RG2’) TABLESPACE TBS2, 
      PARTITION P_REG_3 VALUES (‘RG3’) TABLESPACE TBS3 
     ) 
XMLTYPE COLUMN XMLDOC STORE AS SECUREFILE TRANSPORTABLE BINARY XML;

Your table will be sharded across all participating databases (shards), using the new Transportable Binary XML as storage format for your XML documents without the need of a central repository. Needless to say that TBX is also supported in system or composite sharding environments.

Transportable Binary XML and Search Indexes


Hand in hand with TBX, Oracle Database 23ai introduced new, simplified SQL syntax for XML Search Indexes specifically for TBX. Whenever you require full-text and/or range-search capabilities over large, unstructured XML documents and you adopted the new Transportable Binary XML storage format, Oracle recommends that you use XML Search Index to index your XML data.

To create an XML search index for both full-text and range-search, you can use syntax similar to the following DDL:

CREATE SEARCH INDEX myXMLDocumentsSearchIndex 
ON myXMLDocumentsTable(myXMLTypeColumn)
FOR XML PARAMETERS ('SEARCH_ON TEXT_VALUE (BINARY_DOUBLE, VARCHAR2, TIMESTAMP)');

This example enables both the full-text and range-search components for the specified data types BINARY_DOUBLE, VARCHAR2, and TIMESTAMP.

If you are unsure about your workload and business requirements it is recommended to build the index for search and range scans for common data types in your XML documents.

The use cases of a search index for full text search and range-search using relational operators (>, <, <=, >=, or =) in an XMLExists expression within a SQL where clause are slightly different in nature of internal processing, so you can optimize your XML search index structure for your specific requirements.

If you are aiming solely for text search, you can create your XML search index by specifying the FOR XML clause in the CREATE SEARCH INDEX statement. As mentioned before, you can create such an XML search index only on XML documents stored as TBX and you must be granted the CTXAPP database role.

The syntax to create a search index to solely index documents for text search is as simple as the following example.

CREATE SEARCH INDEX myXMLDocumentsSearchIndex 
ON myXMLDocumentsTable(myXMLTypeColumn) 
FOR XML;

OR, using the more verbose syntax. You would only do this for clarity reasons; this is the default, as seen in the previous example:

CREATE SEARCH INDEX myXMLDocumentsSearchIndex 
ON myXMLDocumentsTtable(myXMLTypeColumn) 
FOR XML PARAMETERS (‘SEARCH_ON TEXT’);

If you are aiming to build your index solely for range scans within your XML documents, you simply specify the list of datatypes that you intend to search on:

CREATE SEARCH INDEX myXMLDocumentsSearchIndex 
ON MyXMLDocumentsTable(myXMLTypeColumn) 
FOR XML PARAMETERS ('SEARCH_ON VALUE (BINARY_DOUBLE, VARCHAR2, TIMESTAMP)');

Conversion to Transportable Binary XML


While it’s easy to see how to adopt TBX for future applications and systems, many existing applications built using XML storage in the Oracle Database will be upgraded to Oracle Database 23ai. It is recommended to migrate all your tables using a legacy XML storage format to TBX.

There are different options to do this migration:

◉ You can do a simple CTAS (Create Table As Select), casting the legacy XML datatype to TBX. This is the most pragmatic approach. However, it introduces downtime for your application and requires you to manually rebuild all dependent structures, such as indexes.

◉ Online Redefinition is similar to the CTAS approach, but enables your migration to happen in an ONLINE mode.

◉ DataPump has specific import parameters to do the storage migration at data import time, namely TRANSFORM = XMLTYPE_STORAGE_CLAUSE: '" TRANSPORTABLE BINARY XML"'.

◉ GoldenGate, Oracle’s flagship product for real-time data integration and replication in heterogeneous IT environments. This method is only applicable if you are moving your whole database. 

Source: oracle.com

Sunday, May 5, 2024

Oracle Announces General Availability of AI Vector Search in Oracle Database 23ai

Oracle Announces General Availability of AI Vector Search in Oracle Database 23ai

Oracle AI Vector Search is a novel capability that allows users to search data based on the semantics or meaning of data, in addition to by the values of data, such as attribute values or keywords, as databases have traditionally supported.

A vector, or vector embedding, is a popular data structure used in AI applications. A vector is a list of numbers, generated by deep learning models from diverse data types (e.g. images, documents, videos, etc.), that encodes the semantics of the data.


Oracle AI Vector Search allows you to generate, store, index, and query vector embeddings along with other business data, using the full power of SQL. As an example, for searching documents, vector search is often considered more effective than keyword-based search, as vector search is based on the meaning and context behind the words and not the actual words themselves. 

Oracle AI Vector Search allows you to combine semantic document search with searches on structured document properties. For example, in a database of technology articles, a question such as “find articles about fine-tuning Large Language Models (LLMs) for enterprise use cases that have been published in the last 5 years by a certain author and a certain publisher in a certain country”, requires searching both the article text as well as article attributes, that may be present in one or more tables.

Oracle AI Vector Search includes a collection of powerful capabilities to enable semantic search for business use cases. These features include

  1. New SQL operators to generate vector embeddings from unstructured data
  2. A new first-class VECTOR data type for storing vector embeddings
  3. New state-of-the-art Vector Indexes for fast approximate searches
  4. New SQL operators, and syntax, to easily express similarity search in business queries
  5. Support for the Full Generative AI pipeline including preprocessing and vectorizing data, and augmenting LLMs with business data

The VECTOR data type is fully integrated in SQL and PL/SQL, and is supported across multiple clients and programming languages, with native binding capabilities in python-oracledb, node-oracledb, JDBC, and ODP.NET drivers. This comprehensive support provides seamless vector search functionality across multiple development environments.

Oracle AI Vector Search is also fully integrated in popular 3rd party Generative-AI frameworks such as LangChain.

Oracle AI Vector Search includes native APIs to make REST callouts to LLM APIs for content generation (like text) or summarization and other operations used in the Generative-AI pipeline. These new capabilities allow seamless support for Retrieval Augmented Generation (RAG), a breakthrough generative AI technique that augments large language models (LLMs), with private business data, to deliver accurate responses to natural language questions on business data.

Benefits of Oracle AI Vector Search


Oracle Database is a leading repository of operational and enterprise data. Enterprise applications usually need to search a combination of business data and unstructured data.  For example, a retail website could feature searches based on a natural language product description and a target product image, along with other filters, such as price, store location, manufacturer, and current availability. This search requires simultaneously searching unstructured catalog data (product description and image), structured catalog data (price, store location and manufacturer) as well as real time transactional data (such as current inventory).

The combination of the converged capabilities of Oracle Database, and Oracle AI Vector Search, provides several unique benefits.

Seamless Combination of AI Vector Data with Your Business Data

This is a key benefit of Oracle AI Vector Search, since it lets users run AI-powered vector similarity searches within their existing Oracle Databases instead of having to move business data to a separate vector database. Avoiding data movement can reduce complexity, improve security, and enable searches on current data.

Oracle AI Vector search allows far more powerful searches than most dedicated Vector databases, by combining sophisticated business data search with AI vector similarity search using simple, intuitive SQL and the full power of converged database – JSON, graph, text, relational, spatial, etc. all within a single query.

Powering Retrieval Augmented Generation with Business Data

Vector Databases improve interactions with LLMs since they provide scenario-specific private context to LLMs to obtain more accurate answers. This is a well-known workflow referred to as Retrieval Augmented Generation (RAG). 

Oracle AI Vector Search also leverages the full power of business data to further refine LLM interactions, making use of business criteria such as security filters, business metrics and business rules, resulting in ultra-sophisticated RAG for the Enterprise.

Support for the Full Generative AI Pipeline for Business Data

Oracle AI Vector Search supports native database APIs to perform all aspects of the generative AI pipeline, from end to end, making it easier for your developers to build next-gen AI applications using your business data, directly within Oracle Database. 

Unique Combination of AI Vector Search and Full Machine Learning Suite

Oracle Database offers both a full suite of in-database machine learning algorithms as well as similarity search on AI vectors. This combination enables Oracle Database to handle a very wide range of AI use cases involving machine learning actions (decisions, predictions, classification, forecasts, etc.) as well as the power of AI-based vector search. For instance, it is easy to combine inference and classification with AI Vector Search, within the same SQL query.

Proven, Enterprise-Class Scalability, Fault Tolerance and Security

Oracle Database is a leading repository of business data, and the combination of business data and semantic search is what enterprises need to implement artificial intelligence solutions. AI Vector Search is built into Oracle Database and leverages:

  • Partitioning, RAC, Sharding and Exadata for proven, industrial-strength scalability.
  • Extreme HA and DR technologies like Data Guard, Golden Gate, Flashback, RMAN, ZDLRA and more.
  • Cutting-edge security with Oracle Advanced Security including features such as Transparent Data Encryption, Key Vault, Audit Vault, Virtual Private Database and more.

Example Use Cases


Oracle AI Vector Search enables the combination of search on semantic and business data resulting in more-accurate answers quickly, and securely. With the addition of AI Vector Search to Oracle Database, users can quickly, and easily get the benefits of artificial intelligence without sacrificing security, data integrity or performance.

Use cases include:

  • Conversational AI, or Chatbots: Build AI-powered digital assistants
  • Similarity Search: Match customers with products
  • Content-Based Filtering: Enable personalized recommendations, locate retail items from pictures
  • Natural Language Processing: Text classification and clustering SQL generation
  • Data Analysis: Anomaly detection, pattern recognition
  • Computer Vision: Face recognition, biometric identification, object detection
  • Biomedical Research: Gene/DNA similarity research, molecular structure search
  • Geographic Information Systems: Spatial analysis, map rendering
  • Industrial Applications: Quality control, predictive maintenance, machinery malfunction

Summary

Oracle AI Vector Search, with Oracle Database, enables a new class of applications making it possible to transform traditional business processes by enabling semantic searches using LLMs augmented with existing business data.

  • New SQL operators, and syntax, enable you to easily combine relational search, on business data, with semantic search on unstructured data
  • AI-powered vector similarity searches using Oracle Database – no need to move business data to a separate vector database
  • Easily utilize Retrieval Augmented Generation (RAG) to augment LLM responses with your enterprise specific content
  • Enhance your applications with the enterprise-class security, scalability, and partitioning capabilities of Oracle Database

Source: oracle.com

Saturday, May 4, 2024

Announcing Oracle Database 23ai : General Availability

General Availability 


We are pleased to announce the general availability of Oracle Database 23ai. Over the last four years, Oracle Database Development has worked hard to deliver the next long-term support release of the Oracle Database, with a focus on AI and developer productivity. Given the focus on AI in this release of the database, we decided to change the database's name from Oracle Database 23c to Oracle Database 23ai. This reflects the focus of this release as well as the climate in which it is being released. 

Announcing Oracle Database 23ai : General Availability

Game-Changing Innovations


Oracle Database 23ai has focused on three key areas.

◉ AI for Data
◉ Dev for Data
◉ Mission Critical for Data

In this Blog, we'll describe some of the critical features of this release that highlight our work on these important focus areas and provide details when you see them on your platform of choice.

AI for Data


Our goal with AI for Data is two-fold:

1. To enable app developers to add AI functionality into their data-driven applications more easily
2. To incorporate GenAI capabilities into our products so that all users of Oracle Database, from data analysts to app developers and DBAs, can be more productive.

This is achieved by employing AI to streamline Oracle's data management processes, offering AI algorithms for uncovering new insights from your data, facilitating natural language interactions for database queries, and empowering users to store AI-generated vectors for swift and efficient object similarity searches. Let's delve into these features further.

Augmenting a new generation of AI models

People are feeling the impact of AI Large Language Models (LLM), exemplified by offerings such as ChatGPT, Google Gemini, Cohere, and LLaMA. Whilst LLMs are having a profound impact on the way we perform everyday tasks, one of the challenges that these engines face is that they are restricted to answering questions in the specific time window of their creation. They also lack context and details of the data held within your organization. To make these and other LLMs useful, we need to augment their existing understanding with data relevant to the questions you ask about the data held within your database. 

In Oracle Database 23ai, we are introducing AI Vector Search, a powerful new technology that enables you to leverage a new generation of AI models to generate and store vectors. These vectors, sometimes referred to as embeddings, are multi-dimensional representations of documents, images, videos, sound, etc. By encoding these objects as vectors, you gain the ability to look for similarities between them using mathematical calculations. The real power of Oracle Database23ai's solution is that you can combine these similarity searches with searches on your business data using simple SQL. Anyone with a basic understanding of SQL can create a powerful statement combining similarity and other search criteria. These types of queries provide LLMs with additional context, augmenting their knowledge and making their responses more accurate and relevant to your customers' or organizations' questions. To enable this functionality, we've added a new data type, new vector indexes, and extensions to the SQL language to make it incredibly simple to query vectors alongside your existing business data by leveraging Oracle Database 23ai's advanced analytical capabilities.

Announcing Oracle Database 23ai : General Availability

Secure encoding of data

To enable the considerable benefits that LLMs offer, we first need to enrich our understanding of our datasets and the objects within them. The models to encode these objects are potentially large and complex pieces of code. As a result, it has become common practice to offload this processing to third-party services. This can require you to share potentially sensitive information with third parties with the inherent risks that entails.

To ensure the secure encoding of objects, Oracle Database 23ai allows you to load your own trusted AI models directly into the database through its support of the ONNX standard. This enables Oracle Database 23ai to encode objects as they are inserted into the database. This enables the secure near real-time inferencing of objects and ensures that data does not need to be extracted from the database and handed off to a third-party service.

Asking questions naturally

While SQL is an incredibly powerful language, it can be a little challenging to get started with. To enable a broader range of users to ask the most complex questions of the Oracle Database, we are integrating with LLMs such as Cohere and Llama to allow you to ask questions using natural language.  So you can simply ask a question like "Show me the last 4 quarters' sales of products that are popular with the younger generation". Oracle Database 23ai will share the table's metadata to answer the question with the LLM along with the user's question. The LLM understands the meaning of "younger generation" and converts it into a data range as a filter in the SQL query, which it returns to the database. We, of course, aren't limiting Oracle Database 23ai to just allowing you to query the information within the database. You'll also have the ability to ask questions on all aspects of the Oracle Database. 

Finding new insights in data

Since the release of Oracle Database 9iR2 over 20 years ago, the database has featured built-in Machine learning (ML) algorithms, enabling you to quickly look for patterns and trends in data held within its tables and predict customer behaviors. These ML algorithms give you the ability to use sophisticated ML algorithms without the need for complex ETL operations to extract the data and write back results. These models could then be used to classify, cluster, and predict as your data is inserted or loaded into the Oracle Database, providing tangible business benefits like customer recommendations and fraud detection. Since that release, we've improved and added new ML algorithms and capabilities to the Oracle Database, creating one of the industry's most sophisticated and capable data mining platforms.

Available in all editions of the Oracle Database

AI Vector Search is part of Oracle Database 23ai and is available at no additional charge in Enterprise Edition, Standard Edition 2, Database Free, and all Oracle Database cloud services.

Feedback from early testers

AI Vector Search generated a great deal of interest even before the launch, and many customers and partners were keen to try out and provide feedback on the latest capabilities like AI Vector Search. Here's a sample of the feedback we received.

“We are happy to see AI Vector Search added to Oracle Database. We appreciate that we can run AI Vector Search in the same Oracle Database as our other workloads, which allows us to provide a reliable and secure solution.”

Shinichiro Otsuka, NRI Certified IT Architect, Nomura Research Institute, Ltd.

“AI Vector Search changes the way we think of the business by bringing AI to your data: for instance we can use natural language to query our documentation to find answers to questions customers are asking. This makes the process of serving customers much faster and the quality of the service improves as well. Being able to use the power of Generative AI with our own data makes a huge difference."

Heli Helskyaho, CEO, Miracle Finland Oy

“We have been consulting on various business transformation projects using advanced digital technology. Oracle Database 23ai AI Vector Search is very simple to implement by adding a vector column to the current relational database system. I am thrilled that the vector data is within a single record with existing data so that similarity reordering can be done with a single SQL statement. This makes it easy to combine large language models and company data to create and operate enterprise-level retrieval augmented generation systems.”

Shigeru Urushibara, Chairman, UL Systems Inc.

"With the decreasing domestic workforce in Japan, XCAT‘s customers want to adopt labor-saving measures. Oracle Database 23ai’s AI Vector Search allows customers to use SQL for vector retrieval, making it an excellent tool for engineers with little or no AI knowledge to handle vector data. In addition, the ability to manage AI data at no additional cost is a major attraction for many Oracle Database customers."

Mitsunori Yamane, President, Cross Cat Co., Ltd.

Dev for Data

Oracle Database 23ai focus was to make the experience of developing applications simpler by removing the complexity associated with your database interaction. Removing complexity from the application development process means you get more opportunities to focus on the intricacies of creating elegant applications that meet your customer's requirements rather than getting bogged down in technical details. Moreover, reducing complexity can lead to faster development cycles, this is crucial in today's fast-paced digital landscape, where market demands can shift rapidly.

JSON or Relational, a difficult choice?

Oracle 23ai introduces a number of key technologies to reduce the complexity for developers. Perhaps the most innovative and important of them is the introduction of JSON Relational Duality. This technology allows you to leverage the power of relational and the simplicity of JSON development approaches all within a single app. JSON provides an elegant data modeling approach where all of the relevant data needed to answer a query is contained in a single object without the need to perform complex joins across tables. The relational approach provides a flexible, storage-efficient, consistent data model that is easy to query with languages such as SQL. Historically the problems come when you are forced to choose which database modelling approach to take at the start of your application lifecycle. Decisions made at this stage may have implications much later on as the business requirements change. The beauty of JSON Relational Duality is that you can benefit from both approaches without the need to use complex and unwieldy Object Relational Mapping frameworks (ORMs). 

By creating simple JSON Duality views on top of relational tables you can build applications that use documents to query and update the underlying data sets. Data duplication, a fundamental issue in document modeling, is avoided, and even the complexities of database-level concurrency control are hidden from you as Oracle Database 23ai manages document-level serializability on your behalf. You can continue to use simple HTTP PUT, POST, and GET operations or use language-specific API such as Oracle's SODA API or even MongoDB API directly against the Oracle database. With JSON Duality Views the old phrase of "You can't have your cake and eat it" no longer holds true.

Announcing Oracle Database 23ai : General Availability

SQL just got a little easier

SQL remains one of the most popular development languages in use with application developers today. Its versatility when asking the simplest or most complex questions of your business data has made it the cornerstone of data management, a position it is likely to hold for the foreseeable future. That is not to say there are areas where it could be improved. In Oracle Database 23ai we listened to common enhanced requests and looked at what could be made simpler or offer more compelling ways to interact with the database. Examples of just a few of the SQL improvements we've made include the addition of two new datatypes "Boolean" and "Vector", we added "Data Usecase Domains" to allow you to enrich the meaning of datatypes used in table definitions, the removal of the need for "from dual" in simple SQL statements to improve readability, making it easier to aggregate over the "interval" datatype, the introduction of table value constructors to make it easy to specify multiple rows in insert select or merge statements. These are just a few of the many improvements we've made to SQL.

As with AI Vector Search, we also appreciate receiving a lot of useful feedback from customers and partners who participated in the Oracle Database 23ai Beta program. Here's a sample of the comments we received -

“JSON Relational Duality Views are a game changer for application development by finally solving the problem of object-relational mapping. And, we at last have ACID-compliant transactions in a multi-tier or web architecture.”

Peter de Vaal, Principal Consultant, Transfer Solutions

“JSON Relational Duality gives you the best of both worlds: Relational experts can work on the same data as Document database experts without having to learn a new skill. App developers have the flexibility they need, and database management is simpler. Oracle has taken a massive leap beyond other databases.”

Patrick Barel, Sr. Oracle Consultant, Qualogy

“Oracle Database 23ai with JSON Relational Duality Views and Oracle APEX have allowed us to create full-featured, standards-compliant applications for working with JSON healthcare data.”

Rade Pobulic, Technical Director, VDEL Informationstechnik & Consulting GmbH

Property Graphs modelling complex relationships

Graph databases provide a fresh perspective on modeling intricate relationships found in real-world scenarios. Yet, until recently, developers grappled with proprietary languages and having to ship data tp databases tailored for managing these relationships. This hindered the widespread acceptance of this groundbreaking technology. Fortunately, the industry has taken a collaborative approach to bring graphs to the masses, culminating in the establishment of standards. These standards empower developers with the versatility and familiarity of SQL, fostering a more accessible landscape. Oracle Database 23ai stands as the pioneering commercial database to embrace this newfound standard. With these advancements, we anticipate a significant surge in the integration of Graph databases within enterprise ecosystems.

As with JSON Duality Views, you can express your intent on how you would like to use your data held within existing relational tables or your JSON collections through the user of property graph views. With a simple view, you can enrich the data to model the potentially complex relationships that your data holds. A new SQL extension makes it much simpler to write powerful queries to find a deeper understanding of information that would have been hard to describe using standard SQL. We've also added a new set of sophisticated graph algorithms (shortest path, classification, connects, etc. ), which will be released shortly after GA.


Announcing Oracle Database 23ai : General Availability

The property graph Property Graph is available in all editions of Oracle Database 23ai.

Mission Critical for Data


Oracle Database is renowned for powering some of the world's most vital applications. Over the years, it has established itself as a stalwart guardian of businesses' most precious asset: data. Unrivalled in its breadth of features, Oracle Database excels in safeguarding critical systems against unforeseen downtime. A prime example is Oracle Real Applications Clusters (RAC), a cutting-edge technology that enables all-active, shared disk databases, ideal for the most mission-critical scenarios. Oracle Active DataGuard and Oracle GoldenGate offer unparalleled flexibility in crafting both homogenous and heterogeneous architectures for data replication. 

Shard better, faster, simpler

For use cases where businesses are looking to distribute their data over larger distances, either for regulatory reasons where the data of users must be stored in the country the customer resides in or to reduce the latency of access associated with globally distributed applications: We introduced support for database sharding in Oracle Database 12c. Sharding distributes a dataset across a number of Oracle databases but is managed as a single logical data set. This sharded architecture allowed the database to be scaled up or down by adding and removing shards and allowing Oracle to automatically rebalance the dataset. Oracle is able to do this online and with little impact on the business as the concept of sharding is baked directly into the database.

In Oracle Database 23ai our Globally Distributed Database technology introduces support for Raft replication. This is a consensus-based protocol that facilitates the automatic configuration of replication across all shards. With raft, replication holds copies called replication units across all of the shards. In the advent of a shard failure or reconfiguration, Oracle Database 23ai automatically rebalances the data. The new Raft protocol also enables sub-second failover to ensure continued business continuity. 

Announcing Oracle Database 23ai : General Availability

Oracle 23ai brings a number of improvements across the board for our high availability products, such as the new "Local Rolling Database Maintenance" to simplify the impact of rolling patches in a cluster by standing up another instance on the same node that is receiving the patch. We are also introducing support for a new Data Guard Per Pluggable Database (PDB) allowing Pluggable Database level disaster protection between two Container databases (CDB) with each CDB running an active workload. We've also improved the integration and management of PDBs within Oracle Clusterware. 

Caching data made simple

Caching data in the application tier can offer considerable benefits. It is often used to make applications more responsive and reduce the load on the database, as queries are offloaded to the cache typically located closer to the code. However, to achieve these benefits, developers face a number of challenges; one of the biggest is ensuring that the cache is consistent with the data held within the backend database or risk serving stale and potentially incorrect data. Developers are often forced to create complex code to manage these scenarios and often have to resort to design patterns such as "time to live" where data is periodically flushed from the cache and reloaded. These and many other challenges often mean the cache becomes difficult and expensive to maintain and, in some instances, a bigger problem than the one it was designed to solve.

In Oracle Database 23c we are looking to solve the problems of caches built against the Oracle Database with a new feature called "True Cache". True Cache is a diskless Oracle Instance running in front of the primary database. The True Cache instances leverage Active Data Guard technology to ensure it is automatically updated as data changes in the primary database instance. One of the most important features of this new caching technology is its simple configuration and minimal code changes that are needed to leverage the benefits it offers. 

Announcing Oracle Database 23ai : General Availability

Making SQL execution safer

One of the challenges that organisations face is continued attempts by malicious actors to steal data or disrupt their day-to-day activities. In Oracle Database 23ai, to prevent the execution of unauthorised SQL, be they via SQL-Injection attacks or simply by unauthorised staff, we are introducing SQL Firewall. SQL Firewall allows you to train the Database on what SQL is permitted to be executed. The "allow list" of SQL can then be used to either log unauthorised SQL or blog its execution. Additional criteria can be added to allow exceptions for IP ranges or authorised calling programs. The power of SQL Firewall is that it is part of the Oracle Database, and as a result, it imposes very little overhead on the execution of SQL. Setting up and training SQL Firewall is incredibly simple being able to be done from a GUI or via a few simple calls to stored procedures. 

Announcing Oracle Database 23ai : General Availability

Over 300+ new features


I discussed the cloud release of Oracle Database 23ai and described some of the 300+ new features. Some other notable features include 

  • Java Script Stored Procedures
  • Priority Transactions
  • Data Usecase Domains
  • Schema privileges
  • Annotations
  • Boolean datatype
  • Developer Role
  • Read-Only-Per-PDB Standby
  • JSON Schema
  • Real-Time SQL Plan Management
  • Performance Improvements
  • Lock Free Reservations
  • Improved Microservice Support
  • XML and JSON Search Index Enhancements
  • And many many more

Platform Rollout

Oracle Database 23ai is now available on Oracle Exadata Cloud@Customer, OCI Exadata Database Service and OCI Base Database Service. It is also available in the Azure Oracle Database Service. For developers, Oracle Database 23ai is now available in Always Free Autonomous Database as well for download in the Autonomous Database 23ai Container Image and Oracle Database 23ai Free. We are also pleased to announce that Oracle GoldenGate 23ai and GoldenGate 23ai Free as also available for download. On-premises versions of Oracle Database 23ai for Linux (OL & RHEL 8/9) and Windows will be out shortly with other platforms rolling out over the coming months. For further details please check the Oracle Support note Doc ID 742060.1

The general availability of Oracle Database 23ai marks a new phase in this important release. Thousands of developers have contributed to its design, development and testing resulting in what we believe is the very best enterprise database in the market.

Source: oracle.com

Friday, May 3, 2024

Why run Oracle Database on Arm

Why run Oracle Database on Arm

Why run Oracle Database on Arm


CPUs or Central Processing Units have been the core talking point in the computing space. Moore's law ("the number of transistors in a dense integrated circuit doubles approximately every two years" still stays valid. Processor speed is always measured in terms of instructions per second. New CPU architectures now measure millions or billions of instructions per second. A rise in demand for mobile gadgets has also shaped the path of today's processors. Power consumption and heat production are two significant factors in a smaller form factor (mobile devices). In recent years, the technological landscape has witnessed a considerable rise in the popularity of Arm processors due to their unique low power consumption capabilities. RISC-based architectures, of which Arm is part, focus on simplicity and efficiency. The RISC-based architecture uses a simplified set of instructions, each performing a single operation, aiming to execute instructions quickly. Arm processors offer unique capabilities in the mobile and handheld segment, but they have now been realized to be helpful in the server market. In this blog, we will explore why Arm processors are better suited for desktops and servers and explore why relational databases can perform exceptionally well on Arm.

Arm-based computing started with mobile and embedded devices; the technology (Arm-based) has now evolved to support hyperscale data centers and cloud computing. Oracle began to offer Arm-shaped VMs because they scale linearly, provide predictable performance, and provide the highest density of cores, all at a lower price point. With Arm-based cloud computing, customers can run existing workloads less expensively and build new applications with superior economics and performance. To help developers transition, make, and run Arm-based workloads, Oracle and other cloud vendors have accelerated the Arm developer ecosystem with the best tools and platform possible. Arm can provide all the capabilities of x86 computing, including virtualization.

What about current workloads:

x86, for decades, has been a de-facto standard for running workloads of various types. Workloads over time have become quite complex, resulting in millions and billions of instructions per second. Arm, with its simple design, offers a complementary substitute for some of the workloads that run on x86 architecture. Oracle Databases running on x86 platforms can seamlessly migrate to Arm architecture using standard methods. Arm architecture provides the following benefits:

  • Efficiency and Power Consumption: Arm processors are renowned for their efficiency and low power consumption compared to x86 processors. Arm's reduced instruction set architecture (RISC) approach enables processors to execute instructions more efficiently, resulting in improved power efficiency and reduced heat generation. By consuming less power, Arm processors can allocate more resources to managing tasks, benefiting the overall performance of a system.
  • Technology trends: The needs of hyper-growth markets, such as cloud-based supercomputing and artificial intelligence, dictate that we turn to specialized hardware where we can add more servers into data centers while staying within energy budgets. Arm is highly customizable, meaning cloud providers can tailor chips to their needs rather than depend on chip vendors. Most cloud vendors are coming up with their implementation of Arm-based chips to fit their needs. For example, NVIDIA has introduced its Grace Hopper super chip, based on Arm, to deliver a CPU+GPU coherent memory model to accelerate AI and high-performance computing applications.
  • Scalability and Customization: Some of the Arm processors available in the market boast 128 cores per processor. One of the Arm vendors boasts 192 processor cores, the highest core count of any available data center server processor. Due to their scalability and customization capabilities, Arm processors have gained significant traction in the mobile and embedded systems markets. Unlike x86, Arm processors are designed to be highly customizable, allowing manufacturers to tailor them to specific use cases. The higher core count also eliminates the need for dual CPU (x86-based systems). This flexibility enables system designers to optimize Arm-based systems for database workloads, enhancing performance and efficiency. Additionally, the modular nature of Arm-based systems allows for easier integration of different components, making them adaptable to various database requirements.
  • Increased Parallel Processing: Relational databases often deal with large volumes of data and complex queries, necessitating efficient parallel processing capabilities. Arm processors, featuring multiple cores and superior multithreading capabilities, excel in this aspect. With the proliferation of multicore Arm processors, database operations can be parallelized, enabling faster query execution and improved overall database performance. This parallel processing prowess of Arm processors is particularly beneficial for demanding workloads in relational databases, where simultaneous execution of multiple queries is essential.
  • Enhanced Memory Bandwidth: Memory bandwidth plays a crucial role in database performance, especially when dealing with high data transfer rates. Arm processors often feature memory architectures designed for optimal bandwidth utilization, which can improve the speed of data access and retrieval. This increased memory bandwidth contributes to a more responsive and efficient database system, enabling faster query execution and reduced latency.
  • Cost-Effectiveness: Arm processors have established a reputation for cost-effectiveness, making them an attractive choice for organizations seeking to optimize their IT infrastructure. The Arm ecosystem offers manufacturers various hardware options, resulting in competitive pricing and increased affordability. For businesses running relational databases, deploying Arm-based systems can be a financially viable solution without compromising performance.
  • Security: Ampere Altra processors based on Arm (available in Oracle Cloud) run on a single thread per core processor, eliminating potential thread-security issues and isolating workloads. When run as a single thread per core, there is no sharing of the execution engine, registers, and L1/L2 cache between threads, which minimizes the attack surface for exploits.

Why Database Engines on Arm:

  • Customization for Database Needs: Arm develops the Arm architecture and licenses the IP to allow its licensees/partners/customers to subsequently build and sell the chip within their designs/products, such as system-on-chip or system-on-modules designs. SoC (system-on-chip) is an efficient design model where significant components of a computer or electronic system reside on a small, low-power board. This approach differs from other CPU manufacturers, such as Intel or AMD, that design and manufacture their chips. The ability to license Arm design gives database vendors, like Oracle, the flexibility to design their chips based on Arm architecture.
  • Scalability: Database engines do require scalability. Oracle Database runs high transactional applications and requires vertical and horizontal scalability. Arm processors, compared to their x86 counterparts, are highly dense and provide more cores on a CPU.
  • Endianness: Endianness describes the order in which a sequence of bytes is stored in computer memory. Big-Endian systems store the most significant byte (big end) first in the order, and Little-Endian systems store the least significant bytes (little end) first. Arm architecture, by design, is bi-endian, which means it can support both big-endian and little-endian byte orderings. To complement its x86 offering, Oracle will use little-endian byte ordering for its Arm implementations and interoperability with x86 counterparts.
  • Portability: Oracle is adopting Arm architecture with Ampere One and Ampere AltraMax processors and will use little-endian format for byte ordering. Using little-endian format means migrating running databases on x86-based systems to Arm-based systems or vice versa will be easier. Generally available migration techniques like backup and restore will work across x86 and Arm-based systems.
 

Oracle Database Availability on Arm


Oracle Database Enterprise Edition is available on the Arm platform (Ampere Altra brands) in the cloud with flexible shapes (VM.Standard.A1.Flex) for Oracle Base Database Service (BaseDB). VM.Standard.A1.Flex is based on Arm computer processors and CPU cores. VM.Standard.A1.Flex shapes for BaseDB support any number of OCPUs, up to a maximum of 64, in a single VM. Each OCPU includes 8 GB of memory and 1 Gbps of network bandwidth, up to a maximum of 40 Gbps. VM.Standard.A1.Flex shapes offer balanced and higher-performance block volume storage that can scale up to 40 TB in data capacity. When provisioned with higher-performance storage, customers can achieve a maximum of 500K IOPS for a single VM.

Oracle Database on the Arm platform was released from Database release version 19.19. This database version complements the Oracle Database you have been running on x86 and other platforms, including RAC (on-premises currently), Data Guard, and others. Oracle Database on Arm is available on-premises and in Oracle Cloud Infrastructure. Currently, Oracle licenses are certified and licensed for Ampere Altra and Ampere Altra Max processors with a core factor of .25. Please refer to the core-factor table here: https://drive.google.com/file/d/14qOc_TP_NPe-kNeyCXbUtkK8Npg1prwJ/view?usp=sharing. All other Arm processors, not referred to in the core factor table, will use a core factor of 1 for licensing purposes.

Source: oracle.com

Monday, April 29, 2024

Where is the Complexity? Part 2

Introduction


In my previous blog post, I described how a simple Teller microservice to transfer funds from one account to another account could be implemented as REST services in Spring Boot. What I didn’t cover were all the possible ways a simple application like this could fail. I only mentioned a couple that could have been implemented. However, to solve this problem of various failures such as network failure, service failure, database failure, etc., one can choose to use a distributed transaction pattern such as the popular Saga pattern. In this post, I’ll examine what it takes to move from the simplistic minimal or no error handling version of these microservices to leveraging Eclipse MicroProfile Long Running Actions. As you’ll learn in reading this post, adding Sagas adds considerable complexity to the application code. Ideally using a distributed transaction pattern should simplify the developer’s life, not make it more complex. Continue reading to see how much complexity the developer takes on in using Sagas.

To use the Saga pattern with Eclipse MicroProfile Long Running Actions (LRA), the developer must keep track of information that is essentially unrelated to the business problem being solved. The basic flow for the LRA in this use case is:


  1. Teller microservice is called to transfer X from account A in department 1 to account B in department 2
  2. Teller microservice begins an LRA. This causes LRA headers to be included in subsequent calls
  3. Teller microservice calls withdraw on department1 for account A
    1. Under the covers, department 1 sees the LRA headers and joins the LRA by enlisting with the LRA coordinator
    2. Department 1 withdraws X from account A and returns to the teller microservice
  4. Teller microservice calls deposit on department 2 for account B
    1. Under the covers, department 2 sees the LRA headers and joins the LRA by enlisting with the LRA coordinator
    2. Department 2 deposits X into account B and returns to the teller microservice
  5. Teller microservice closes the LRA which causes
    1. LRA Coordinator to call back to the complete endpoint for each joined participant
    2. Each participant’s complete endpoint then performs any final steps for the LRA, more on this later
  6. Teller microservice returns success

Handling errors

Now to handle the possible various failures that can occur, the following additional steps may need to be performed:

  1. If the withdraw request fails, teller should cancel the LRA and return failure to its caller
    1. This in turn will cause the LRA coordinator to call back to each enrolled participant at their compensate endpoint, which at this point is only the initiator
    2. The initiator will get called back on its compensate endpoint if defined, at which it has nothing to do. The sample code doesn’t define a compensate or completion callback as there is nothing to do in the teller to compensate/complete the LRA as the teller maintains no state.
  2. Likewise, if the deposit request to account B fails, teller should cancel the LRA.
    1. This in turn will cause the LRA coordinator to call back to each enrolled participant at their compensate endpoint, which at this point is likely just the initiator and department 1. It might also include department 2 if department 2 had a chance to join before failing to deposit.
      1. The initiator will get called back on its compensate endpoint if defined, at which it has nothing to do
      2. Department 1 will get called back on its compensate endpoint, at which point it needs to figure out how to compensate the previous withdraw operation. This is where the fun begins. More on this shortly!
      3. Department 2 may also get called back on its compensate endpoint and go through the same drill as department 1.
  3. If both withdraw and deposit succeed, the teller should close the LRA. This causes the LRA coordinator to:
    1. Call the completion endpoint on the teller
      1. The teller really has nothing to do at this point
    2. Call the completion endpoint on department 1
      1. Department 1’s completion endpoint needs to clean-up any bookkeeping information it has on the LRA
    3. Call the completion endpoint on department 2
      1. Department 2’s completion endpoint needs to clean-up any bookkeeping information it has on the LRA

And we’re done! Well, sort of. Let’s examine in more detail what was mentioned about the fun beginning and bookkeeping. When a participant is called at its complete or compensate endpoints, all that is passed is the LRA Id in the REST headers. What it means to complete or compensate the transaction is completely up to the application code provided by the developer. So typically, this means creating some sort of log or journal to record the changes that were made as part of the LRA such that the microservice knows what needs to be done to complete or compensate the transaction.

Here is the updated Teller microservice code from TransferResource.java looks like:

    @Autowired
    @Qualifier("MicroTxLRA")
    RestTemplate restTemplate;

    @Value("${departmentOneEndpoint}")
    String departmentOneEndpoint;

    @Value("${departmentTwoEndpoint}")
    String departmentTwoEndpoint;

    @RequestMapping(value = "transfer", method = RequestMethod.POST)
    @LRA(value = LRA.Type.REQUIRES_NEW, end = true, cancelOnFamily = {HttpStatus.Series.CLIENT_ERROR, HttpStatus.Series.SERVER_ERROR})
    public ResponseEntity<?> transfer(@RequestBody Transfer transferDetails,
                                      @RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {

        LOG.info("Transfer initiated: {}", transferDetails);
        try {
            ResponseEntity<String> withdrawResponse = withdraw(transferDetails.getFrom(), transferDetails.getAmount());
            if (!withdrawResponse.getStatusCode().is2xxSuccessful()) {
                LOG.error("Withdraw failed: {} Reason: {}", transferDetails, withdrawResponse.getBody());
                return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                        .body(new TransferResponse("Withdraw failed. " + withdrawResponse.getBody()));
            }

            ResponseEntity<String> depositResponse = deposit(transferDetails.getTo(), transferDetails.getAmount());
            if (!depositResponse.getStatusCode().is2xxSuccessful()) {
                LOG.error("Deposit failed: {} Reason: {} ", transferDetails, depositResponse.getBody());
                return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                        .body(new TransferResponse("Deposit failed"));
            }
        } catch (Exception e) {
            LOG.error("Transfer failed with exception {}", e.getLocalizedMessage());
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body(new TransferResponse("Transfer failed. " + e.getLocalizedMessage()));
        }
        LOG.info("Transfer successful: {}", transferDetails);
        return ResponseEntity
                .ok(new TransferResponse("Transfer completed successfully"));
    }

You’ll notice that we’ve switched the RestTemplate to one provided by MicroTx that provides filters to automate things like passing MicroTx transaction headers. We added the @LRA annotation to indicate that a new LRA needs to be started. As well we’ve removed the redepositWithdrawnAmount method as the LRA will ensure corrective action takes place by eventually calling the department 1’s compensate endpoint. Finally, because of the “end = true” option in the LRA annotation, the LRA will automatically be closed (or canceled if the return status is a server or client error, 4xx or 5xx) when the transfer method completes.

So far, the changes are minimal. Let’s see what changes need to be made to the department deposit and withdraw services. In the first blog post, these services were contained in the AccountResource.java file. In this post, we’ve split that class into 3 separate classes (AccountAdminService, DepositService, and WithdrawService) as we want to register completion and/or compensation callbacks that are unique to the withdraw or deposit services.

Let’s look at the changes to how the withdraw service is implemented in WithdrawService.java: 

@RestController
@RequestMapping("/withdraw")
public class WithdrawService {

    private static final Logger LOG = LoggerFactory.getLogger(WithdrawService.class);

    @Autowired
    IAccountOperationService accountService;

    @Autowired
    JournalRepository journalRepository;

    @Autowired
    AccountTransferDAO accountTransferDAO;

    @Autowired
    IAccountQueryService accountQueryService;

    /**
     * cancelOnFamily attribute in @LRA is set to empty array to avoid cancellation from participant.
     * As per the requirement, only initiator can trigger cancel, while participant returns right HTTP status code to initiator
     */
    @RequestMapping(value = "/{accountId}", method = RequestMethod.POST)
    @LRA(value = LRA.Type.MANDATORY, end = false, cancelOnFamily = {})
    public ResponseEntity<?> withdraw(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,
                                      @PathVariable("accountId") String accountId, @RequestParam("amount") double amount) {
        try {
            this.accountService.withdraw(accountId, amount);
            accountTransferDAO.saveJournal(new Journal(JournalType.WITHDRAW.name(), accountId, amount, lraId, ParticipantStatus.Active.name()));
            LOG.info(amount + " withdrawn from account: " + accountId);
            return ResponseEntity.ok("Amount withdrawn from the account");
        } catch (NotFoundException e) {
            return ResponseEntity.status(HttpStatus.NOT_FOUND).body(e.getMessage());
        } catch (UnprocessableEntityException e) {
            LOG.error(e.getLocalizedMessage());
            return ResponseEntity.status(HttpStatus.UNPROCESSABLE_ENTITY).body(e.getMessage());
        } catch (Exception e) {
            LOG.error(e.getLocalizedMessage());
            return ResponseEntity.internalServerError().body(e.getLocalizedMessage());
        }
    }

Where is the Complexity? Part 2
As you can see the withdraw service got more complex as we needed to introduce a journal to keep track of changes made by an LRA. We keep this journal so when asked to compensate we’ll know how to compensate the request. The withdraw service updates the account balance immediately and tracks the change in its journal.

    /**
     * Update LRA state. Do nothing else.
     */
    @RequestMapping(value = "/complete", method = RequestMethod.PUT)
    @Complete
    public ResponseEntity<?> completeWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {
        LOG.info("withdraw complete called for LRA : " + lraId);
        Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.WITHDRAW);
        if (journal != null) {
            String lraState = journal.getLraState();
            if (lraState.equals(ParticipantStatus.Completing.name()) ||
                    lraState.equals(ParticipantStatus.Completed.name())) {
                // idempotency : if current LRA stats is already Completed, do nothing
                return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));
            }
            journal.setLraState(ParticipantStatus.Completed.name());
            accountTransferDAO.saveJournal(journal);
        }
        return ResponseEntity.ok(ParticipantStatus.Completed.name());
    }

    /**
     * Read the journal and increase the balance by the previous withdrawal amount before the LRA
     */
    @RequestMapping(value = "/compensate", method = RequestMethod.PUT)
    @Compensate
    public ResponseEntity<?> compensateWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {
        LOG.info("Account withdraw compensate() called for LRA : " + lraId);
        try {
            Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.WITHDRAW);
            if (journal != null) {
                String lraState = journal.getLraState();
                if (lraState.equals(ParticipantStatus.Compensating.name()) ||
                        lraState.equals(ParticipantStatus.Compensated.name())) {
                    // idempotency : if current LRA stats is already Compensated, do nothing
                    return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));
                }
                accountTransferDAO.doCompensationWork(journal);
            } else {
                LOG.warn("Journal entry does not exist for LRA : {} ", lraId);
            }
            return ResponseEntity.ok(ParticipantStatus.Compensated.name());
        } catch (Exception e) {
            LOG.error("Compensate operation failed : " + e.getMessage());
            return ResponseEntity.ok(ParticipantStatus.FailedToCompensate.name());
        }
    }

    @RequestMapping(value = "/status", method = RequestMethod.GET)
    @Status
    public ResponseEntity<?> status(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,
                                    @RequestHeader(LRA_HTTP_PARENT_CONTEXT_HEADER) String parentLRA) throws Exception {
        return accountTransferDAO.status(lraId, JournalType.WITHDRAW);
    }

    /**
     * Delete journal entry for LRA (or keep for auditing)
     */
    @RequestMapping(value = "/after", method = RequestMethod.PUT)
    @AfterLRA
    public ResponseEntity<?> afterLRA(@RequestHeader(LRA_HTTP_ENDED_CONTEXT_HEADER) String lraId, @RequestBody String status) {
        LOG.info("After LRA called for lraId : {} with status {} ", lraId, status);
        accountTransferDAO.afterLRA(lraId, status, JournalType.WITHDRAW);
        return ResponseEntity.ok().build();
    }

The remaining portion of the WithdrawService class is dealing with completing or compensating the LRA, allowing the department's view of the LRA status, and registering a callback when the LRA is completed or compensated. If the LRA is canceled and the compensation endpoint is called by the coordinator, it redeposits the withdrawn amount and sets its LRA state to compensated. If the LRA is completed, then the completion endpoint is called which has nothing to do as the funds have already been withdrawn and committed, so just its LRA state is updated. When the LRA is finally done, either completed or compensated, the after callback is made by the coordinator. In this case the callback simply deletes the journal entry as it is no longer needed.

Let's look at the deposit service DepositService.java:

@RestController
@RequestMapping("/deposit")
public class DepositService {

    private static final Logger LOG = LoggerFactory.getLogger(DepositService.class);

    @Autowired
    IAccountOperationService accountService;

    @Autowired
    JournalRepository journalRepository;

    @Autowired
    AccountTransferDAO accountTransferDAO;

    /**
     * cancelOnFamily attribute in @LRA is set to empty array to avoid cancellation from participant.
     * As per the requirement, only initiator can trigger cancel, while participant returns right HTTP status code to initiator
     */
    @RequestMapping(value = "/{accountId}", method = RequestMethod.POST)
    @LRA(value = Type.MANDATORY, end = false, cancelOnFamily = {})
    public ResponseEntity<?> deposit(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,
                                     @PathVariable("accountId") String accountId, @RequestParam("amount") double amount) {
        accountTransferDAO.saveJournal(new Journal(JournalType.DEPOSIT.name(), accountId, amount, lraId, ParticipantStatus.Active.name()));
        return ResponseEntity.ok("Amount deposited to the account");
    }

    /**
     * Increase balance amount as recorded in journal during deposit call.
     * Update LRA state to ParticipantStatus.Completed.
     */
    @RequestMapping(value = "/complete", method = RequestMethod.PUT)
    @Complete
    public ResponseEntity<?> completeWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {
        try {
            LOG.info("deposit complete called for LRA : " + lraId);
            Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.DEPOSIT);
            if (journal != null) {
                String lraState = journal.getLraState();
                if (lraState.equals(ParticipantStatus.Completing.name()) ||
                        lraState.equals(ParticipantStatus.Completed.name())) {
                    // idempotency : if current LRA stats is already Completed, do nothing
                    return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));
                }
                accountTransferDAO.doCompleteWork(journal);
            } else {
                LOG.warn("Journal entry does not exist for LRA : {} ", lraId);
            }
            return ResponseEntity.ok(ParticipantStatus.Completed.name());
        } catch (Exception e) {
            LOG.error("Complete operation failed : " + e.getMessage());
            return ResponseEntity.ok(ParticipantStatus.FailedToComplete.name());
        }
    }

and the doCompleteWork method looks like:

    public void doCompleteWork(Journal journal) throws Exception {
        try {
            Account account = accountQueryService.getAccountDetails(journal.getAccountId());
            account.setAmount(account.getAmount() + journal.getJournalAmount());
            accountService.save(account);
            journal.setLraState(ParticipantStatus.Completed.name());
            journalRepository.save(journal);
        } catch (Exception e) {
            journal.setLraState(ParticipantStatus.FailedToComplete.name());
            journalRepository.save(journal);
            throw new Exception("Failed to complete", e);
        }
    }

which finally adds the amount deposited to the account. If we look at the compensate callback for deposit, it just sets the LRA status in the journal to compensated:

    /**
     * Update LRA state to ParticipantStatus.Compensated.
     */
    @RequestMapping(value = "/compensate", method = RequestMethod.PUT)
    @Compensate
    public ResponseEntity<?> compensateWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {
        LOG.info("Account deposit compensate() called for LRA : " + lraId);
        Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.DEPOSIT);
        if (journal != null) {
            String lraState = journal.getLraState();
            if (lraState.equals(ParticipantStatus.Compensating.name()) ||
                    lraState.equals(ParticipantStatus.Compensated.name())) {
                // idempotency : if current LRA stats is already Compensated, do nothing
                return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));
            }
            journal.setLraState(ParticipantStatus.Compensated.name());
            accountTransferDAO.saveJournal(journal);
        }
        return ResponseEntity.ok(ParticipantStatus.Compensated.name());
    }

    @RequestMapping(value = "/status", method = RequestMethod.GET)
    @Status
    public ResponseEntity<?> status(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,
                                    @RequestHeader(LRA_HTTP_PARENT_CONTEXT_HEADER) String parentLRA) throws Exception {
        return accountTransferDAO.status(lraId, JournalType.DEPOSIT);
    }

Finally the after callback will be triggered and it will delete the associated journal entry by calling the afterLRA method on the accountTransferDAO.

    /**
     * Delete journal entry for LRA (or keep for auditing)
     */
    @RequestMapping(value = "/after", method = RequestMethod.PUT)
    @AfterLRA
    public ResponseEntity<?> afterLRA(@RequestHeader(LRA_HTTP_ENDED_CONTEXT_HEADER) String lraId, @RequestBody String status) {
        LOG.info("After LRA Called : " + lraId);
        accountTransferDAO.afterLRA(lraId, status, JournalType.DEPOSIT);
        return ResponseEntity.ok().build();
    }

The accountTransferDAO afterLRA method is what actually deletes the journal entry:

    public void afterLRA(String lraId, String lraStatus, JournalType journalType){
        Journal journal = getJournalForLRAid(lraId, journalType);
        if (Objects.nonNull(journal) && isLRASuccessfullyEnded(lraStatus)) {
            journalRepository.delete(journal);
        }
    }

As the above code shows, a lot of additional work is required the application developer to ensure data consistency. However even with all these changes, the above code doesn't handle two calls to deposit or withdraw in the same LRA. The journal implementation only records the last update.

Source: oracle.com