Phân tích DeadLock trong cơ sở dữ liệu và cách phòng tránh

1. Nguyên lý consistency của relational database – SQL Server

SQL Server nói riêng và các database quan hệ nói chung là dạng database có độ nhất quán dữ liệu cao nhất. Dữ liệu trong database luôn ở dạng consistency tại mọi thời điểm. Nghĩa là với cùng một dữ liệu thì không thể có việc đọc ghi tại cùng một thời điểm. Việc đọc ghi cùng một dữ liệu sẽ dẫn tới hiện tượng lock lẫn nhau để đảm bảo dữ liệu được consistency. Điều đó dẫn tới một số tình huống sau:

• Lệnh Select sẽ xung đột với các lệnh Update, Insert, Delete. Vì các lệnh Update, Insert, Delete làm thay đổi dữ liệu của một table do đó tại một thời điểm nếu thực hiện lệnh select thì sẽ không thể insert, update, delete trên cùng một table hoặc ngược lại. Điều này rất quan trọng, vì nếu không lock dữ liệu thì sẽ dẫn tới việc đọc dữ liệu ra sai so với thực tế. Ví dụ: kiểm tra đơn hàng xem đã được xác nhận hay chưa, nhưng ngay tại thời điểm đó đơn hàng được update thành trạng thái xác nhận, như vậy nếu không lock dữ liệu đọc ra sẽ khác với thực tế và có thể dẫn tới các logic bị sai. Tương tự, nếu khi insert hoặc delete dữ liệu xảy ra cùng thời điểm với lệnh tìm kiếm nếu không lock sẽ dẫn tới kết quả sai lệch.
• Các lệnh Select không lock lẫn nhau. Các lệnh select thự hiện chế độ share lock, nghĩa là việc thực hiện lệnh đọc đồng thời cùng một dữ liệu thì không bị ảnh hưởng lẫn nhau.
• Các lệnh Insert, Update, Delete lock theo row không lock theo table. Nghĩa là có thể thực hiện đồng thời các lệnh đó trên cùng một table miễn là chúng không tranh chấp nhau cùng một row dữ liệu. Lock để đảm bảo tính consistency data là một trong các ưu điểm nổi bật của cơ sở dữ liệu quan hệ. Vì vậy nếu đã dùng cơ sở dữ liệu quan hệ thì phải biết tận dụng nó. Tránh tối đa việc sử dụng các chế độ bỏ lock như mode nolock vì có thể dẫn tới hiện tượng đọc sai lệch dữ liệu (lệch giá cả, số lượng, trạng thái …).

 

2. Lock và deadlock

Lock thực ra không đáng sợ, nó chỉ ảnh hưởng một phần tới performance khi các lệnh thực hiện đồng thời có thể sẽ phải đợi nhau một cách tuần tự. Nhưng deadlock thì khác, nó dẫn tới không lệnh nào được thực hiện. Vậy deadlock là gì?

Deadlock là hiện tượng tranh chấp tài nguyên giữa hai hay nhiều lệnh trong đó lệnh này giữ tài nguyên mà lệnh kia cần dẫn tới việc không lệnh nào có thể kết thúc để giải phóng tài nguyên. Điều đó thể hiện qua hình minh họa:

Trong trường hợp này: Giao dịch T1 giữ được tài nguyên R1, nhưng cần tài nguyên R2 để kết thúc và giải phóng R1, nhưng R2 lại đang bị T2 giữ và T2 cần R1 để kết thúc và sau đó giải phóng R2. Trong trường hợp này cả T1 và T2 không thể kết thúc. Trường hợp này trong SQL Server sẽ phải chọn kill một trong hai giao dịch.

 

3. Demo tái tạo hiện tượng deadlock giữa các sql transaction.

Một transaction được thực thi và hoàn tất khi và chỉ khi tất cả các lệnh trong một transaction được hoàn tất.

Deadlock trong database sẽ chỉ xảy ra khi có nhiều hơn một tài nguyên cần phải bị chiếm giữa hoàn tất giao dịch. Xét một câu trúc DB đơn giản như sau:

Thực hiện hai giao dịch đồng thời:

• Giao dịch 1:
  • Insert bảng A
  • Insert bảng B
• Giao dịch 2:
  • Select bảng B
  • Select bảng A

Code demo viết bằng ngôn ngữ C# thực hiện các truy vấn tới cơ sở dữ liệu:

Transaction 1:

