鋒利的sql和代碼

1、第 17 章并發(fā)控制17.5 使用行版本的事務(wù)級別17.5.2 使用基于行版本控制的級別1使用快照在會(huì)話 1 上執(zhí)行下面的語句：在會(huì)話 2 上執(zhí)行下面的語句：USE AdventureWorks; GO- 開始一個(gè)事務(wù)BEGRANION;USE AdventureWorks; GO- 在數(shù)據(jù)庫上允許快照. ALTER DATABASE AdventureWorksSET ALLOW_SNAPSHOT_ISOLATION ON;GO- 開始一個(gè)快照事務(wù)SET TRANION ISOLATION LEVEL SNAPSHOT; GOBEGRANION;- 該SELECT 語句，返回的 Vacati

2、onHours 列的值為 48 SELECT EmployeeID, VacationHoursFROM HumanResour.EmployeeWHERE EmployeeID = 4;在會(huì)話 1 上再執(zhí)行下面的語句。由于使用了快照，下面的 SELECT 語句將從版本行中數(shù)據(jù)，因此 VacationHours 的值仍舊為 48，而不是被會(huì)話 2 所更新后的 40。在會(huì)話 2 上執(zhí)行下面的語句，提交事務(wù)，進(jìn)行數(shù)據(jù)修改在會(huì)話 1 上執(zhí)行下面的語句：2使用通過行版本控制的 READ COMMITTED例如，在會(huì)話 1 上執(zhí)行下面的語句：USE AdventureWorks; GO- 在數(shù)據(jù)庫上允許

3、MITTED_SNAPSHOT- 下面的語句要執(zhí)行成功，必須僅有該會(huì)話連接到AdventureWorks 數(shù)據(jù)庫ALTER DATABASE AdventureWorks- 即使會(huì)話 2 已經(jīng)提交了修改，但是 VacationHours 的值仍舊為 48。 SELECT EmployeeID, VacationHoursFROM HumanResour.Employee WHERE EmployeeID = 4;- 由于數(shù)據(jù)已經(jīng)被當(dāng)前快照之外的事務(wù)修改，所以下面的更新將失敗- 并生成 3960 錯(cuò)誤，事務(wù)將結(jié)束UPDATE HumanResour.EmployeeSET SickLeaveHo

4、urs = SickLeaveHours - 8 WHERE EmployeeID = 4;- 撤消會(huì)話 1 中的修改，但是，不能撤消會(huì)話 2 中所做的修改ROLLBACK TRANIONCOMMIT TRANION;SELECT EmployeeID, VacationHours FROM HumanResour.EmployeeWHERE EmployeeID = 4;- 將EmployeeID 為 4 雇員的 VacationHours 值減去 8，由于在快照下，不需要共享鎖定. UPDATE HumanResour.EmployeeSET VacationHours = Vacatio

5、nHours - 8 WHERE EmployeeID = 4;- 驗(yàn)證 VacationHours 的值，此時(shí)應(yīng)當(dāng)為 40 SELECT VacationHoursFROM HumanResour.EmployeeWHERE EmployeeID = 4;在會(huì)話 2 上執(zhí)行下面的語句：在會(huì)話 1 上再執(zhí)行下面的語句。由于會(huì)話 2 還沒有將修改進(jìn)行進(jìn)行提交，所以下面的語句仍舊從版本行中數(shù)據(jù)，得到 VacationHours 的值仍舊為 48。在會(huì)話 2 上執(zhí)行下面的語句，提交修改：在會(huì)話 1 上執(zhí)行下面的語句：- 由于會(huì)話 2 已經(jīng)提交修改，所以下面的 SELECT 語句檢索到的 Vacati

6、onHours 的值- 為 40。這是與快照不同的地方，快照事務(wù)即使別的事務(wù)已經(jīng)提交了更新，- 它仍舊從版本行中值COMMIT TRANION;SELECT EmployeeID, VacationHours FROM HumanResour.EmployeeWHERE EmployeeID = 4;USE AdventureWorks; GO- 開始一個(gè)事務(wù)BEGRANION;- 更新 VacationHours 的值。由于在MITTED 快照級別下- 不要求共享鎖，因此更新不會(huì)被會(huì)話 1 阻塞UPDATE HumanResour.EmployeeSET VacationHours = Va

7、cationHours - 8 WHERE EmployeeID = 4;- 驗(yàn)證 VacationHours 的值，此時(shí)應(yīng)當(dāng)為 40.SELECT VacationHoursFROM HumanResour.Employee WHERE EmployeeID = 4;SETMITTED_SNAPSHOT ON;GO- 開始一個(gè)MITTED 事務(wù)SET TRANION ISOLATION LEVEL READ COMMITTED; GOBEGRANION;- 該SELECT 語句將返回 VacationHours 的值為 48 SELECT EmployeeID, VacationHoursF

8、ROM HumanResour.EmployeeWHERE EmployeeID = 4;17.6 處理死鎖17.6.2 使用TRY.CATCH 處理死鎖USE AdventureWorks; GO- 驗(yàn)證表是否已經(jīng)存在IF OBJECT_ID (Nmy_sales,NU) IS NOT NULLDROP TABLE my_sales;GO- 創(chuàng)建表并數(shù)據(jù)CREATE TABLE my_sales (ItemidPRIMARY KEY,Salesnot null);GOINSERT my_sales (itemid, sales) VALUES (1, 1); INSERT my_sales

