{"id":103976,"date":"2024-10-18T08:27:00","date_gmt":"2024-10-18T08:27:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=103976"},"modified":"2024-09-25T20:40:55","modified_gmt":"2024-09-25T20:40:55","slug":"consistency-and-concurrency-in-newsql-database-systems","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/cloud\/big-data\/consistency-and-concurrency-in-newsql-database-systems\/","title":{"rendered":"Consistency and Concurrency in NewSQL Database Systems"},"content":{"rendered":"\n

Companies today require database systems that are reliable and capable of efficiently handling large volumes of data and numerous transactions. Traditional relational databases, once the foundation of data management, often struggle to meet these modern demands, leading to delays and program slowdowns. In response, NewSQL<\/a> databases, a new class of SQL<\/a> systems, has emerged. These databases combine the best features of contemporary NoSQL<\/a> systems and traditional SQL databases, aiming to deliver the scalability and performance associated with NoSQL while maintaining the reliability and consistency of SQL.<\/p>\n\n\n\n

NewSQL is a term that describes a new class of databases that aim to deliver the same reliable and predictable performance as traditional SQL databases but with the scalability needed for modern applications. These databases are designed to handle large-scale, high-transaction environments, making them ideal for applications that require both high availability and high performance.<\/p>\n\n\n\n

This means they can spread their workload across multiple servers without sacrificing the benefits of the structured query language (SQL) and transactional integrity. When you make a transaction in a database, whether it’s transferring money between bank accounts or updating a user profile, you want to ensure that the transaction is completed reliably. This is where ACID properties come in. ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These properties are essential for ensuring that database transactions are processed reliably, and that the database stays in a correct and consistent state even in the face of errors, crashes, or other issues.<\/p>\n\n\n\n

Maintaining consistency in distributed environments, where data is spread across multiple servers or data centers is challenging. In such environments, ensuring that all copies of the data remain in sync and reflect the same state at any given time is difficult due to various factors like network latency, server failures, and data replication delays.<\/p>\n\n\n\n

NewSQL databases aim to maintain ACID properties, which are critical for ensuring data integrity, while also scaling out across distributed systems, but there are limitations. One of the main challenges is defined in the CAP theorem<\/strong><\/a>, which states that a distributed database can only guarantee two out of the following three properties simultaneously: Consistency, Availability, and Partition tolerance. Consistency means that every reader receives the most recent write. Availability means that every request receives a response in a reasonably amount of time, even if some of the data is out of date. Partition tolerance means that the system continues to operate despite network partitions. In practice, this means that achieving strong consistency often requires sacrificing some availability or partition tolerance.<\/p>\n\n\n\n

Another challenge is network latency and speed, including the time it takes data to be processed and the time it takes for data to travel across the network. In a distributed system, data must often be synchronized across multiple servers, which can introduce delays. These delays can lead to temporary inconsistencies where different servers have different versions of the data.<\/p>\n\n\n\n

Network partitions, where communication between servers is temporarily broken, also pose a challenge. During a partition, some servers may not be able to communicate with others, leading to inconsistent states. Once the partition is resolved, the system must reconcile these differences and ensure all servers are brought back to a consistent state.<\/p>\n\n\n\n

<\/a>Understanding ACID Properties of Transactions<\/h2>\n\n\n\n

Ensuring these databases work reliably is important, and this is where the ACID properties come in. These properties ensure that database transactions are processed in a reliable and predictable manner. In this section, I will explain each of these properties to help you understand why they are important and how they work.<\/p>\n\n\n\n

<\/a>Atomicity<\/h3>\n\n\n\n

Atomicity in database terms means that a transaction is an indivisible unit. All steps are either completed in full or not at all. If any part of the transaction fails, the entire transaction fails, and the database is left unchanged. Atomicity prevents partial updates, which could lead to inconsistencies. Even single SQL statements that change just one column in one row will have multiple steps to change and log rows for recovery.<\/p>\n\n\n\n

Imagine you are transferring money from one bank account to another. This transaction involves two operations: deducting money from one account and adding it to another. If the transaction only completes the first operation (deducting money) but fails to complete the second (adding money), the money will disappear. Atomicity ensures that this doesn\u2019t happen.<\/p>\n\n\n\n

For example, let’s say we have a database of bank accounts, and we want to transfer $200 from Account A to Account B.<\/p>\n\n\n\n

BEGIN TRANSACTION;\n\nUPDATE accounts\nSET balance = balance - 200\nWHERE account_id = 'A';\n\nUPDATE accounts\nSET balance = balance + 200\nWHERE account_id = 'B';\n\nCOMMIT;<\/pre>\n\n\n\n
In this example, the transaction starts with:<\/p>\n\n\n\n
BEGIN TRANSACTION. \n\nUPDATE accounts \nSET balance = balance - 200 \nWHERE account_id = \u2018A\u2019;<\/pre>\n\n\n\n
Which deducts $200 from Account A. while:<\/p>\n\n\n\n
UPDATE accounts \nSET balance = balance - 100 \nWHERE account_id = \u2018B\u2019;<\/pre>\n\n\n\n
Adds $200 to Account B. If any part of this transaction fails:<\/p>\n\n\n\n
COMMIT<\/pre>\n\n\n\n
Will not be executed; it finalizes the transaction, and the database will roll back to its previous state, ensuring atomicity. Note that you may need additional error handling, depending on the client tools that are executing these statements.<\/p>\n\n\n\n
<\/a>Consistency<\/h3>\n\n\n\n
Consistency ensures that a transaction brings the database from one valid state to another, adhering to all predefined rules, such as constraints, cascades, and triggers. This means that any transaction will leave the database in a valid state. Consistency is important for maintaining the integrity of the database. Consistency prevents data from becoming corrupt or illogical. Sadly, it only keeps it as consistent as the rules you implement!<\/p>\n\n\n\n
For instance, if a database has a rule that the balance of any bank account cannot be negative, a transaction that would result in a negative balance should be rejected.<\/p>\n\n\n\n
Continuing with our bank example, let’s enforce a rule that no account can have a negative balance.<\/p>\n\n\n\n
BEGIN TRANSACTION;\n\n-- Deduct $200 from Account A\nUPDATE accounts\nSET balance = balance - 200\nWHERE account_id = 'A';\n\n-- Check if Account A's balance is still \n-- non-negative after the deduction\nIF (SELECT balance FROM accounts WHERE account_id = 'A') >= 0 THEN\n\n    -- If the balance is non-negative, add $200 to Account B\n    UPDATE accounts\n    SET balance = balance + 200\n    WHERE account_id = 'B';\n\n    -- Finalize the transaction and make changes permanent\n    COMMIT;\n\nELSE\n\n    -- If the balance is negative, undo the \n    -- transaction to prevent inconsistency\n    ROLLBACK;\n\nEND IF;<\/pre>\n\n\n\n
In the example above, the transaction begins with the BEGIN TRANSACTION<\/code> command, which marks the start of a series of operations that need to be treated as a single unit. The first operation is:<\/p>\n\n\n\n
UPDATE accounts \nSET balance = balance - 200 \nWHERE account_id = 'A';<\/pre>\n\n\n\n
Which deducts $200 from Account A. After this deduction, the transaction checks if the balance of Account A remains non-negative with the condition<\/p>\n\n\n\n
IF (SELECT balance FROM accounts WHERE account_id = 'A') >= 0.<\/pre>\n\n\n\n
If this condition is true, meaning Account A still has a non-negative balance, the subsequent operation<\/p>\n\n\n\n
UPDATE accounts \nSET balance = balance + 200 \nWHERE account_id = 'B';<\/pre>\n\n\n\n
is performed, adding $200 to Account B, and the transaction is finalized with COMMIT<\/code>.<\/p>\n\n\n\n
If the condition is false, indicating that the balance would be negative. In that case, the transaction is aborted with ROLLBACK<\/code>, undoing any changes made during the transaction and thus maintaining the database’s consistency by preventing an invalid state where an account balance is negative. However, it\u2019s important to note that under certain isolation levels, there is no guarantee that Account A’s balance will remain non-negative by the time the second UPDATE<\/code> occurs. This is because other concurrent transactions could modify the balance of Account A after the initial check but before the UPDATE<\/code>.<\/p>\n\n\n\n
<\/a>Isolation<\/h3>\n\n\n\n
Isolation actively shields concurrent transactions from interfering with each other. This ensures each transaction runs as if it’s the only one happening, even when multiple transactions occur simultaneously. Without isolation, concurrent transactions could cause inconsistencies. For example, if two people simultaneously attempt to withdraw money from the same bank account, both transactions might read the same initial balance, leading to an overdraft. Isolation protects against such issues by managing how transactions interact, allowing you to fine-tune the level of interference permitted between them. This fine-tuning is important for maintaining data consistency and integrity in systems where multiple transactions occur simultaneously.<\/p>\n\n\n\n
Consider two transactions attempting to withdraw $200 from Account A simultaneously.<\/p>\n\n\n\n
-- Transaction 1\nBEGIN TRANSACTION;\n\n-- Lock the row for Account A to prevent other \n-- transactions from modifying it until this \n-- transaction completes.\nSELECT balance \nFROM accounts \nWHERE account_id = 'A' FOR UPDATE;\n\n-- Deduct $200 from Account A\nUPDATE accounts\nSET balance = balance - 200\nWHERE account_id = 'A';\n\n-- Finalize the transaction and make changes permanent\nCOMMIT;\n\n\n-- Transaction 2 (a different connection\nBEGIN TRANSACTION;\n\n-- Attempt to lock the same row for Account A. \n-- This will wait if Transaction 1 hasn\u2019t finished yet.\nSELECT balance FROM accounts WHERE account_id = 'A' FOR UPDATE;\n\n-- Deduct $200 from Account A after the lock is acquired\nUPDATE accounts\nSET balance = balance - 200\nWHERE account_id = 'A';\n\n-- Finalize the transaction and make changes permanent\nCOMMIT;<\/pre>\n\n\n\n
In this example, the process begins with initiating each transaction using the BEGIN TRANSACTION<\/code> command. Following this, the database locks the row corresponding to Account A using<\/p>\n\n\n\n
SELECT balance\nFROM accounts\nWHERE account_id = 'A' FOR UPDATE;<\/code><\/pre>\n\n\n\n
This lock ensures that only one transaction at a time can access and modify the balance of Account A, thereby preventing potential conflicts and maintaining isolation between concurrent transactions. Next, the transaction deducts $200 from Account A with:<\/p>\n\n\n\n
UPDATE accounts \nSET balance = balance - 200 \nWHERE account_id = 'A';.<\/pre>\n\n\n\n
Once the deduction is made, the transaction is finalized and committed using COMMIT<\/code>, ensuring that all changes are permanently saved in the database.<\/p>\n\n\n\n
<\/a>Durability<\/h3>\n\n\n\n
Durability guarantees that once a transaction is committed, it will remain so, even in the event of a system crash. This guarantee is typically achieved through logging changes that have been made in the data file. Durability is important for ensuring that data is not lost once a transaction is completed. For instance, once the bank confirms a money transfer, it should be permanent, even if the system crashes immediately after.<\/p>\n\n\n\n
Of course, this only works if the crashed system does not lose its data and log drives simultaneously. Then, you need to have backups to go back to a point in time, and you likely lose very recent transactions.<\/p>\n\n\n\n
Let’s discuss how the engine ensures that our bank transactions are durable.<\/p>\n\n\n\n
-- Start the transaction\nBEGIN TRANSACTION;\n\n-- Deduct $200 from Account A\nUPDATE accounts\nSET balance = balance - 200\nWHERE account_id = 'A';\n\n-- Add $200 to Account B\nUPDATE accounts\nSET balance = balance + 200\nWHERE account_id = 'B';\n\n-- Commit the transaction to make changes permanent\nCOMMIT;<\/pre>\n\n\n\n
Here, BEGIN TRANSACTION<\/code> starts a new transaction, signalling that all following actions should be treated as part of this single transaction. The code continues as:<\/p>\n\n\n\n
UPDATE accounts \nSET balance = balance - 200 \nWHERE account_id = \u2018A\u2019<\/pre>\n\n\n\n
The statement deducts $200 from Account A, and then:<\/p>\n\n\n\n
UPDATE accounts \nSET balance = balance + 200 \nWHERE account_id = \u2018B\u2019<\/pre>\n\n\n\n
Will add $200 to Account B. These changes are temporarily staged and will not be visible to others until COMMIT is executed, which finalizes the transaction and makes all changes permanent. Internally, databases use Write-Ahead Logging (WAL<\/a>) to ensure durability. WAL involves writing changes to a log file before updating the actual database files. This log is stored on durable storage, allowing the database to recover and apply committed transactions even if a failure occurs. In SQL Server, for example, this process is handled automatically by the system, so users do not need to manually insert log records. This mechanism ensures that all changes made in a transaction are preserved and will survive system failures.<\/p>\n\n\n\n
<\/a>NewSQL Architecture<\/h2>\n\n\n\n
In this section, let’s look at the key components of NewSQL architecture.<\/p>\n\n\n\n
<\/a>Key components of NewSQL architecture<\/h3>\n\n\n\n
NewSQL databases are built on several core components that enable them to handle large-scale, high-performance workloads while maintaining ACID properties. Here are the key components:<\/p>\n\n\n\n
Distributed Data Storage<\/strong>: One of the fundamental aspects of NewSQL databases is distributed data storage. Instead of storing all data on a single server, NewSQL databases distribute data across multiple nodes or servers. This distribution allows the system to balance the load and scale horizontally, meaning more servers can be added to handle increasing amounts of data and traffic.<\/p>\n\n\n\n
Automatic Sharding<\/strong>: Sharding is the process of splitting a database into smaller, more manageable pieces called shards. NewSQL databases automatically handle sharding, distributing data across nodes to optimize performance and storage.<\/p>\n\n\n\n
Distributed Transaction Management<\/strong>: NewSQL databases handle transactions in a distributed manner while maintaining ACID properties. They often use sophisticated algorithms like Paxos or Raft<\/a> for distributed consensus, ensuring that all nodes agree on the transaction’s outcome before it is committed.<\/p>\n\n\n\n
Concurrency Control<\/strong>: To manage simultaneous transactions, NewSQL databases employ advanced concurrency control mechanisms. Techniques like optimistic concurrency control and multi-version concurrency control (MVCC<\/a>) helps prevent conflicts and ensure that transactions are processed smoothly without interfering with each other.<\/p>\n\n\n\n
Replication<\/strong>: Replication is crucial for high availability and fault tolerance. NewSQL databases replicate data across multiple nodes so that if one node fails, others can continue to provide access to the data. This replication can be synchronous or asynchronous, depending on the specific needs of the application.<\/p>\n\n\n\n
<\/a>Examples of NewSQL Database Systems<\/h3>\n\n\n\n
Two examples of NewSQL databases are Google Spanner and CockroachDB. Let’s look at each of these in detail.<\/p>\n\n\n\n
<\/a>Google Spanner<\/h3>\n\n\n\n
Google Spanner<\/a> is a globally distributed NewSQL database that provides strong consistency and horizontal scalability. It uses Google’s TrueTime API<\/a> to achieve external consistency, which ensures that transactions are consistently ordered across distributed nodes. This makes it possible to have a single, globally consistent view of the data.<\/p>\n\n\n\n
Key Features<\/strong><\/p>\n\n\n
\n