Mô tả chặn và khóa

12 phút

Khóa là một tính năng chính của các cơ sở dữ liệu quan hệ, cần thiết để duy trì tính nguyên tử, tính nhất quán và tính cô lập của mô hình ACID. Tất cả các hành động chặn RDBMSs sẽ vi phạm tính nhất quán và cô lập của cơ sở dữ liệu viết. Lập trình viên SQL phải bắt đầu và kết thúc giao dịch ở đúng điểm để đảm bảo tính nhất quán của dữ liệu. Công cụ cơ sở dữ liệu cung cấp cơ chế khóa để bảo vệ tính nhất quán lô-gic của các bảng bị ảnh hưởng, nền tảng cho mô hình quan hệ.

Trong SQL Server, việc chặn xảy ra khi một quá trình giữ một khóa trên một tài nguyên cụ thể (hàng, trang, bảng, cơ sở dữ liệu) và một quá trình thứ hai cố gắng để có được một khóa với một loại khóa không tương thích trên cùng một tài nguyên. Thông thường, khóa được giữ trong một khoảng thời gian ngắn và sau khi quá trình giữ khóa phát hành khóa, quá trình bị chặn có thể có được khóa và hoàn tất giao dịch của khóa.

SQL Server khóa lượng dữ liệu nhỏ nhất cần thiết để hoàn tất một giao dịch, cho phép số lượng đồng thời tối đa. Ví dụ: nếu SQL Server khóa một hàng đơn, tất cả các hàng khác trong bảng vẫn sẵn dùng cho các quy trình khác, cho phép làm việc đồng thời. Tuy nhiên, mỗi khóa yêu cầu tài nguyên bộ nhớ, do đó, nó không có hiệu quả chi phí cho một quá trình để giữ hàng ngàn khóa cá nhân trên một bảng duy nhất. Để cân bằng đồng thời với chi phí, SQL Server sử dụng kỹ thuật được gọi là tăng cấp khóa. Nếu cần khóa nhiều hơn 5.000 hàng trên một đối tượng duy nhất trong một câu lệnh, SQL Server sẽ nâng cấp nhiều khóa hàng thành một khóa bảng duy nhất.

Khóa là một hành vi bình thường và xảy ra thường xuyên trong ngày. Sự cố chỉ trở nên có vấn đề khi gây ra hiện tượng chặn không được giải quyết nhanh chóng. Có hai loại sự cố hiệu năng gây ra do việc chặn:

Quy trình sẽ khóa một tập hợp tài nguyên trong một khoảng thời gian dài trước khi phát hành chúng, khiến các quy trình khác chặn và làm giảm hiệu suất truy vấn cũng như đồng thời.
Một quá trình có được khóa trên một tập hợp các tài nguyên và không bao giờ phát hành chúng, đòi hỏi sự can thiệp của người quản trị để giải quyết.

Deadlocking là một kịch bản chặn xảy ra khi một giao dịch giữ một khóa trên một tài nguyên, và một giao dịch giữ một khóa trên một nguồn tài nguyên khác. Mỗi giao dịch sau đó cố gắng để có được một khóa trên tài nguyên hiện đang bị khóa bởi các giao dịch khác, dẫn đến một chờ đợi vô hạn như không có giao dịch có thể hoàn thành. Công cụ SQL Server phát hiện các kịch bản và giải quyết đình trệ bằng cách giết chết một trong các giao dịch, dựa trên đó giao dịch đã thực hiện số lượng công việc ít nhất cần phải được quay trở lại. Giao dịch bị giết được gọi là nạn nhân bế tắc. Bế tắc được ghi lại trong phiên sự kiện mở rộng system_health , được bật theo mặc định.

Điều quan trọng là phải hiểu khái niệm giao dịch. Autocommit là chế độ mặc định của SQL Server và Cơ sở dữ liệu Azure SQL, có nghĩa là những thay đổi được thực hiện bởi câu lệnh sau đây sẽ tự động được ghi lại trong nhật ký giao dịch của cơ sở dữ liệu.

INSERT INTO DemoTable (A) VALUES (1);

Để cho phép nhà phát triển có quyền kiểm soát chi tiết hơn đối với mã ứng dụng của họ, SQL Server cũng cho phép bạn kiểm soát rõ ràng các giao dịch của mình. Truy vấn sau đây sẽ khóa một hàng trong bảng DemoTable sẽ không được phát hành cho đến khi một lệnh tiếp theo để cam kết giao dịch được thêm vào.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

Cách thích hợp để viết truy vấn sau đây là như sau:

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

Lệnh cam COMMIT TRANSACTION kết rõ ràng một bản ghi các thay đổi đối với nhật ký giao dịch. Dữ liệu đã thay đổi cuối cùng sẽ đi vào tệp dữ liệu một cách không đồng bộ. Các giao dịch này đại diện cho một đơn vị làm việc với công cụ cơ sở dữ liệu. Nếu nhà phát triển quên phát hành COMMIT TRANSACTION lệnh, giao dịch vẫn mở và các khóa sẽ không được phát hành. Đây là một trong những lý do chính cho các giao dịch chạy dài.

