--認清SQL_Server_2005的基于行版本控制的兩種隔離級別
--By:zc_0101? Date:2010-03-31
--快照隔離級別(snapshot)和已提交讀快照隔離級別(read?committed?snapshot)
--特點：在這兩種隔離級別下，讀取數據時不再請求共享鎖，而且永遠不會與修改進程的數據發生沖突，如果請求的
--????????行被鎖定(例如正在被更新)，SQL_Server會從行版本存儲區返回最早的關于該行的記錄(SQL_server會在
--????????更新時將之前的行數據在tempdb庫中形成一個鏈接列表,當然目前我沒有搞清楚之前的數據到底存到了那里)
--????????這兩個快照提供了樂觀的并發模型
--說明：首先這兩種隔離級別都是基于快照的實現模式，所以使用前必須修改數據庫選項"允許快照隔離"為ON，否則
--????????以下兩種隔離級別將都被禁用：
????????ALTER?DATABASE?DBNAME?SET?ALLOW_SNAPSHOT_ISOLATION?ON
--????????修改這個選項時可能會需要將數據庫置為單用戶模式
????????ALTER?DATABASE?DBNAME?SET?SINGLE_USER?WITH?ROLLBACK?IMMEDIATE
--????????修改完允許快照隔離后再將數據庫重置為多用戶模式
????????ALTER?DATABASE?DBNAME?set?MULTI_USER
--????一、快照隔離級別是一種全新的隔離級別，在打開“允許快照隔離”選項后，不管是否使用快照隔離級別
????????SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT?
--????????在更新數據時，SQL?SERVER總是會在tempdb庫中保存更改前的最后的行數據鏈接列表,從這里可以想到
--????????將會影響SQL?SERVER在更新數據時的事務性能
--????????當然，該隔離級別的主要作用是提高并發，所以在有讀取數據的地方時，請使用
????????SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT?
--????二、已提交讀快照隔離級別，說得明白點，其實就是SQL?Server默認隔離級別已提交讀的衍生品，或者說是
--????????另一種版本的已提交讀，或者是官方的說法：是已提交讀的新實現，官方的說法總是讓我們不能僅憑字面
--????????意思就可以理解到本質。打開此數據庫選項的命令是：
????????ALTER?DATABASE?DBNAME?SET?read_committed_snapshot?ON
--????????在這里大家要明白，它只是一個數據庫選項開關，是在?READ?COMMITTED?隔離模式下時才會起作用，而且
--????????將改變整個數據庫的全局行為。因為SQL?SERVER默認就是在READ?COMMITTED隔離模式下，所以在稍后的
--????????示例中我們不會用到SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED語句，但是我們心里要明白。
--適用情況：主要是讀取數據的環境，在這種環境下偶爾需要修改操作并且很少發生更新沖突。
--區別：我們會在稍后的演示中進行說明，那樣更容易理解一些。
--示例一：快照
--創建環境：
????IF?DB_ID('DB_TEST')?IS?NOT?NULL?DROP?DATABASE?DB_TEST;
????GO
????CREATE?DATABASE?DB_TEST
????USE?DB_TEST;
????GO
????IF?OBJECT_ID('T_TEST','U')?IS?NOT?NULL?DROP?TABLE?T_TEST
????GO
????CREATE?TABLE?T_TEST(ID?INT?IDENTITY(1,1),COL?VARCHAR(50))
????GO
????INSERT?INTO?T_TEST?SELECT?'AAAAAAAAA'?UNION?ALL?SELECT?'BBBBBBBBBB'
????GO
????SELECT?*?FROM?T_TEST
????/*
????????ID????COL
????????1????AAAAAAAAA
????????2????BBBBBBBBBB
????*/
--????在連接1中執行如下語句（確保ALLOW_SNAPSHOT_ISOLATION已置為ON）
????USE?DB_TEST;
????GO
????ALTER?DATABASE?DB_TEST?SET?ALLOW_SNAPSHOT_ISOLATION?ON;
????GO
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
????????UPDATE?T_TEST?SET?COL='CCCCCCC'?WHERE?ID=2;
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????通過輸出的結果我們可以看到，在未完成的事務中ID=2的COL值從'B'變為'C',而且你應該注意到我這里沒有使用
--????快照隔離級別，還是用的SQL?SERVER默認隔離級別，但是因為我們打開了ALLOW_SNAPSHOT_ISOLATION選項，這
--??個時候，我們的事務應該在更改ID=2的COL值之前就把之前的行狀態存儲到了tempdb中，那么我們怎么才能證明
--????這個猜測呢，動態視圖sys.dm_tran_version_store可以幫助我們，執行：
????SELECT?*?FROM?sys.dm_tran_version_store
--????你一定可以看到在版本存儲區中已經有了一行數據了，接下來我們再打開一個連接2，執行如下SQL:
????USE?DB_TEST
????GO
????--SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT;????--這里我們先注釋掉設置隔離級別為快照模式
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????可以看到查詢一直在等待，是因為我們在連接1中一直保持著該行的排它鎖X。但是現在我們把該事務commit或rollback
--????掉，然后把快照隔離模式的注釋打開，重新執行上面的語句，我們就可以看到
????/*
????????BBBBBBBBBB
????*/
--????我們可以想象到SQL?SERVER在這種隔離級別下的查找思路，它會先去原表查找該行數據，待發現該行被鎖后，則去
--????tempdb數據庫存儲的行版本列表中取出最近的一次數據，這樣就避免了等待，但是前提是要求數據查詢不用那么精確
--????的情況下，當然，你是否在這里忽略了一個問題，即：SQL?SERVER僅會在修改該行數據前才會去存儲最新的行版本，
--????而在修改的事務結束后，SQL?SERVER并不會去更新之前的快照到最新的行版本，但是即使這樣我們也不用擔心，因為
--????這個時候原表的該行數據已經不是鎖定狀態，其他之后的查詢依然會得到最新的數據。唯一注意的一點，我們還是用
--????代碼說明：在連接1中用COMMIT?TRAN?提交事務，然后繼續執行連接2中的查詢：
????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????我們發現數據還是之前的數據BBBBBBB，為什么？因為事務隔離級別！在事務中的任何地方讀取該行數據時，它獲取的
--????總是在事務開始時獲取的數據，這里要牢記，因為他是稍后我們要說的已提交讀快照隔離級別的第一個不同點。
--????接下來我們說說快照隔離級別的另一個特點：沖突檢測，代碼說明，簡潔易懂：
--????在連接1中執行如下語句：
????USE?DB_TEST;
????GO
????SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT;--注意這里我們要設置隔離級別為快照模式
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????這里我們可以得到一個數據，然后再打開一個連接2，執行如下SQL:
????USE?DB_TEST;
????GO
????UPDATE?T_TEST?SET?COL='DDDDDDD'?WHERE?ID=2;
--????回到連接1，繼續執行SQL:
????UPDATE?T_TEST?SET?COL='EEEEEEE'?WHERE?ID=2;
--????這時SQL?SERVER?就會檢測到你在連接1中事務開始時讀取的數據已經與現在的數據發生了改變，所以就會報出更新
--????沖突的錯誤：
????/*
????消息?3960，級別?16，狀態?4，第?1?行
????快照隔離事務由于更新沖突而中止。您無法在數據庫'DB_Test'中使用快照隔離來直接或間接訪問表?'dbo.T_TEST'，
以便更新、刪除或插入已由其他事務修改或刪除的行。請重試該事務或更改?update/delete?語句的隔離級別。
????*/
--????這里，其實就是快照隔離級別和已提交讀快照隔離級別的第二大區別了，READ?COMMITTED?SNAPSHOT不會檢測更新沖突
--示例二：已提交讀快照
--????在連接1中執行如下語句：
????ALTER?DATABASE?DB_TEST?SET?READ_COMMITTED_SNAPSHOT?ON;--首先我們打開該數據庫選項(注意該選項需要
--????上面提到的ALLOW_SNAPSHOT_ISOLATION選項的支持)
????USE?DB_TEST;
????GO;
????BEGIN?TRAN
????????UPDATE?T_TEST?SET?COL='FFFFFFF'?WHERE?ID=2;
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????在該事務里，你將得到你剛剛更新過的值FFFFFFFF
--????在連接2中執行如下語句：
????USE?DB_TEST;
????GO
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????這里你將得到連接1中的事務在修改數據之前的值，而非FFFFFF，這是肯定的。
--????這時我們提交連接1中的事務：
????COMMIT?TRAN;
--????在連接2中再進行查詢時，我們驚奇的發現與在快照中不同的是，我們竟然在未完成的事務2中得到了連接1中的事務
--????更改后的值！這也是為什么不會進行更新沖突檢測的原因，不如我們測試一下：
--????將之前連接1中的事務提交或回滾，然后執行如下SQL:
????USE?DB_TEST;
????GO
????SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED;--這里我們顯示指定隔離級別是因為剛才指定的快照隔離
--????????????????????????????????????????????????????級別會在沒有關閉的會話中一直有效。
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--之后，我們再把連接2中的事務提交或回滾掉，執行如下SQL：
????SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED;
????UPDATE?T_TEST?SET?COL='aaaaa'?WHERE?ID=2;
????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????好了，這個時候我們再在連接1中更新這條在事務1中讀取后但是在外部被更新過的數據：
????UPDATE?T_TEST?SET?COL='測試已提交讀更新沖突檢測'?WHERE?ID=2;
--????我們發現更新可以正常進行，最后我們關閉所有連接，并更改數據庫選項：
????ALTER?DATABASE?DB_TEST?SET?ALLOW_SNAPSHOT_ISOLATION?OFF;
????ALTER?DATABASE?DB_TEST?SET?READ_COMMITTED_SNAPSHOT?OFF;
--總結：快照隔離模式是樂觀并發模型，可以避免臟讀、丟失更新、不可重復讀、幻讀、而且有更新沖突檢測的特點。
--???????已提交快照讀隔離模式和已提交讀模式是相同的，都是只能避免臟讀，都無更新沖突檢測，但是不同的是，已
--????????提交讀快照隔離級別是樂觀并發模型，并且讀取數據不會發生等待。
--另附所有隔離級別的允許或防止的問題等。
==============================================================================================
隔離級別????????臟讀????????丟失更新????????不可重復讀????幻讀????????并發模型????????更新沖突檢測
----------------------------------------------------------------------------------------------
未提交讀????????是????????????是????????????是????????是????????悲觀????????????????否
----------------------------------------------------------------------------------------------
已提交讀????? 否????????????是????????????是????????是???????悲觀????????????????否
----------------------------------------------------------------------------------------------
可重復讀????????否????????????否????????????否????????是????????悲觀????????????????否
----------------------------------------------------------------------------------------------
可串行讀????????否????????????否????????????否????????否????????悲觀????????????????否
----------------------------------------------------------------------------------------------
快照????????????否????????????否????????????否????????否????????樂觀????????????????是
----------------------------------------------------------------------------------------------
已提交讀快照????否????????????是????????????是????????是????????樂觀????????????????否
==============================================================================================
--快照隔離級別(snapshot)和已提交讀快照隔離級別(read?committed?snapshot)
--特點：在這兩種隔離級別下，讀取數據時不再請求共享鎖，而且永遠不會與修改進程的數據發生沖突，如果請求的
--????????行被鎖定(例如正在被更新)，SQL_Server會從行版本存儲區返回最早的關于該行的記錄(SQL_server會在
--????????更新時將之前的行數據在tempdb庫中形成一個鏈接列表,當然目前我沒有搞清楚之前的數據到底存到了那里)
--????????這兩個快照提供了樂觀的并發模型
--說明：首先這兩種隔離級別都是基于快照的實現模式，所以使用前必須修改數據庫選項"允許快照隔離"為ON，否則
--????????以下兩種隔離級別將都被禁用：
????????ALTER?DATABASE?DBNAME?SET?ALLOW_SNAPSHOT_ISOLATION?ON
--????????修改這個選項時可能會需要將數據庫置為單用戶模式
????????ALTER?DATABASE?DBNAME?SET?SINGLE_USER?WITH?ROLLBACK?IMMEDIATE
--????????修改完允許快照隔離后再將數據庫重置為多用戶模式
????????ALTER?DATABASE?DBNAME?set?MULTI_USER
--????一、快照隔離級別是一種全新的隔離級別，在打開“允許快照隔離”選項后，不管是否使用快照隔離級別
????????SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT?
--????????在更新數據時，SQL?SERVER總是會在tempdb庫中保存更改前的最后的行數據鏈接列表,從這里可以想到
--????????將會影響SQL?SERVER在更新數據時的事務性能
--????????當然，該隔離級別的主要作用是提高并發，所以在有讀取數據的地方時，請使用
????????SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT?
--????二、已提交讀快照隔離級別，說得明白點，其實就是SQL?Server默認隔離級別已提交讀的衍生品，或者說是
--????????另一種版本的已提交讀，或者是官方的說法：是已提交讀的新實現，官方的說法總是讓我們不能僅憑字面
--????????意思就可以理解到本質。打開此數據庫選項的命令是：
????????ALTER?DATABASE?DBNAME?SET?read_committed_snapshot?ON
--????????在這里大家要明白，它只是一個數據庫選項開關，是在?READ?COMMITTED?隔離模式下時才會起作用，而且
--????????將改變整個數據庫的全局行為。因為SQL?SERVER默認就是在READ?COMMITTED隔離模式下，所以在稍后的
--????????示例中我們不會用到SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED語句，但是我們心里要明白。
--適用情況：主要是讀取數據的環境，在這種環境下偶爾需要修改操作并且很少發生更新沖突。
--區別：我們會在稍后的演示中進行說明，那樣更容易理解一些。
--示例一：快照
--創建環境：
????IF?DB_ID('DB_TEST')?IS?NOT?NULL?DROP?DATABASE?DB_TEST;
????GO
????CREATE?DATABASE?DB_TEST
????USE?DB_TEST;
????GO
????IF?OBJECT_ID('T_TEST','U')?IS?NOT?NULL?DROP?TABLE?T_TEST
????GO
????CREATE?TABLE?T_TEST(ID?INT?IDENTITY(1,1),COL?VARCHAR(50))
????GO
????INSERT?INTO?T_TEST?SELECT?'AAAAAAAAA'?UNION?ALL?SELECT?'BBBBBBBBBB'
????GO
????SELECT?*?FROM?T_TEST
????/*
????????ID????COL
????????1????AAAAAAAAA
????????2????BBBBBBBBBB
????*/
--????在連接1中執行如下語句（確保ALLOW_SNAPSHOT_ISOLATION已置為ON）
????USE?DB_TEST;
????GO
????ALTER?DATABASE?DB_TEST?SET?ALLOW_SNAPSHOT_ISOLATION?ON;
????GO
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
????????UPDATE?T_TEST?SET?COL='CCCCCCC'?WHERE?ID=2;
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????通過輸出的結果我們可以看到，在未完成的事務中ID=2的COL值從'B'變為'C',而且你應該注意到我這里沒有使用
--????快照隔離級別，還是用的SQL?SERVER默認隔離級別，但是因為我們打開了ALLOW_SNAPSHOT_ISOLATION選項，這
--??個時候，我們的事務應該在更改ID=2的COL值之前就把之前的行狀態存儲到了tempdb中，那么我們怎么才能證明
--????這個猜測呢，動態視圖sys.dm_tran_version_store可以幫助我們，執行：
????SELECT?*?FROM?sys.dm_tran_version_store
--????你一定可以看到在版本存儲區中已經有了一行數據了，接下來我們再打開一個連接2，執行如下SQL:
????USE?DB_TEST
????GO
????--SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT;????--這里我們先注釋掉設置隔離級別為快照模式
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????可以看到查詢一直在等待，是因為我們在連接1中一直保持著該行的排它鎖X。但是現在我們把該事務commit或rollback
--????掉，然后把快照隔離模式的注釋打開，重新執行上面的語句，我們就可以看到
????/*
????????BBBBBBBBBB
????*/
--????我們可以想象到SQL?SERVER在這種隔離級別下的查找思路，它會先去原表查找該行數據，待發現該行被鎖后，則去
--????tempdb數據庫存儲的行版本列表中取出最近的一次數據，這樣就避免了等待，但是前提是要求數據查詢不用那么精確
--????的情況下，當然，你是否在這里忽略了一個問題，即：SQL?SERVER僅會在修改該行數據前才會去存儲最新的行版本，
--????而在修改的事務結束后，SQL?SERVER并不會去更新之前的快照到最新的行版本，但是即使這樣我們也不用擔心，因為
--????這個時候原表的該行數據已經不是鎖定狀態，其他之后的查詢依然會得到最新的數據。唯一注意的一點，我們還是用
--????代碼說明：在連接1中用COMMIT?TRAN?提交事務，然后繼續執行連接2中的查詢：
????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????我們發現數據還是之前的數據BBBBBBB，為什么？因為事務隔離級別！在事務中的任何地方讀取該行數據時，它獲取的
--????總是在事務開始時獲取的數據，這里要牢記，因為他是稍后我們要說的已提交讀快照隔離級別的第一個不同點。
--????接下來我們說說快照隔離級別的另一個特點：沖突檢測，代碼說明，簡潔易懂：
--????在連接1中執行如下語句：
????USE?DB_TEST;
????GO
????SET?TRANSACTION?ISOLATION?LEVEL?SNAPSHOT;--注意這里我們要設置隔離級別為快照模式
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????這里我們可以得到一個數據，然后再打開一個連接2，執行如下SQL:
????USE?DB_TEST;
????GO
????UPDATE?T_TEST?SET?COL='DDDDDDD'?WHERE?ID=2;
--????回到連接1，繼續執行SQL:
????UPDATE?T_TEST?SET?COL='EEEEEEE'?WHERE?ID=2;
--????這時SQL?SERVER?就會檢測到你在連接1中事務開始時讀取的數據已經與現在的數據發生了改變，所以就會報出更新
--????沖突的錯誤：
????/*
????消息?3960，級別?16，狀態?4，第?1?行
????快照隔離事務由于更新沖突而中止。您無法在數據庫'DB_Test'中使用快照隔離來直接或間接訪問表?'dbo.T_TEST'，
以便更新、刪除或插入已由其他事務修改或刪除的行。請重試該事務或更改?update/delete?語句的隔離級別。
????*/
--????這里，其實就是快照隔離級別和已提交讀快照隔離級別的第二大區別了，READ?COMMITTED?SNAPSHOT不會檢測更新沖突
--示例二：已提交讀快照
--????在連接1中執行如下語句：
????ALTER?DATABASE?DB_TEST?SET?READ_COMMITTED_SNAPSHOT?ON;--首先我們打開該數據庫選項(注意該選項需要
--????上面提到的ALLOW_SNAPSHOT_ISOLATION選項的支持)
????USE?DB_TEST;
????GO;
????BEGIN?TRAN
????????UPDATE?T_TEST?SET?COL='FFFFFFF'?WHERE?ID=2;
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????在該事務里，你將得到你剛剛更新過的值FFFFFFFF
--????在連接2中執行如下語句：
????USE?DB_TEST;
????GO
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????這里你將得到連接1中的事務在修改數據之前的值，而非FFFFFF，這是肯定的。
--????這時我們提交連接1中的事務：
????COMMIT?TRAN;
--????在連接2中再進行查詢時，我們驚奇的發現與在快照中不同的是，我們竟然在未完成的事務2中得到了連接1中的事務
--????更改后的值！這也是為什么不會進行更新沖突檢測的原因，不如我們測試一下：
--????將之前連接1中的事務提交或回滾，然后執行如下SQL:
????USE?DB_TEST;
????GO
????SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED;--這里我們顯示指定隔離級別是因為剛才指定的快照隔離
--????????????????????????????????????????????????????級別會在沒有關閉的會話中一直有效。
????BEGIN?TRAN
????????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--之后，我們再把連接2中的事務提交或回滾掉，執行如下SQL：
????SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED;
????UPDATE?T_TEST?SET?COL='aaaaa'?WHERE?ID=2;
????SELECT?COL?FROM?T_TEST?WHERE?ID=2;
--????好了，這個時候我們再在連接1中更新這條在事務1中讀取后但是在外部被更新過的數據：
????UPDATE?T_TEST?SET?COL='測試已提交讀更新沖突檢測'?WHERE?ID=2;
--????我們發現更新可以正常進行，最后我們關閉所有連接，并更改數據庫選項：
????ALTER?DATABASE?DB_TEST?SET?ALLOW_SNAPSHOT_ISOLATION?OFF;
????ALTER?DATABASE?DB_TEST?SET?READ_COMMITTED_SNAPSHOT?OFF;
--總結：快照隔離模式是樂觀并發模型，可以避免臟讀、丟失更新、不可重復讀、幻讀、而且有更新沖突檢測的特點。
--???????已提交快照讀隔離模式和已提交讀模式是相同的，都是只能避免臟讀，都無更新沖突檢測，但是不同的是，已
--????????提交讀快照隔離級別是樂觀并發模型，并且讀取數據不會發生等待。
--另附所有隔離級別的允許或防止的問題等。
==============================================================================================
隔離級別????????臟讀????????丟失更新????????不可重復讀????幻讀????????并發模型????????更新沖突檢測
----------------------------------------------------------------------------------------------
未提交讀????????是????????????是????????????是????????是????????悲觀????????????????否
----------------------------------------------------------------------------------------------
已提交讀????? 否????????????是????????????是????????是???????悲觀????????????????否
----------------------------------------------------------------------------------------------
可重復讀????????否????????????否????????????否????????是????????悲觀????????????????否
----------------------------------------------------------------------------------------------
可串行讀????????否????????????否????????????否????????否????????悲觀????????????????否
----------------------------------------------------------------------------------------------
快照????????????否????????????否????????????否????????否????????樂觀????????????????是
----------------------------------------------------------------------------------------------
已提交讀快照????否????????????是????????????是????????是????????樂觀????????????????否
==============================================================================================
