SNAPSHOT ISOLATION locks pessimistically; that is, they physically

SNAPSHOT ISOLATION locks pessimistically; that is, they physically prevent any access to data that might compromise the required isolation level. In some cases, this will delay a transaction as it waits for a lock to be freed, or may even cause it to fail because of a timeout waiting for the lock. SQL Server 2005 adds SNAPSHOTisolation that, in effect, provides alternate implementations of SERIALIZABLE and READ COMMITTED levels of isolation that use optimistic locking to control concurrent access rather than pessimistic locking. For some applications, SNAPSHOT isolation may provide better performance than pre SQL Server 2005 implementations did. In addition, SNAPSHOTisolation makes it much easier to port database applications to SQL Server from database engines that make extensive use of SNAPSHOTisolation. SQL Server 2005 has two kinds of SNAPSHOTisolation: transaction-level and statement level. Transaction-level SNAPSHOT isolation makes transactions perfect, the same as SERIALIZABLEdoes. Statement-level SNAPSHOT isolation makes transactions that have the same degree of isolation as READ COMMITTEDdoes. The transaction-level SNAPSHOT isolation optimistically assumes that if a transaction operates on an image of that database s committed data when the transaction started, the result will be the same as a transaction run at the SERIALIZABLE isolation level. Some time before the transaction completes, the optimistic assumption is tested, and if it proves not to be true, the transaction is rolled back. Transaction-level SNAPSHOT isolation works by, in effect, making a version of the database by taking a snapshot of it when a transaction starts. Figure 7-1 shows this. There are three transactions in Figure 7-1: transaction 1, transaction 2, and transaction 3. When transaction 1 starts, it is given a snapshot of the initial database. Transaction 2 starts before transaction 1 finishes, so it is also given a snapshot of the initial database. Transaction 3 starts after transaction 1 finishes but before transaction 2 does. Transaction 3 is given a snapshot of the initial database plus all the changes committed by transaction 1. The result of using SERIALIZABLE or transaction-level SNAPSHOT isolation is the same; some transactions will fail and have to be retried, and may fail again, but the integrity of the database is always guaranteed. Of course, SQL Server can t actually make a snapshot of the entire database, but it gets that effect by keeping track of each change to the database until all transactions that were started before the change was made are completed. This technique is called row versioning.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Bookmark the permalink.

Comments are closed.