調(diào)整事務(wù)隔離級(jí)別的方法

1、http:/tech n/librarv/msl73763.aspxsql server 2005 聯(lián)機(jī)叢書(2007 年 9 月)set transaction isolation level (transact-sql)語法set transaction isolation level read uncommitted| read committed| repeatable read| snapshot| serializable；參數(shù)read uncommitted指定語句可以讀取已由其他事務(wù)修改但尚未提交的行。在read uncommitted級(jí)別運(yùn)行的出務(wù)，不會(huì)發(fā)出共享鎖來防上其他事

2、務(wù)修改當(dāng)前事務(wù)讀取的數(shù) 據(jù)。read uncommitted事務(wù)也不會(huì)被排他鎖阻塞，排他鎖會(huì)禁止當(dāng)前事務(wù)讀取jt他爭務(wù)已修改 但尚未提交的行。設(shè)置此選項(xiàng)之后，可以讀取未提交的修改，這種讀取稱為臟讀。在爭務(wù)結(jié)束之前，可 以更改數(shù)據(jù)中的值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集中消失。該選項(xiàng)的作用與在事務(wù)內(nèi)所冇 select語句中的所有表上設(shè)置nolock相同。這是隔離級(jí)別中限制最少的級(jí)別。在sql server 2005中，您還可以使用下列任意一種方法，在保護(hù)來務(wù)不臟讀未提交的數(shù)據(jù)修改的 同時(shí)盡量減少鎖定爭用： read committed隔離級(jí)別，并將read_committed_snapshot數(shù)

3、據(jù)庫選項(xiàng)設(shè)置 為on。 snapshot隔離級(jí)別。read committed指定語句不能讀取己由其他爭務(wù)修改但尚未提交的數(shù)據(jù)。這樣可以避免臟讀。其他事務(wù)可以在當(dāng)前事務(wù) 的各個(gè)語句之間更改數(shù)據(jù)，從而產(chǎn)牛不可重復(fù)讀取和幻像數(shù)據(jù)。該選項(xiàng)是sql server的默認(rèn)設(shè)置。read committed的行為取決于read_committed_snapshot數(shù)據(jù)庫選項(xiàng)的設(shè)置： 如果將read_committed_snapshot設(shè)置為off（默認(rèn)設(shè)置），則數(shù)據(jù)庫引擎會(huì)使用 共方鎖防止其他事務(wù)在當(dāng)前事務(wù)執(zhí)行讀取操作期間修改行.共孚鎖還會(huì)阻止語句在其他事務(wù) 完成之前讀取由這些事務(wù)修改的行。語旬完成后便會(huì)釋

4、放共亨鎖。 如果將read_committed_snapshot設(shè)置為0n，則數(shù)據(jù)庫引擎會(huì)使用行版木控制為 每個(gè)語句提供一個(gè)在事務(wù)上一致的數(shù)據(jù)快照，因?yàn)樵摂?shù)據(jù)在語句開始時(shí)就存在。不使用鎖來 防止其他事務(wù)更新數(shù)據(jù)。當(dāng) read_committed_snapshot數(shù)據(jù)庫選項(xiàng)設(shè)置為 on時(shí)，您可以使用 readcommittedlock表提示為read committed隔離級(jí)別上運(yùn)行的事務(wù)中的各語句請(qǐng)求 共亨鎖，而不是行版木控制。彳注意：設(shè)置readcommitted snapshot選項(xiàng)時(shí)，數(shù)據(jù)庫中僅允許存在執(zhí)行alter database命令的連接。在alter database完成z前，數(shù)

5、據(jù)庫中不允許有其他打開的連接。數(shù)據(jù)庫不必處于單用戶模式。repeatable read指定語句不能讀取已由其他事務(wù)修改但尚未提交的行，并r指定，其他任何事務(wù)都不能在當(dāng)前事務(wù)完成z前修改由當(dāng)前來務(wù)讀取的數(shù)據(jù)。對(duì)事務(wù)中的每個(gè)語句所讀取的全部數(shù)據(jù)都設(shè)睥了共享鎖，并且該共享鎖一直保持到事務(wù)完成為止。這樣 可以防止其他事務(wù)修改當(dāng)前事務(wù)讀取的任何行。其他事務(wù)可以插入與當(dāng)前事務(wù)所發(fā)出語句的搜索條件和 匹配的新行。如果當(dāng)前事務(wù)隨后重試執(zhí)行該語句，它會(huì)檢索新行，從而產(chǎn)生幻讀。由于共京鎖一直保持 到事務(wù)結(jié)束，而不是在每個(gè)語句結(jié)束時(shí)釋放，所以并發(fā)級(jí)別低于默認(rèn)的read committed隔離級(jí) 別。此選項(xiàng)只在必要時(shí)