Cơ chế khác mà bộ máy cơ sở dữ liệu sử dụng để giúp đồng thời của cơ sở dữ liệu là lập phiên bản hàng. Khi cấp độ cô lập phiên bản hàng được bật cho cơ sở dữ liệu, công cụ duy trì các phiên bản của mỗi hàng được sửa đổi trong TempDB. Điều này thường được sử dụng trong khối lượng công việc sử dụng hỗn hợp, để ngăn không cho truy vấn đọc chặn truy vấn đang ghi vào cơ sở dữ liệu.

Để giám sát các giao dịch đang mở đang chờ chuyển giao hoặc quay lui, hãy chạy truy vấn sau:

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

Mức độ cách ly

SQL Server cung cấp một số mức độ cách ly để cho phép bạn xác định mức độ nhất quán và chính xác mà bạn cần đảm bảo cho dữ liệu của mình. Mức độ cô lập cho phép bạn tìm thấy sự cân bằng giữa đồng thời và nhất quán. Mức độ cô lập không ảnh hưởng đến các ổ khóa được thực hiện để ngăn sửa đổi dữ liệu. Một giao dịch sẽ luôn nhận được một khóa độc quyền trên dữ liệu đang sửa đổi. Tuy nhiên, mức độ cô lập của bạn có thể ảnh hưởng đến thời lượng khóa của bạn được giữ. Mức độ cô lập thấp hơn làm tăng khả năng của nhiều người dùng quá trình truy cập dữ liệu cùng một lúc, nhưng tăng nguy cơ nhất quán dữ liệu có thể xảy ra. Mức độ cô lập trong SQL Server như sau:

Đọc không cam kết - Mức cách ly thấp nhất có sẵn. Đọc bẩn được cho phép, có nghĩa là một giao dịch có thể thấy các thay đổi được thực hiện bởi một giao dịch khác chưa được cam kết.
Đã đọc cam kết – cho phép một giao dịch đọc dữ liệu đã đọc trước đó, nhưng không được sửa đổi bởi một giao dịch khác mà không cần đợi giao dịch đầu tiên kết thúc. Cấp độ này cũng sẽ phát hành khóa đọc ngay sau khi thao tác chọn được thực hiện. Đây là mức SQL Server mặc định.
Đọc lặp lại - Cấp độ này giữ khóa đọc và ghi thu được trên dữ liệu đã chọn cho đến khi kết thúc giao dịch.
Serializable – Đây là mức độ cách ly cao nhất nơi các giao dịch được cô lập. Đọc và ghi khóa được lấy trên dữ liệu được chọn và không được phát hành cho đến khi kết thúc giao dịch.

SQL Server cũng bao gồm hai mức cách ly bao gồm lập phiên bản hàng.

Đọc ảnh chụp nhanh đã cam kết - Ở cấp độ này, các hoạt động đọc không có khóa hàng hoặc trang và công cụ hiển thị cho mỗi hoạt động một ảnh chụp nhanh nhất quán của dữ liệu như nó đã tồn tại khi bắt đầu truy vấn. Mức này thường được sử dụng khi người dùng đang chạy truy vấn báo cáo thường xuyên đối với một cơ sở dữ liệu OLTP, để ngăn chặn các hoạt động đọc chặn các hoạt động ghi.
Ảnh chụp nhanh – Cấp độ này cung cấp tính nhất quán khi đọc ở cấp độ giao dịch thông qua lập phiên bản hàng. Mức này dễ bị xung đột cập nhật. Nếu một giao dịch đang chạy ở cấp độ này đọc dữ liệu được sửa đổi bởi một giao dịch khác, một bản cập nhật của giao dịch ảnh chụp màn hình sẽ bị chấm dứt và quay lui. Đây không phải là sự cố về cách ly ảnh chụp nhanh đã đọc.

Mức độ cô lập được đặt cho mỗi phiên với lệnh T-SQL #D0, như minh họa:

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

Không có cách nào để đặt mức độ phân tách chung tất cả các truy vấn đang chạy trong cơ sở dữ liệu hoặc cho tất cả các truy vấn do một người dùng cụ thể chạy. Đó là cài đặt mức phiên.

Giám sát để chặn sự cố

Việc xác định các vấn đề chặn có thể khó khăn do tính chất lẻ t xem của chúng. DMV, khi sys.dm_tran_lockstham gia với , cung cấp sys.dm_exec_requeststhông tin về các khóa được tổ chức bởi mỗi phiên. Một cách hiệu quả hơn để theo dõi các sự cố chặn là sử dụng công cụ Sự kiện Mở rộng trên cơ sở liên tục.

Việc chặn sự cố thường được chia thành hai loại:

Thiết kế giao dịch kém: Ví dụ, một giao dịch không có sẽ không COMMIT TRANSACTION bao giờ kết thúc. Việc cố gắng thực hiện quá nhiều công việc trong một giao dịch duy nhất hoặc thực hiện giao dịch phân tán bằng kết nối máy chủ được liên kết có thể dẫn đến hiệu suất không thể đoán trước.
Các giao dịch chạy lâu gây ra bởi thiết kế sơ đồ: Việc này thường liên quan đến cập nhật trên cột thiếu chỉ mục hoặc truy vấn cập nhật thiết kế kém.

Việc giám sát các vấn đề về hiệu năng liên quan đến khóa cho phép bạn nhanh chóng xác định tình trạng giảm hiệu suất liên quan đến khóa.

Để biết thêm thông tin về cách giám sát chặn, hãy xem Hiểu và giải quyết các sự cố chặn SQL Server.

Phản hồi

Trang này có hữu ích không?