Nested Transactions in CockroachDB 20.1

Nested Transactions in CockroachDB 20.1

CockroachDB 20.1 introduces support for nested transactions, a SQL feature which simplifies the work of programmers of certain client applications. In this post, we'll explore when and where to use nested transactions, when not to use them, and how they work in a distributed environment.

CockroachDB 20.1 introduces support for nested transactions, a SQL feature which simplifies the work of programmers of certain client applications. In this post, we'll explore when and where to use nested transactions, when _not _to use them, and how they work in a distributed environment. 

What Are Nested Transactions?

A regular SQL transaction groups one or more statements together and provides them the A (Atomicity) and I (Isolation) of ACID. These are guarantees over the effects of the transaction from the perspective of concurrent clients running other transactions, or subsequent transactions by the same client. Atomicity means that all the statements inside the transaction appear to execute either successfully, or not at all. Isolation means that a concurrent transaction cannot see the intermediate steps inside the transaction and can only perceive the state of the database before the first statement starts, or after the last completes.

Nested transactions are additional transactions that occur within a regular transaction or other nested transactions, like Russian dolls. Nested transactions are invisible to concurrent clients, due to the atomicity and isolation of their surrounding, “outermost” transaction. Therefore, they are a feature that exists only for the benefit of the client issuing the outer transaction.

More specifically, nested transactions have been invented for the benefit of software engineers of client applications.

In component-based design, different programmers are responsible for the internals of different components and may not know about each other's work. If their components need to collectively operate over a database, they face an important challenge: how can each component safely contribute its portion of the database work, in the context of a multi-component transaction? If one component fails its work, it is usual to want another component to “take over” and complete the work in a different way, without giving up on the overall transaction. Nested transactions facilitate this, by providing atomicity (all succeed, or all fail) to the work of sub-components in the client app.

An example of this could be an hypothetical order system of your favorite assemble-your-own-furniture store. As the customer is walking the aisle and selecting their parts, their partial order starts a transaction that gets a lock on the supplies they need, incrementally. At some point, they may be reaching the kitchen area and start a design project using the provided interactive console. As they are experimenting, they wish to move forward with their new kitchen only if all the parts are available. What if the logic that allocates the parts encounters an insufficient supply and fails to instantiate the full kitchen on their order?

At that point, the customer may want to resume their shopping, without the kitchen order, but with all the other items they had in their shopping cart before: they want to roll back the kitchen sub-transaction, without giving up on the surrounding transaction. Nested transactions help achieve that.

cockroachdb acid compliance transaction handling sql nest

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen

Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

The Easy Guide on How to Use Subqueries in SQL Server

This article provides an outlook on various types of subqueries in SQL such as select or other T-SQL statements and caveats when using them.