The MySQL transaction architecture fully satisfies the ACID tests for transaction safety via its InnoDB storage engine. Older table types, such as the MyISAM type, do not support transactions. Transactions in such systems, therefore, can only be implemented through the use of explicit table locks (although this may not be ACID-compliant). The term “ACID” is an acronym, stating four properties that every transactional RDBMS must comply with. To qualify for ACID compliance, an RDBMS must exhibit the following characteristics, as described in the following sections.
Atomicity
Atomicity means that every transaction must be treated as an indivisible unit. Given a transaction consisting of two or more tasks, all the statements within it must be successful for the transaction to be considered successful. In the event of a transaction failure, the system should be returned to its pre-transaction state.
With reference to the previous stock exchange example, atomicity means the sale of shares by Trader A and the purchase of the same by Trader B cannot occur independently of each other, and both must take place for the transaction to be considered complete. Similarly, in the airline example, atomicity implies that it would not be possible for the system to add a flight without also adding corresponding departure timings and class/seat information.
For a transaction to meet the atomicity requirement, if any of the statements in the transaction fail, all of the preceding statements must be rolled back to ensure the integrity of the database is unaffected. This is particularly important in mission-critical, real-world applications (like financial systems) that perform data entry or updates and require a high degree of safety from undetected data loss.
Consistency
Consistency means that every transaction must ensure that the database is in a consistent state once it completes executing. Or, to put it another way, consistency means that the database must never reflect a partially completed transaction at any time.
With reference to the previous stock exchange example, consistency means that every debit from a seller’s account results in a corresponding and equal credit to a buyer’s account. If a transaction reduces Trader A’s account by 400 shares, but only credits 300 shares to Trader B’s account, the consistency constraint will be violated because the total number of shares in the system changes. Similarly, the consistency property would ensure that if a flight is removed, all data related to that flight, including departure timings and seat/class information, would also be removed.
Isolation
Isolation means that every transaction must occur in its own separate and independent “transaction space,” and its impact on the database only becomes visible once the transaction has completed executing (regardless of whether the transaction was successful or not). This is particularly important in multiuser, multitransaction systems, because it implies that the effects of a particular transaction are not “felt” until the transaction is complete. In the absence of the isolation property, two conflicting transactions might quickly produce data corruption, because each transaction would violate the other’s integrity. With reference to the previous stock exchange example, for instance, isolation implies the transaction between the two traders is independent of all other transactions on the exchange and its result is visible to the public at large only once it has been completed.
When considering a flight modification, it implies that the list of available flights is updated only once the transaction is complete, and does not reflect other transactions that might still be in process at any given instant.
In reality, of course, the only way to obtain absolute isolation is to ensure that only a single user can access the database at any time. This is not a practical solution at all when dealing with a multiuser RDBMS like MySQL. Instead, most transactional systems use either page-level locking or row-level locking to isolate the changes made by different transactions from each other, at some cost in performance.
Durability
Durability means that changes made by a successful transaction will not be lost, even if the system crashes. Most RDBMS products ensure data durability by keeping a log of all activity that alters data in the database in any way. This database log keeps track of any and all updates made to tables, queries, reports, and so on.
In the event of a system crash or a corruption of the data storage media, the system is able to recover to the last successful update on restart and reflect the changes carried out by transactions that were still in progress when it went down through the use of its logs. In the context of the previous share transfer example, durability means that once the transfer of shares from Trader A to Trader B has completed successfully, the system should reflect that state, even if a system failure subsequently takes place. Or, when dealing with the airline database, flights that have been added should not vanish from the database in the event of a system failure.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
Atomicity
Atomicity means that every transaction must be treated as an indivisible unit. Given a transaction consisting of two or more tasks, all the statements within it must be successful for the transaction to be considered successful. In the event of a transaction failure, the system should be returned to its pre-transaction state.
With reference to the previous stock exchange example, atomicity means the sale of shares by Trader A and the purchase of the same by Trader B cannot occur independently of each other, and both must take place for the transaction to be considered complete. Similarly, in the airline example, atomicity implies that it would not be possible for the system to add a flight without also adding corresponding departure timings and class/seat information.
For a transaction to meet the atomicity requirement, if any of the statements in the transaction fail, all of the preceding statements must be rolled back to ensure the integrity of the database is unaffected. This is particularly important in mission-critical, real-world applications (like financial systems) that perform data entry or updates and require a high degree of safety from undetected data loss.
Consistency
Consistency means that every transaction must ensure that the database is in a consistent state once it completes executing. Or, to put it another way, consistency means that the database must never reflect a partially completed transaction at any time.
With reference to the previous stock exchange example, consistency means that every debit from a seller’s account results in a corresponding and equal credit to a buyer’s account. If a transaction reduces Trader A’s account by 400 shares, but only credits 300 shares to Trader B’s account, the consistency constraint will be violated because the total number of shares in the system changes. Similarly, the consistency property would ensure that if a flight is removed, all data related to that flight, including departure timings and seat/class information, would also be removed.
Isolation
Isolation means that every transaction must occur in its own separate and independent “transaction space,” and its impact on the database only becomes visible once the transaction has completed executing (regardless of whether the transaction was successful or not). This is particularly important in multiuser, multitransaction systems, because it implies that the effects of a particular transaction are not “felt” until the transaction is complete. In the absence of the isolation property, two conflicting transactions might quickly produce data corruption, because each transaction would violate the other’s integrity. With reference to the previous stock exchange example, for instance, isolation implies the transaction between the two traders is independent of all other transactions on the exchange and its result is visible to the public at large only once it has been completed.
When considering a flight modification, it implies that the list of available flights is updated only once the transaction is complete, and does not reflect other transactions that might still be in process at any given instant.
In reality, of course, the only way to obtain absolute isolation is to ensure that only a single user can access the database at any time. This is not a practical solution at all when dealing with a multiuser RDBMS like MySQL. Instead, most transactional systems use either page-level locking or row-level locking to isolate the changes made by different transactions from each other, at some cost in performance.
Durability
Durability means that changes made by a successful transaction will not be lost, even if the system crashes. Most RDBMS products ensure data durability by keeping a log of all activity that alters data in the database in any way. This database log keeps track of any and all updates made to tables, queries, reports, and so on.
In the event of a system crash or a corruption of the data storage media, the system is able to recover to the last successful update on restart and reflect the changes carried out by transactions that were still in progress when it went down through the use of its logs. In the context of the previous share transfer example, durability means that once the transfer of shares from Trader A to Trader B has completed successfully, the system should reflect that state, even if a system failure subsequently takes place. Or, when dealing with the airline database, flights that have been added should not vanish from the database in the event of a system failure.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
|
|
0 comments
Post a Comment