| 導購 | 订阅 | 在线投稿
分享
 
 
當前位置: 王朝網路 >> oracle >> Oracle、SQL Server中如何鎖定DB和解鎖DB
 

Oracle、SQL Server中如何鎖定DB和解鎖DB

2008-08-07 07:35:02  編輯來源:互聯網  简体版  手機版  評論  字體: ||
 
 
  今天有同事問到關于多人同時更新和操作DB時,如何防止數據沖突的問題,整理了一篇文檔,雖然不是原創,但對于理解數據庫的鎖定,DB解鎖等都有一定的幫助。

  一. 爲什麽要引入鎖

  多個用戶同時對數據庫的並發操作時會帶來以下數據不一致的問題:

  丟失更新

  A,B兩個用戶讀同一數據並進行修改,其中一個用戶的修改結果破壞了另一個修改的結果,比如訂票系統

  髒讀

  A用戶修改了數據,隨後B用戶又讀出該數據,但A用戶因爲某些原因取消了對數據的修改,數據恢複原值,此時B得到的數據就與數據庫內的數據産生了不一致

  不可重複讀

  A用戶讀取數據,隨後B用戶讀出該數據並修改,此時A用戶再讀取數據時發現前後兩次的值不一致

  並發控制的主要方法是封鎖,鎖就是在一段時間內禁止用戶做某些操作以避免産生數據不一致

  二 鎖的分類

  鎖的類別有兩種分法:

  1. 從數據庫系統的角度來看:分爲獨占鎖(即排它鎖),共享鎖和更新鎖

  MS-SQL Server 使用以下資源鎖模式。

  鎖模式 描述

  共享 (S) 用于不更改或不更新數據的操作(只讀操作),如 SELECT 語句。

  更新 (U) 用于可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。

  排它 (X) 用于數據修改操作,例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。

  意向鎖 用于建立鎖的層次結構。意向鎖的類型爲:意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

  架構鎖 在執行依賴于表架構的操作時使用。架構鎖的類型爲:架構修改 (Sch-M) 和架構穩定性 (Sch-S)。

  大容量更新 (BU) 向表中大容量複制數據並指定了 TABLOCK 提示時使用。

  共享鎖

  共享 (S) 鎖允許並發事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時,任何其它事務都不能修改數據。一旦已經讀取數據,便立即釋放資源上的共享 (S) 鎖,除非將事務隔離級別設置爲可重複讀或更高級別,或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。

  更新鎖

  更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成,此事務讀取記錄,獲取資源(頁或行)的共享 (S) 鎖,然後修改行,此操作要求鎖轉換爲排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖,然後試圖同時更新數據,則一個事務嘗試將鎖轉換爲排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間,因爲一個事務的排它鎖與其它事務的共享模式鎖不兼容;發生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由于兩個事務都要轉換爲排它 (X) 鎖,並且每個事務都等待另一個事務釋放共享模式鎖,因此發生死鎖。

  若要避免這種潛在的死鎖問題,請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源,則更新 (U) 鎖轉換爲排它 (X) 鎖。否則,鎖轉換爲共享鎖。

  排它鎖

  排它 (X) 鎖可以防止並發事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數據。

  意向鎖

  意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如,放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨後在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能,因爲 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

  意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

  鎖模式 描述

  意向共享 (IS) 通過在各資源上放置 S 鎖,表明事務的意向是讀取層次結構中的部分(而不是全部)底層資源。

  意向排它 (IX) 通過在各資源上放置 X 鎖,表明事務的意向是修改層次結構中的部分(而不是全部)底層資源。IX 是 IS 的超集。

  與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖,表明事務的意向是讀取層次結構中的全部底層資源並修改部分(而不是全部)底層資源。允許頂層資源上的並發 IS 鎖。例如,表的 SIX 鎖在表上放置一個 SIX 鎖(允許並發 IS 鎖),在當前所修改頁上放置 IX 鎖(在已修改行上放置 X 鎖)。雖然每個資源在一段時間內只能有一個 SIX 鎖,以防止其它事務對資源進行更新,但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

  獨占鎖:只允許進行鎖定操作的程序使用,其他任何對他的操作均不會被接受。執行數據更新命令時,SQL Server會自動使用獨占鎖。當對象上有其他鎖存在時,無法對其加獨占鎖。

  共享鎖:共享鎖鎖定的資源可以被其他用戶讀取,但其他用戶無法修改它,在執行Select時,SQL Server會對對象加共享鎖。

  更新鎖:當SQL Server准備更新數據時,它首先對數據對象作更新鎖鎖定,這樣數據將不能被修改,但可以讀取。等到SQL Server確定要進行更新數據操作時,他會自動將更新鎖換爲獨占鎖,當對象上有其他鎖存在時,無法對其加更新鎖。

  2. 從程序員的角度看:分爲樂觀鎖和悲觀鎖

  樂觀鎖:完全依靠數據庫來管理鎖的工作。

  悲觀鎖:程序員自己管理數據或對象上的鎖處理。

  MS-SQLSERVER 使用鎖在多個同時在數據庫內執行修改的用戶間實現悲觀並發控制

  三 鎖的粒度

  鎖粒度是被封鎖目標的大小,封鎖粒度小則並發性高,但開銷大,封鎖粒度大則並發性低但開銷小

  SQL Server支持的鎖粒度可以分爲爲行、頁、鍵、鍵範圍、索引、表或數據庫獲取鎖

  資源 描述

  RID 行標識符。用于單獨鎖定表中的一行。

  鍵 索引中的行鎖。用于保護可串行事務中的鍵範圍。

  頁 8 千字節 (KB) 的數據頁或索引頁。

  擴展盤區 相鄰的八個數據頁或索引頁構成的一組。

  表 包括所有數據和索引在內的整個表。

  DB 數據庫。

  四 鎖定時間的長短

  鎖保持的時間長度爲保護所請求級別上的資源所需的時間長度。

  用于保護讀取操作的共享鎖的保持時間取決于事務隔離級別。采用 READ COMMITTED 的默認事務隔離級別時,只在讀取頁的期間內控制共享鎖。在掃描中,直到在掃描內的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務隔離級別設置爲 REPEATABLE READ 或 SERIALIZABLE,則直到事務結束才釋放鎖。

  根據爲遊標設置的並發選項,遊標可以獲取共享模式的滾動鎖以保護提取。當需要滾動鎖時,直到下一次提取或關閉遊標(以先發生者爲准)時才釋放滾動鎖。但是,如果指定 HOLDLOCK,則直到事務結束才釋放滾動鎖。

  用于保護更新的排它鎖將直到事務結束才釋放。

  如果一個連接試圖獲取一個鎖,而該鎖與另一個連接所控制的鎖沖突,則試圖獲取鎖的連接將一直阻塞到:

  將沖突鎖釋放而且連接獲取了所請求的鎖。

  連接的超時間隔已到期。默認情況下沒有超時間隔,但是一些應用程序設置超時間隔以防止無限期等待

  五 SQL Server 中鎖的自定義

  1 處理死鎖和設置死鎖優先級

  死鎖就是多個用戶申請不同封鎖,由于申請者均擁有一部分封鎖權而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

  可以使用SET DEADLOCK_PRIORITY控制在發生死鎖情況時會話的反應方式。如果兩個進程都鎖定數據,並且直到其它進程釋放自己的鎖時,每個進程才能釋放自己的鎖,即發生死鎖情況。

  2 處理超時和設置鎖超時持續時間

  @@LOCK_TIMEOUT 返回當前會話的當前鎖超時設置,單位爲毫秒

  SET LOCK_TIMEOUT 設置允許應用程序設置語句等待阻塞資源的最長時間。當語句等待的時間大于 LOCK_TIMEOUT 設置時,系統將自動取消阻塞的語句,並給應用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息

  示例

  下例將鎖超時期限設置爲 1,800 毫秒。

  SET LOCK_TIMEOUT 1800

  3) 設置事務隔離級別。

  4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。

  5) 配置索引的鎖定粒度

  可以使用 sp_indexoption 系統存儲過程來設置用于索引的鎖定粒度

  六 查看鎖的信息

  1 執行 EXEC SP_LOCK 報告有關鎖的信息

  2 查詢分析器中按Ctrl+2可以看到鎖的信息

  七 使用注意事項

  如何避免死鎖

  1 使用事務時,盡量縮短事務的邏輯處理過程,及早提交或回滾事務;

  2 設置死鎖超時參數爲合理範圍,如:3分鍾-10分種;超過時間,自動放棄本次操作,避免進程懸挂;

  3 優化程序,檢查並避免死鎖現象出現;

  4 .對所有的腳本和SP都要仔細測試,在正是版本之前。

  5 所有的SP都要有錯誤處理(通過@error)

  6 一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖

  解決問題 如何對行 表 數據庫加鎖

  八 幾個有關鎖的問題

  1 如何鎖一個表的某一行

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  SELECT * FROM table ROWLOCK WHERE id = 1

  2 鎖定數據庫的一個表

  SELECT * FROM table WITH (HOLDLOCK)

  加鎖語句:

  sybase:

  update 表 set col1=col1 where 1=0 ;

  MSSQL:

  select col1 from 表 (tablockx) where 1=0 ;

  oracle:

  LOCK TABLE 表 IN EXCLUSIVE MODE ;

  加鎖後其它人不可操作,直到加鎖用戶解鎖,用commit或rollback解鎖

  幾個例子幫助大家加深印象

  設table1(A,B,C)

  A B C

  a1 b1 c1

  a2 b2 c2

  a3 b3 c3

  1)排它鎖

  新建兩個連接

  在第一個連接中執行以下語句

  begin tran

  update table1

  set A='aa'

  where B='b2'

  waitfor delay '00:00:30' --等待30秒

  commit tran

  在第二個連接中執行以下語句

  begin tran

  select * from table1

  where B='b2'

  commit tran

  若同時執行上述兩個語句,則select查詢必須等待update執行完畢才能執行即要等待30秒

  2)共享鎖

  在第一個連接中執行以下語句

  begin tran

  select * from table1 holdlock -holdlock人爲加鎖

  where B='b2'

  waitfor delay '00:00:30' --等待30秒

  commit tran

  在第二個連接中執行以下語句

  begin tran

  select A,C from table1

  where B='b2'

  update table1

  set A='aa'

  where B='b2'

  commit tran

  若同時執行上述兩個語句,則第二個連接中的select查詢可以執行

  而update必須等待第一個事務釋放共享鎖轉爲排它鎖後才能執行 即要等待30秒

  3)死鎖

  增設table2(D,E)

  D E

  d1 e1

  d2 e2

  在第一個連接中執行以下語句

  begin tran

  update table1

  set A='aa'

  where B='b2'

  waitfor delay '00:00:30'

  update table2

  set D='d5'

  where E='e1'

  commit tran

  在第二個連接中執行以下語句

  begin tran

  update table2

  set D='d5'

  where E='e1'

  waitfor delay '00:00:10'

  update table1

  set A='aa'

  where B='b2'

  commit tran

  同時執行,系統會檢測出死鎖,並中止進程

  補充一點:

  Sql Server2000支持的表級鎖定提示

  HOLDLOCK 持有共享鎖,直到整個事務完成,應該在被鎖對象不需要時立即釋放,等于SERIALIZABLE事務隔離級別

  NOLOCK 語句執行時不發出共享鎖,允許髒讀 ,等于 READ UNCOMMITTED事務隔離級別

  PAGLOCK 在使用一個表鎖的地方用多個頁鎖

  READPAST 讓sql server跳過任何鎖定行,執行事務,適用于READ UNCOMMITTED事務隔離級別只跳過RID鎖,不跳過頁,區域和表鎖

  ROWLOCK 強制使用行鎖

  TABLOCKX 強制使用獨占表級鎖,這個鎖在事務期間阻止任何其他事務使用這個表

  UPLOCK 強制在讀表時使用更新而不用共享鎖

  應用程序鎖:

  應用程序鎖就是客戶端代碼生成的鎖,而不是sql server本身生成的鎖

  處理應用程序鎖的兩個過程

  sp_getapplock 鎖定應用程序資源

  sp_releaseapplock 爲應用程序資源解鎖

  注意: 鎖定數據庫的一個表的區別

  SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表,但不能更新刪除

  SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除
 
 
 
