Транзакции | Введение | ACID | CAP | Обработка ошибок

Hello everyone. We're on the Simple Code channel. We're exploring databases and video transaction topics. A transaction is a way to group multiple database requests into a single logical unit. A transaction is an atomic unit, meaning it can either complete successfully with a commit, committing all changes, or fail completely, aborting and rolling back all changes.

Let's look at transactions using an example of online store data. Imagine a warehouse database consists of three tables: a product table, an order table, and an order position table. A user wants to order two items. To save this order in the database, we need to add one record to the order table, two records to the order position table, and update two records in the product table to change the inventory balance available for other orders. What happens if all these requests aren't grouped into a single transaction? That is, each request, taken individually, can either succeed or fail. For example, the first four queries are successful, but the fifth fails. This could be due to a lost network connection to the database, for example. In this case, the balance for one of the products in the warehouse won't be updated. Then another user tries to order this product and can also place an order.

The database will then have two orders for the product, although there's only one unit in stock. This problem can occur even if only one user is accessing the warehouse at a time.

However, if multiple users are accessing the data concurrently, more complex cases of incorrect concurrent data updates arise. For example, Masha and Katya are interested in the same product. This product's ID. The quantity in stock is one unit. First, Masha queries the quantity and receives a DBMS response of amount 1. Then Katya makes the same query and also receives a response of amount 1. Masha creates an order based on her response, and Katya also creates an order based on her response. They are unaware of each other and unaware that they are ordering the same unit. As a result, two orders for a product appear in the data warehouse, although there's only one item in stock. Once again, we see incorrect, inconsistent data. The data is invalid. All causes of invalid data can be divided into two broad classes.

The first class is errors in executing a set of queries by a single user. This occurs when a user performs complex data modifications, locks data in multiple tables or multiple rows of a single table, executes multiple queries, and some of the queries fail for unrelated reasons. What could these reasons be? For example, a database server restart, a network connection interruption, a client-side application crash, or a query failing due to integrity constraints. All such problems are usually easily remedied simply by using transaction atomicity.

Another, more complex class of problems is concurrent data access issues, which occur when different users update data inconsistently in parallel. In such cases, one user may read another user's incomplete data, or different users may simultaneously update the same record and overwrite each other's updates. One user's writes can influence another user's read results. This will leave the other user with the impression of an unpredictable, inconsistent system state. Solving this class of concurrent access problems is significantly simplified by choosing the right transaction isolation level. Transactions simplify programming for working with data, but they aren't always necessary. Sometimes, it's worth forgoing transactional reliability guarantees to achieve greater scalability and availability. Data warehouses can be broadly divided into two categories: transactional warehouses, such as MySQL or PostgreSQL, and non-transactional warehouses, such as Mongo or Cassandra. Transactional databases are SQL databases, relational databases. The SQL standard describes all possible transaction isolation levels, and each relational database supports some subset of this set of levels. Transactional databases are also called ACID databases. The acronym ACID stands for the first letters of four terms. The letter A, for atomicity, means that if several data changes are grouped into a single transaction, only two results are possible after their execution. The first result: the transaction completed successfully. Then all changes are applied to the data, and the storage is updated. The second result: the transaction ended with an abort or rollback, in which case no changes were saved to the storage. Letter C. Consistency, data integrity. Transactional databases contain tools for monitoring data integrity. For example, foreign keys for controlling relationships between rows in different tables or unique constraints for column values. However, complete data integrity, from a business perspective, can only be verified by application tools. Complete data validation requires very complex logic, which is generally impossible to implement in SQL. At the DBMS level, data integrity can only be partially ensured. Letter I - isolation. The isolation property means that a relational database allows you to configure how much one transaction can influence the results of another transaction. The transaction isolation level is configured. The higher the isolation level, the less mutual influence transactions have, but the higher the overhead of supporting transactions. Letter D. Durability, fault tolerance. What does this mean? If a transaction has successfully committed, the changes are committed and committed to storage, then subsequent DBMS failures cannot roll back these changes. This means that if a transaction is committed, failures of subsequent transactions cannot undo the changes made during the committed transactions. Similarly, a database server crash and restoring data from a backup should not corrupt the records saved during the committed transaction.

However, failures that cannot be handled at the DBMS level are, of course, possible. For example, a complete hard drive failure and the destruction of all data. Thus, in the ACID acronym, two properties—atomicity and isolation—are fully implemented at the DBMS level, while the other two properties—integrity and fault tolerance—can only be partially implemented at the DBMS level. Another large category of data storage systems are non-transactional, also known as NoSQL data storage systems. Typically, these are distributed storage systems designed for easy scalability and high availability. They operate in multi-node mode. Data is stored as multiple copies, called replicas, on different nodes. This prevents the failure of one node from losing all data and ensures that replicas of this data stored on other nodes are operational. Furthermore, the simultaneous operation of multiple nodes distributed with a database management system allows queries to be distributed across these nodes, increasing availability and system performance. NoSQL data warehouses are described by the CAP theorem. CAP represents the first letters of three terms. The letter C stands for consistency. Consistency in a distributed system means that different nodes storing different replicas of the same data return the same response to the same read request. The letter A stands for availability. Availability means that the distributed system continues to process queries when some nodes fail. If a node fails, queries directed to that node are redirected to other nodes in the system. The letter P stands for partition tolerance. Let's imagine a distributed storage system consisting of four nodes. It's possible that some of the links between the nodes will break, and the cluster of four nodes will split into two segments, each with two nodes. The segments won't see each other, but nodes within a segment will continue to communicate with each other. This situation is called split-brain. It can occur, for example, if our nodes are distributed across different data centers in two regions and the network connection

