理解鎖
在一個多用戶環境中並發是最重要的問題之一當多個會話向共享的資源讀取或寫入數據時數據可能丟失它的完整性為了避免出現這種情況每一個稱職的RDBMS都實現了並發控制機制對於RDBMS服務器通過各種鎖機制來管理並發所有RDBMS（包括MicrosoftAccess）都實現了一些復雜的並發管理機制盡管SQL標准為游標聲明了鎖定但鎖定既不是SQL的一部分也不是一種標准（游標是一種用於逐行訪問數據的特殊結構）
Oracle的鎖機制可能是最先進也是最復雜的它遵循這樣的規則讀取和寫入進程相互之間不能阻礙對方即使它們正在訪問相同（或接近）的數據集在Oracle中每一個會話將接收到一個讀一致的數據快照因此即使某些其他進程已經開始修改數據集中的數據但只要並未提交這些修改隨後的每一會話將依然能從快照中讀取到原來的數據一旦在第一個會話中提交了更改則其他每一個會話也都能看到更改後的數據僅當將修改提交到數據庫時才需要鎖Oracle自動選擇限制最小的鎖用戶可以選擇手動鎖定一個資源（例如一個表）在這種情況下根據使用的鎖類型其他用戶可能依然能夠訪問這些數據
IBM DB 和Microsoft SQL Server 都使用了鎖這兩種數據庫中的鎖允許讀取操作鎖定寫入操作或者寫入操作鎖定讀取操作並發訪問數據的問題在某種程度上由於鎖的粒度（就是對表頁列或行的鎖）得以緩解對於只讀的查詢DDL語句DML查詢等都需要獲得相應的鎖在絕大多數情況下用戶無須擔心鎖的問題RDBMS將自動地為特定的操作選擇最合適的鎖（或者多個鎖）只有在程序邏輯失敗的情況下才需要使用SQL語句以手動方式聲明鎖
MySQL本身僅在表級別上鎖定數據它依賴於支持事務的InnoDB（或者IBMDBI）存儲引擎和附加的行級別鎖定和事務支持PostgreSQL具有健壯的表級和行級鎖定機制每一種鎖定機制都有良好的粒度
鎖定模式
並發的模式可以分為兩大類樂觀並發和悲觀並發這兩個術語是不言自明的樂觀並發模式的事務假定當多個事務工作於同一個數據集時不太可能發生資源沖突的情況（盡管有可能發生沖突但將任何潛在的問題留給數據庫去解決）樂觀事務檢查當把修改提交給數據庫時潛在的沖突並通過重新提交數據來解決沖突的問題（在這種情況下最後提交的數據具有優先權）悲觀並發模式的事務設想從一開始就存在沖突並鎖定它們試圖使用的資源盡管悲觀並發模式能確保數據最高級別的一致性但它也是最昂貴的可能會使數據庫陷入停頓通常情況下RDBMS既支持樂觀並發模式也支持悲觀並發模式有時用戶可以指定事務使用其中一種並發模式悲觀並發模式提供了更好的一致性但必須付出性能的代價
鎖的粒度對數據庫系統的性能有顯著的影響行級別的鎖可以增加並發性（它不會阻塞其他事務對表的訪問）但也常常引起過多的管理開銷降低數據庫的性能就系統資源方面而言全表鎖定具有較小的開銷但代價就是降低了系統的並發性和性能在設計數據庫應用程序時應該注意這兩個問題
鎖用於實現悲觀並發模式的事務每一種RDBMS都具有自己的鎖級別但它們都是大同小異的通常根據使用資源的方式將其分為共享鎖和排他鎖
例如下面的語句以排他模式鎖定了LIBRARY數據庫的BOOKS表
LOCK TABLE books IN EXCLUSIVE MODE;
發出該語句的事務將試圖鎖定表以排他方式使用但是允許執行SELECT語句如果任何其他進程在該表上保持了一個鎖則該事務將被放到一個隊列中當接收到優先權時它將獲得鎖在該事務期間該鎖將被一直持有（直到執行了COMMIT語句）在不同的RDBMS中該語句實際的語法可能不盡相同但差別不大
鎖機制也帶來了一個潛在的問題如果一個已經在表上持有鎖的事務試圖獲得另外一個資源上的鎖但是第二個事務已經對該資源進行了鎖定就可能發生死鎖的情況（請參考下一小節）
某些數據庫特別是Microsoft SQL Server 和IBM DB 已經實現了一種稱為鎖提升的機制該術語指的是將很多個精細的鎖轉換為更粗放的高級別的鎖以減少系統在鎖管理上的開銷Oracle g/g希望在系統資源上具有更好的並發性因此它從不對鎖進行提升另外MySQL（使用InnoDB存儲引擎）和PostgreSQL都不支持鎖提升機制
所有的鎖都是由RDBMS酌情決定的（並非用戶配置的）默認的鎖是行級鎖可以將一個行級鎖提升為一個表級鎖可以使用前面介紹的LOCKTABLE語句提升鎖級別鎖提升的阈值則由DBA配置
高粒度的鎖（即行級別的鎖）是有代價的因為SQL Server需要為行級別的鎖定操作分配更多的資源因此它會降低SQL Server的性能另外也增加了死鎖發生的可能性
關於各個RDBMS中所支持的不同鎖類型之間的比較請參考本書的網站wwwwroxcom或者訪問wwwagilitatorcom
處理死鎖
發生死鎖的典型情形是兩個（或多個）會話都在等待獲得某個共享資源上的鎖但是這兩個會話都不能繼續執行因為第一個會話在第二個會話要求的資源上有一個鎖而第二個會話在第一個會話所要求的某個其他資源上也具有一個鎖可以設想這樣的情形會話持有資源A並試圖訪問資源B而會話持有資源B並且試圖訪問資源A在這種情況下就會發生死鎖
通常情況下RDBMS采用這樣的辦法來解決死鎖問題自動地殺掉其中一個進程並回滾該進程已經執行的所有操作
Oracle實現了一種復雜的機制用於執行如下規則讀取和寫入進程不能相互阻塞對方該規則背後的思想是為每一個進程提供一個一致的數據映像其中不包含未提交的更改盡管如此在Oracle中依然會發生死鎖問題並由RDBMS來進行判決在極少的情況下需要手動解決死鎖問題選擇犧牲一個死鎖的進程最常見的死鎖類型是ORA（排隊死鎖）和ORA（庫緩存死鎖）可以聲明NOWAIT子句或者設置會話超時來避免死鎖另外還可以使用其他一些技術來避免死鎖例如顯式鎖定和事務的隔離級別通過Oracle的界面也可以手動解決死鎖問題
IBM DB運行一個後台進程Deadlock Detector用於發現和解決死鎖問題被選定作為死鎖犧牲品的會話將被回滾並產生一個特殊的錯誤（SQLCODESQLSTATE）DB主要選擇只讀進程作為死鎖的犧牲品然後再應用最小開銷條件來選擇要殺掉的會話如果總是出現死鎖的問題IBM建議使用系統監視工具來收集關於死鎖情況的信息要麼優化系統要麼重新設計引起死鎖的應用程序
Microsoft SQL Server 采用了一種私有的死鎖檢測算法並采用與其他RDBMS實現的類似方法來解決死鎖問題Microsoft SQL Server 可以自動地處理死鎖問題用戶也可以通過Enterprise Manager Console手動處理死鎖問題通過在會話中設置DEADLOCK_PRIORITY參數Microsoft SQL Server 可以找到一個自願作為死鎖犧牲品的會話（請參考本章之前關於會話的討論）
PostgreSQL和MySQL試圖通過中止其中一個競爭的事務來解決死鎖的問題
總而言之避免死鎖的最佳實踐包括首先就運行較小的事務盡可能快地運行提交命令重構訪問表的邏輯減少顯式鎖定指令的使用等
返回目錄SQL實戰新手入門
編輯推薦
Oracle索引技術
高性能MySQL
數據倉庫與數據挖掘培訓視頻教程
From:http://tw.wingwit.com/Article/program/SQL/201311/16446.html