上一篇《Oracle 10g使用RMAN創建physical standby》
下一篇《Oracle 11g for linux X86的SILENT安裝》
 
 
 
日版寵物情人插曲《Winding Road》歌詞

日版寵物情人2017的插曲,很帶節奏感,日語的,女生唱的。 最後聽見是在第8集的時候女主手割傷了,然後男主用嘴幫她吸了一下,插曲就出來了。 歌手:Def...

兄弟共妻,我成了他們夜裏的美食

老鍾家的兩個兒子很特別,就是跟其他的人不太一樣,魔一般的執著。兄弟倆都到了要結婚的年齡了,不管自家老爹怎麽磨破嘴皮子,兄弟倆說不娶就不娶,老父母爲兄弟兩操碎了心...

如何磨出破洞牛仔褲?牛仔褲怎麽剪破洞?

把牛仔褲磨出有線的破洞 1、具體工具就是磨腳石,下面墊一個硬物,然後用磨腳石一直磨一直磨,到把那塊磨薄了,用手撕開就好了。出來的洞啊很自然的。需要貓須的話調幾...

我就是掃描下圖得到了敬業福和愛國福

先來看下敬業福和愛國福 今年春節,支付寶再次推出了“五福紅包”活動,表示要“把欠大家的敬業福都還給大家”。 今天該活動正式啓動,和去年一樣,需要收集“五福”...

