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

MongoDB to Couchbase for Developers: Part 2: Database Objects

    MongoDB developers and DBAs work with physical clusters, machines, instances, storage systems, disks, etc. All MongoDB users, developers, and their applications work with logical entities: databases, collections, documents, fields, shards, users, data types.  There are a lot of similarities with Couchbase since both are document (JSON) oriented databases.  Let’s compare and contrast the two with respect to database objects.  Here’s the first part of this series comparing the architecture.   MongoDB Organization A full list of MongoDB schema objects is listed here and here . A database instance can have many databases; A database can have many collections; Each collection can have many indexes.  Each collection can be sharded based into multiple chunks on multiple nodes of a cluster using hash-sharding strategy or index sharding strategy.  The MongoDB indexes are local to their data.  Therefore, the indexes use the same strategy as ...
3 comments
Read more

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

MongoDB to Couchbase for Developers: Part 1: Architecture

  Introduction By any measure, MongoDB is a popular document-oriented JSON database. In the last dozen years, it has grown from its humble beginnings of a single lock per database to a modern multi-document transaction with snapshot isolation. MongoDB University has trained a large number of developers to develop the MongoDB database. There are many JSON databases now. While it's easy to start with MongoDB to learn NoSQL and flexible JSON schema, many customers choose Couchbase for performance, scale, and SQL. As you progress in your database evaluation and evolution, it's easier to start from what you know now. That's the idea for this series. Use your MongoDB knowledge to easily learn and eventually master Couchbase.  Don't despair if you don't know MongoDB do know any one of the RDBMS. This Oracle to Couchbase series will help you learn by comparison.  Many nations are known by their language: French, German, Spanish). Many databases are known for their language...
1 comment
Read more