Data has become the lifeblood of modern businesses - data on sales, data on customers, data on competitors, and data on partners are all critical to leverage. The more accurate and reliable data you have, the better your business will be at making key decisions. So then how do companies store and manage data? How do they ensure that the data they rely on is well... reliable?
Most businesses store their data in databases (although data warehouses and data lakes are becoming more common). In many cases, these are relational databases that use SQL to query and report on the data - altho noSQL databases also exist. Because these databases are so important, the mechanism of storage and how changes are made to the database are also important to understand. To manage such large volumes of information and handle frequent changes, and to protect against errors, power failures and other mishaps, best practices were developed for database transactions.
ACID is a best practices set of properties for database transactions, to ensure the dependability of a database.
What is ACID
The ACID acronym means
• Atomic: all database changes that have some relationship happen together, or none of them happen at all
• Consistent: all database changes must conform to established software and business rules
• Isolated: when a database handles two or more concurrent, simultaneous data changes, the end state of the database must match the end state as if those changes had occurred sequentially)
• Durable: the database resource must preserve all changes to the data hosted in that resource)
and we'll examine each of these properties, in more detail.
Why ACID compliance is important
You want your databases to be ACID compliant because you want the data to be reliable, consistent, and without errors. This benefits both the business itself and customers or partners that it works with. ACID compliant databases:
- Never lose customer data
- Never randomly create incorrect data
- Operate with total consistency, predictability, and reliability
Businesses can have confidence in the decisions that they make, experience less customer issues due to data, and operate much more smoothly with reliable databases.
What are ACID properties in SQL?
We'll define a SQL database transaction as one or more database operations that happen as a single combined unit. Within an ACID compliant database, all operations within a transaction happen together, or none of those operations happen at all.
What is the function of ACID transaction properties in SQL?
The ACID transaction properties of a SQL database guarantee the consistency, predictability, and reliability of its operations.
ACID and Normalization for SQL Databases
As a concept, database "normalization" has great depth and many aspects. Here, however, a normalized database application eliminates redundant data in the data resources that it hosts. The ACID state of a database depends on many factors, not just normalization. However, the more we can normalize a database, the closer we can move it to that fully ACID state, because of the importance of normalization.
SQL database normalization supports ACID compliance, because database developers, and the database tool itself, won't need to cover the same operations on redundant data.
Otherwise, the tools and applications would become avoidably more complex and more expensive to build, operate, and maintain. If data change operations (create / update / delete) must cover multiple instances of data that represent the same information, the database tool and applications must cover ACID requirements across all of that data. The avoidable complexities, dependencies, and expenses would explode.
ACID SQL Examples
We'll cover examples of each aspect of ACID DBs in this section.
What is Atomicity in SQL?
For a database transaction, SQL atomicity groups all transaction operations together. Every operation in that group happens, or none of them happen at all.
For a banking database application, a transfer of funds between customer accounts must debit the source account, AND credit the target account, in equal amounts. If both of these related operations do not happen as a group, then neither operation must happen.
What is Consistency in SQL?
SQL consistency guarantees that all SQL transaction operations must follow all the rules of the database tool, the supporting database applications, and all defined business rules that drive those database applications.
As an example, a banking database application must guarantee that a debit operation in one customer account will have a matching credit operation, with a matching balance, in another customer account.
What is Isolation in SQL?
SQL isolation guarantees that all concurrent transaction operations, leave the database in the same state as if those operations had occurred sequentially.
As an example, a payroll application transaction might debit one employer account, and credit one thousand employee accounts. At the end of the transaction, the balances of all accounts - employer and employees - must match the balances as if the component transaction operations had happened sequentially.
What is Durability in SQL?
SQL durability guarantees the permanence of database operations. Database operations will make changes to underlying data that won't disappear.
After a fund transfer between accounts, durability guarantees that the supporting database permanently reflects the account changes made in that transfer. A power failure, equipment failure, software crash, hack, etc. must not change or destroy the data that describes that transfer.
Final Thoughts: ACID SQL
The modern world relies on SQL database tools and technologies for business operations: everything from looking up a customer’s order to generating a sales report relies on data. These databases represent huge business value, and ACID
• Atomic
• Consistent
• Isolated
• Durable
best practices form the reliable foundation for all of that data.