冰箱異味産生的原因和臭味去除的方法

有時候我們打開冰箱就會聞到一股異味,冰箱裏的這種異味是因爲一些物質發出的氣味的混合體,聞起來讓人惡心。 産生這些異味的主要原因有以下幾點。 1、很多人有這種習...

《極品家丁》1-31集大結局分集劇情介紹

簡介 《極品家丁》講述了現代白領林晚榮無意回到古代金陵,並追隨蕭二小姐化名“林三”進入蕭府,不料卻陰差陽錯上演了一出低級家丁拼搏上位的“林三升職記”。...

李溪芮《極品家丁》片尾曲《你就是我最愛的寶寶》歌詞

你就是我最愛的寶寶 - 李溪芮 (電視劇《極品家丁》片尾曲) 作詞:常馨內 作曲:常馨內 你的眉 又鬼馬的挑 你的嘴 又壞壞的笑 上一秒吵鬧 下...

烏梅的功效與作用以及烏梅的食用禁忌有哪些?

烏梅,又稱春梅,中醫認爲,烏梅味酸,性溫,無毒,具有安心、除熱、下氣、祛痰、止渴調中、殺蟲的功效,治肢體痛、肺痨病。烏梅泡水喝能治傷寒煩熱、止吐瀉,與幹姜一起制...

