October 25, 2023

Build Resilient Transactional Apps with SingleStore

Mohan Manthena

Staff Product Manager

Eric Hanson

Director of Product Management

Supporting mission-critical workloads with SingleStore: Part 2.

SingleStore is a cloud-based distributed database that delivers blazing-fast performance, even when you're dealing with billions of rows of data. This, coupled with ACID transactional consistency across multiple partitions, high reliability and availability, makes SingleStore an ideal database for building your next mission-critical transactional (OLTP) application.

Build Resilient Transactional Apps with SingleStore

In the second part of this two-part series, we will discuss the transactional capabilities of SingleStore. SingleStore supports a variety of transactional features including single- and multi-statement transactions, atomic distributed transactions, multi-version concurrency control and various locking mechanisms. We will also discuss SingleStore's fault-tolerance capabilities, as 24/7 availability is critical for OLTP workloads. To learn about transactional performance, read part one of this series.

Transactions

All database read and write operations are done as transactions in SingleStore. These have classic ACID properties: they are atomic, consistent, isolated and durable.

Single-statement transactions

Every SQL statement in SingleStoreDB is run in a transaction by itself by default. For example, suppose you have the orders_CS table from Part 1 of this blog series and you run this SQL statement:

DELETE from orders_CS WHERE order_date > "2023-09-30";

This statement will be atomic. That is, all the rows matching the WHERE clause will be deleted — or if the transaction fails for any reason, none of them will. Some reasons a transaction might fail include hardware failure, an operating system restart or a DBA using the KILL command to terminate it.

Multi-statement transactions

You can explicitly create transactions involving multiple statements. Multi-statement transactions are critical to ensure data consistency when performing update operations involving multiple steps. These are a sequence of one or more SQL statements that start with BEGIN WORK and end with COMMIT to finalize the transaction.

The whole transaction is treated as a single unit of work. If the transaction fails for any reason, including a hardware or software failure, or an explicit call to ROLLBACK, the whole transaction is undone. Lets look at an example by using our orders_CS and line_items tables introduced in part one.

BEGIN WORK;
INSERT INTO orders_CS (order_id, customer_id, order_date)  
    VALUES (12345, 145678, NOW()); 

INSERT INTO line_items (order_id, product_id, quantity, price) 
    VALUES (12345, 9876, 25, 750.00), (12345, 23456, 25, 
    200.00), (12345, 36789,  35,  350.00); 
COMMIT;

In this example, we started a transaction using BEGIN WORK and inserted a new order into the orders_CS table, then inserted the corresponding line items into the line_items table. The BEGIN WORK and COMMIT statements ensure that the data is inserted into the tables as a single unit of work, maintaining data consistency. For example, this transaction can't create an order without a lineitem.

While using multi-statement transactions, remember to choose the transaction size wisely. If it is too long, it could result in longer lock times for the tables or rows that are updated or inserted. This could affect the performance of other queries that need to access these tables or rows by making them wait. You should never include human thinktime in a transaction.

Two-phase commit

Let's try updating two records across multiple partitions. SingleStore supports the two-phase commit protocol (a well-known technique for ACID-compliant distributed commit processing) to ensure the atomicity of distributed transactions across multiple partitions. In this example, we will update a set of rows that spans multiple partitions.

    DELETE FROM orders_CS
    WHERE order_date BETWEEN '1994-01-01' AND '1997-12-31';

Here we started a transaction and deleted multiple rows within the order date range. The Two-phase commit ensures that this operation that spans across multiple partitions is atomic, even if there is a failure during the COMMIT processing.

Concurrency control

In addition to maintaining atomicity, it is critical for a transactional application to ensure that transactions are isolated from one another. SingleStore uses multi-version concurrency control to make sure readers and writers don't block each other. And it uses locks to ensure that writers don't interfere with each other. For fine-grain control of waiting for applications that need it, you can use SELECT FOR UPDATE to achieve serializable isolation (e.g. for small financial transactions).

Multi-version concurrency control

