Bạn hiện đang ngoại tuyến, hãy chờ internet để kết nối lại

Làm thế nào để giải quyết vấn đề chặn xảy ra do khóa leo thang trong SQL Server

QUAN TRỌNG: Bài viết này được dịch bằng phần mềm dịch máy của Microsoft chứ không phải do con người dịch. Microsoft cung cấp các bài viết do con người dịch và cả các bài viết do máy dịch để bạn có thể truy cập vào tất cả các bài viết trong Cơ sở Kiến thức của chúng tôi bằng ngôn ngữ của bạn. Tuy nhiên, bài viết do máy dịch không phải lúc nào cũng hoàn hảo. Loại bài viết này có thể chứa các sai sót về từ vựng, cú pháp hoặc ngữ pháp, giống như một người nước ngoài có thể mắc sai sót khi nói ngôn ngữ của bạn. Microsoft không chịu trách nhiệm về bất kỳ sự thiếu chính xác, sai sót hoặc thiệt hại nào do việc dịch sai nội dung hoặc do hoạt động sử dụng của khách hàng gây ra. Microsoft cũng thường xuyên cập nhật phần mềm dịch máy này.

Nhấp chuột vào đây để xem bản tiếng Anh của bài viết này:323630
TÓM TẮT
Khóa leo thang là quá trình chuyển đổi rất nhiều hạt mịn ổ khóa (ví dụ như hàng hoặc trang ổ khóa) vào bảng khóa. Microsoft SQL Máy chủ động sẽ xác định khi nào để thực hiện các khóa leo thang. Khi thực hiện điều này quyết định, SQL Server sẽ đưa vào tài khoản số ổ khóa được tổ chức vào một cụ thể quét, số lượng các ổ khóa được tổ chức bởi các giao dịch toàn bộ, và bộ nhớ đang được sử dụng cho ổ khóa trong hệ thống như một toàn thể. Thông thường, SQL Server-kết quả mặc định hành vi trong khóa leo thang xảy ra chỉ tại những điểm nơi nó sẽ cải thiện hiệu suất hoặc khi bạn phải giảm quá nhiều hệ thống khóa bộ nhớ đến một mức độ hợp lý hơn. Tuy nhiên, một số thiết kế ứng dụng hoặc truy vấn có thể kích hoạt khóa leo thang tại một thời điểm khi nó là không mong muốn, và escalated bảng khóa có thể ngăn chặn người dùng khác. Bài viết này thảo luận về làm thế nào để xác định xem khóa leo thang là gây ra chặn và làm thế nào để đối phó với leo thang không mong muốn khóa.
THÔNG TIN THÊM

Làm thế nào để xác định liệu leo thang Lock là gây ra chặn

Leo thang khóa không gây ra vấn đề chặn nhất. Để xác định liệu leo thang khóa là xảy ra trong khoảng thời gian khi bạn kinh nghiệm chặn các vấn đề, bắt đầu một dấu vết SQL Profiler bao gồm các Khóa: leo thang sự kiện. Nếu bạn không thấy bất kỳ Khóa: leo thang sự kiện, leo thang khóa là không xảy ra trên máy chủ của bạn và các thông tin trong bài viết này không áp dụng cho tình hình của bạn.

Nếu khóa leo thang là xảy ra, hãy kiểm chứng rằng escalated bảng khóa là chặn người dùng khác.

Cho biết thêm thông tin về làm thế nào để xác định đầu chặn và như thế nào để xác định các khóa tài nguyên tổ chức bởi bộ chặn đầu đó là chặn khác máy chủ quá trình ID (SPID), nhấp vào số bài viết sau đây để xem bài viết trong cơ sở kiến thức Microsoft:
224453Sự hiểu biết và giải quyết các SQL Server 7.0 hay 2000 chặn vấn đề
Nếu khóa là chặn người dùng khác bất cứ điều gì khác hơn là một ổ khóa TAB (bảng cấp) với một chế độ khóa s (chia sẻ), hoặc X (độc quyền), leo thang khóa không phải là vấn đề. Đặc biệt, nếu TAB khóa là một khóa ý định (ví dụ như khóa chế độ IS, IU hoặc IX), đây không phải là các kết quả của khóa leo thang. Nếu vấn đề chặn của bạn đang không được gây ra bởi khóa leo thang, xem bài viết Q224453 để xử lý sự cố bước.

