Sql server® 2012 t-sql fundamentals

326  

Microsoft SQL Server 2012 T-SQL Fundamentals

Conclusion

This chapter introduced you to transactions and concurrency. I described what transactions are and 

how SQL Server manages them. I explained how SQL Server isolates data accessed by one transaction 

from inconsistent use by other transactions, and how to troubleshoot blocking scenarios. I described 

how you can control the level of consistency that you get from the data by choosing an isolation 

level, and the impact that your choice has on concurrency. I described four isolation levels that do not 

rely on row versioning and two that do. Finally, I covered deadlocks and explained practices that you 

can follow to reduce the frequency of their occurrence.

To practice what you’ve learned, perform the practice exercises.

Exercises

This section provides exercises to help you familiarize yourself with the subjects discussed in this chap-

ter. The exercises for most of the previous chapters involve requests for which you have to figure out 

a solution in the form of a T-SQL query or statement. The exercises for this chapter are different. You 

will be provided with instructions to follow to troubleshoot blocking and deadlock situations, and to 

observe the behavior of different isolation levels. Therefore, this chapter’s exercises have no separate 

“Solutions” section, as in other chapters. 

For all exercises in this chapter, make sure you are connected to the TSQL2012 sample database by 

running the following code.

USE TSQL2012;

Exercises 1-1 through 1-6 deal with blocking.

1-1

Open three connections in SQL Server Management Studio (the exercises will refer to them as Con-

nection 1, Connection 2, and Connection 3). Run the following code in Connection 1 to update rows 

in Sales.OrderDetails.

BEGIN TRAN; 

 

  UPDATE Sales.OrderDetails 

    SET discount = 0.05 

  WHERE orderid = 10249;

1-2

Run the following code in Connection 2 to query Sales.OrderDetails; Connection 2 will be blocked 

(remember to uncomment the hint if you are running against SQL Database).

www.it-ebooks.info

---