A special feature that SingleStore offers to support high volumes of concurrent reads and writes is Multi-Version Concurrency Control (MVCC). MVCC increases transaction concurrency and reduces the response time for read-only transactions by maintaining a history of versions of each row. Readers see the version of the row that is current at the time they started. This allows readers and writers to not block each other with MVCC. MVCC uses the row versions to enable reads and writes of the same row to happen at the same time with no blocking.

Lets look at this example that executes two transactions simultaneously:

In this example, the second transaction’s (Transaction 2) SELECT query can still proceed and return the state of the row with order_id = 296492326 as it was before the first transaction (Transaction 1) started. The first transaction updates won't affect the second since it's working with a different version of the row, thanks to MVCC. When the first transaction is finally committed, the data changes become a part of the dataset and would be seen in subsequent transactions.

Update locks

Read-only transactions normally don't use locks; they synchronize with writers using multi-version concurrency control (MVCC). But update transactions must synchronize with each other, and this is what locks are used for.

SingleStore supports row-level and partition-level locking to facilitate concurrency while making it appear that transactions are running one at a time. When you use any of the insert, update or delete operations, SingleStore applies row-level locking and escalates to partition-level locking once default_columnstore_table_lock_threshold rows are locked within a partition. This helps achieve better concurrency by minimizing the locked scope, allowing other write transactions to operate on the non-locked rows. For example in the following update statement, a row lock is applied only to the row with order_id = 1797. Remaining rows in the order_CS table can still be accessed by other write transactions.

UPDATE orders-CS SET customer_id = 920987 WHERE order_id = 1797;

To see what transactions are waiting for others based on locks, consult mv_blocked_queries. It can be helpful to debug unexpected waiting.

Serializable isolation using SELECT FOR UPDATE

Serializable isolation is a form of isolation where transactions composed of read or write operations (or both) appear to run in a serial order. This is in some sense the ideal type of concurrency control because it gives perfect isolation — but it must be used judiciously because it can limit concurrency and throughput that's critical for real-time analytics applications.

To achieve serializable isolation in SingleStore you can use SELECT FOR UPDATE. It can be useful, for say, fine-grain multi-statement transactions that SELECT a few rows and UPDATE them in a later statement where you don't want any other transaction to see the state of the rows after they are initially read.

The SELECT FOR UPDATE statement acquires a write lock on the rows returned by the SELECT query, and this lock is held until the transaction is committed or rolled back. For a transactional application, this type of lock ensures that the data you are reading is consistent. SELECT FOR UPDATE does not use MVCC; it forces the SELECT to see the latest version of the records accessed.

Suppose you are reading a row to update it, and you want to make sure the data you are reading is the same data that is currently in the database. If another transaction updates the row in the meantime, you may be working with outdated data. The SELECT FOR UPDATE statement prevents this from happening by locking the rows so no other transaction can update them until your transaction is complete.

An example of where you might want to use SELECT FOR UPDATE is the following. Suppose you want to subtract a value from the balance in an "account" record, and if the balance drops below zero, write a record into a "flagged_account" table. You want to make sure you don't flag the same account twice with the same negative balance — regardless of concurrent updates to the account. You can do this like so:

DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS flagged_account;
CREATE TABLE account(id bigint, balance numeric(18,2));
INSERT INTO account VALUES(1000, 500.00);

CREATE TABLE flagged_account(id bigint, dt datetime(6), hypothetical_balance numeric(18,2));

delimiter //
CREATE OR REPLACE PROCEDURE debit(_id bigint, _debit_amount numeric(18,2)) AS
DECLARE _amount numeric(18,2);
BEGIN /* Do not confuse this with BEGIN WORK. This starts the stored procedure body. */

START TRANSACTION;

SELECT balance INTO _amount
FROM account
WHERE id = _id
FOR UPDATE;

if _amount < _debit_amount then
  INSERT INTO flagged_account VALUES(_id, now(6), _amount - _debit_amount);
  COMMIT; /* important to not leave transaction open after returning */
  RETURN;
