Đã đăng vào Thứ Tư, 11:14 CH 14 phút đọc

Locks, blocks, deadlocks trong SQL Server

Locks, blocks và deadlocks là những vấn đề liên quan tới việc kiểm soát quyền truy cập đồng thời vào dữ liệu trong database.

Locking là cơ chế cần thiết để đảm bảo tính nhất quán và toàn vẹn dữ liệu trong các môi trường có nhiều giao dịch đồng thời. Nó không phải là xấu hay tốt một cách tự nhiên, mà là điều bắt buộc để database hoạt động đúng cách.

Blocking là hệ quả của locking. Mặc dù nó có thể gây ra các vấn đề về hiệu suất, nhưng nó là một phần bình thường của các hoạt động database và đảm bảo rằng các giao dịch chờ khóa để duy trì tính nhất quán của dữ liệu.

Deadlocking là một tình huống không mong muốn, khi các giao dịch mắc kẹt trong sự phụ thuộc tuần hoàn, chờ nhau giải phóng khóa. Deadlock có thể ảnh hưởng nghiêm trọng đến hiệu suất database và cần được tránh hoặc giải quyết nhanh chóng.

Mặc dù có những khác biệt này, cả ba khái niệm đều có mối liên hệ chặt chẽ với nhau và đóng vai trò quan trọng trong việc duy trì tính nhất quán và toàn vẹn dữ liệu trong hệ thống database.

1. ACID trong quản lý transaction

Transaction trong SQL Server là đơn vị công việc nhằm đảm bảo tính toàn vẹn của dữ liệu. SQL Server đảm bảo rằng tất cả các thao tác trong một transaction sẽ được thực hiện hoàn toàn (commit) hoặc bị hủy bỏ hoàn toàn (rollback) nếu có lỗi xảy ra, từ đó đảm bảo nguyên tắc ACID:

Atomicity: Tính nguyên tử – tất cả các thao tác phải được thực hiện hoặc không có thao tác nào được thực hiện.
Consistency: Tính nhất quán – dữ liệu phải duy trì trạng thái hợp lệ sau khi transaction kết thúc.
Isolation: Tính độc lập – các transaction không được can thiệp vào nhau.
Durability: Tính bền vững – khi transaction đã commit, thay đổi phải được lưu vĩnh viễn.

BEGIN TRANSACTION; 
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1; 
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; 
COMMIT;

Trong ví dụ trên, nếu một trong hai câu lệnh UPDATE gặp lỗi, toàn bộ transaction sẽ bị ROLLBACK, đảm bảo không có thay đổi nào được thực hiện một cách dang dở.

Trong MSSQL chế độ mặc định của transaction là auto-commit. Điều này có nghĩa là nếu bạn không bắt đầu một transaction một cách rõ ràng bằng cách sử dụng BEGIN TRANSACTION, mỗi câu lệnh SQL bạn thực thi sẽ tự động được commit sau khi hoàn thành.

Các chế độ transaction trong SQL Server

Auto-commit Transaction (Mặc định): Mỗi câu lệnh SQL (như INSERT, UPDATE, DELETE) được coi là một transaction tự động và sẽ commit ngay lập tức sau khi thực thi, nếu không có lỗi xảy ra.

UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;

Explicit Transaction (Transaction tường minh): Là giao dịch mà bạn tường minh khai báo bằng cách sử dụng BEGIN TRANSACTION. Bạn phải thực hiện COMMIT hoặc ROLLBACK một cách rõ ràng để hoàn thành hoặc hủy bỏ giao dịch.

BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;
COMMIT;

Implicit Transaction (Transaction ngầm định): Trong chế độ này, SQL Server sẽ tự động bắt đầu một transaction mới sau mỗi lần commit hoặc rollback, nhưng bạn cần tường minh commit hoặc rollback để hoàn tất.

SET IMPLICIT_TRANSACTIONS ON;
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;
COMMIT;

Như vậy, nếu bạn không rõ ràng chỉ định transaction, SQL Server sẽ tự động commit mỗi câu lệnh SQL mà bạn thực thi.

2. Isolation Levels

Isolation Levels xác định cách mà SQL Server quản lý khóa giữa các transaction, điều khiển việc một transaction có thể thấy dữ liệu chưa được commit từ một transaction khác hay không.

Cấp độ cách ly phổ biến:

Read Uncommitted

- Cho phép đọc dữ liệu chưa được commit, có thể dẫn đến "dirty read".

- Tính Cô lập thấp nhất, không bảo vệ dữ liệu, nhưng hiệu suất cao hơn do không yêu cầu khóa.