However, if multiple users are accessing the data concurrently, more complex cases of incorrect concurrent data updates arise. For example, Masha and Katya are interested in the same product. This product's ID. The quantity in stock is one unit. First,

Masha queries the quantity and receives a DBMS response of mount 1. Then Katya makes the same query and also receives a response of mount 1. Masha creates an order based on her response, and Katya also creates an order based on her

response. They are unaware of each other and unaware that they are ordering the same unit. As a result, two orders for a product appear in the data warehouse, although there's only one item in stock. Once again, we see

incorrect, inconsistent data. The data is invalid. All causes of invalid data can be divided into two broad classes.

The first class is errors in executing a set of queries by a single user. This occurs when a user performs complex data modifications, locks data in multiple tables or multiple rows of a single table, executes multiple queries , and some of the queries fail for

unrelated reasons. What could these reasons be? For example, a database server restart, a network connection interruption, a client-side application crash, or a query failing due to integrity constraints. All such problems are usually easily remedied simply by using transaction atomicity.

Another, more complex class of problems is concurrent data access issues, which occur when different users update data inconsistently in parallel. In such cases, one user may read another user's incomplete data, or different users may simultaneously update the same record

and overwrite each other's updates. One user's writes can influence another user's read results. This will leave the other user with the impression of an unpredictable, inconsistent system state. Solving this class of concurrent access problems is significantly simplified by choosing the right

transaction isolation level. Transactions simplify programming for working with data, but they aren't always necessary. Sometimes, it's worth forgoing transactional reliability guarantees to achieve greater scalability and availability. Data warehouses can be broadly divided into two categories: transactional warehouses, such as MySQL or Pozerc,

and non-transactional warehouses, such as Monb or Candra. Transactional databases are SQL databases, relational databases. The SQL standard describes all possible transaction isolation levels, and each relational database supports some subset of this set of levels. Transactional databases are also called AC databases. The acronym

AIT stands for the first letters of four terms. The letter A, for atomicity , means that if several data changes are grouped into a single transaction, only two results are possible after their execution. The first result: the transaction completed successfully.

Then all changes are applied to the data, and the storage is updated. The second result: the transaction ended with an abort or rollback, in which case no changes were saved to the storage. Letter C. Consistency, data integrity. Transactional databases

contain tools for monitoring data integrity. For example, per-keys for controlling relationships between rows in different tables or unique constraints for column values. However, complete data integrity, from a business perspective, can only be verified by application tools. Complete data validation requires very complex

logic, which is generally impossible to implement in SQL. At the DBMS level, data integrity can only be partially ensured. Letter I-isolation. The isolation property means that a relational database allows you to configure how much one transaction can influence the results of another

transaction. The transaction isolation level is configured. The higher the isolation level, the less mutual influence transactions have, but the higher the overhead of supporting transactions. Letter D. Durability, fault tolerance. What does this mean?

If a transaction has successfully committed, the changes are committed and committed to storage, then subsequent DBMS failures cannot roll back these changes. This means that if a transaction is committed, failures of subsequent transactions cannot undo the changes made during the committed

transactions. Similarly, a database server crash and restoring data from a backup should not corrupt the records saved during the committed transaction.

However, failures that cannot be handled at the DBMS level are, of course, possible. For example, a complete hard drive failure and the destruction of all data. Thus, in the AIT acronym, two properties—atomicity and isolation—are fully implemented at the DBMS level, while the other two

properties—integrity and fault tolerance—can only be partially implemented at the DBMS level. Another large category of data storage systems are non-transactional, also known as NoQuL data storage systems. Typically, these are distributed storage systems designed for easy scalability and high

availability. They operate in multi-node mode. Data is stored as multiple copies, called replicas, on different nodes. This prevents the failure of one node from losing all data and ensures that replicas of this data stored on other nodes are

operational. Furthermore, the simultaneous operation of multiple nodes distributed with a database management system allows queries to be distributed across these nodes, increasing availability and system performance. No-SQL data warehouses are described by the C theorem. Cap represents the first letters of three

terms. The letter C stands for consistency. Consistency in a distributed system means that different nodes storing different replicas of the same data return the same response to the same read request. The letter A stands for availability. Availability means that the distributed system

continues to process queries when some nodes fail. If a node fails, queries directed to that node are redirected to other nodes in the system. The letter P stands for partition tolerance. Let's imagine a distributed storage system consisting of four nodes. It's possible

