Nov 1, 2017 One of the prevailing conversations around database systems lately revolves around ACID support. But not everybody knows what is meant by the term ACID. Oh, perhaps they know that it involves how data integrity is maintained or that it impacts locking. And, at a high level many folks know that relational/SQL systems support ACID whereas that is not always the case for NoSQL database systems. So, with this in mind, let’s examine what is meant
by ACID. Firstly, ACID is an acronym for atomicity, consistency, isolation, and durability. Each of these four qualities contribute to the ability of a transaction to ensure data integrity. Atomicity means that a transaction must exhibit an “all or nothing” behavior. Either all of the instructions within the transaction happen successfully, or none of them happen. Atomicity preserves the “completeness” of the business process. Consistency refers to
the state of the data both before and after the transaction is executed. A transaction maintains the consistency of the state of the data. In other words, after running a transaction, all data in the database is “correct.” Isolation means that transactions can run at the same time. Any transactions running in parallel have the illusion that there is no concurrency. In other words, it appears that the system is running only a single transaction at a time. No other concurrent
transaction has visibility to the uncommitted database modifications made by any other transactions. To achieve isolation, a locking mechanism is required. Durability refers to the impact of an outage or a failure on a running transaction. A durable transaction will not impact the state of data if the transaction ends abnormally. In other words, the data survives any failures. LockingAn ExampleLet’s use an example to better understand the importance of ACID transactions to database applications. Consider a banking application. Assume that you wish to withdraw $50 from your account with Mega Bank. This “business process” requires a transaction to be executed. You request the money either in person by handing a slip to a bank teller or by using an ATM (Automated Teller Machine). When the bank receives the request, it performs the following tasks, which make up the complete business process. The bank will
The transaction that is run to perform the withdrawal must complete all of these steps, or none of these steps, or else one of the parties in the transaction will be dissatisfied. If the bank debits your account but does not give you your money, then you will not be satisfied. If the bank gives you the money but does not debit the account, the bank will be unhappy. Only the completion of every one of these steps results in a “complete business process.” Database developers therefore must understand the entire business process and then design transactions that ensure ACID properties. To summarize, a transaction—when executed alone, on a consistent database—will either complete, producing correct results, or terminate, with no effect. In either case the resulting condition of the database will be a consistent state. BASE vs. ACIDMany NoSQL database systems forgo ACID compliance in favor of an alternative approach known as BASE. BASE stands for Basically Available, Soft State with Eventual Consistency. Basically Available means that there is no guarantee of any specific piece of data being available, but the system will respond to any request. In a Soft State system changes are constantly happening. But the data you retrieve at a given point in time may eventually get over-written by more recent data. Eventual Consistency means that there will be times when the database is in an inconsistent state. When multiple copies of the data reside on separate servers, an update may not be immediately made to all copies simultaneously. So the data is inconsistent for a time, but the database replication mechanism will eventually update all of the copies of the data to be consistent. Some applications can tolerate inconsistent data, whereas others cannot. SummaryKeep in mind what ACID means and what the alternative means in terms of data integrity for your applications and business requirements. Only by being informed and implementing the appropriate technology for your needs will you be able to succeed with your database and data management systems. DBMS is the management of data that should remain integrated when any changes are done in it. It is because if the integrity of the data is affected, whole data will get disturbed and corrupted. Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties. The ACID properties are meant for the transaction that goes through a different group of tasks, and there we come to see the role of the ACID properties. In this section, we will learn and understand about the ACID properties. We will learn what these properties stand for and what does each property is used for. We will also understand the ACID properties with the help of some examples. ACID PropertiesThe expansion of the term ACID defines for:  1) AtomicityThe term atomicity defines that the data remains atomic. It means if any operation is performed on the data, either it should be performed or executed completely or should not be executed at all. It further means that the operation should not break in between or execute partially. In the case of executing operations on the transaction, the operation should be completely executed and not partially. Example: If Remo has account A having $30 in his account from which he wishes to send $10 to Sheero's account, which is B. In account B, a sum of $ 100 is already present. When $10 will be transferred to account B, the sum will become $110. Now, there will be two operations that will take place. One is the amount of $10 that Remo wants to transfer will be debited from his account A, and the same amount will get credited to account B, i.e., into Sheero's account. Now, what happens - the first operation of debit executes successfully, but the credit operation, however, fails. Thus, in Remo's account A, the value becomes $20, and to that of Sheero's account, it remains $100 as it was previously present. In the above diagram, it can be seen that after crediting $10, the amount is still $100 in account B. So, it is not an atomic transaction. The below image shows that both debit and credit operations are done successfully. Thus the transaction is atomic. Thus, when the amount loses atomicity, then in the bank systems, this becomes a huge issue, and so the atomicity is the main focus in the bank systems. 2) ConsistencyThe word consistency means that the value should remain preserved always. In DBMS, the integrity of the data should be maintained, which means if a change in the database is made, it should remain preserved always. In the case of transactions, the integrity of the data is very essential so that the database remains consistent before and after the transaction. The data should always be correct. Example: In the above figure, there are three accounts, A, B, and C, where A is making a transaction T one by one to both B & C. There are two operations that take place, i.e., Debit and Credit. Account A firstly debits $50 to account B, and the amount in account A is read $300 by B before the transaction. After the successful transaction T, the available amount in B becomes $150. Now, A debits $20 to account C, and that time, the value read by C is $250 (that is correct as a debit of $50 has been successfully done to B). The debit and credit operation from account A to C has been done successfully. We can see that the transaction is done successfully, and the value is also read correctly. Thus, the data is consistent. In case the value read by B and C is $300, which means that data is inconsistent because when the debit operation executes, it will not be consistent. 3) IsolationThe term 'isolation' means separation. In DBMS, Isolation is the property of a database where no data should affect the other one and may occur concurrently. In short, the operation on one database should begin when the operation on the first database gets complete. It means if two operations are being performed on two different databases, they may not affect the value of one another. In the case of transactions, when two or more transactions occur simultaneously, the consistency should remain maintained. Any changes that occur in any particular transaction will not be seen by other transactions until the change is not committed in the memory. Example: If two operations are concurrently running on two different accounts, then the value of both accounts should not get affected. The value should remain persistent. As you can see in the below diagram, account A is making T1 and T2 transactions to account B and C, but both are executing independently without affecting each other. It is known as Isolation. 4) DurabilityDurability ensures the permanency of something. In DBMS, the term durability ensures that the data after the successful execution of the operation becomes permanent in the database. The durability of the data should be so perfect that even if the system fails or leads to a crash, the database still survives. However, if gets lost, it becomes the responsibility of the recovery manager for ensuring the durability of the database. For committing the values, the COMMIT command must be used every time we make changes. Therefore, the ACID property of DBMS plays a vital role in maintaining the consistency and availability of data in the database. Thus, it was a precise introduction of ACID properties in DBMS. We have discussed these properties in the transaction section also. What properties of SQL databases help ensure the integrity of data in the database?Properties (atomicity, consistency, isolation, durability) that guarantee relational database transactions are processed reliably and ensure the integrity of data in the database.
Which property ensures that the database?Consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
What is data integrity in SQL?Data integrity refers to the overall accuracy, completeness, and reliability of data. It can be specified by the lack of variation between two instances or consecutive updates of a record, indicating that your information is error-free.
How data integrity is maintained in a database?Data integrity is imposed within a database when it is designed and is authenticated through the ongoing use of error checking and validation routines. As a simple example, to maintain data integrity numeric columns/cells should not accept alphabetic data.
|
Which properties of SQL databases help ensure the integrity of data in the database?
zusammenhängende Posts
Urheberrechte © © 2024 ihoctot Inc.