Read Committed

- Đảm bảo rằng giao dịch chỉ đọc dữ liệu đã được commit. Sử dụng khóa chia sẻ trong quá trình đọc để ngăn các giao dịch khác sửa đổi dữ liệu đang đọc. Điều này giúp tránh được Dirty Read, nhưng vẫn có thể xảy ra Non-Repeatable Read (đọc không lặp lại) hoặc Phantom Row (dòng bóng ma).

- Phổ biến và đảm bảo dữ liệu đọc là dữ liệu an toàn đã được commit.

Repeatable Read

- Khóa chia sẻ được giữ trên các hàng dữ liệu đã đọc trong suốt thời gian giao dịch, ngăn các giao dịch khác sửa đổi hoặc xóa dữ liệu đã đọc. Tuy nhiên, các giao dịch khác vẫn có thể chèn các hàng mới, dẫn đến vấn đề Phantom Rows.

- Giải quyết vấn đề Non-Repeatable Read, nhưng có thể làm giảm hiệu suất do cần giữ khóa lâu hơn.

Non-Repeatable Read: Mặc dù mức cách ly này ngăn chặn Dirty Read, nó vẫn có thể dẫn đến Non-Repeatable Read. Non-Repeatable Read xảy ra khi một giao dịch đọc một hàng dữ liệu tại hai thời điểm khác nhau, nhưng giá trị của dữ liệu bị thay đổi giữa các lần đọc do giao dịch khác đã thay đổi và commit dữ liệu sau lần đọc đầu tiên.

Serializable

- Cấp độ cô lập cao nhất. Nó ngăn cả việc sửa đổi, xóa và chèn các hàng dữ liệu có liên quan tới dữ liệu được đọc bởi giao dịch hiện tại. Tất cả các giao dịch khác phải đợi cho đến khi giao dịch hiện tại kết thúc.

- Đảm bảo tính toàn vẹn của dữ liệu và loại bỏ hoàn toàn các vấn đề Dirty Read, Non-Repeatable Read, và Phantom Rows, nhưng có thể làm tăng khả năng bị khóa và làm giảm hiệu suất.

Phantom Row: Đây là hiện tượng khi một giao dịch thực hiện hai lần truy vấn giống nhau, nhưng trong lần truy vấn thứ hai, kết quả trả về khác so với lần đầu vì một giao dịch khác đã thêm hoặc xóa các hàng dữ liệu giữa hai lần truy vấn.

Snapshot

- Giao dịch đọc dữ liệu dưới dạng bản sao nhất quán từ thời điểm bắt đầu giao dịch, mà không bị ảnh hưởng bởi các thay đổi từ các giao dịch khác. Không sử dụng khóa cho việc đọc, nên các giao dịch đọc và ghi không chặn lẫn nhau.

- Cải thiện khả năng cô lập mà không làm giảm hiệu suất do khóa, nhưng yêu cầu thêm tài nguyên để duy trì các phiên bản dữ liệu cũ (row versioning).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
BEGIN TRANSACTION; 
SELECT * FROM Orders WHERE OrderID = 1;
-- Các transaction khác không thể thay đổi dữ liệu của OrderID = 1 cho đến khi transaction này kết thúc.

Các mức độ cô lập thấp hơn như Read Uncommitted và Read Committed cho phép hiệu suất cao hơn, nhưng có nguy cơ dẫn đến các vấn đề về dữ liệu. Ngược lại, Serializable đảm bảo tính toàn vẹn dữ liệu cao nhất nhưng có thể gây tắc nghẽn giao dịch (blocking) cao hơn, ảnh hưởng đến hiệu suất.

Mặc định Isolation Levels trong MSSQL là Read Uncommitted

-- check isolation level current 
DBCC USEROPTIONS; 

-- set isolation level 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. Locking

Locking là cơ chế mà SQL Server sử dụng để kiểm soát việc truy cập vào các tài nguyên như bảng, hàng, hoặc trang dữ liệu khi có nhiều transaction đang thực hiện đồng thời đảm bảo rằng các phiên (session) không đọc hoặc ghi vào các tài nguyên đang được giao dịch khác sử dụng.