9、(itemid, sales) VALUES (2, 1);SELECT EmployeeID, VacationHours FROM HumanResour.Employee WHERE EmployeeID = 4;- 下面的更新語句在快照事務(wù)中會(huì)發(fā)生錯(cuò)誤，但是在MITTED 快照中- 會(huì)更新成功.UPDATE HumanResour.EmployeeSET SickLeaveHours = SickLeaveHours - 8 WHERE EmployeeID = 4;- 撤消會(huì)話 1 中的修改，但不能撤消會(huì)話 2 中的修改ROLLBACK TRANION;下面的會(huì)話 1 和會(huì)話 2 代

10、碼運(yùn)行。兩個(gè)會(huì)話都嘗試更新表中的相在兩個(gè)單獨(dú)的SQL Server Management Studio 連接下同時(shí)。在第一次嘗試過程中，其中一個(gè)會(huì)話將成功完成更新操作，而另一個(gè)會(huì)話將被選擇為死鎖犧牲品。死鎖犧牲品錯(cuò)誤將使執(zhí)行跳至 CATCH 塊，事務(wù)將進(jìn)入無法提交狀態(tài)。在 CATCH 塊中，死鎖犧牲品會(huì)回滾事務(wù)并重試更新此表，直到更新成功或達(dá)到了重試限制。會(huì)話 1會(huì)話 2USE AdventureWorks; GO- 定義并設(shè)置變量，指定嘗試提交更新的次數(shù)DECLARE retry SET retry = 5;- 如果被作為了死鎖犧牲品，保持嘗試更新WHILE (retry 0) BEGINB

11、EGRYBEGRANION;UPDATE my_sales SET sales = sales + 1 WHERE itemid = 2;- 延時(shí)等待，此時(shí)itemid 為 1 和 2 的行- 在沒有提交前，都無法鎖WAITFOR DELAY 00:00:07;UPDATE my sales SET sales = sales + 1 WHERE itemid = 1;USE AdventureWorks; GO- 定義并設(shè)置變量，指定嘗試提交更新的次數(shù)DECLARE retry SET retry = 5;- 如果被作為了死鎖犧牲品，保持嘗試更新WHILE (retry 0) BEGINBE

12、GRYBEGRANION;UPDATE my_sales SET sales = sales + 1 WHERE itemid = 1;- 延時(shí)等待，此時(shí)itemid 為 1 和 2 的行- 在沒有提交前，都無法鎖WAITFOR DELAY 00:00:13;UPDATE my sales SET sales = sales + 1 WHERE itemid = 2;GO- 驗(yàn)證過程是否已經(jīng)存在IF OBJECT_ID (Nusp_MyErrorLog,NP) IS NOT NULLDROP PROCEDURE usp_MyErrorLog;GO- 創(chuàng)建過程，用于輸出錯(cuò)誤消息 CREATE P

13、ROCEDURE usp_MyErrorLog ASPRN錯(cuò)誤 + CONVERT(VARCHAR(50), ERROR_NUMBER() +N, 嚴(yán)重級別 + CONVERT(VARCHAR(5), ERROR_SEVERITY() + N, 狀態(tài) + CONVERT(VARCHAR(5), ERROR_SE() +N, 行 + CONVERT(VARCHAR(5), ERROR_LINE();PRERROR_MESSAGE();下面是會(huì)話 1 中返回的消息，表示兩行都已經(jīng)被更新。下面是會(huì)話 2 中返回的消息，會(huì)話 2 被作為了死鎖犧牲品。(1 行受影響)- 由于死鎖，會(huì)話 2 事務(wù)中只成功

14、更新 1 行，這時(shí)會(huì)發(fā)生回滾操作錯(cuò)誤 1205, 嚴(yán)重級別 13, 狀態(tài) 51, 行 18- 由過程usp_MyErrorLog 輸出的錯(cuò)誤消息事務(wù)(進(jìn)程 ID 52)與另一個(gè)進(jìn)程被死鎖在 鎖 資源上，并且已被選作死鎖犧牲品。請重新運(yùn)行該事務(wù)。 -SQL Server 的提示(1 行受影響) - 本行和下行消息是重新嘗試更新后得到的提示消息(1 行受影響)(1 行受影響)(1 行受影響)SET retry = 0;COMMIT TRANION; END TRYBEGIN CATCH- 檢測錯(cuò)誤，如果是死鎖犧牲品，- 則減少重新嘗試計(jì)數(shù)。如果是其他- 錯(cuò)誤，則退出WHILE 循環(huán)IF (ERROR_NUMBER() = 1205)SET retry = retry - 1; ELSESET retry = -1;- 輸出錯(cuò)誤消息EXECUTE usp MyErrorLog;- 會(huì)話中包含無法提交的事務(wù)- XACT_SE 將返回 -1 IF XACT SE() 0ROLLBACK TRANION; END CATCH;END; - 結(jié)束WHILE 循環(huán)SET retry = 0;COMMIT TRANION; END TRYBEGIN CATCH- 檢測錯(cuò)