that some of the links between the nodes will break, and the cluster of four nodes will split into two segments , each with two nodes. The segments won't see each other, but nodes within a segment will continue to communicate

with each other. This situation is called splitbrain. It can occur, for example, if our nodes are distributed across different data centers in two regions and the network connection between the regions is lost. Partition tolerance means that in this case, the

segment in each data center continues to service local requests for its region. The CAP theorem states that it's impossible to simultaneously ensure all three properties: consistency, availability, and partition tolerance. For example, if the nodes of a Nesqul database are

distributed across different regions, and we want it to continue working under a split-brain scenario, we'll have to choose between consistency and availability. When a partition occurs, we choose either consistency or availability. If we choose consistency, data replicas in different shards must

provide identical responses to different read requests. In this case, the database becomes unavailable for data update and write requests, because nodes in one shard can process the request, while nodes in another shard will be unaware of the data change. If

we choose availability, we must sacrifice consistency, because nodes in one shard can modify a record, and then nodes in different shards will provide different responses to the same read request. Which DBMS to choose— transactional or NOSQL—depends on the

task at hand. Large, complex systems typically combine the use of multiple storage systems, both AIT and Nocale.

If transactions are required for our task, we should consider in advance how the application will behave in the event of an interruption or a transaction execution error. Sometimes a good strategy is to retry the transaction. This strategy works well

if errors in interaction with the database server are temporary and will resolve themselves over time. For example, a client sends a request to the DB server and receives a response.

The client then sends another request, but the database server is rebooting, causing it to be unavailable for a while and not responding to any requests. The client waits for a response to its request for the duration of the request timeout and

receives no response. The client then retry its transaction, sends the request again, but the server has recovered from the reboot, and the client receives a successful transaction commit response. A server reboot is a temporary occurrence. Such cases are easily handled by retrying

the request. However, if the server is unresponsive because it is overloaded with requests and simply cannot process them all, then immediately retrying the transaction can worsen the server overload and further slow its performance. In such a situation, the

optimal solution is to wait a while before retrying the request. A good practice is to implement a wait strategy. If we wait for a while, retry the request, and the server still doesn't respond, then we wait longer the next time, then even longer, and

then increase the wait time to a certain maximum value. When the server starts responding to requests again, we stop waiting and execute transactions at the normal rate. Another pitfall of retrying: possible data duplication.

Example: A client creates a new order on the BD server. The server creates a record and assigns it an ID of 45. The client commits the changes. The server applies the changes to the database. It sends a response to the client, but

network problems arise in communicating with the client. The client does not receive a report of a successful commit from the server within the request timeout. The client thinks the transaction has not completed. The client retries the transaction, again

sending a request to create an order to the server. The server creates a new order, assigns it a new ID of 46. The client sends a commit request to the server and receives a report of a successful transaction commit from the

server. The client thinks everything went well, but in fact, two orders were created in the data warehouse instead of one. The client simply did not receive a response from the server about the creation of the first order.

To avoid data duplication in such situations, you can create and store an order ID on the client side. For example, a client assigns an ID of 62 to a new order and sends a command to the server to create the order. The

server responds that the record has been created. The client then sends a commit command to the server. The server commits the changes to the repository, but due to network issues, the client doesn't receive a commit report from the server indicating that

the commit was successful. The client waits for a while for a response, but it never receives one within the request timeout. Then the client sends another request to the server to create an order with the same ID of 62,

because the server assumes the order hasn't been created previously. The server assumes the transaction has already failed. The second time, the server returns an error stating that an order with the same ID already exists.

The second transaction is aborted. In this case, it appears to the client that neither transaction has been completed. However, in the database, one order has actually been correctly created, as expected. In this case, it's worth processing the server's

error code in the client application. The error code can then be used to determine whether the order was actually created last time. The user's page will refresh, the application will query the database for the order, and the user will see that their order has

indeed been created when the order created in the first transaction is downloaded from the server. A good practice for choosing an identifier is to generate a unique ID. This allows clients to avoid a collision, where two clients accidentally assign the same ID

to a new entity. Another possible error handling strategy is to display a message to the user. This strategy is suitable when the cause of the error is some kind of data integrity violation due to user action. For example, a user

is filling out a product catalog and attempts to save a product with a name that already exists in the data warehouse. In this case, the application can analyze the error code from the database server and inform the user which integrity constraint is

violated, prompting the user to correct their entry. However, more complex situations are possible, where the integrity violation is not the user's fault at all. Furthermore, the user is not necessarily the initiator of the transaction. The transaction can be

initiated by some external software system or by a request from an external system. In this case, it's worth sending a signal to the monitoring system and designing and thinking through debug logs during the application development phase. This way

, when the maintenance team analyzes the signal received by the monitoring system, they can use the logs to understand what data the transaction was executed on, what queries were executed against that data, and why the transaction failed. In the next video,

we'll look at the different transaction isolation levels. Thanks for watching. Keep coding, and see you in the next video.