Lock cấp độ hàng (Row Lock): Khóa 1 row dữ liệu.
Lock cấp độ trang (Page Lock): Khóa 1 page dữ liệu (bao gồm nhiều row).
Lock cấp độ bảng (Table Lock): Khóa toàn bộ bảng dữ liệu.
Lock cấp độ database (Database Lock): Khóa database.
Lock cấp độ file (File Lock): Khóa toàn bộ file dữ liệu.
Lock cấp độ phân đoạn (Extent Lock): Khóa một phần của file dữ liệu.
Lock cấp độ filegroup (Filegroup Lock): Khóa một filegroup.
Lock cấp độ đối tượng (Object Lock): Khóa một đối tượng cụ thể như bảng, view, hoặc stored procedure.

SQL Server cung cấp một số loại khóa chính:

Shared Lock (S): Được sử dụng trong các truy vấn chỉ đọc (SELECT). Cho phép nhiều phiên truy cập nhưng không cho phép ghi.
Exclusive Lock (X): Dành cho các giao dịch ghi (INSERT, UPDATE, DELETE). Không cho phép bất kỳ truy cập nào từ các giao dịch khác.
Intent Lock (IX, IS): Chỉ báo hiệu rằng một số lượng khóa nhỏ hơn (như row lock) sẽ được giữ bên trong tài nguyên lớn hơn (như table lock).
Update Lock (U): Được sử dụng khi một transaction đọc dữ liệu với khả năng có thể cập nhật sau đó.

VD:
Giả sử có hai người dùng A và B đang thực hiện thao tác trên cùng một bảng Orders:
User A:
BEGIN TRANSACTION; SELECT * FROM Orders WHERE OrderID = 1; -- Shared Lock (S)

User B:
BEGIN TRANSACTION; UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1; -- Exclusive Lock (X)

User A có một Shared Lock trên bản ghi OrderID = 1 để đọc, trong khi User B muốn Exclusive Lock để cập nhật cùng bản ghi này. User B phải chờ đến khi User A hoàn thành việc đọc và giải phóng khóa.

-- Check current transaction lock
SELECT
request_session_id AS SPID,
resource_type,
resource_database_id,
resource_associated_entity_id AS ResourceID,
request_mode AS LockType,
request_status AS Status,
request_owner_type AS OwnerType
FROM
sys.dm_tran_locks;

-- show detail lock by sessionId
SELECT
session_id,
host_name,
program_name,
login_name,
status,
blocking_exec_sql.text AS BlockingSQL
FROM
sys.dm_exec_sessions AS s
LEFT JOIN
sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN
sys.dm_exec_sql_text(r.sql_handle) AS blocking_exec_sql
WHERE
s.session_id = <SPID>;

-- stop transaction locking
KILL <SPID>;

4. Blocking

Blocking xảy ra khi một transaction giữ khóa trên một tài nguyên (ví dụ, hàng dữ liệu), và một transaction khác muốn truy cập vào tài nguyên đó nhưng bị chặn do khóa chưa được giải phóng.

User A thực hiện một cập nhật:
BEGIN TRANSACTION; 
UPDATE Orders SET Status = 'Processing' WHERE OrderID = 1; 
-- Giữ khóa Exclusive (X) trên OrderID = 1

User B muốn đọc hàng dữ liệu đó:
SELECT * FROM Orders WHERE OrderID = 1;

User B sẽ bị block bởi User A vì transaction của User A chưa hoàn tất, và User B không thể đọc dữ liệu cho đến khi transaction của User A COMMIT hoặc ROLLBACK.


-- Check transaction blocking
SELECT
blocking_session_id AS BlockingSPID,
session_id AS WaitingSPID,
wait_type,
wait_time,
wait_resource
FROM
sys.dm_exec_requests
WHERE
blocking_session_id <> 0;

- Find All the Blocked Processes:
SELECT
spid,
status,
loginame=SUBSTRING(loginame,1,12),
hostname=SUBSTRING(hostname,1, 12),
blk = CONVERT(char(3), blocked),
dbname=SUBSTRING(DB_NAME(dbid),1, 10),
cmd,
waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

- Identify the blocking query:
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

- View all current processes / sessions on the server:
select * from master.dbo.sysprocesses

Để giảm thiểu và giải quyết vấn đề blocking trong SQL Server, bạn có thể áp dụng các giải pháp sau:

Tối ưu hóa truy vấn

- Sử dụng chỉ mục (index): Đảm bảo rằng các bảng có chỉ mục phù hợp để tăng tốc độ truy vấn và giảm thời gian giữ khóa.

- Tránh truy vấn không cần thiết: Chỉ truy vấn các cột và hàng cần thiết để giảm tải cho hệ thống.

2. Sử dụng các mức độ cô lập (isolation levels) phù hợp