end if;

UPDATE account SET balance = balance - _debit_amount 
WHERE id = _id;

COMMIT;

END
//
delimiter ;

SELECT * FROM account;
CALL debit(1000, 600.00);
SELECT * FROM account;
SELECT * FROM flagged_account;

The SELECT FOR UPDATE holds the lock on the affected account record until COMMIT. That prevents anybody else from reading the same value at the same time, writing an incorrect flagged_account record — or even updating the account record with the wrong value.

SELECT FOR UPDATE will limit concurrency because it uses locks for read operations, as opposed to standard MVCC where readers don't block. It’s important you only use it when you need it to get the update synchronization necessary for a transactional application.

Fault tolerance

In previous sections, we discussed how to build a transactionally-consistent OLTP application using SingleStore. We understand databases are a critical backbone of any application, and disruptions or failures can result in costly downtime. In this section, we will discuss how SingleStore gives you the enhanced database fault tolerance needed for transactional applications. We will start by covering various durability options, how to maintain high availability (HA) of your database, discuss disaster recovery strategies and delve into various backup and restore options, including Point-in-Time-Recovery (PITR).

Transaction-log-based durability

To ensure transactions are durable and the database is highly available, SingleStore uses two strategies: maintaining a transaction log and replicating it. The transaction log, also known as the write-ahead log, records all changes made during a transaction, guaranteeing data consistency even in a crash. SingleStore has two durability modes for transactions: synchronous and asynchronous. Synchronous durability waits for the writes to be committed to the disk before acknowledging, while asynchronous durability allows for more flexible writing of updates.

In addition to durability, SingleStore also replicates the transaction log to secondary partitions to prevent data loss and provide high availability (HA). This replication can be configured to work synchronously or asynchronously. The default configuration is sync replication with async durability. Under this configuration, information in committed transactions is always on two nodes at commit time. Losing a node won't lose data.

In this example with default configuration:

Application initiates writes to the database
These changes are written to the transaction log and sync replicated to the secondary partitions
Once changes are in both partitions, a commit is acknowledged to the application
Simultaneously, writes are asynchronously written to the disk

Even though the default is async durability, disk writes happen almost instantly (within a few milliseconds). This setup strikes a good balance between ensuring data consistency through replication and achieving faster writes.

High availability and disaster recovery

Ensuring constant data availability is a top priority for us, and that's why SingleStore is highly available by default. Our high availability (HA) feature safeguards against node failure, availability zone (AZ) failure or an entire region failure. This is made possible by SingleStore's scale-out architecture, which includes aggregators and leaf nodes, and replication of data across leaf nodes.

Aggregators manage query routing and display results to clients/apps, while leaf nodes store customer data. In the event of an aggregator node failure, we rely on the cloud provider's infrastructure and Kubernetes orchestration to quickly replace the failed node. However, if a leaf node fails, a method is needed to avoid the risk of data loss or data unavailability.

To mitigate this risk, data is distributed across multiple leaf nodes and stored in both primary and secondary partitions. These leaf nodes are organized into availability groups, ensuring that SingleStore always maintains two copies of your data to protect against single-node failure. Let's consider an example in the event of a node failure.

The primary partitions are evenly distributed across nodes in the first diagram, while secondaries of primary partitions in an availability group are evenly spread across leaf nodes in the other availability group. Look at “db_0 primary” on Node 1 has a replica “db_0 replica” on Node 2. Similarly, db_1 has a replica on Node 4.

If Node 1 should fail, the secondary of db_0 on Node 2 and the replica of db_1 on Node 4 are both promoted to primary replicas. This provides the database with high availability and balances the load across the surviving nodes, avoiding hotspots.

We extend this concept by using availability groups to survive an AZ failure when you use our cloud database service. As a premium customer, SingleStoreDB deploys a copy of all the partitions in your database in each of two availability groups, and puts each availability group in a separate AZ. In the event of an AZ failure, the primary aggregator automatically redirects queries to the secondary partitions in the surviving AZ.