Làm thế nào để ngăn chặn leo thang Lock

Cách đơn giản nhất và an toàn nhất để ngăn chặn leo thang khóa là để giữ cho các giao dịch ngắn và để giảm dấu chân khóa truy vấn đắt tiền như vậy rằng các ngưỡng leo thang khóa không được vượt quá. Có một số cách để có được mục tiêu này, nhiều người trong số đó được liệt kê:
  • Phá vỡ các hoạt động lớn lô thành nhiều nhỏ hơn hoạt động kinh doanh. Ví dụ, giả sử bạn chạy truy vấn sau đây để loại bỏ một vài hồ sơ cũ trăm ngàn từ một bảng kiểm toán, và sau đó bạn thấy là nó gây ra một leo thang khóa chặn người sử dụng khác:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    Bằng cách loại bỏ những hồ sơ này vài trăm tại một thời điểm, bạn có thể đáng kể giảm số ổ khóa mà tích lũy cho mỗi giao dịch và ngăn chặn các khóa leo thang. Ví dụ:
    SET ROWCOUNT 500delete_more:     DELETE FROM LogMessages WHERE LogDate < '2/1/2002'IF @@ROWCOUNT > 0 GOTO delete_moreSET ROWCOUNT 0
  • Giảm phát thải khóa các truy vấn bằng cách truy vấn như hiệu quả nhất có thể. Lớn quét hoặc một số lớn các tra cứu Bookmark có thể tăng cơ hội của leo thang khóa; thêm vào đó, nó làm tăng cơ hội deadlocks, và nói chung bất lợi ảnh hưởng concurrency và hiệu suất. Sau khi bạn tìm thấy các truy vấn mà nguyên nhân leo thang khóa, tìm kiếm cơ hội để tạo mới chỉ số hoặc thêm cột vào một chỉ số hiện có để loại bỏ chỉ số hoặc bảng quét và để tối đa hóa hiệu quả của chỉ mục tìm kiếm. Xem xét việc dán các truy vấn vào một cửa sổ truy vấn truy vấn Analyzer để thực hiện một phân tích tự động chỉ số trên nó. Để làm như vậy, trên các Truy vấn trình đơn, nhấp vào Chỉ số chỉnh thuật sĩ trong SQL Server 2000 hoặc nhấp chuột Thực hiện phân tích chỉ số trong SQL Server 7.0.

    Một trong những mục tiêu của tối ưu hóa này là làm cho chỉ mục tìm kiếm lợi nhuận hàng càng ít càng tốt để giảm thiểu chi phí Tra cứu (tối đa hóa sự chọn lọc của các chỉ số cho đặc biệt là đánh dấu truy vấn). Nếu SQL Server ước tính rằng một nhà điều hành hợp lý trang đánh dấu Lookup có thể trở về nhiều hàng, nó có thể sử dụng một PREFETCH để thực hiện việc tra cứu dấu trang. Nếu SQL Hệ phục vụ sử dụng PREFETCH cho một tra cứu dấu trang, nó phải làm tăng các giao dịch cô lập cấp của một phần của các truy vấn để lặp lại đọc cho một phần của các truy vấn. Điều này có nghĩa rằng những gì có thể trông giống như một lựa chọn tuyên bố ở mức độ cam kết đọc cách ly có thể có được nhiều ngàn khóa ổ khóa (trên cả hai chỉ số tập hợp và một chỉ số nonclustered), mà có thể gây như vậy một truy vấn để vượt quá ngưỡng leo thang khóa. Đây là đặc biệt là quan trọng nếu bạn thấy rằng escalated khóa là một khóa dùng chung bảng, trong đó, Tuy nhiên, không được thường được thấy ở cấp độ chi cam kết sự cô lập mặc định. Nếu một điều khoản Bookmark Lookup nhãn PREFETCH gây ra sự leo thang, xem xét thêm thêm cột chỉ số nonclustered xuất hiện trong chỉ mục Tìm kiếm hoặc nhà điều hành hợp lý chỉ số quét dưới Lookup Bookmark hợp lý nhà điều hành trong kế hoạch truy vấn. Có thể tạo ra một chỉ số bao gồm (một chỉ số bao gồm tất cả các cột trong một bảng đã được sử dụng trong truy vấn), hoặc tại ít nhất là một chỉ số nằm trên các cột được sử dụng cho tiêu chí tham gia hoặc trong WHERE khoản nếu bao gồm tất cả mọi thứ trong danh sách chọn cột là không thực tế.

    Một tham gia lồng nhau Loop cũng có thể sử dụng PREFETCH, và điều này gây ra cùng một hành vi khóa.

    Để biết thêm thông tin, hãy bấm vào số bài viết sau để xem bài viết trong Cơ sở Kiến thức Microsoft:
    260652Tham gia lồng nhau vòng lặp có sử dụng một "dấu trang LOOKUP...VỚI PREFETCH"có thể giữ ổ khóa dài hơn
  • Leo thang khóa không thể xảy ra nếu một dịch vụ SPID khác nhau là hiện nay đang nắm giữ một khóa không tương thích bảng. Khóa leo thang luôn leo thang một bảng khóa, và không bao giờ để trang ổ khóa. Ngoài ra, nếu một leo thang khóa nỗ lực không thành công vì một SPID giữ một không tương thích TAB khóa, truy vấn đó leo thang đã cố gắng không chặn trong khi chờ đợi một khóa TAB. Thay vào đó, nó vẫn tiếp tục để có được các ổ khóa của nó cấp ban đầu, hơn hột (hàng, chìa khóa, hoặc trang), định kỳ làm cho leo thang thêm cố. Vì vậy, một trong những phương pháp để ngăn chặn leo thang khóa trên một bảng cụ thể là để có được để giữ một ổ khóa trên một kết nối khác nhau là không tương thích với các escalated khóa loại. Một IX (ý định độc quyền), ở mức độ bảng khóa không khóa bất kỳ hàng hoặc trang, nhưng nó là vẫn không tương thích với một S (chia sẻ) escalated hoặc x (độc quyền) TAB khóa. Ví dụ, giả sử rằng bạn phải chạy một công việc thực thi mà Sửa đổi một số lớn các hàng trong các mytable bảng và đó đã gây ra chặn xảy ra vì của khóa leo thang. Nếu công việc này luôn luôn hoàn thành trong ít hơn một giờ, bạn có thể tạo một công việc Transact-SQL có chứa mã sau đây, và lịch trình công việc mới để bắt đầu một vài phút trước khi công việc thực thi thời gian bắt đầu:
    BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0WAITFOR DELAY '1:00:00'COMMIT TRAN				
    Truy vấn này mua lại và giữ một khóa IX trên mytable trong một giờ, mà ngăn cản khóa leo thang trên bàn trong thời gian đó. Lô này không sửa đổi bất kỳ dữ liệu hoặc chặn các truy vấn (trừ khi truy vấn khác buộc một bảng khóa gợi ý TABLOCK hoặc nếu một người quản trị đã vô hiệu hoá trang hoặc hàng ổ khóa bằng cách sử dụng một sp_indexoption thủ tục được lưu trữ).