- Read Committed Snapshot Isolation (RCSI): Sử dụng RCSI để giảm blocking bằng cách cho phép các truy vấn đọc dữ liệu phiên bản cũ mà không cần chờ khóa.

- Snapshot Isolation: Cho phép các transaction đọc dữ liệu nhất quán mà không cần chờ khóa.

3. Phân chia transaction

- Giữ transaction ngắn gọn: Chia nhỏ các transaction dài thành các transaction ngắn hơn để giảm thời gian giữ khóa.

- Commit sớm: Commit các thay đổi sớm nhất có thể để giải phóng khóa.

4. Sử dụng các kỹ thuật truy vấn không khóa (lock-free):

- NOLOCK hint: Sử dụng NOLOCK hint trong các truy vấn SELECT để đọc dữ liệu mà không cần khóa. Lưu ý rằng điều này có thể dẫn đến việc đọc dữ liệu không nhất quán.

- READPAST hint: Sử dụng READPAST hint để bỏ qua các hàng bị khóa bởi các transaction khác.

VD:
SELECT * 
FROM Orders WITH (NOLOCK)
WHERE OrderDate > '2023-01-01';
Lưu ý rằng việc sử dụng NOLOCK có thể dẫn đến việc đọc dữ liệu không nhất quán, vì vậy cần cân nhắc kỹ trước khi áp dụng.

5. Theo dõi và phân tích blocking:

- Sử dụng SQL Server Profiler hoặc Extended Events: Theo dõi các sự kiện blocking để xác định nguyên nhân và thời gian blocking.

- Dynamic Management Views (DMVs): Sử dụng DMVs như sys.dm_exec_requests và sys.dm_tran_locks để phân tích các khóa và blocking.

6. Cải thiện thiết kế database:

- Chuẩn hóa database: Đảm bảo database được thiết kế chuẩn hóa để giảm thiểu các xung đột khóa.

- Sử dụng partitioning: Sử dụng phân vùng bảng để giảm kích thước bảng và tăng hiệu suất truy vấn.

5. Deadlock

Deadlock xảy ra khi hai hoặc nhiều transaction giữ các khóa trên các tài nguyên khác nhau và đang chờ nhau giải phóng khóa. Điều này tạo ra một vòng lặp, khiến không transaction nào có thể tiếp tục. SQL Server có thể phát hiện deadlock và buộc phải dừng (rollback) một trong các transaction để giải phóng tài nguyên.

User A:
BEGIN TRANSACTION; 
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1; -- Giữ khóa Exclusive (X) trên OrderID = 1 
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1; -- Đang chờ khóa trên CustomerID = 1

User B:
BEGIN TRANSACTION; 
UPDATE Customers SET Balance = Balance + 100 WHERE CustomerID = 1; -- Giữ khóa Exclusive (X) trên CustomerID = 1 
UPDATE Orders SET Status = 'Processing' WHERE OrderID = 1; -- Đang chờ khóa trên OrderID = 1

User A giữ khóa trên OrderID = 1 và chờ tài nguyên CustomerID = 1 mà User B đang giữ. Đồng thời, User B giữ khóa trên CustomerID = 1 và chờ tài nguyên OrderID = 1 của User A. SQL Server sẽ phát hiện deadlock và rollback một trong hai transaction để giải quyết vấn đề.

Deadlock Detection (Phát hiện Deadlock)

SQL Server cung cấp các công cụ như Extended Events, SQL Server Profiler, và các hệ thống bảng động như sys.dm_tran_locks, sys.dm_exec_requests để theo dõi và phát hiện deadlock. Khi phát hiện deadlock, SQL Server sẽ ghi lại chi tiết vào Error Log và giải quyết bằng cách dừng một transaction.

- Detection Deadlock

