tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 142

Quản lý tài nguyên tranh là về cách viết code sử dụng như là nguồn tài nguyên ít nhất có thể để cho phép nhiều người sử dụng có thể thực thi mã tại cùng một thời điểm. | Part IX Performance Tuning and Optimization Result Spid Object Type Mode Status 54 PAGE IX GRANT 54 KEY X GRANT Transaction 2 ensures that the transaction isolation level is set to the default and then attempts to read the same row transaction 1 is updating -- Transaction 2 USE AdventureWorks2008 SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT Name FROM WHERE DepartmentID 1 There is no result yet for transaction 2. It s waiting for transaction 1 to complete blocked by transaction 1 s exclusive lock. Requerying reveals that the second transaction SPID 51 has an intent to share IS read lock and is waiting for a share S read lock. Result Spid Object Type Mode Status 51 PAGE IS GRANT 51 PAGE S WAIT 54 KEY X GRANT 54 PAGE IX GRANT 54 PAGE IX GRANT 54 KEY X GRANT 54 KEY X GRANT While transaction 1 is holding its exclusive lock transaction 2 has to wait. In other words transaction 1 is blocking transaction 2. Now transaction 1 commits the transaction and releases the exclusive lock -- Transaction 1 COMMIT TRANSACTION Immediately transaction 1 completes and releases its locks. Transaction 2 springs to life and performs the select reading the committed change. 1372 Managing Transactions Locking and Blocking 66 Result Name New Name The point of transaction isolation level read committed is to avoid reading uncommitted data. What if the update doesn t change the data If transaction 1 updates the data from John to John what s the harm of reading John SQL Server handles this situation by not respecting an exclusive lock if the page hasn t been changed . if the page isn t flagged as dirty. This means that sometimes because there s probably more data on the page than just the .