6、使用。snapshot指定事務(wù)中任何語句讀取的數(shù)據(jù)都將是在事務(wù)開始時(shí)便存在的數(shù)據(jù)的事務(wù)上一致的版本。事務(wù)只能識(shí)別 在其開始之前提交的數(shù)據(jù)修改。在當(dāng)前事務(wù)中執(zhí)行的語句將看不到在當(dāng)前事務(wù)開始以后由其他事務(wù)所做 的數(shù)據(jù)修改。其效杲就好像事務(wù)中的語句獲得了已提交數(shù)據(jù)的快照，因?yàn)樵摂?shù)據(jù)在事務(wù)開始時(shí)就存在。除非正在恢復(fù)數(shù)據(jù)庫，否則snapshot爭務(wù)不會(huì)在讀取數(shù)據(jù)時(shí)請(qǐng)求鎖。讀取數(shù)據(jù)的snapshot事 務(wù)不會(huì)阻止其他事務(wù)寫入數(shù)據(jù)。寫入數(shù)據(jù)的事務(wù)也不會(huì)阻止snapshot事務(wù)讀取數(shù)據(jù)。在數(shù)據(jù)庫恢復(fù)的冋滾階段，如果嘗試讀取由其他止在冋滾的事務(wù)鎖定的數(shù)據(jù)，則snapshot事務(wù)將 詰:求一個(gè)鎖。在事務(wù)完成回滾之

7、前，snapshot事務(wù)會(huì)一直被阻塞。當(dāng)事務(wù)取得授權(quán)之后，便會(huì)立即 釋放鎖。必須將allow_snapshot_isolation數(shù)據(jù)庫選項(xiàng)設(shè)置為on，才能開始一個(gè)使用snapshot 隔離級(jí)別的事務(wù)。如果使用snapshot隔離級(jí)別的事務(wù)訪問多個(gè)數(shù)據(jù)庫中的數(shù)據(jù)，則必須在每個(gè)數(shù) 據(jù)庫中將 allow_snapshot_isolation 都設(shè)置為 on。不能將通過其他隔離級(jí)別開始的事務(wù)設(shè)置為snapshot隔離級(jí)別，否則將導(dǎo)致事務(wù)中止。如果一個(gè) 事務(wù)在snapshot隔離級(jí)別開始，則可以將它更改為另一個(gè)隔離級(jí)別，然后再返冋snapshoto 事務(wù)在第一次訪問數(shù)據(jù)時(shí)啟動(dòng)。在snapshot隔離級(jí)

8、別下運(yùn)行的事務(wù)可以查看由該事務(wù)所做的更改。例如，如果事務(wù)對(duì)表執(zhí)行 update，然后對(duì)同一個(gè)表發(fā)出select語句，則修改后的數(shù)據(jù)將包含在結(jié)果集中。serializable請(qǐng)指定下列內(nèi)容： 語句不能讀取已由其他事務(wù)修改但尚未提交的數(shù)據(jù)。 任何其他事務(wù)都不能在當(dāng)前事務(wù)完成之前修改由當(dāng)前事務(wù)瀆取的數(shù)據(jù)。 在當(dāng)前事務(wù)完成z前，其他事務(wù)不能使川當(dāng)前事務(wù)屮任何沼句讀取的鍵值插入新行。范圍鎖處于與爭務(wù)中執(zhí)行的每個(gè)語句的搜索條件相匹配的鍵值范圍z內(nèi)。這樣可以阻止其他爭務(wù)更新或 插入任何行，從而限定當(dāng)前事務(wù)所執(zhí)行的任何語句。這意味著如果再次執(zhí)行爭務(wù)中的任何語句，則這些 語句便會(huì)讀取同一組行。在事務(wù)完成之炯將一

