Databases: Transactions & ACID

Transactions

A database transaction is a unit of work that is performed on a database. A transaction typically involves one or more operations that modify the data in the database, such as inserting, updating, or deleting data. A transaction is considered to be successful if all of the operations are completed successfully, and it is considered to be failed if any of the operations fail or are rolled back.

The concept of a database transaction is important because it allows multiple operations to be treated as a single unit of work. This means that the operations within a transaction are either all completed successfully, or none of them are completed at all. This helps to ensure the integrity and consistency of the data in the database, and it allows for reliable and accurate updates to the database.

Database transactions are typically managed by the database management system (DBMS), and they are used to ensure that the data in the database remains consistent and accurate, even in the face of errors, failures, or concurrency issues. Transactions can be used to implement complex business logic, to manage concurrent access to the database, and to ensure the reliability and integrity of the data in the database.

Why we need database transaction?

1. to provide a reliable and consistent unit of work, for some case of system failure
2. to provide isolation between programs that access the database concurrently

To achieve these 2 goals database transaction must be ACID

ACID

ACID is a set of properties that are used to describe the behavior of a database transaction. The acronym ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties are designed to ensure the integrity and reliability of data in a database, and they are an important concept in database design and management.

Atomicity

The "A" in ACID stands for Atomicity, which is a property of a database transaction that ensures that it is either completed in its entirety, or not completed at all. Atomicity is an important property of a database transaction because it helps to ensure the integrity and consistency of the data in the database.

To illustrate this concept, consider the following example:

Suppose you have a database that stores information about customer orders for a retail business. You want to update the database to reflect a new order that was placed by a customer. In order to do this, you need to perform the following operations:

Insert a new row into the "orders" table, which contains information about the order, such as the customer's name, the items in the order, and the total cost of the order. Update the "customers" table to reflect the new order, by adding the total cost of the order to the customer's total spending. Update the "inventory" table to reflect the items that were included in the order, by reducing the quantities of those items in the inventory. If you were to perform these operations one at a time, there is a risk that the database could become inconsistent if any of the operations fail. For example, if the first operation (inserting a new row into the "orders" table) fails, the database would be left in an inconsistent state, with the "customers" and "inventory" tables reflecting the new order, but the "orders" table not reflecting it.

In order to prevent this kind of inconsistency, you can use a database transaction to ensure that all of the operations are either completed successfully, or not completed at all. A database transaction allows you to group the operations together and treat them as a single unit of work. If any of the operations within the transaction fail, the transaction is automatically rolled back, and the database is restored to its previous state. This ensures that the data in the database remains consistent and accurate, even in the face of errors or failures.

In the example above, you could use a database transaction to perform the three operations that are required to update the database to reflect the new order. The transaction would ensure that all of the operations are either completed successfully, or not completed at all. If any of the operations fail, the transaction would be automatically rolled back, and the database would be restored to its previous state. This would help to ensure the integrity and consistency of the data in the database, and it would prevent any inconsistent or incomplete updates to the database.

Overall, this is an example of how the "A" in ACID (Atomicity) can be used to ensure the integrity and consistency of the data in a database, by ensuring that a database transaction is either completed in its entirety, or not completed at all. Atomicity is an important property of a database transaction, and it is essential for maintaining the reliability and accuracy of the data in a database.

Consistency

The C principle of ACID is focused on ensuring that all data in the database is in a consistent state, meaning that it follows the rules and constraints set by the database system. This is important for maintaining the integrity and reliability of the database, as well as ensuring the accuracy and completeness of the data.

To understand the C principle, it is helpful to consider another example. Imagine a database with a table of products, including columns for the product name, price, and quantity in stock. The C principle would ensure that all product records have the same set of columns, and that any data entered into these columns follows the rules of the database, such as not allowing null values in the name column or negative values in the price or quantity columns.

In this way, the C principle helps to ensure that all data is consistent and follows the rules of the database system, which is essential for maintaining the integrity and reliability of the database. This is important for a wide range of applications, including inventory management and e-commerce.

For example, in an e-commerce database, the C principle would ensure that all data related to orders, payment information, and product availability is consistent and follows the rules set by the database system. This would include ensuring that all orders have the same set of columns, such as order number, date, and total cost, and that any changes to product availability or payment information follow the rules of the database, such as not allowing negative stock levels or invalid payment information.

or

For example, in a bank database, the C principle would ensure that all data related to customer accounts, transactions, and balances is consistent and follows the rules set by the database system. This would include ensuring that all customer accounts have the same set of columns, such as account number, type, and balance, and that any transactions made to or from an account follow the rules of the database, such as not allowing negative balances or duplicate transactions.

Overall, the C principle of ACID is essential for ensuring the reliability and integrity of a database, and is critical for maintaining the accuracy and consistency of the data within it.

Isolation

The I principle of ACID is focused on ensuring that the actions of one user or transaction do not interfere with the actions of another user or transaction. This is important for maintaining the integrity and reliability of the database, as well as ensuring the accuracy and completeness of the data.

To understand the I principle, it is helpful to consider an example. Imagine a database with a table of products, including columns for the product name, price, and quantity in stock. The I principle would ensure that multiple users or transactions can access and update the table simultaneously, without interfering with each other. For example, if one user is adding a new product to the table, the I principle would ensure that another user can view or update existing products without any conflicts or errors.

In this way, the I principle helps to ensure that the database can handle multiple concurrent users or transactions without any interference or errors, which is essential for maintaining the reliability and integrity of the database. This is important for a wide range of applications, including inventory management and e-commerce.

For example, in an inventory management database, the I principle would ensure that multiple users can access and update the database simultaneously, without any conflicts or errors. This would include allowing multiple users to view or update product information, inventory levels, and order details without any interference. This is important for ensuring that the database can handle high levels of traffic and transactions without any errors or delays.

Overall, the I principle of ACID is essential for ensuring the reliability and integrity of a database, and is critical for allowing multiple users or transactions to access and update the database simultaneously without any interference or errors.

Durability

The D principle of ACID is focused on ensuring that any changes made to the database are permanent and will not be lost, even in the event of a power failure or other unexpected interruption. This is important for maintaining the integrity and reliability of the database, as well as ensuring the accuracy and completeness of the data.

the D principle helps to ensure that the database can recover from any unexpected interruptions without losing any data, which is essential for maintaining the reliability and integrity of the database.

For example, in a financial transactions database, the D principle would ensure that any changes made to the database, such as updating account balances or adding new transactions, are permanent and will not be lost, even if the database system experiences a power failure or other unexpected interruption. This is important for ensuring the accuracy and reliability of the financial data, and for maintaining trust with customers.

Overall, the D principle of ACID is essential for ensuring the reliability and integrity of a database, and is critical for ensuring that any changes made to the database are permanent and will not be lost, even in the event of an unexpected interruption.