Microsoft® SQL Server® 2012 Bible

1047 CHAPTER 47 Managing Transactions, Locking, and Blocking IN THIS CHAPTER Using the Transactional Integrity Theory Understand ...

1048 Part VIII: Performance Tuning and Optimization What’s New with Transactions? SQL Server has always had transactional integr ...

1049 Chapter 47: Managing Transactions, Locking, and Blocking 47 Consider the following example. Assume a foreign key constraint ...

1050 Part VIII: Performance Tuning and Optimization example, a single insert command that inserts 25 rows is a logical unit of w ...

1051 Chapter 47: Managing Transactions, Locking, and Blocking 47 COMMIT TRANSACTION; END TRY BEGIN CATCH; ROLLBACK TRANSACTION; ...

1052 Part VIII: Performance Tuning and Optimization Xact_Abort A common SQL Server myth is that all error conditions roll back a ...

1053 Chapter 47: Managing Transactions, Locking, and Blocking 47 3 0 If the code might have nested transactions, it’s a good ide ...

1054 Part VIII: Performance Tuning and Optimization Save Points You can also declare a save point within the sequence of tasks i ...

1055 Chapter 47: Managing Transactions, Locking, and Blocking 47 FIGURE 47-1 Opening multiple Query Editor windows and sending t ...

1056 Part VIII: Performance Tuning and Optimization FROM HumanResources.Department WHERE DepartmentID = 1; There is no result ye ...

1057 Chapter 47: Managing Transactions, Locking, and Blocking 47 Monitoring Locking and Blocking SQL Server provides many differ ...

1058 Part VIII: Performance Tuning and Optimization FIGURE 47-3 Activity Monitor displays information about the current locks an ...

1059 Chapter 47: Managing Transactions, Locking, and Blocking 47 FIGURE 47-4 SQL Server Profi ler can monitor and display the bl ...

1060 Part VIII: Performance Tuning and Optimization Viewing all the locks is possible with the sys.dm_tran_locks DMV. The follow ...

1061 Chapter 47: Managing Transactions, Locking, and Blocking 47 and ■ Transaction 2 has a lock on data B and needs to lock data ...

1062 Part VIII: Performance Tuning and Optimization To execute the code, you need to do the following: Create two Query windows. ...

1063 Chapter 47: Managing Transactions, Locking, and Blocking 47 Transaction 1 now has an exclusive lock on BusinessEntityID 101 ...

1064 Part VIII: Performance Tuning and Optimization FIGURE 47-6 SQL Server Profi ler can monitor deadlocks using the Locks: Dead ...

1065 Chapter 47: Managing Transactions, Locking, and Blocking 47 SET @retry = 1 WHILE @retry = 1 BEGIN BEGIN TRY SET @retry = 0 ...

1066 Part VIII: Performance Tuning and Optimization Minimizing Deadlocks Even though deadlocks can be detected and handled, it’s ...