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

Level 2 - Tìm hiểu cam kết SQL Server mặc định của cấp cô lập giao dịch, đọc cam kết, (được mô tả trước đây) cũng giống như một hàng rào, đẹp picket trắng lịch sự giữa hai nước láng giềng tốt. Nó ngăn ngừa bẩn đọc, nhưng không bog xuống với hệ thống khóa tranh quá mức. Vì lý do này, đó là cấp cô lập mặc định của SQL Server và sự lựa chọn lý tưởng cho hầu hết các dự án OTLP. | Part IX Performance Tuning and Optimization Level 2 - Read committed SQL Server s default transaction isolation level read committed described previously is like a nice polite white-picket fence between two good neighbors. It prevents dirty reads but doesn t bog the system down with excessive lock contention. For this reason it s SQL Server s default isolation level and an ideal choice for most OTLP projects. Best Practice Unless there s a specific reason to escalate the transaction isolation level I strongly recommend that you keep your transactions at read committed. Level 3 - Repeatable read The third level of isolation repeatable read is like a 10-foot chain-link fence with barbed wire on top. There s a significant difference between read committed s white-picket fence and repeatable read. Read committed only has to lock the transaction that s doing the writing. To ensure that reads are consistent share locks on the reading transaction have to be extended as well. First here s a walk-through of a nonrepeatable read fault. Nonrepeatable read fault A nonrepeatable read is similar to a dirty read but a nonrepeatable read occurs when a transaction can see the committed updates from another transaction see Figure 66-8 . True isolation means that one transaction never affects another transaction. If the isolation is complete then no data changes from outside the transaction should be seen by the transaction. Reading a row inside a transaction should produce the same results every time. If reading a row twice results in different values that s a nonrepeatable read type of transaction fault. FIGURE 66-8 A nonrepeatable read transaction fault is when transaction 2 selects the same data twice and sees different values. Isolation t1 Update Commit Retrieves Updated Value tT Select Select 1392 Managing Transactions Locking and Blocking 66 To demonstrate a nonrepeatable read the following sequence sets up two concurrent transactions. Transaction 2 on the