Ngoài ra, bạn có thể vô hiệu hóa khóa leo thang bằng cách cho phép dấu vết cờ 1211. Tuy nhiên, này cờ water vô hiệu hoá tất cả các khóa leo thang trên toàn cầu trong thể hiện của SQL Server. Khóa leo thang phục vụ một mục đích rất hữu ích trong SQL Máy chủ của tối đa hóa hiệu quả của các truy vấn nếu không làm chậm lại xuống bằng chi phí của việc mua và phát hành hàng nghìn ổ khóa. Khóa leo thang cũng giúp giảm thiểu bộ nhớ cần thiết để theo dõi các ổ khóa. Bộ nhớ SQL Server có thể tự động phân bổ cho các cấu trúc khóa là hữu hạn, vì vậy nếu bạn vô hiệu hóa khóa leo thang và bộ nhớ khóa mọc lớn đủ, cố gắng cấp phát ổ khóa bổ sung cho bất kỳ truy vấn có thể thất bại và những lỗi sau xảy ra:

Lỗi: 1204, mức độ nghiêm trọng: 19 Nhà nước: 1
SQL Server không thể có được một nguồn lực khóa vào lúc này. Chạy lại tuyên bố của bạn khi có rất ít người dùng hoạt động hoặc yêu cầu hệ thống quản trị viên để kiểm tra cấu hình nào về khóa và bộ nhớ máy chủ SQL.
Chú ý Khi xảy ra một lỗi "1204", nó dừng chế biến của các tuyên bố hiện tại và gây ra một cuộn ngược hoạt động giao dịch. Cuộn ngược chính nó có thể chặn người dùng hoặc dẫn đến một cơ sở dữ liệu dài thời gian phục hồi nếu bạn khởi động lại dịch vụ SQL Server.

