Skip to main content

Introduction to Couchbase for Oracle Developers and Experts: Part 8: Transactions




Six thousand years ago, the Sumerians invented writing for transaction processing - Gray and Reuter

Transaction is a set of read and write actions consisting of

  • Unprotected actions on the transient state outside the transaction statement scope and state (e.g. storage allocations, dictionary management)

  • Protected change the persisted data, actions must be reflected in transaction outcome

  • Real actions using sensors and actuators, once done, cannot be undone.


Transaction Examples

ORACLE

Couchbase

Oracle automatically starts a multi-statement transaction for every new statement. So, issuing BEGIN WORK or START TRANSACTION is unnecessary

BEGIN WORK, START TRANSACTION are all synonymous and start a multi statement transaction. 

– Transaction begins automatically

INSERT INTO customer(cid, name, balance) VALUES(4872, “John Doe”, 724.23);

INSERT INTO customer(cid, name, balance) VALUES(1924, “Bob Stanton”, 2735.48);

COMMIT;

START TRANSACTION;

INSERT INTO customer

VALUES(“cx4872”, {“cid”: 4872, “name”:”John Doe”, “balance”:724.23});

INSERT INTO customer

VALUES(“cx1924”, {“cid”: 1924, “name”:”Bob Stanton”, “balance”:2735.48});

COMMIT;

– Transaction begins automatically

UPDATE customer SET balance = balance + 100 WHERE cid = 4872;

SELECT cid, name, balance  from customer;

UPDATE customer SET balance = balance – 100 WHERE cid = 1924;

SELECT cid, name, balance from customer;

COMMIT ;


START TRANSACTION;

UPDATE customer SET balance = balance + 100 WHERE cid = 4872;

SELECT cid, name, balance  from customer;

UPDATE customer SET balance = balance – 100 WHERE cid = 1924;

SELECT cid, name, balance from customer;

COMMIT ;

– Transaction begins automatically

UPDATE customer SET balance = balance + 100 WHERE cid = 4872;

SELECT cid, name, balance  from customer;

SAVEPOINT s1;

UPDATE customer SET balance = balance – 100 WHERE cid = 1924;

SELECT cid, name, balance from customer;

ROLLBACK WORK TO SAVEPOINT s1;

SELECT cid, name, balance from customer;

COMMIT ;


START TRANSACTION;

UPDATE customer SET balance = balance + 100 WHERE cid = 4872;

SELECT cid, name, balance  from customer;

SAVEPOINT s1;

UPDATE customer SET balance = balance – 100 WHERE cid = 1924;

SELECT cid, name, balance from customer;

ROLLBACK WORK TO SAVEPOINT s1;

SELECT cid, name, balance from customer;

COMMIT ;




Transaction Discussion

ORACLE

Couchbase

Transaction documentation:

Intro docs

Concurrency Control

Transaction documentation:

N1QL Transactions documentation

N1QL Transactions overview

ACID


Atomic is a notion that a transaction either commits or aborts. Protected actions in a transaction are all or nothing.   Oracle transactions supports this. Any unfinished transaction is assumed to be aborted using the principle of presumed abort.

Couchbase transactions are ACID. You must use the BEGIN WORK or set a single statements tximplicit attribute to true. Without these, updates support atomicity at per-document level. 

Similar to RDBMS, the unfinished transaction (or timed out transactions) are presumed abort.  Their protected actions won’t be durable and not be seen by any other transaction. 

Consistency is the sequence of actions to transform the database from one consistent state to another. This is important since the schema can have many constraints and data has to confirm to them at the end of the transaction.   Within an Oracle transaction, you can defer the constraint checks to end of the transaction, but the verification will happen before the transaction is committed.  If there’s a constraint failure, a statement or the whole transaction will be rolled back.

Couchbase only supports unique key constraint on the document key. The document key is user generated and at the document INSERT time, this is verified. This uniqueness is again verified at the COMMIT time. 


Other transactions won’t see any intermediate state from other transactions.

Isolation deals with providing an compartmentalized view of the data for each transaction so they don’t interfere with each other. If they do, each database can take actions (e.g. blocking, aborting) to prevent conflicts and inconsistencies.


Oracle implements three isolation levels: Read committed, serializable and Read-only

Couchbase implements one isolation level: Read committed. This is the common isolation level used by most applications. 

Durability: What gets committed, stays committed. Once the protected actions are committed, it survives hardware and software failures. 

Couchbase is a distributed system and for high availability, there can (and should be) multiple copes of the same data; Hence, there are multiple durability options which you can choose based on your SLA: 

  • majority (copy to majority of active nodes)

  • majorityAndPersistActive (copy to all active and ensure each of them persist to disk)

  • persistToMajority (ensure majority of the active persist to disk)

  • even “none” for memory only writes. . 

See details here and here.

CONSISTENCY

This consistency is different from the consistency in ACID. This section is about read consistency of data and index. In the “real world”, you can never see (experience or process) as things happen due to speed limit of light and warping of spactime. We all see a delayed slice of our reality.This is true for databases as well – unless you make it a single user read-write database, which conflicts with our concurrency and performance goal.

Multiversion read consistency

This provides point-in-time read consistency and non blocking queries (readers and writers do not block one another). 

