VFP 愛用者社區

Ruey · 註冊時間: 2003-03-12 文章: 1698 來自: tunglo 第 1 樓

來源:雅狐

轉載《SQL7中的LOCK的理解1》
lock是資料庫中的一個比較重要，但又容易被忽視的問題。

以前在CTEC上課時，總有學員聽的時候懂，但考試時有不太瞭解。
在編程中，又因所用的編程工具的不同而不能很好的解決lock的問題。

下面我將SQL7中的lock做個簡單的介紹，

小型資料庫以及大多數的MIS系統，對lock沒什麼要求，
原因是資料量小，併發少，用戶端的回應要求不高。

由此可見，上面三個因素正是我們實現lock策略的目的。

在SQL7中，QueryAnalyzer是一個非常好的工具，用它可以清楚的分析
不同的語句在不同的環境下產生的鎖，併發性操作等等。

簡單的說，就是利用sp_Lock
和隱式交易來做到這一點。

首先需要強調的是lock完全依賴你的需要。並不是說lock越多越好。或是lock
越少越好。我們應該制定合適的lock策略來滿足我們的需要。那就是在併發性和資料完整性的平衡點。
用過word的都知道，user1打開了一個word文擋，user2打開同一個文擋時，word會有提示告之user2只能在唯讀方式下工作。在這裏，為了保護文擋的資料完整性(user1加了lock)，併發操作受到了限制(user2 readonly)。SQL7也有同樣的情況。

其次，我們要認識到lock是由SQL7來產生的，也是由SQL7來管理的，lock產生的數量和種類依賴我們的sql語句和環境。我們通常是通過控制環境變數來間接控制lock的產生，（雖然SQL7支持在sql語句中直接加鎖，但這種用法少）。

再有的就是lock的生命期。當語句執行時產生鎖，當語句結束時lock被釋放，在事務中lock要到事務結束才釋放。從這個特點中我們就可以找到觀察lock的方法。那就是不讓交易結束。SQL7的隱式交易就具有這種能力，需要說明的是，SQL7的隱式交易經常被用於改寫production資料庫中的資料。當改寫正確時才提交，避免出錯。SQL7的隱式交易有很強的實踐意義。

轉載《SQL7中的LOCK的理解2》

SQL7中鎖的種類很多，有DB級，表級，PAGE級，行級(key)，行級是7中的新功能，這也是大型資料庫的一個標誌。但即使這樣，SQL7的鎖也不如DB2和ORACLE。我們最關心的是表級和行級，這裏介紹鎖主要目的是為了瞭解在SP_LOCK中出現的資訊的含義。

先介紹表級，主要兩種，S，X。 S和S相容，X和任何都不相容。例：
statement1 在表A上施加了S，此時如果statement2在表A上也將施加S，那麼statement2就可以被執行(併發)，如果statement2在表A上將施加X，那麼statement2將等待，直到statement1執行完後(S被釋放)才被執行(沒了併發)。

如果statement1 在表A上施加了X，此時如果statement2在表A上也將施加S或X，那麼statement2將等待，直到statement1執行完後(X被釋放)才被執行(沒了併發)。

接著介紹行級，從表級鎖就可以看出，表級鎖對併發的支援有限，因此大型資料庫才利用行級鎖來支持更多的併發。行級鎖也主要有兩種，S，X。 S和S相容，X和任何都不相容。當SQL7實現行級鎖的時候，它自動在表上施加表級鎖IS，IX。
也就是只有在表上先施加IS，才能在行上施加S，只有在表上先施加IX，才能在行上施加X。一一對應。例：
statement1 在表A上的行A施加了S，自然在表A上施加了IS，此時如果statement2在表A的任一行上將施加S(自然在表A上施加了IS)，那麼statement2就可以被執行(併發)，如果statement2在表A上除行A上將施加X(自然在表A上施加了IX)，那麼statement2也可以被執行(併發)，如果statement2在表A上行A上將施加X(自然在表A上施加了IX)，那麼statement2將等待，直到statement1執行完後(S被釋放)才被執行(沒了併發)。