Bằng cách sử dụng một gợi ý khóa như ROWLOCK chỉ thay đổi kế hoạch ban đầu khóa. Khóa gợi ý không ngăn chặn leo thang khóa.

Các phương pháp khác ngăn ngừa khóa leo thang mà sẽ được thảo luận trước đó trong bài viết này là lựa chọn tốt hơn so với việc cho phép các water cờ. Ngoài ra, các phương pháp khác thường kết quả trong tốt hơn hiệu suất cho truy vấn hơn vô hiệu hóa khóa leo thang cho toàn bộ Ví dụ. Microsoft khuyến cáo cho phép này dấu vết cờ duy nhất để giảm thiểu nghiêm trọng ngăn chặn đó gây ra bởi leo thang khóa trong khi các tùy chọn khác, chẳng hạn như những thảo luận trước đó trong bài viết này, đang được điều tra. Để kích hoạt một dấu vết cờ do đó nó đã được trên bất cứ khi nào SQL Server bắt đầu, thêm nó như một máy chủ tham số khởi động.

Thêm một tham số khởi động máy chủ, nhấp chuột phải vào các máy chủ trong quản lý doanh nghiệp SQL, bấm Thuộc tính, và sau đó vào các Tổng quát tab, bấm vào Tham số khởi động, và sau đó thêm các tham số sau đây (đúng như được hiển thị):
-T1211
Bạn phải chu kỳ dịch vụ SQL Server cho một tham số khởi động mới có hiệu lực. Nếu bạn chạy truy vấn sau đây trong truy vấn Analyzer cờ water có hiệu lực ngay lập tức:
DBCC TRACEON (1211, -1)				
Tuy nhiên, nếu bạn không thêm các -T1211 tham số khởi động, tác dụng của một traceon lệnh là bị mất khi các dịch vụ SQL Server cycled. Bật Quốc kỳ water ngăn ngừa bất kỳ trong tương lai khóa escalations, nhưng nó không phải đảo ngược bất kỳ escalations khóa đã xảy ra trong một hoạt động giao dịch.

Cảnh báo: Bài viết này được dịch tự động

Thuộc tính

ID Bài viết: 323630 - Xem lại Lần cuối: 08/27/2011 15:20:00 - Bản sửa đổi: 2.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlsetup kbinfo kbmt KB323630 KbMtvi
Phản hồi