什麽是脂肪粒?如何消除臉部脂肪粒?

什麽是脂肪粒 在我們的臉上總會長一個個像脂肪的小顆粒,弄也弄不掉,而且顔色還是白白的。它既不是粉刺也不是其他的任何痘痘,它就是脂肪粒。 脂肪粒雖然也是由油脂...

網絡安全治理:國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰受害者

來源:中國青年報 新的攻擊方法不斷湧現,黑客幾乎永遠占據網絡攻擊的上風,我們不可能通過技術手段杜絕網絡攻擊。國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰...

 
 
 
今天有同事問到關于多人同時更新和操作DB時,如何防止數據沖突的問題,整理了一篇文檔,雖然不是原創,但對于理解數據庫的鎖定,DB解鎖等都有一定的幫助。 一. 爲什麽要引入鎖 多個用戶同時對數據庫的並發操作時會帶來以下數據不一致的問題: 丟失更新 A,B兩個用戶讀同一數據並進行修改,其中一個用戶的修改結果破壞了另一個修改的結果,比如訂票系統 髒讀 A用戶修改了數據,隨後B用戶又讀出該數據,但A用戶因爲某些原因取消了對數據的修改,數據恢複原值,此時B得到的數據就與數據庫內的數據産生了不一致 不可重複讀 A用戶讀取數據,隨後B用戶讀出該數據並修改,此時A用戶再讀取數據時發現前後兩次的值不一致 並發控制的主要方法是封鎖,鎖就是在一段時間內禁止用戶做某些操作以避免産生數據不一致 二 鎖的分類 鎖的類別有兩種分法: 1. 從數據庫系統的角度來看:分爲獨占鎖(即排它鎖),共享鎖和更新鎖 MS-SQL Server 使用以下資源鎖模式。 鎖模式 描述 共享 (S) 用于不更改或不更新數據的操作(只讀操作),如 SELECT 語句。 更新 (U) 用于可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。 排它 (X) 用于數據修改操作,例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。 意向鎖 用于建立鎖的層次結構。意向鎖的類型爲:意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。 架構鎖 在執行依賴于表架構的操作時使用。架構鎖的類型爲:架構修改 (Sch-M) 和架構穩定性 (Sch-S)。 大容量更新 (BU) 向表中大容量複制數據並指定了 TABLOCK 提示時使用。 共享鎖 共享 (S) 鎖允許並發事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時,任何其它事務都不能修改數據。一旦已經讀取數據,便立即釋放資源上的共享 (S) 鎖,除非將事務隔離級別設置爲可重複讀或更高級別,或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。 更新鎖 更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成,此事務讀取記錄,獲取資源(頁或行)的共享 (S) 鎖,然後修改行,此操作要求鎖轉換爲排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖,然後試圖同時更新數據,則一個事務嘗試將鎖轉換爲排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間,因爲一個事務的排它鎖與其它事務的共享模式鎖不兼容;發生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由于兩個事務都要轉換爲排它 (X) 鎖,並且每個事務都等待另一個事務釋放共享模式鎖,因此發生死鎖。 若要避免這種潛在的死鎖問題,請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源,則更新 (U) 鎖轉換爲排它 (X) 鎖。否則,鎖轉換爲共享鎖。 排它鎖 排它 (X) 鎖可以防止並發事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數據。 意向鎖 意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如,放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨後在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能,因爲 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。 意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。 鎖模式 描述 意向共享 (IS) 通過在各資源上放置 S 鎖,表明事務的意向是讀取層次結構中的部分(而不是全部)底層資源。 意向排它 (IX) 通過在各資源上放置 X 鎖,表明事務的意向是修改層次結構中的部分(而不是全部)底層資源。IX 是 IS 的超集。 與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖,表明事務的意向是讀取層次結構中的全部底層資源並修改部分(而不是全部)底層資源。允許頂層資源上的並發 IS 鎖。例如,表的 SIX 鎖在表上放置一個 SIX 鎖(允許並發 IS 鎖),在當前所修改頁上放置 IX 鎖(在已修改行上放置 X 鎖)。雖然每個資源在一段時間內只能有一個 SIX 鎖,以防止其它事務對資源進行更新,但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。 獨占鎖:只允許進行鎖定操作的程序使用,其他任何對他的操作均不會被接受。執行數據更新命令時,SQL Server會自動使用獨占鎖。當對象上有其他鎖存在時,無法對其加獨占鎖。 共享鎖:共享鎖鎖定的資源可以被其他用戶讀取,但其他用戶無法修改它,在執行Select時,SQL Server會對對象加共享鎖。 更新鎖:當SQL Server准備更新數據時,它首先對數據對象作更新鎖鎖定,這樣數據將不能被修改,但可以讀取。等到SQL Server確定要進行更新數據操作時,他會自動將更新鎖換爲獨占鎖,當對象上有其他鎖存在時,無法對其加更新鎖。 2. 從程序員的角度看:分爲樂觀鎖和悲觀鎖 樂觀鎖:完全依靠數據庫來管理鎖的工作。 悲觀鎖:程序員自己管理數據或對象上的鎖處理。 MS-SQLSERVER 使用鎖在多個同時在數據庫內執行修改的用戶間實現悲觀並發控制 三 鎖的粒度 鎖粒度是被封鎖目標的大小,封鎖粒度小則並發性高,但開銷大,封鎖粒度大則並發性低但開銷小 SQL Server支持的鎖粒度可以分爲爲行、頁、鍵、鍵範圍、索引、表或數據庫獲取鎖 資源 描述 RID 行標識符。用于單獨鎖定表中的一行。 鍵 索引中的行鎖。用于保護可串行事務中的鍵範圍。 頁 8 千字節 (KB) 的數據頁或索引頁。 擴展盤區 相鄰的八個數據頁或索引頁構成的一組。 表 包括所有數據和索引在內的整個表。 DB 數據庫。 四 鎖定時間的長短 鎖保持的時間長度爲保護所請求級別上的資源所需的時間長度。 用于保護讀取操作的共享鎖的保持時間取決于事務隔離級別。采用 READ COMMITTED 的默認事務隔離級別時,只在讀取頁的期間內控制共享鎖。在掃描中,直到在掃描內的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務隔離級別設置爲 REPEATABLE READ 或 SERIALIZABLE,則直到事務結束才釋放鎖。 根據爲遊標設置的並發選項,遊標可以獲取共享模式的滾動鎖以保護提取。當需要滾動鎖時,直到下一次提取或關閉遊標(以先發生者爲准)時才釋放滾動鎖。但是,如果指定 HOLDLOCK,則直到事務結束才釋放滾動鎖。 用于保護更新的排它鎖將直到事務結束才釋放。 如果一個連接試圖獲取一個鎖,而該鎖與另一個連接所控制的鎖沖突,則試圖獲取鎖的連接將一直阻塞到: 將沖突鎖釋放而且連接獲取了所請求的鎖。 連接的超時間隔已到期。默認情況下沒有超時間隔,但是一些應用程序設置超時間隔以防止無限期等待 五 SQL Server 中鎖的自定義 1 處理死鎖和設置死鎖優先級 死鎖就是多個用戶申請不同封鎖,由于申請者均擁有一部分封鎖權而又等待其他用戶擁有的部分封鎖而引起的無休止的等待 可以使用SET DEADLOCK_PRIORITY控制在發生死鎖情況時會話的反應方式。如果兩個進程都鎖定數據,並且直到其它進程釋放自己的鎖時,每個進程才能釋放自己的鎖,即發生死鎖情況。 2 處理超時和設置鎖超時持續時間 @@LOCK_TIMEOUT 返回當前會話的當前鎖超時設置,單位爲毫秒 SET LOCK_TIMEOUT 設置允許應用程序設置語句等待阻塞資源的最長時間。當語句等待的時間大于 LOCK_TIMEOUT 設置時,系統將自動取消阻塞的語句,並給應用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息 示例 下例將鎖超時期限設置爲 1,800 毫秒。 SET LOCK_TIMEOUT 1800 3) 設置事務隔離級別。 4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。 5) 配置索引的鎖定粒度 可以使用 sp_indexoption 系統存儲過程來設置用于索引的鎖定粒度 六 查看鎖的信息 1 執行 EXEC SP_LOCK 報告有關鎖的信息 2 查詢分析器中按Ctrl+2可以看到鎖的信息 七 使用注意事項 如何避免死鎖 1 使用事務時,盡量縮短事務的邏輯處理過程,及早提交或回滾事務; 2 設置死鎖超時參數爲合理範圍,如:3分鍾-10分種;超過時間,自動放棄本次操作,避免進程懸挂; 3 優化程序,檢查並避免死鎖現象出現; 4 .對所有的腳本和SP都要仔細測試,在正是版本之前。 5 所有的SP都要有錯誤處理(通過@error) 6 一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖 解決問題 如何對行 表 數據庫加鎖 八 幾個有關鎖的問題 1 如何鎖一個表的某一行 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM table ROWLOCK WHERE id = 1 2 鎖定數據庫的一個表 SELECT * FROM table WITH (HOLDLOCK) 加鎖語句: sybase: update 表 set col1=col1 where 1=0 ; MSSQL: select col1 from 表 (tablockx) where 1=0 ; oracle: LOCK TABLE 表 IN EXCLUSIVE MODE ; 加鎖後其它人不可操作,直到加鎖用戶解鎖,用commit或rollback解鎖 幾個例子幫助大家加深印象 設table1(A,B,C) A B C a1 b1 c1 a2 b2 c2 a3 b3 c3 1)排它鎖 新建兩個連接 在第一個連接中執行以下語句 begin tran update table1 set A='aa' where B='b2' waitfor delay '00:00:30' --等待30秒 commit tran 在第二個連接中執行以下語句 begin tran select * from table1 where B='b2' commit tran 若同時執行上述兩個語句,則select查詢必須等待update執行完畢才能執行即要等待30秒 2)共享鎖 在第一個連接中執行以下語句 begin tran select * from table1 holdlock -holdlock人爲加鎖 where B='b2' waitfor delay '00:00:30' --等待30秒 commit tran 在第二個連接中執行以下語句 begin tran select A,C from table1 where B='b2' update table1 set A='aa' where B='b2' commit tran 若同時執行上述兩個語句,則第二個連接中的select查詢可以執行 而update必須等待第一個事務釋放共享鎖轉爲排它鎖後才能執行 即要等待30秒 3)死鎖 增設table2(D,E) D E d1 e1 d2 e2 在第一個連接中執行以下語句 begin tran update table1 set A='aa' where B='b2' waitfor delay '00:00:30' update table2 set D='d5' where E='e1' commit tran 在第二個連接中執行以下語句 begin tran update table2 set D='d5' where E='e1' waitfor delay '00:00:10' update table1 set A='aa' where B='b2' commit tran 同時執行,系統會檢測出死鎖,並中止進程 補充一點: Sql Server2000支持的表級鎖定提示 HOLDLOCK 持有共享鎖,直到整個事務完成,應該在被鎖對象不需要時立即釋放,等于SERIALIZABLE事務隔離級別 NOLOCK 語句執行時不發出共享鎖,允許髒讀 ,等于 READ UNCOMMITTED事務隔離級別 PAGLOCK 在使用一個表鎖的地方用多個頁鎖 READPAST 讓sql server跳過任何鎖定行,執行事務,適用于READ UNCOMMITTED事務隔離級別只跳過RID鎖,不跳過頁,區域和表鎖 ROWLOCK 強制使用行鎖 TABLOCKX 強制使用獨占表級鎖,這個鎖在事務期間阻止任何其他事務使用這個表 UPLOCK 強制在讀表時使用更新而不用共享鎖 應用程序鎖: 應用程序鎖就是客戶端代碼生成的鎖,而不是sql server本身生成的鎖 處理應用程序鎖的兩個過程 sp_getapplock 鎖定應用程序資源 sp_releaseapplock 爲應用程序資源解鎖 注意: 鎖定數據庫的一個表的區別 SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表,但不能更新刪除 SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除
󰈣󰈤
 
 
 
  免責聲明:本文僅代表作者個人觀點,與王朝網路無關。王朝網路登載此文出於傳遞更多信息之目的,並不意味著贊同其觀點或證實其描述,其原創性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,並請自行核實相關內容。
 
 
Afternoon Girl
精致的外景拍攝
零點一公分的距離
俏麗迷人 視覺享受
北海老城隨拍(二)
夢中的郭河
Gassin(法國最美麗小鎮之一) part
瑤池夕照
 
>>返回首頁<<
 
 熱帖排行
 
 
 
 
© 2005- 王朝網路 版權所有