DECLARE @filepath NVARCHAR(260)
SELECT @filepath = SLC.PATH
FROM sys.dm_os_server_diagnostics_log_configurations AS SLC;
SELECT @filepath = @filepath + N'system_health_*.xel'
DROP TABLE IF EXISTS #DeadLockTable
SELECT
CONVERT(XML, event_data) AS SessionData
INTO #DeadLockTable
FROM sys.fn_xe_file_target_read_file(@filepath, NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
SELECT
SessionData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UTCDeadLockOccuredAt,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,
SessionData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS SystemTime,
SessionData.query('event/data/value/deadlock') AS XMLReport
FROM #DeadLockTable
ORDER BY UTCDeadLockOccuredAt DESC;

- Kết quả sẽ ra được thông tin Graph lưu trữ dưới dạng XML. Để xem chi tiết Graph, bạn làm theo các bước sau:
  1. Chạy truy vấn để lấy thông tin Graph dưới dạng XML.
  2. Click vào một hàng kết quả XML của truy vấn để hiển thị toàn bộ XML trong SSMS.
  3. Copy toàn bộ nội dung XML và dán vào Notepad.
  4. Lưu file Notepad dưới định dạng .xdl.
  5. Mở file .xdl vừa tạo trong SSMS để xem Graph.
Graph này sẽ giúp bạn biết được các truy vấn đang tham gia vào ngữ cảnh Deadlock và hiểu chúng đang tranh chấp những khóa nào. Từ đó, bạn có thể chỉnh sửa truy vấn, chỉ mục (index), hoặc bảng (table) để tránh deadlock xảy ra.

Cách Giải Quyết Vấn Đề DeadLock

Để giải quyết vấn đề deadlock, có một số phương pháp và kỹ thuật có thể áp dụng:

1. Sắp xếp thứ tự truy cập tài nguyên

- Đảm bảo các transaction truy cập tài nguyên theo cùng một thứ tự.

Thay vì:
-- Transaction A
BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1;
COMMIT;

-- Transaction B
BEGIN TRANSACTION;
UPDATE Customers SET Balance = Balance + 100 WHERE CustomerID = 1;
UPDATE Orders SET Status = 'Processing' WHERE OrderID = 1;
COMMIT;

Nên sử dụng:
-- Transaction A
BEGIN TRANSACTION;
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1;
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;
COMMIT;

-- Transaction B
BEGIN TRANSACTION;
UPDATE Customers SET Balance = Balance + 100 WHERE CustomerID = 1;
UPDATE Orders SET Status = 'Processing' WHERE OrderID = 1;
COMMIT;

Trước khi tối ưu hóa:
Transaction A truy cập bảng Orders trước, sau đó truy cập bảng Customers.
Transaction B truy cập bảng Customers trước, sau đó truy cập bảng Orders.
Điều này có thể dẫn đến deadlock nếu Transaction A giữ khóa trên Orders và chờ khóa trên Customers, trong khi Transaction B giữ khóa trên Customers và chờ khóa trên Orders.
Sau khi tối ưu hóa:
Cả Transaction A và Transaction B đều truy cập bảng Customers trước, sau đó truy cập bảng Orders.
Việc sắp xếp thứ tự truy cập tài nguyên theo cùng một thứ tự giúp giảm khả năng xảy ra deadlock, vì các transaction không còn chờ nhau theo vòng lặp phụ thuộc.

2. Sử dụng NOWAIT hoặc READPAST

- READPAST: Bỏ qua các hàng đã bị khóa.

- NOWAIT: Ngay lập tức hủy bỏ transaction nếu tài nguyên bị khóa.

VD:
UPDATE Orders WITH (NOWAIT)
SET Status = 'Shipped'
WHERE OrderID = 1;

3. Giảm thời gian giữ khóa:

- Rút ngắn thời gian transaction, chỉ giữ khóa khi cần thiết.

VD:
-- Thao tác không cần khóa
DECLARE @TotalOrders INT;
SELECT @TotalOrders = COUNT(*) FROM Orders WHERE CustomerID = 1;

BEGIN TRANSACTION;
-- Thao tác cần khóa
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;
COMMIT;

-- Thao tác không cần khóa
DECLARE @CustomerBalance DECIMAL(18, 2);
SELECT @CustomerBalance = Balance FROM Customers WHERE CustomerID = 1;

BEGIN TRANSACTION;
-- Thao tác cần khóa
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1;
COMMIT;

Trước khi tối ưu hóa: Transaction giữ khóa trong suốt quá trình thực hiện, bao gồm cả các thao tác không cần thiết phải giữ khóa, làm tăng thời gian giữ khóa và khả năng xảy ra blocking và deadlock.
Sau khi tối ưu hóa: Transaction chỉ giữ khóa khi cần thiết và commit sớm nhất có thể, giảm thời gian giữ khóa và giảm khả năng xảy ra blocking và deadlock.
Bằng cách tách các thao tác không cần khóa ra khỏi transaction và chỉ giữ khóa khi cần thiết, bạn có thể giảm thiểu thời gian giữ khóa và cải thiện hiệu suất của hệ thống cơ sở dữ liệu.

4. Sử dụng isolation level thích hợp:

- Chọn mức isolation phù hợp để giảm khả năng xảy ra deadlock.

VD:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION
Các câu lệnh SQL
COMMIT