One of the most important properties of a transaction-capable RDBMS is its capability to “isolate” the different sessions in progress at any given instance on the server. In a single-user environment, this property is largely irrelevant for obvious reasons: There is nothing to isolate because usually only a single session is active at any time. In more complex real-world scenarios, however, it is unlikely this assumption will remain true.
In a multiuser environment, many RDBMS sessions will usually be active at any given time. In the stock trading example discussed previously, for instance, it is unlikely that only a single trade will be taking place at a particular point in time. Far more likely is that hundreds of trades will occur simultaneously. In such a situation, it is essential that the RDBMS isolate transactions so that they do not interfere with each other, while simultaneously ensuring the database’s performance does not suffer as a result.
To understand the importance of isolation, consider what would happen if it wasn’t enforced. In the absence of transaction isolation, different SELECT statements would retrieve different results within the context of the same transaction because the underlying data was modified by other transactions in the interim. This would create inconsistency and make it difficult to trust a particular result set or use it as the basis for calculations with any degree of confidence. Isolation thus imposes a degree of insulation between transactions, guaranteeing that an application only sees consistent data within the scope of a transaction. MySQL provides the following four isolation levels in accordance with the ANSI/ISO SQL specification:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
These transaction isolation levels determine the degree to which other transactions can “see” inside an in-progress transaction, and are arranged in hierarchical order, beginning with the least secure (and most problematic) level and gradually moving to the most secure level. These isolation levels can be manipulated with the TRANSACTION ISOLATION LEVEL variable, which is discussed in greater detail in the section “Modifying the Transaction Isolation Level.” Let’s now look at what each of the isolation levels does.
The READ UNCOMMITTED Isolation Level
The READ UNCOMMITTED isolation level provides the minimum amount of insulation between transactions. In addition to being vulnerable to phantom reads and unrepeatable reads, a transaction at this isolation level can read data that has not yet been committed by other transactions. If this transaction now uses the uncommitted changes made by other transactions as the basis for calculations of its own, and those uncommitted changes are then rolled back by their parent transactions, it can result in massive data corruption.
The READ COMMITTED Isolation Level
Even less secure than the REPEATABLE READ isolation level is the READ COMMITTED isolation level. At this level, a transaction can see the committed changes of other transactions during its lifetime. Put another way, this means multiple SELECT statements within the same transaction might return different results if the corresponding tables have been modified by other transactions in the intervening period.
In this case, the second transaction will continue to see zero records during the lifetime of the first transaction. However, once the first transaction commits its changes, the second one will see one flight, even though it is still in progress. This is obviously a problem—if the second transaction sees two different results for the same operation, it isn’t going to know which one to trust as the correct one. Extrapolate a little and assume that instead of a single transaction, many transactions are committing updates to the database, and you’ll see every query executed by a transaction could produce a different result set (hence, the term “unrepeatable read” for this kind of situation).
The REPEATABLE READ Isolation Level
For applications that are willing to compromise a little on security for better performance, MySQL offers the REPEATABLE READ isolation level. At this level, a transaction will not see the changes carried out by concurrent transactions until it itself has concluded.
In this case, the second transaction can see the new flight added by the first transaction only once both transactions are complete. This is, in fact, the way most users expect transactions to work, and it should come as no surprise that this is MySQL’s default transaction isolation level. The InnoDB storage engine accomplishes this by using multiversioning to store a snapshot of the query results when the query is executed for the first time; it then reuses this snapshot for all subsequent queries until the transaction is committed.
The SERIALIZABLE Isolation Level
This SERIALIZABLE isolation level offers the maximum amount of insulation between transactions by treating concurrent transactions as though they were executing sequentially, one after the other.
Here, the first transaction is adding a new flight to the database, while the second is attempting to view the total number of flights. However, because MySQL is executing these transactions serially, the INSERT operation in the first transaction will lock the table until the transaction is complete. This will force the SELECT operation in the second transaction to wait until the lock is released before it can obtain a result. This “serialized” approach to handling transactions is the most secure: Sequentially locking and unlocking the table ensures that each transaction only sees data that has actually been committed to the database, with no possibility of dirty or unrepeatable reads. However, this comes at a price: MySQL will take a performance hit if every transaction runs at this isolation level because of the large amount of resources required to handle the various transactional locks at any given instant.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
In a multiuser environment, many RDBMS sessions will usually be active at any given time. In the stock trading example discussed previously, for instance, it is unlikely that only a single trade will be taking place at a particular point in time. Far more likely is that hundreds of trades will occur simultaneously. In such a situation, it is essential that the RDBMS isolate transactions so that they do not interfere with each other, while simultaneously ensuring the database’s performance does not suffer as a result.
To understand the importance of isolation, consider what would happen if it wasn’t enforced. In the absence of transaction isolation, different SELECT statements would retrieve different results within the context of the same transaction because the underlying data was modified by other transactions in the interim. This would create inconsistency and make it difficult to trust a particular result set or use it as the basis for calculations with any degree of confidence. Isolation thus imposes a degree of insulation between transactions, guaranteeing that an application only sees consistent data within the scope of a transaction. MySQL provides the following four isolation levels in accordance with the ANSI/ISO SQL specification:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
These transaction isolation levels determine the degree to which other transactions can “see” inside an in-progress transaction, and are arranged in hierarchical order, beginning with the least secure (and most problematic) level and gradually moving to the most secure level. These isolation levels can be manipulated with the TRANSACTION ISOLATION LEVEL variable, which is discussed in greater detail in the section “Modifying the Transaction Isolation Level.” Let’s now look at what each of the isolation levels does.
The READ UNCOMMITTED Isolation Level
The READ UNCOMMITTED isolation level provides the minimum amount of insulation between transactions. In addition to being vulnerable to phantom reads and unrepeatable reads, a transaction at this isolation level can read data that has not yet been committed by other transactions. If this transaction now uses the uncommitted changes made by other transactions as the basis for calculations of its own, and those uncommitted changes are then rolled back by their parent transactions, it can result in massive data corruption.
The READ COMMITTED Isolation Level
Even less secure than the REPEATABLE READ isolation level is the READ COMMITTED isolation level. At this level, a transaction can see the committed changes of other transactions during its lifetime. Put another way, this means multiple SELECT statements within the same transaction might return different results if the corresponding tables have been modified by other transactions in the intervening period.
In this case, the second transaction will continue to see zero records during the lifetime of the first transaction. However, once the first transaction commits its changes, the second one will see one flight, even though it is still in progress. This is obviously a problem—if the second transaction sees two different results for the same operation, it isn’t going to know which one to trust as the correct one. Extrapolate a little and assume that instead of a single transaction, many transactions are committing updates to the database, and you’ll see every query executed by a transaction could produce a different result set (hence, the term “unrepeatable read” for this kind of situation).
The REPEATABLE READ Isolation Level
For applications that are willing to compromise a little on security for better performance, MySQL offers the REPEATABLE READ isolation level. At this level, a transaction will not see the changes carried out by concurrent transactions until it itself has concluded.
In this case, the second transaction can see the new flight added by the first transaction only once both transactions are complete. This is, in fact, the way most users expect transactions to work, and it should come as no surprise that this is MySQL’s default transaction isolation level. The InnoDB storage engine accomplishes this by using multiversioning to store a snapshot of the query results when the query is executed for the first time; it then reuses this snapshot for all subsequent queries until the transaction is committed.
The SERIALIZABLE Isolation Level
This SERIALIZABLE isolation level offers the maximum amount of insulation between transactions by treating concurrent transactions as though they were executing sequentially, one after the other.
Here, the first transaction is adding a new flight to the database, while the second is attempting to view the total number of flights. However, because MySQL is executing these transactions serially, the INSERT operation in the first transaction will lock the table until the transaction is complete. This will force the SELECT operation in the second transaction to wait until the lock is released before it can obtain a result. This “serialized” approach to handling transactions is the most secure: Sequentially locking and unlocking the table ensures that each transaction only sees data that has actually been committed to the database, with no possibility of dirty or unrepeatable reads. However, this comes at a price: MySQL will take a performance hit if every transaction runs at this isolation level because of the large amount of resources required to handle the various transactional locks at any given instant.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
|
|
0 comments
Post a Comment