static void Main( string[] args )
{
Console.ReadKey();
Console.WriteLine( "Start" );
using ( TransactionScope transaction = new TransactionScope() )
{
using ( SqlConnection con = new SqlConnection( connectionString2 ) )
{
con.Open();
string cmd = "Select * From B";
SqlCommand command = new SqlCommand( cmd, con );
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
Console.WriteLine( (string)reader[ "Name" ] );
}
reader.Close();
Console.WriteLine( "-------" );
cmd = "Select * From A";
command = new SqlCommand( cmd, con );
reader = command.ExecuteReader();
while ( reader.Read() )
{
Console.WriteLine( (string)reader[ "Name" ] );
}
}
transaction.Complete();
}

Console.WriteLine( "Finish!" );
Console.ReadKey();
}

Transaction 2:

static void Main( string[] args )
{
Console.ReadKey();
Console.WriteLine( "Start" );
using ( TransactionScope transaction = new TransactionScope() )
{
using ( SqlConnection con = new SqlConnection( connectionString2 ) )
{
con.Open();
string cmd = "Select * From B";
SqlCommand command = new SqlCommand( cmd, con );
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
Console.WriteLine( (string)reader[ "Name" ] );
}
reader.Close();
Console.WriteLine( "-------" );
cmd = "Select * From A";
command = new SqlCommand( cmd, con );
reader = command.ExecuteReader();
while ( reader.Read() )
{
Console.WriteLine( (string)reader[ "Name" ] );
}
}
transaction.Complete();
}

Console.WriteLine( "Finish!" );
Console.ReadKey();
}

Ở đây, trong giao dịch 1 ta để thêm lệnh Thread.Sleep(5000) để quan sát hiện tượng deadlock được rõ hơn. Thực hiện hai chương trình gần như đồng thời, chương trình của giao dịch 1 chạy trước, ngay sau là chương trình giao dịch 2 thì sẽ ghi nhận deadlock exception xảy ra:

Nguyên nhân xảy ra:

• Giao dịch 1 vào trước chiếm giữ bảng A để insert, sau đó dừng 5s để tiếp tục chiếm giữa bảng B insert. Trong thời gian chờ bảng B hoàn toàn không bị lock. Vì trong một giao dịch nên lệnh Insert vào bảng A chưa kết thúc chừng nào lệnh insert bảng B kết thúc, bảng A bị giữ bởi T1.
• Giao dịch 2 vào sau nhưng chiếm được bảng B để select trước, sau đó nó cần chiếm bảng A để tiếp tục select. Do trong cùng một giao dịch nên bảng B bị giữ chừng nào T2 chiếm được bảng A để thực hiện hoàn tất một giao dịch.

Như vậy cả T1 và T2 đều lock lẫn nhau và không thằng nào thoát ra được. Dẫn tới SQL Server buộc phải kill một trong hai giao dịch, trong trường hợp này là giao dịch T2 bị kill, T1 thực hiện bình thường.

Một điều cần lưu ý là nếu hai giao dịch trên chỉ có các lệnh insert và update, delete mà không có các lệnh select thì khả năng deadlock thấp hơn rất nhiều. Vì các lệnh insert và update chỉ lock row, do đó việc tranh chấp dẫn tới deadlock khó xảy ra.

 

4. Chiến lược giảm thiếu nguy cơ deadlock

Không đảm bảo có thể loại bỏ hoàn toàn deadlock, nhưng áp dụng các chiến lược sau sẽ giúp việc xảy ra deadlock ít xảy ra hơn:

• Giảm thiểu số lượng bảng cần tác động trong một giao dịch, giao dịch có số bảng tham gia càng ít thì khả năng xảy ra deadlock càng thấp.
• Kiểm soát việc thưc thi các giao dịch sau cho thứ tự tác động các bảng là giống nhau như vậy sẽ chỉ xảy ra hiện tượng lock chứ không xảy ra hiện tượng deadlock.
• Thực hiện giao dịch dạng batch thay vì gọi nhiều lần từ trong ứng dụng tới database. Nghĩa là thay vì gọi rời rạc thì generate ra một script duy nhất rồi gửi vào database hoặc gọi store procedure để thực thi giao dịch. Việc này giúp giảm thiếu deadlock vì hai lý do sau:
  • Giảm thời gian round trip qua đường mạng do việc gọi đi gọi lại do đó giao dịch kết thúc nhanh hơn.
  • Việc gửi script dạng batch, hoặc thực thi store procedure giúp sql server phân tích được toàn bộ mã script từ đó tạo ra một execution plan hiệu quả, khả năng tránh deadlock cao hơn. Không như việc gọi thực thi nhiều lần từ trong ứng dụng, SQL Server không thể biết sau lệnh thứ nhất thì lệnh tiếp theo là gì do đó không thể tạo ra chiến lược giải quyết deadlock. Cùng là ví dụ trên nhưng nếu để thành store procedure và thực thi đồng thời thì hiện tượng deadlock không xảy ra:

Transaction 1:

CREATE PROCEDURE [dbo].[Test1]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION TEST1
-- Insert statements for procedure here
Insert Into A(Name) Values('Zz')
WAITFOR DELAY '00:00:10';
Insert Into B(Name) Values('Xz')
COMMIT TRANSACTION TEST1
END

Transaction 2:

CREATE PROCEDURE [dbo].[Test2]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION TEST2
select *
from B

select *
from A
COMMIT TRANSACTION TEST2
END

Mặc dù store procedure Test1 có thời gian delay lâu hơn 10 giây, nhưng hiện tượng deadlock vẫn không xảy ra.

• Tránh tương tác khi thực thi giao dịch để đảm bảo giao dịch thực thi nhanh nhất có thể. Ví dụ: insert vào bảng A, chờ xác nhận người dùng, insert vào bảng B rồi hoàn tất.
• Sử dụng các mức độ lock phù hợp (Isolation Level)

5. Lưu ý về việc sử dụng SQL Transaction.

Việc sử dụng transaction cần phải hiểu ý nghĩa và không nên lạm dụng. Giả sử để thực hiện được một tính năng cần tác động tới hai hoặc nhiều bảng, vậy có nhất thiết phải gói tất cả các lệnh vào một giao dịch? Để trả lời chính xác thì phải hiểu thế nào là một giao dịch.

Một giao dịch là chuỗi các hành động mà nếu một trong các hành động không hoàn thành thì toàn bộ các hành động khác cũng phải không được hoàn thành. Xét ví dụ kinh điển về giao dịch rút tiền:

Lệnh 1: Thực hiện lệnh update trừ tiền
Lệnh 2: Xác nhận người dùng nhận được tiền

Như vậy nếu lệnh 1 trừ tiền xong, nhưng lệnh xác nhận người dùng nhận được tiền không thực thi được thì lệnh 1 cũng phải không được thực hiện mà phải rollback về trạng thái đầu.

Nhưng nếu có một nghiệp vụ cập nhật thông tin hồ sơ cho học sinh khác như sau:
Lệnh 1: cập nhật tuổi bảng profile
Lệnh 2: cập nhật điểm bảng lớp học
Rõ ràng để thực hiện nghiệp vụ trên cần phải thực hiện cả hai hành động trên, nhưng không có nghĩa là nếu lệnh 2 không thành công thì lệnh 1 không được thực hiện, vì sau đó ta hoàn toàn có thể kiểm tra là lệnh 1 mà thực thi rồi thì chỉ thực thi lệnh 2, việc không thực thi đồng thời hai lệnh trên không làm sai nghiệp vụ của hệ thống.

Do đó khi thực hiện giao dịch cần phải rất thận trọng và phải có đánh giá tổng thể xem việc thực thi đó có bị xung đột nhau hay không? Việc sử dụng tràn lan transation, thậm chí cả với các lệnh select đọc dữ liệu là vô cùng tai hại và đó là thể hiện sự máy móc, thiếu hiểu biết trong lập trình.

• Tránh sử dụng transaction tối đa có thể
• Transaction càng ngắn càng tốt

6. Lưu ý về việc sử dụng các mode nolock, readpast.

Lock trong CSDL là vô cùng quan trọng, nó đảm bảo tính consistency database. Nhưng trong một số trường hợp cần hiệu năng cao thì có thể sử dụng một số mode đặc biệt để đọc, ghi mà không cần lock dữ liệu:

• Nolock: là bỏ hoàn toàn chế đố lock, cho phép đọc dữ liệu bẩn (dirty read).
• Readpast: đọc các dữ liệu không bị lock, bỏ qua các dữ liệu đang bị lock (bởi lệnh insert, update…). Cả hai lệnh trên đều ảnh hưởng tới consistency data. Do đó phải đặc biệt thận trọng khi sử dụng và chỉ khi sử dụng vào những trường hợp vô cùng đặc biệt. Vì nếu không sau này có thể xảy ra các sai lệch dẫn tới các nghiệp vụ bị sai. Mà việc trace lại là vô cùng khó khăn, do việc sai lệch chỉ diễn ra trong tích tắc. Ví dụ như sai lệnh số lượng hàng hóa trong kho, sai lệch về giá…

Trích nguồn: PAVietNam