SQLServer學習筆記系列8

標籤：

一.寫在前面的話

最近一直在思考一個問題，什麼才能讓我們不顯得浮躁，真正的靜下心來，用心去感受，用心去回答每個人的問題，用心去協助別人。現實的生活，往往讓我們顯得精疲力盡，然後我們仔細想過沒用，其實支援我們一直走下去的其實是心裡的那一份希冀，希望未來會更好，用一個逼格比較高的詞語，或許那就是理想。那麼，有人會說，什麼是理想，因為生活的重擔已將我們所謂的理想磨滅了。但是，那又何妨了，而今，我已經被打敗過了，我用曾經的飛蛾撲火，換來今天手心裡握著的一把餘溫尚存的灰燼。值得慶幸的是，我依然沒有忘記，這把灰燼的名字叫做理想。順勢而為的同時對事物執著而堅定，向著目標努力，理想也許也就不遠了，所以理想還是要有的，萬一實現了呢？

                         人的活動如果沒有理想的鼓舞，就會變得空虛而渺小。 —— 車爾尼雪夫斯基

 

二.表的約束

在我們建立表的時候，為了保證資料的正確性和完整性，通常我們需要對資料進行校正，也就是我們這裡說到的表的約束。其中常用的表的約束包括：

（1）主鍵的約束。

（2）唯一的約束。

（3）非空的約束。

（4）檢查的約束。

（5）外鍵的約束

這裡我們首先建立一張班級表，同時建立對應的約束：

其中班級的id和班級名稱分別定義了主鍵約束和唯一約束，主鍵約束保證了表中只存在唯一一列，唯一約束確保該欄欄位值不能重複，必須唯一。

接著我們定義一張學生表，也建立相應的約束。

通過建表我們可以看到，其中學生的編號（studid）定義為identity，意思是欄位自增長並且從100開始增長，每次增長1。其中性別（gender）存在一個檢查約束，保證插入到資料表裡面的資料只存在（‘男’，‘女’）兩種方式，避免其他的髒資料插入。

再說一下比較重要的一個約束——外鍵約束。外鍵約束，表示一個表中的資料依靠另一個表，其中存在關聯關係。正如上面的兩張表一樣，一個學生表中可以定義一個班級iD，來區別這個學生隸屬於哪個班級。那麼可以這樣定義：

也就是說，只有在班級表裡面存在的班級編號（classid），在學生表裡面才可以出現。避免學生找不到班級的情況。這樣以後班級表和學生表通過外鍵classid，建立了一種關係。假如我們要刪除班級表時，系統會報錯，因為在學生表（Students）裡面存在外部索引鍵關聯。故必須先刪除學生表再刪除班級表。

 

三.事務（Transaction）

1.所謂事務，即作為單個工作單元而執行的一系列操作。也就是完成某一件事情，需要包含很多步驟，所有的步驟完成以後，那麼這個事情才算完成，其中任何一步出現問題，這個事情就不可能完成。這裡所說的一系列步驟，看成一個整體，那麼就是一個事務。

（1）隱含交易

隱含交易是隱藏的，也就是每執行一次DML操作，就直接提交到資料庫儲存。我們先看看貨運公司表的資料：

1 SELECT * FROM sales.Shippers

比如我們的刪除操作，針對貨運公司表（sales.shippers）,我們刪除shipperid為6號的公司：

1  DELETE FROM Sales.Shippers WHERE shipperid=6

刪除以後，我們對比之前的記錄，發現確實將6號貨運公司刪除掉了，這就是隱含交易，預設已經提交了，影響是永久的。

（2）顯示事務

顯示事務，就是明確的指出事務的起始邊界。可以通過復原操作（rollback）和提交來處理事務的結果。顯示事務的好處就是可以確保執行的完整性，比如銀行的轉賬，要麼成功，要麼失敗，採取復原操作，回到原來的狀態。

定義事務的邊界，事務開始用begin transaction——》操作（包含增刪改）——》提交（commit）或者復原（rollback）。

接著我們同樣採取刪除操作，刪除貨運公司表裡面貨運公司號為5，6的公司。那麼我們要保證5,6號公司要麼全部刪除，要麼就都不刪除，所以需要用到事務。sql如下：

1        BEGIN TRANSACTION;2        3         DELETE FROM Sales.Shippers WHERE shipperid=4;4         DELETE FROM Sales.Shippers WHERE shipperid=5;5         6         ROLLBACK;

我們先開始一個事務，然後進行刪除操作，最後執行rollback操作，資料庫表回到以前的狀態，雖然進行了刪除操作，但是rollback復原，讓資料回到以前狀態。

假如我們執行的是commit操作的話，那麼資料就無法復原。因為已提交到資料庫儲存。

1        BEGIN TRANSACTION;2        3         DELETE FROM Sales.Shippers WHERE shipperid=4;4         DELETE FROM Sales.Shippers WHERE shipperid=5;5         6         COMMIT;

 

2.事務的屬性（ACID）