9、直保持范圍鎖。這是限制最多的隔離級(jí)別，因?yàn)樗i定了 鍵的整個(gè)范圍，并在事務(wù)完成之前一直保持范圍鎖。因?yàn)椴l(fā)級(jí)別較低，所以應(yīng)只在必耍時(shí)才使用該選 項(xiàng)。該選項(xiàng)的作用與在事務(wù)內(nèi)所有select語句中的所有表上設(shè)置holdlock相同。備注一次只能設(shè)置一個(gè)隔離級(jí)別選項(xiàng)，而且設(shè)置的選項(xiàng)將一直對(duì)那個(gè)連接始終有效，直到顯式更改該選項(xiàng)為止。事務(wù) 中執(zhí)行的所有讀取操作都會(huì)在指定的隔離級(jí)別的規(guī)則下運(yùn)行，除非語句的from子句中的表提示為表指定了其 他鎖定行為或版本控制行為。事務(wù)隔離級(jí)別定義了可為讀取操作獲取的鎖類型。針對(duì)read committed或repeatable read獲取的 共享鎖通常為行鎖，盡管當(dāng)讀

10、取弓用了頁或表中大雖的行時(shí)，行鎖可以升級(jí)為頁鎖或表鎖。如果某行在被讀取z 后市申務(wù)進(jìn)行了修改，則該出務(wù)會(huì)獲取-個(gè)用于保護(hù)該行的排他鎖，并且該排他鎖在事務(wù)完成z前將-直保持。 例如，如果repeatable read事務(wù)具冇用丁某行的共享鎖，并且該事務(wù)魏后修改了該行，則共享行鎖便會(huì) 轉(zhuǎn)換為排他行鎖。在事務(wù)進(jìn)行期間，可以隨時(shí)將事務(wù)從一個(gè)隔離級(jí)別切換到另一個(gè)隔離級(jí)別，但有一種情況例外。即在從任-隔離 級(jí)別更改到snapshot隔離時(shí)，不能進(jìn)行上述操作。否則會(huì)導(dǎo)致事務(wù)失敗并回滾。但是，可以將在snapshot 隔離屮啟動(dòng)的事務(wù)更改為任何其他隔離級(jí)別。將察務(wù)從一個(gè)隔離級(jí)別更改為另一個(gè)隔離級(jí)別z后，便會(huì)根

11、據(jù)新級(jí)別的規(guī)則走更改后讀取的資源執(zhí)行保護(hù)。在更 改前讀取的資源將繼續(xù)按照以前級(jí)別的規(guī)則受到保擴(kuò)例如，如果某個(gè)爭務(wù)從read committed更改為 serializable.則在該事務(wù)結(jié)束前，更改后所獲取的共享鎖將一直處于保留狀態(tài)。如果在存儲(chǔ)過程或觸發(fā)器中發(fā)出set transaction isolation level.則當(dāng)對(duì)象返冋控制時(shí)，隔離級(jí)別 會(huì)重設(shè)為在調(diào)用對(duì)象時(shí)有效的級(jí)別。例如，如果在批處理中設(shè)置repeatable read，并且該批處理調(diào)用一個(gè) 將隔離級(jí)別設(shè)置為serializable的存儲(chǔ)過程，則當(dāng)該存儲(chǔ)過程將控制返回給該批處理時(shí)，隔離級(jí)別就會(huì)恢復(fù) 為 repeatable

12、read。占注意：用戶定義的函數(shù)和公共語言運(yùn)行時(shí)(clr)用戶定義的類型無法執(zhí)行set transaction isolationlevelo但是，可通過使用表提示來重寫隔離級(jí)別。有關(guān)詳細(xì)信息，請(qǐng)參閱表捉示(transact-sql)。當(dāng)您使用sp_bindsession綁定兩個(gè)會(huì)話時(shí)，每個(gè)會(huì)話都會(huì)保留它自身的隔離級(jí)別設(shè)置。.使用set transaction isolation level更改某個(gè)會(huì)話的隔離級(jí)別設(shè)骨-時(shí)，不會(huì)影響與該會(huì)話綁定的其他任何會(huì)話 的設(shè)置。set transaction isolation level會(huì)在執(zhí)行或運(yùn)行時(shí)生效，而不是在分析時(shí)生效。針對(duì)堆的優(yōu)化大容量負(fù)載操作

