事务

个人笔记使用,截取他人博客

原文来之Careyson:http://www.cnblogs.com/CareySon/archive/2012/01/29/2331088.html

& https://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

为了避免上述几种事务之间的影响,SQL Server通过设置不同的隔离等级来进行不同程度的避免。因为高的隔离等级意味着更多的锁，从而牺牲性能.所以这个选项开放给了用户根据具体的需求进行设置。不过默认的隔离等级Read Commited符合了99%的实际需求.

    SQL Server隔离事务之间的影响是通过锁来实现的，这个概念比较繁杂，所以本文不会详细对这个概念进行讲解.通过阻塞来阻止上述效果

    SQL Server提供了5种选项来避免不同级别的事务之间的影响

    隔离等级由低到高分别为

    Read Uncommited(最高的性能，但可能出现脏读，不可重复读，幻读)

    Read commited(可能出现不可重复读，幻读)

    Repeatable Read(可能出现幻读)

    Serializable(最低的性能，Range锁会导致并发下降)

    SNOPSHOT(这个是通过在tempDB中创建一个额外的副本来避免脏读，不可重复读，会给tempDB造成额外负担，因为不是标准ANSI SQL标准，不详细讨论)

 

SQL Server 2005 added two new options:

• A variation on READ COMMITTED where you set READ_COMMITTED_SNAPHOT ON at the database level and any transaction that uses the READ COMMITTED isolation level will not acquire share locks and will not wait on any locks.  Rather, you will get the committed version of all rows at the time the SELECT statement begins.
• A new isolation level called SNAPSHOT where you set ALLOW_SNAPSHOT_ISOLATION ON at the database level and any transaction that explicitly sets the transaction isolation level to snapshot will not acquire share locks and will not wait on any locks.  Rather, you will get the committed version of all rows at the time the transaction begins. 

Both of the above SQL Server 2005 enhancements are made possible by maintaining committed versions of rows in tempdb (referred to as the version store).  When a read encounters a row that has been modified and not yet committed, it retrieves the appropriate latest committed row from the version store.  The maintenance and traversing of the version store is performed by SQL Server automatically; there are no code changes required.