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

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 ;

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 support 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 statement's tximplicit attribute to true. Without these, updates support atomicity at the 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 conform to them at the end of the transaction.   Within an Oracle transaction, you can defer the constraint checks to the 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 constraints 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 a 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 copies of the same data; Hence, there are multiple durability options that you can choose based on your SLA: 

• the majority (copy to a majority of active nodes)

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

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

• even “none” for memory only writes. 

CONSISTENCY

This consistency is different from the consistency in ACID. This section is about the read consistency of data and index. In the “real world”, you can never see (experience or process) as things happen due to the speed limit of light and warping of spacetime. 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). 

Coucbase is a distributed system. The updates to the data are streamed to the 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 version 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 multi-version or point in time read consistency. 

GSI Indexes, 

• Supports multi-version concurrency

• Version (snapshot) selected depends on the scan consistency 

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

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

• Unbounded is the latest version available; request_plus is consistent 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 transactions 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 transactions 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.