锋利的sql和代码

1、第 17 章并发控制17.5 使用行版本的事务级别17.5.2 使用基于行版本控制的级别1使用快照在会话 1 上执行下面的语句：在会话 2 上执行下面的语句：USE AdventureWorks; GO- 开始一个事务BEGRANION;USE AdventureWorks; GO- 在数据库上允许快照. ALTER DATABASE AdventureWorksSET ALLOW_SNAPSHOT_ISOLATION ON;GO- 开始一个快照事务SET TRANION ISOLATION LEVEL SNAPSHOT; GOBEGRANION;- 该SELECT 语句，返回的 Vacati

2、onHours 列的值为 48 SELECT EmployeeID, VacationHoursFROM HumanResour.EmployeeWHERE EmployeeID = 4;在会话 1 上再执行下面的语句。由于使用了快照，下面的 SELECT 语句将从版本行中数据，因此 VacationHours 的值仍旧为 48，而不是被会话 2 所更新后的 40。在会话 2 上执行下面的语句，提交事务，进行数据修改在会话 1 上执行下面的语句：2使用通过行版本控制的 READ COMMITTED例如，在会话 1 上执行下面的语句：USE AdventureWorks; GO- 在数据库上允许

3、MITTED_SNAPSHOT- 下面的语句要执行成功，必须仅有该会话连接到AdventureWorks 数据库ALTER DATABASE AdventureWorks- 即使会话 2 已经提交了修改，但是 VacationHours 的值仍旧为 48。 SELECT EmployeeID, VacationHoursFROM HumanResour.Employee WHERE EmployeeID = 4;- 由于数据已经被当前快照之外的事务修改，所以下面的更新将失败- 并生成 3960 错误，事务将结束UPDATE HumanResour.EmployeeSET SickLeaveHo

4、urs = SickLeaveHours - 8 WHERE EmployeeID = 4;- 撤消会话 1 中的修改，但是，不能撤消会话 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;- 验证 VacationHours 的值，此时应当为 40 SELECT VacationHoursFROM HumanResour.EmployeeWHERE EmployeeID = 4;在会话 2 上执行下面的语句：在会话 1 上再执行下面的语句。由于会话 2 还没有将修改进行进行提交，所以下面的语句仍旧从版本行中数据，得到 VacationHours 的值仍旧为 48。在会话 2 上执行下面的语句，提交修改：在会话 1 上执行下面的语句：- 由于会话 2 已经提交修改，所以下面的 SELECT 语句检索到的 Vacati

6、onHours 的值- 为 40。这是与快照不同的地方，快照事务即使别的事务已经提交了更新，- 它仍旧从版本行中值COMMIT TRANION;SELECT EmployeeID, VacationHours FROM HumanResour.EmployeeWHERE EmployeeID = 4;USE AdventureWorks; GO- 开始一个事务BEGRANION;- 更新 VacationHours 的值。由于在MITTED 快照级别下- 不要求共享锁，因此更新不会被会话 1 阻塞UPDATE HumanResour.EmployeeSET VacationHours = Va

7、cationHours - 8 WHERE EmployeeID = 4;- 验证 VacationHours 的值，此时应当为 40.SELECT VacationHoursFROM HumanResour.Employee WHERE EmployeeID = 4;SETMITTED_SNAPSHOT ON;GO- 开始一个MITTED 事务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- 验证表是否已经存在IF OBJECT_ID (Nmy_sales,NU) IS NOT NULLDROP TABLE my_sales;GO- 创建表并数据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;- 下面的更新语句在快照事务中会发生错误，但是在MITTED 快照中- 会更新成功.UPDATE HumanResour.EmployeeSET SickLeaveHours = SickLeaveHours - 8 WHERE EmployeeID = 4;- 撤消会话 1 中的修改，但不能撤消会话 2 中的修改ROLLBACK TRANION;下面的会话 1 和会话 2 代

10、码运行。两个会话都尝试更新表中的相在两个单独的SQL Server Management Studio 连接下同时。在第一次尝试过程中，其中一个会话将成功完成更新操作，而另一个会话将被选择为死锁牺牲品。死锁牺牲品错误将使执行跳至 CATCH 块，事务将进入无法提交状态。在 CATCH 块中，死锁牺牲品会回滚事务并重试更新此表，直到更新成功或达到了重试限制。会话 1会话 2USE AdventureWorks; GO- 定义并设置变量，指定尝试提交更新的次数DECLARE retry SET retry = 5;- 如果被作为了死锁牺牲品，保持尝试更新WHILE (retry 0) BEGINB

11、EGRYBEGRANION;UPDATE my_sales SET sales = sales + 1 WHERE itemid = 2;- 延时等待，此时itemid 为 1 和 2 的行- 在没有提交前，都无法锁WAITFOR DELAY 00:00:07;UPDATE my sales SET sales = sales + 1 WHERE itemid = 1;USE AdventureWorks; GO- 定义并设置变量，指定尝试提交更新的次数DECLARE retry SET retry = 5;- 如果被作为了死锁牺牲品，保持尝试更新WHILE (retry 0) BEGINBE

12、GRYBEGRANION;UPDATE my_sales SET sales = sales + 1 WHERE itemid = 1;- 延时等待，此时itemid 为 1 和 2 的行- 在没有提交前，都无法锁WAITFOR DELAY 00:00:13;UPDATE my sales SET sales = sales + 1 WHERE itemid = 2;GO- 验证过程是否已经存在IF OBJECT_ID (Nusp_MyErrorLog,NP) IS NOT NULLDROP PROCEDURE usp_MyErrorLog;GO- 创建过程，用于输出错误消息 CREATE P

13、ROCEDURE usp_MyErrorLog ASPRN错误 + CONVERT(VARCHAR(50), ERROR_NUMBER() +N, 严重级别 + CONVERT(VARCHAR(5), ERROR_SEVERITY() + N, 状态 + CONVERT(VARCHAR(5), ERROR_SE() +N, 行 + CONVERT(VARCHAR(5), ERROR_LINE();PRERROR_MESSAGE();下面是会话 1 中返回的消息，表示两行都已经被更新。下面是会话 2 中返回的消息，会话 2 被作为了死锁牺牲品。(1 行受影响)- 由于死锁，会话 2 事务中只成功

14、更新 1 行，这时会发生回滚操作错误 1205, 严重级别 13, 状态 51, 行 18- 由过程usp_MyErrorLog 输出的错误消息事务(进程 ID 52)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。 -SQL Server 的提示(1 行受影响) - 本行和下行消息是重新尝试更新后得到的提示消息(1 行受影响)(1 行受影响)(1 行受影响)SET retry = 0;COMMIT TRANION; END TRYBEGIN CATCH- 检测错误，如果是死锁牺牲品，- 则减少重新尝试计数。如果是其他- 错误，则退出WHILE 循环IF (ERROR_NUMBER() = 1205)SET retry = retry - 1; ELSESET retry = -1;- 输出错误消息EXECUTE usp MyErrorLog;- 会话中包含无法提交的事务- XACT_SE 将返回 -1 IF XACT SE() 0ROLLBACK TRANION; END CATCH;END; - 结束WHILE 循环SET retry = 0;COMMIT TRANION; END TRYBEGIN CATCH- 检测错