（1）原子性。事務務必是原子工作單元，這就意味著事務中進行的操作，要麼全部執行，那麼全都不執行。

（2）一致性。也就是要麼都更新，要麼都不更新。比如我們要將貨運公司編號為1,2的公司名稱更改為abc，xyz，那麼我們在事務中可以這樣執行：

 

1        BEGIN TRANSACTION;2        3         UPDATE  Sales.Shippers SET companyname=‘abc‘ WHERE shipperid=1;4         UPDATE  Sales.Shippers SET companyname=‘XYZ‘ WHERE shipperid=2;

 

我們再建立一個查詢時段，查詢一下更新的結果：

那麼我們提交以後，就可以查詢出來結果，並且確實已經將1,2號貨運公司的名稱更改了。

1        BEGIN TRANSACTION;2        3         UPDATE  Sales.Shippers SET companyname=‘abc‘ WHERE shipperid=1;4         UPDATE  Sales.Shippers SET companyname=‘XYZ‘ WHERE shipperid=2;5         6         COMMIT;

這就反應了事務的一致性，只有兩個都更新完了，才能執行其他的操作。

（3）隔離性。其實上面的也反映了一種隔離性，也就是說隔離性，讓我們看不到事務內部如何一步一步的更新的，只有事務提交以後，才能看到統一的結果。類似於上面的更新1,2號貨運公司公司名稱，只有當他們都更新完了，我們才看到更新的結果。

（4）持久性。提交到資料庫儲存資料，對資料庫是持久性的，影響永久的。

四.鎖定（lock）和阻塞

鎖是事務擷取的一種控制資源，用於保護資料資源，防止其他事務對資料進行衝突或不相容的訪問。

（1）獨佔鎖定

自己獨自佔有資源，別的事務無法訪問。

（2）共用鎖定

共用資源，都可以訪問，每個人各自享用資料資源。

為了說明鎖的應用情境，我們建立三個查詢時段，進程號分別為52,53,54：

在52視窗中，我們建立一個事務來執行，對產品表中產品編號為2的產品的單價增加一塊錢。

1 BEGIN TRANSACTION;2 3 UPDATE Production.Products 4 SET unitprice=unitprice+15 WHERE productid=2;

為了更新這一行，那麼會話必定會獲得一個獨佔鎖定，這樣才能保證更新的完成。那麼此時我們試著在53號視窗，進行同一行記錄的查詢結果為怎樣了？

在此處就體現了獨佔鎖定和共用鎖定。要得到有關鎖的執行資訊，我們可以在54號視窗中，進行相關的查詢，即動態管理檢視（sys.dm_tran_locks)。

 1 SELECT  2  request_session_id, --進程ID 3  resource_type,     --資源類型 4  resource_database_id, 5  DB_NAME(request_session_id), 6 resource_description, 7 resource_associated_entity_id, 8 request_mode, 9 request_status10  11 FROM sys.dm_tran_locks

為了獲得更多與阻塞相關的連結資訊，那麼我們可以在54號視窗中接著再查詢sys.dm_exec_connections動態管理檢視。

1 SELECT2 session_id,3 connect_time,4 last_read,5 last_write,6 most_recent_sql_handle7 FROM sys.dm_exec_connections8 WHERE session_id IN(52,53);

執行結果中，我們可以看到連結時間，以及最後讀、寫的時間，還有執行sql的編號。

為了更加細緻的看出結果，我們還可以查出系統執行的sql語句。

1 UPDATE Production.Products   SET unitprice=unitprice+1  WHERE productid=2;

查詢結果中，我們可以看到系統執行的sql語句:

有時候我們為了排除某一阻塞，我們可以查詢出哪些進程被堵塞了，那麼我們就可以通過相應的操作，將阻塞解除。利用sql可以這樣查詢：

 1 SELECT  2 session_id, 3 blocking_session_id, 4 command, 5 sql_handle, 6 database_id, 7 wait_type, 8 wait_time, 9 wait_resource10 FROM  sys.dm_exec_requests11 WHERE blocking_session_id>0;

查詢結果可以看出53號進程被52號進程給堵塞住了，同時可看到等待的時間，以及等待的資源。

這樣52號進程一直在等待，為了使進程釋放出來，不一直等下去，那麼我們可以設定逾時，超過響應設定的時間，則結束會話。設定逾時可以這樣設定：

1     SET LOCK_TIMEOUT 5000;2     3     SELECT * FROM  Production.Products 4     WHERE productid=2;

設定以後，那麼超過請求時間，則結束回話。

其中設定(set locktimeout -1)為無限等待。預設為-1;同時有另一種結束回話的用法kill。可以直接結束掉相應的進程號。

例如我們結束掉52號進程，（kiil 52）。

1 KILL 52;

今天先學習到這裡，下次接著學習鎖所產生的隔離等級。

 

希望各位大牛給出指導，不當之處虛心接受學習！謝謝！

 

SQLServer學習筆記系列8