13、阻塞了運(yùn)行在以下隔離級(jí)別下面的查詢： snapshot read uncommitted 使用行版木控制的read committed相反，運(yùn)行在這些隔離級(jí)別下面的查詢阻塞了針對(duì)堆的優(yōu)化大容量負(fù)載操作。有關(guān)犬容量加載操作的詳細(xì)信息, 譴參閱關(guān)于大容量導(dǎo)入和大容量導(dǎo)出操作和優(yōu)化大容量導(dǎo)入性能。q示例以下示例為會(huì)話設(shè)置了 transaction isolation level。對(duì)于每個(gè)后續(xù)transact-sql語句，sqlserver將所有共宇鎖一直保持到事務(wù)結(jié)束為止。心復(fù)制代碼use adventureworks:goset transaction isolation level repeat

14、able read;gobegin transaction;goselect *from humanresourcesempioyeepayhistory;goselect *from humanresources.department;gocommit transaction;go請(qǐng)參閱參考alter database (transact-sql)dbcc useroptions gnsact-sql)select (transact-sql)set (transact-sql)表提示(transact-sql)其他資源自定義事務(wù)隔離級(jí)別數(shù)據(jù)斥引擎中的隔離級(jí)別幫助和信息茯取 sql serv

15、er 2005 幫助http:/tech cn/iibrarv/msl89542.aspxsql server 2005 聯(lián)機(jī)叢書(2007 年 9 月)調(diào)整事務(wù)隔離級(jí)別隔離屬性是原子性、一致性、隔離性和持久性（acid）這四個(gè)屬性之一，邏輯工作單元必須具備這四個(gè)屬性才 能稱為事務(wù)。隔離屬性能夠使事務(wù)免受其他并發(fā)事務(wù)執(zhí)行的更新的影響。每個(gè)事務(wù)的隔離級(jí)別實(shí)際上都是可以自 定義的。sql server數(shù)據(jù)庫引擎支持sql-92中定義的爭務(wù)隔離級(jí)別。設(shè)置用務(wù)隔離級(jí)別使程序員而臨的風(fēng)險(xiǎn)因某此 完整性問題而增加，但好處是可以支持更好的數(shù)據(jù)并發(fā)訪問。事務(wù)隔離級(jí)別有： read uncommitted re

16、ad committed repeatable read snapshot serializable通過將更嚴(yán)格的鎖持有更長時(shí)間，每個(gè)隔離級(jí)別都比上一個(gè)級(jí)別提供更好的隔離性，但有兩個(gè)例外。這兩個(gè)例外 是將 read_committed_snapshot 設(shè)置為 on 時(shí)的 snapshot 和 read committed。這兩個(gè)隔 離級(jí)別在讀取操作期間并不獲取數(shù)據(jù)行的共亨鎖。只在表級(jí)持有sch-s鎖?？梢允褂胻ransact-sql或通過數(shù)據(jù)庫api來設(shè)置事務(wù)隔離級(jí)別。transact-sqltransact-sql 腳木使用 set transaction isolation level

17、語句。adoado 應(yīng)用程序?qū)?connection 對(duì)象的 isolationlevel屬性設(shè)置為 adxactreaduncommitted 、 adxactreadcommitted 、 adxactrepeatableread 或 adxactreadserializableoado.net使用 system.data.sqlclient托管命名空間的 ado.net應(yīng)用程序可以調(diào)用 sqlconnection.begintransaction 方法并將 isolationlevel 選項(xiàng)設(shè)置為 unspecified - chaos、readuncommittedreadcommit

18、ted' repeatableread> serializable 或 snapshot。ole db開始事務(wù)時(shí),使用ole db的應(yīng)用程序調(diào)用itransactionlocal:starttransaction>其中iso levelisolationlevel readuncommittedisolationlevel_readcommitted 、 isolationlevel_repeatableread 、isolationlevel_snapshot 或 isolationlevel_serializable。在自動(dòng)提交模式下指定事務(wù)隔離級(jí)別時(shí)，ole db應(yīng)川程序可以捋dbpropset_session屬性odbcdbprop_sess_autocommitisolevelsdbpropval_ti_readuncommitteddbpropval_ti_cursorstabil