|
對(duì)鎖機(jī)制的研究要具備兩個(gè)條件： 1．?dāng)?shù)據(jù)量大 2．多個(gè)用戶同時(shí)并發(fā) 如果缺少這兩個(gè)條件，數(shù)據(jù)庫(kù)不容易產(chǎn)生死鎖問題。研究起來可能會(huì)事倍功半。如果這兩個(gè)條件都有，但你還是按數(shù)據(jù)庫(kù)缺省設(shè)置來處理數(shù)據(jù)，則會(huì)帶來很多的問題，比如： 1）丟失更新 A,B兩個(gè)用戶讀同一數(shù)據(jù)并進(jìn)行修改,其中一個(gè)用戶的修改結(jié)果破壞了另一個(gè)修改的結(jié)果 2）臟讀 A用戶修改了數(shù)據(jù)時(shí),B用戶也在讀該數(shù)據(jù),但A用戶因?yàn)槟承┰蛉∠藢?duì)數(shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時(shí)B得到的數(shù)據(jù)就與數(shù)據(jù)庫(kù)內(nèi)的數(shù)據(jù)產(chǎn)生了不一致 3）不可重復(fù)讀 B用戶讀出該數(shù)據(jù)并修改,同時(shí)，A用戶也在讀取數(shù)據(jù)，此時(shí)A用戶再讀取數(shù)據(jù)時(shí)發(fā)現(xiàn)前后兩次的值不一致 SQL SERVER 作為多用戶數(shù)據(jù)庫(kù)系統(tǒng)，以事務(wù)為單位，使用鎖來實(shí)現(xiàn)并發(fā)控制。SQLSERVER使用“鎖”確保事務(wù)完整性和數(shù)據(jù)一致性。 一、鎖的概念 鎖（LOCKING）是最常用的并發(fā)控制機(jī)構(gòu)。是防止其他事務(wù)訪問指定的資源控制、實(shí)現(xiàn)并發(fā)控制的一種主要手段。鎖是事務(wù)對(duì)某個(gè)數(shù)據(jù)庫(kù)中的資源（如表和記錄）存取前，先向系統(tǒng)提出請(qǐng)求，封鎖該資源，事務(wù)獲得鎖后，即取得對(duì)數(shù)據(jù)的控制權(quán)，在事務(wù)釋放它的鎖之前，其他事務(wù)不能更新此數(shù)據(jù)。當(dāng)事務(wù)撤消后，釋放被鎖定的資源。 當(dāng)一個(gè)用戶鎖住數(shù)據(jù)庫(kù)中的某個(gè)對(duì)象時(shí)，其他用戶就不能再訪問該對(duì)象 二、鎖的粒度 SQL Server 2000 具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的的資源。為了使鎖定的成本減至最少，SQL Server 自動(dòng)將資源鎖定在適合任務(wù)的級(jí)別。鎖定在較小的粒度（例如行）可以增加并發(fā)但需要較大的開銷，因?yàn)槿绻i定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就并發(fā)而言是相當(dāng)昂貴的，因?yàn)殒i定整個(gè)表限制了其它事務(wù)對(duì)表中任意部分進(jìn)行訪問，但要求的開銷較低，因?yàn)樾枰S護(hù)的鎖較少。SQL Server 可以鎖定行、頁(yè)、擴(kuò)展盤區(qū)、表、庫(kù)等資源。
選擇多大的粒度，根據(jù)對(duì)數(shù)據(jù)的操作而定。如果是更新表中所有的行，則用表級(jí)鎖;如果是更新表中的某一行，則用行級(jí)鎖。 行級(jí)鎖是一種最優(yōu)鎖，因?yàn)樾屑?jí)鎖不可能出現(xiàn)數(shù)據(jù)既被占用又沒有使用的浪費(fèi)現(xiàn)象。但是，如果用戶事務(wù)中頻繁對(duì)某個(gè)表中的多條記錄操作，將導(dǎo)致對(duì)該表的許多記錄行都加上了行級(jí)鎖，數(shù)據(jù)庫(kù)系統(tǒng)中鎖的數(shù)目會(huì)急劇增加，這樣就加重了系統(tǒng)負(fù)荷，影響系統(tǒng)性能。因此，在SQL Server中，還支持鎖升級(jí)(lock escalation)。 所謂鎖升級(jí)是指調(diào)整鎖的粒度，將多個(gè)低粒度的鎖替換成少數(shù)的更高粒度的鎖，以此來降低系統(tǒng)負(fù)荷。在SQL Server中當(dāng)一個(gè)事務(wù)中的鎖較多，達(dá)到鎖升級(jí)門限時(shí)，系統(tǒng)自動(dòng)將行級(jí)鎖和頁(yè)面鎖升級(jí)為表級(jí)鎖。 特別值得注意的是，在SQL Server中，鎖的升級(jí)門限以及鎖升級(jí)是由系統(tǒng)自動(dòng)來確定的，不需要用戶設(shè)置。 三、鎖的模式 鎖模式以及描述表
鎖模式 描述 四 SQL Server 中鎖的設(shè)置 1 處理死鎖和設(shè)置死鎖優(yōu)先級(jí) 死鎖就是多個(gè)用戶申請(qǐng)不同封鎖,由于申請(qǐng)者均擁有一部分封鎖權(quán)而又等待其他用戶擁有的部分封鎖而引起的無休止的等待 可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時(shí)會(huì)話的反應(yīng)方式。 Syntax: SET DEADLOCK_PRIORITY { LOW | NORMAL} 其中LOW說明該進(jìn)程會(huì)話的優(yōu)先級(jí)較低，在出現(xiàn)死鎖時(shí)，可以首先中斷該進(jìn)程的事務(wù)。 2 處理超時(shí)和設(shè)置鎖超時(shí)持續(xù)時(shí)間。 @@LOCK_TIMEOUT 返回當(dāng)前會(huì)話的當(dāng)前鎖超時(shí)設(shè)置，單位為毫秒 SET LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長(zhǎng)時(shí)間。當(dāng)語句等待的時(shí)間大于 LOCK_TIMEOUT 設(shè)置時(shí)，系統(tǒng)將自動(dòng)取消阻塞的語句，并給應(yīng)用程序返回"已超過了鎖請(qǐng)求超時(shí)時(shí)段"的 1222 號(hào)錯(cuò)誤信息 示例 1）將鎖超時(shí)期限設(shè)置為 1,800 毫秒。 SET LOCK_TIMEOUT 1800 2) 配置索引的鎖定粒度 可以使用 sp_indexoption 系統(tǒng)存儲(chǔ)過程來設(shè)置用于索引的鎖定粒度 3）設(shè)置事務(wù)隔離級(jí)別 SET TRANSACTION ISOLATION LEVEL 五 查看鎖的信息 1 執(zhí)行 EXEC SP_LOCK 報(bào)告有關(guān)鎖的信息 2 查詢分析器中按Ctrl+2可以看到鎖的信息 六、奇怪的sql語句
update titles set title_idid＝title_id where 1＝2，這個(gè)條件是永遠(yuǎn)也不會(huì)成立的，如此寫的含義是什么呢？ 這里的where子句看起來很奇怪，盡管計(jì)算出的結(jié)果總是false。當(dāng)優(yōu)化器處理此查詢時(shí)，因?yàn)樗也坏饺魏斡行У腟ARG，它的查詢規(guī)劃就會(huì)強(qiáng)制使用一個(gè)獨(dú)占鎖定來進(jìn)行表掃描。此事務(wù)執(zhí)行時(shí)，where子句立即得到一個(gè)false值，于是不會(huì)執(zhí)行實(shí)際上的掃描，但此進(jìn)程仍得到了一個(gè)獨(dú)占的表鎖定。 因?yàn)榇诉M(jìn)程現(xiàn)在已有一個(gè)獨(dú)占的表鎖，所以可以保證沒有其他事務(wù)會(huì)修改任何數(shù)據(jù)行，能進(jìn)行重復(fù)讀，且避免了由于holdlock所引起的潛在性死鎖。 但是，在使用表鎖定來盡可能地減少死鎖的同時(shí)，也增加了對(duì)表鎖定的爭(zhēng)用。因此，在實(shí)現(xiàn)這種方法之前，你需要權(quán)衡一下：避免死鎖是否比允許并發(fā)地對(duì)表進(jìn)行訪問更重要。 所以，在這個(gè)事務(wù)中，沒有其他進(jìn)程修改表中任何行的price。 七 如何避免死鎖 1 使用事務(wù)時(shí)，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)； 2 設(shè)置死鎖超時(shí)參數(shù)為合理范圍，如：3分鐘-10分種；超過時(shí)間，自動(dòng)放棄本次操作，避免進(jìn)程懸掛； 3 所有的SP都要有錯(cuò)誤處理（通過@error） 4 一般不要修改SQL SERVER事務(wù)的默認(rèn)級(jí)別。不推薦強(qiáng)行加鎖 5 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)； 1）合理安排表訪問順序 2）在事務(wù)中盡量避免用戶干預(yù)，盡量使一個(gè)事務(wù)處理的任務(wù)少些。 3）采用臟讀技術(shù)。臟讀由于不對(duì)被訪問的表加鎖，而避免了鎖沖突。在客戶機(jī)/服務(wù)器應(yīng)用環(huán)境中，有些事務(wù)往往不允許讀臟數(shù)據(jù)，但在特定的條件下，我們可以用臟讀。 4）數(shù)據(jù)訪問時(shí)域離散法。數(shù)據(jù)訪問時(shí)域離散法是指在客戶機(jī)/服務(wù)器結(jié)構(gòu)中，采取各種控制手段控制對(duì)數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)中的對(duì)象訪問時(shí)間段。主要通過以下方式實(shí)現(xiàn): 合理安排后臺(tái)事務(wù)的執(zhí)行時(shí)間，采用工作流對(duì)后臺(tái)事務(wù)進(jìn)行統(tǒng)一管理。工作流在管理任務(wù)時(shí)，一方面限制同一類任務(wù)的線程數(shù)（往往限制為1個(gè)），防止資源過多占用; 另一方面合理安排不同任務(wù)執(zhí)行時(shí)序、時(shí)間，盡量避免多個(gè)后臺(tái)任務(wù)同時(shí)執(zhí)行，另外，避免在前臺(tái)交易高峰時(shí)間運(yùn)行后臺(tái)任務(wù) 5）數(shù)據(jù)存儲(chǔ)空間離散法。數(shù)據(jù)存儲(chǔ)空間離散法是指采取各種手段，將邏輯上在一個(gè)表中的數(shù)據(jù)分散到若干離散的空間上去，以便改善對(duì)表的訪問性能。主要通過以下方法實(shí)現(xiàn): 第一，將大表按行或列分解為若干小表; 第二，按不同的用戶群分解。 6）使用盡可能低的隔離性級(jí)別。隔離性級(jí)別是指為保證數(shù)據(jù)庫(kù)數(shù)據(jù)的完整性和一致性而使多用戶事務(wù)隔離的程度，SQL92定義了4種隔離性級(jí)別：未提交讀、提交讀、可重復(fù)讀和可串行。如果選擇過高的隔離性級(jí)別，如可串行，雖然系統(tǒng)可以因?qū)崿F(xiàn)更好隔離性而更大程度上保證數(shù)據(jù)的完整性和一致性，但各事務(wù)間沖突而死鎖的機(jī)會(huì)大大增加，大大影響了系統(tǒng)性能。 7）使用Bound Connections。Bound connections 允許兩個(gè)或多個(gè)事務(wù)連接共享事務(wù)和鎖，而且任何一個(gè)事務(wù)連接要申請(qǐng)鎖如同另外一個(gè)事務(wù)要申請(qǐng)鎖一樣，因此可以允許這些事務(wù)共享數(shù)據(jù)而不會(huì)有加鎖的沖突。 8）考慮使用樂觀鎖定或使事務(wù)首先獲得一個(gè)獨(dú)占鎖定。 八如何對(duì)行、 表、數(shù)據(jù)庫(kù)加鎖 1 如何鎖一個(gè)表的某一行
2 鎖定數(shù)據(jù)庫(kù)的一個(gè)表 select col1 from 表 (tablockx) where 1=1 ; 加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖 3.實(shí)例 建表
1）排它鎖
2）共享鎖
3）死鎖
九、sqlserver提供的表級(jí)鎖 sqlserver所指定的表級(jí)鎖定提示有如下幾種 1． HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語句執(zhí)行完立即釋放所添加的鎖。 2． NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語句。 3． PAGLOCK：指定添加頁(yè)鎖（否則通?？赡芴砑颖礞i） 4． READCOMMITTED用與運(yùn)行在提交讀隔離級(jí)別的事務(wù)相同的鎖語義執(zhí)行掃描。默認(rèn)情況下，SQL Server 2000 在此隔離級(jí)別上操作。 5． READPAST: 跳過已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應(yīng)用于READ COMMITTED隔離性級(jí)別下事務(wù)操作中的SELECT語句操作 6． READUNCOMMITTED：等同于NOLOCK。 7． REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別。 8． ROWLOCK：使用行級(jí)鎖，而不使用粒度更粗的頁(yè)級(jí)鎖和表級(jí)鎖。 9． SERIALIZABLE：用與運(yùn)行在可串行讀隔離級(jí)別的事務(wù)相同的鎖語義執(zhí)行掃描。等同于 HOLDLOCK。 10． TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁(yè)面級(jí)的鎖，SQL Server在該語句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束。 11． TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數(shù)據(jù)，直到這個(gè)語句或整個(gè)事務(wù)結(jié)束。 12． UPDLOCK ：指定在讀表中數(shù)據(jù)時(shí)設(shè)置更新鎖（update lock）而不是設(shè)置共享鎖，該鎖一直保持到這個(gè)語句或整個(gè)事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶先讀取數(shù)據(jù)（而且不阻塞其他用戶讀數(shù)據(jù)），并且保證在后來再更新數(shù)據(jù)時(shí)，這一段時(shí)間內(nèi)這些數(shù)據(jù)沒有被其他用戶修改 SELECT * FROM table WITH (HOLDLOCK) 其他事務(wù)可以讀取表，但不能更新刪除 SELECT * FROM table WITH (TABLOCKX) 其他事務(wù)不能讀取表,更新和刪除 十、應(yīng)用程序鎖 應(yīng)用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖處理應(yīng)用程序鎖的兩個(gè)系統(tǒng)存儲(chǔ)過程 sp_getapplock： 鎖定應(yīng)用程序資源 sp_releaseapplock： 為應(yīng)用程序資源解鎖 |
|
|