Couchase is a distributed system. The updates to the data are streamed to indexer. So, the indexer is maintained (updated) asynchronously. 


For the Couchbase data (collections), under the Couchbase transaction regime:

  • Reads are either the version last committed by a transaction or the latest verson updated within the transaction (read your own writes).

  • Writers don’t block readers. When a document is being updated (writing), readers will see the previous version.

  • Couchbase data does not have point-in-time reads for the data (collection)

  • Data (collections) does not have multiversion or point in time read consistency. 

GSI Indexes, 

  • Supports multi-version concurrency

  • Version (snapshot) selected depends on the scan consistency 

  • Each scan requests from the query service comes with consistency request: unbounded, request_plus

  • Each scan request is done over a snapshot, per consistency

  • Unbounded is latest version available; request_plus is consistent to as of now ( read-your own writes)

  • Within transactions, all of the index reads uses request_plus by default, can be changed to unbounded by the user

Statement-Level Read Consistency

All of the data read is committed and is consistent up to a single point of time. 


Couchbase consistency follows the protocol above based on index scan consistency. 

Transaction-Level Read Consistency is similar to statement level consistency, except all of the statements in a transaction use the transaction start time as a single point of consistency.


Unavailable.

Statement-Level atomicity


Supported

Supported

Statements supported


All the DMLs are supported: SELECT, INSERT, UPDATE, DELETE, MERGE and these statements inside PL/SQL and other routines.

All the DMLs are supported: SELECT, INSERT, UPDATE, DELETE, MERGE.   In Couchbase 7.1, these statements can be used inside JavaScript UDFs as part of a transaction. Look for Couchbase 7.1 release in 2022. 

SAVEPOINT: Supported

Supported



Distributed Transaction

Supports transaction in a multi-node RAC deployment as well.

Couchbase is a fully distributed database. 

Transactions are supported for this distributed database.

Federated Transaction

Oracle supports single transaction to update tables in multiple Oracle databases in multiple instances.


Couchbase transactions support updating collections in multiple scopes and multiple buckets, but only a single database instance. 

Concurrency Infrastructure
Pessimissic locking -- both on a single node configuration and a multi-node configuratio (RAC) using distributed lock manager.
Optimistic locking throughout -- for simple single document update and multi-document updates. For singleton document update via simple API,  a CAS within every document is used to implement the check-and-set protocol.  For multi-document updates, Couchbase implemented a novel commit protocol using CAS and additional infrastructure within Couchase.


Six thousand years later... 

Online transactions today are key to life as we know it:  Bruce Lindsay 


Labels:
Location: Union City, CA, USA

Comments

Post a Comment

Popular posts from this blog

Swami Vivekananda: The Monk That Nobody Sent to Chicago

  There’s a saying in Chicago: “We don’t want nobody that nobody sent.” This was the cold reception Swami Vivekananda faced when he arrived in the windy city in July 1893, determined to attend the World Parliament of Religions that September. He belonged to no organization, carried no letter of recommendation, his countrymen were nobody, and represented an alien religion to the Western world. As the days passed, his hope of attending the parliament dwindled. With money running out and the odds stacked against him, he left the Windy City and went to Boston, praying for a glimmer of opportunity.  Swamiji came to America to share India’s most profound gift: the wisdom of the Hindu sages, preserved through centuries of oral tradition and embodied by its monks. This was 1893, not 1993—India was under the British grip, its resources drained, and its spirit subdued. Swamiji’s mission was not just a cultural exchange; it was a bold step toward envisioning a future where India could re...
Post a Comment
Read more

Why Should Databases Go Natural?

From search to CRM, applications are adopting natural language and intuitive interactions. Should databases follow? This article provides a strategic perspective. Amid the many technological evolutions in software and hardware (CISC/RISC, Internet, Cloud, and AI), one technology has endured:  Relational Database Systems   (RDBMS), aka SQL databases. For over 50 years, RDBMS has survived and thrived, overcoming many challenges. It has evolved and adopted beneficial features from emerging technologies like object-relational databases and now competes robustly with   NoSQL databases .  Today, RDBMS dominates the market, with four of the top five databases and seven of the top ten being relational. RDBMS has smartly borrowed ideas, like JSON support, from NoSQL, while NoSQL has also borrowed from RDBMS. NoSQL no longer rejects SQL. From a user perspective, all modern databases have SQL-inspired query language and a set of APIs. All applications manage the respective data...
Post a Comment
Read more

iQ Interactive: Cool Things for Developers on Couchbase Capella iQ

  The landscape of software development is ever-evolving with the advent of new technologies. As we venture into 2023, natural language processing ( NLP ) is rapidly emerging as a pivotal aspect of programming. Unlike previous generations of tools that primarily aimed at enhancing coding productivity and code quality, the new generation of Artificial Intelligence ( GenAI ) tools, like iQ, is set to revolutionize every facet of a developer's workflow. This encompasses a wide range of activities: Reading, writing, and rewriting specifications Designing, prototyping, and coding Reviewing, refactoring, and verifying software Going through the iterative cycle of deploying, debugging, and improving the software Create a draft schema and sample data for any use case Natural language queries. Generate sample queries on a given dataset Fix the syntax error for a query Don't stop here. Let your imagination fly. Although the insights garnered from iQ are preliminary and should be treated ...
Post a Comment
Read more