We're also developing a solution to support high availability for primary aggregators (in preview) in case the AZ where the primary aggregator (MA) resides is also unavailable. The HA for MA solution automatically deploys MAs across three AZs and uses an election algorithm for the surviving nodes to elect a new MA if one goes down. This algorithm uses a variant of the RAFT protocol.

Finally, ensuring application availability during a whole region outage is equally important. While these events are infrequent, we plan for all scenarios. For our self-managed customers, you can use the REPLICATE DATABASE command to asynchronously replicate your clusters to a secondary region or cluster. This ensures your data is safely and consistently stored in a different region, allowing for easy failover if the primary region becomes unavailable. Additionally, we are actively working on a managed cross-region disaster recovery (DR) solution where customer databases are replicated across regions.

In our cloud service, a set of virtual machines dedicated to a customer and used to host databases is called a workspace. With a simple click of a button or API call, you will be able to fail over to the secondary region while preserving all data, workspace topology, users and permissions. This further enhances the resilience of your applications and data in the face of unexpected regional disruptions.

Point-in-Time Recovery (PITR)

Another exciting feature SingleStore supports to help protect your mission-critical workloads is Point-in-Time Recovery. PITR ensures you can always recover your data from unexpected writes or data corruption by restoring your database to any point in time within a predefined window. For example, let's say you have an ETL job that runs nightly and a bug in the job corrupts multiple records in a table. If you know the timeframe when the ETL job started, you can simply detach the database and reattach it at a point in time before the job began to recover the data.

PITR is also very fast, since not all data must be restored to resume query processing. It works by first reconstructing the latest snapshots taken from a given point in time and then playing forward the corresponding log files up to the desired point in time. To get started, you can use the following commands:

DETACH DATABASE database_name;
ATTACH DATABASE database_name AT TIME 'YYYY-MM-DD HH:MM:SS';

For more information about PITR, please visit our documentation.

Backup and restore

SingleStore offers strong backup and restore options to safeguard your data. While SingleStore continuously copies data to an object store as part of bottomless databases, customers often choose to back up to an external location to keep a second copy of their data. This helps meet compliance requirements and allows for long-term storage. Self-managed customers using SingleStore on bare metal also rely on backups to recover from hardware or software failures.

SingleStore supports various backup targets including local filesystems, network filesystems and public and private cloud storage services. When you create a database backup, it contains all objects and data in the database including tables, views, procedures, pipelines, functions and stored procedures. Full backups include all row-store in-memory data, as well as columnstore blobs.

To minimize data movement, SingleStore provides incremental backups for columnstore blobs. This allows backups of only the data that changed from the previous full or incremental backup, reducing the average cost of backups.

We use scale-out and concurrency to get fast backups. To give you control of this concurrency, we recently introduced a new global variable called backup_multipart_upload_concurrency. This feature segments the backup into multiple parts and uploads them concurrently, resulting in a backup process that's more than 3x faster.

To learn more about SingleStore backup and restore, please visit our documentation.

Conclusion

This concludes our two-part series on how you can use SingleStore for your transactional applications. Our customers use these transactional and fault-tolerance features to solve a variety of their use cases. For example, one of our financial customers uses a combination of multi-statement transactions and multi-version concurrency control to process multiple financial transactions at the same time with confidence, ensuring data consistency is maintained. Similarly, our customers from network security, digital marketing, eCommerce and other sectors regularly tap into our fault-tolerance capabilities to guarantee continuous application availability.

Thank you for following along on our journey. We hope we've conveyed the depth and breadth of the technology in SingleStore that can support mission-critical workloads with high performance, availability and data integrity. SingleStore's unique value is its ability to handle both your transactional OLTP applications and OLAP real-time use cases. You can get started with SingleStore for free here. And, check out this simple step-by-step guide to create your first SingleStore database.

Finally, explore the SingleStore Training page for self-paced courses and SingleStore Resources for great demos and developer projects.

Product