在表級鎖中還有一種是SIX，也就是首先在表級加S，同時需要在行級加X。

從上面的介紹就可以得到各表級鎖，S X IS IX SIX的相容關係。我們也可以從後面的實驗來驗證這一點。

實驗：實驗中要建立到DB的兩個連接。其中一個連接運行在隱含交易下。
目標一，在運行在隱含交易下的連接裏，運行DML，再運行sp_lock,理解DML語句所產生的鎖的不同。
目標二，在運行在隱含交易下的連接裏，設置不同的事物隔離級別，再運行DML，理解在不同隔離級別下的同一語句所產生不同的鎖。
目標三，同時在另一連接中運行不同的DML，通過執行情況，來驗證鎖的相容性。

轉載《SQL7中的LOCK的理解3》

在實際情況中，通常會因為lock發生四中情況。
下面用個定座系統舉例：所有的語句都工作在默認隔離級別下
1 丟失的更新
user1 run
begin tran
select 空座位得到1，2，3
update 1 定座位1
commit

如果user2在user1 update之前運行同樣的程式，都會得到空座位是1,2,3的資訊

，當然user2也可以定座位1。結果就是user2的定座是最終有效的，user1的定座

被覆蓋。為了避免這種情況，看看用隔離級別能不能解決。
這四種隔離級別最多在表上施加S，由於S是相容的，因此user2照樣能得到空座位

是1,2,3的資訊，丟失的更新也照樣會發生。SQL提供了表級鎖定選項TABLOCKX，
能避免這種情況的發生，但喪失了併發。

2 髒讀
user1 run
begin tran
update row A
commit

如果user2能在user1 update的同時讀出表中的資料(包含 row A) 那麼就產生了

髒讀，因為user1可能commit也可能rollback，update並沒有被確認。默認情況下

是不會產生髒讀的，因為update將產生行的X，表的IX，而表IX和S是不相容的。

那麼在什麼情況下我們需要髒讀呢，如果我們不需要確切的資料，我們需要的是

一個大概的趨勢，個別資料的不確定沒有關係，想想一個股票系統，同一時刻的

改寫會很多，也就是表上會有許多IX鎖，如果我們需要查詢表獲得大致情況，那

麼只有等改寫結束，為了提高併發，此時我們需要髒讀。解決方法就是在user2的

會話環境中設置事務隔離級別為read uncommitted

3 不可重複讀
user1 run
begin tran
select tab1 where
...
...
select tab1 where
commit

當user1在交易中執行兩次select，所得到的結果不一樣，這便是不可重複讀。
發生的原因是user2在user1第二次select之前對tab1做了修改。select語句雖然

對表有讀鎖，但在默認情況下，讀鎖在select語句執行完就被釋放，而不是保持

到交易結束。為了避免不可重複讀的發生，只要select產生的鎖保持到交易結束

就可以了，將user1的會話環境中設置事務隔離級別為repeatable read

4 幻影
user1 run
begin tran
select tab1 where
...
...
select tab1 where
commit
雖然將user1的會話環境中設置事務隔離級別為repeatable read，但當user1在交

易中執行兩次select，所得到的結果還會不一樣，這便是幻影。因為user1僅僅將

select出來的行加了S鎖，但user2可以insert滿足where條件的新行，使得兩次

select的結果不一樣，解決方法是將user1的會話環境中設置事務隔離級別為

serializable,或者在表級鎖定選項中選holdlock

鎖定超時：
前面說過，當某個語句因為鎖而不能立即執行時，會等待，直到鎖被釋放。但如

果持有鎖的語句執行時間過長(未優化)。那麼就會等待過長，影響響應，因此，

我們通過lock_timeout來將等待過長的語句rollback。需要注意的是，當資料量

增加時，可能發生不希望的lock_timeout。原因是SQL需要更多的時間來處理資料

，為此我們也需要適當的增加lock_timeout。