SQL Lock Conflict là gì?

Hãy tưởng tượng Database giống như một ngôi nhà và những session kết nối vào database giống như những người đang sống trong căn nhà đó.

Trong căn nhà này có một nguyên tắc:

• Tại một thời điểm, mỗi đồ vật trong nhà chỉ được sử dụng bởi 1 người duy nhất.
• Nếu nhiều người đều muốn sử dụng chung một đồ vật, chúng ta sẽ áp dụng nguyên tắc “người nào tới trước thì được sử dụng trước”, những người đằng sau sẽ phải đợi.

Ví dụ:

• Hai người A và B cùng có mặt trong tòa nhà C
• Cả hai người đều muốn sử dụng máy chấm công được đặt ở cửa ra vào.
• Ông A là người đến lúc 9 giờ và B đến lúc 9 giờ 5 phút.
• Theo nguyên tắc của “người vào tới trước thì được sử dụng trước”, lúc này mọi chuyện sẽ xảy ra như sau:
• A sẽ được sử dụng máy chấm công trước
• B sẽ buộc phải đợi khi A sử dụng xong thì mới được dùng. Việc đợi này có thể diễn ra LÂU hay CHÓNG là do A quyết định.

Hiện tượng tôi vừa mô tả bên trên chính là hình ảnh của SQL Lock Conflict (hay một số anh em còn gọi đơn giản là SQL Lock hoặc Transaction Lock hoặc Lock Conflict, một số người có thể gọi nhanh là Lock, dù gọi thế nào đi nữa tôi muốn mọi người hãy hiểu về bản chất của nó.).

SQL Lock Conflict xảy ra khi có nhiều transaction cùng thay đổi một bản ghi trong cùng TABLE.

Transaction nào tới trước sẽ thực hiện “KHÓA” (thuật ngữ gọi là LOCK) các bản ghi (ROWS) bị thay đổi lại, bản chất phải có cơ chế này để đảm bảo tính toàn vẹn cho dữ liệu.

Tất cả những transaction tới sau sẽ không thể thay đổi các bản ghi (ROWS) đã bị khóa.

Các “khóa” này sẽ được giải phóng khi transaction đang giữ “khóa” thực hiện COMMIT hoặc ROLLBACK.

Phân tích ví dụ về SQL Lock Conflict (Transaction Lock Conflict)

Ví dụ 1

Chúng ta có 2 session cùng kết nối vào Database Test của Wecommit.

Hai session này đều muốn cập nhật dữ liệu của bảng EMPLOYEES.

Session thứ nhất muốn chỉnh sửa lương của nhân viên có mã 11 (EMP_ID=11)

SQL> update employees set salary=salary + 10 where emp_id=11;

Session thứ hai muốn chỉnh sửa lương của nhân viên có mã 11 (EMP_ID=10)

SQL> update employees set salary=salary + 10 where emp_id=10;

Hỏi rằng:

• Biết rằng Session thứ nhất thực hiện lệnh UPDATE lúc 9h, còn session thứ hai thực hiện lệnh sau session thứ nhất 1 phút (lúc 9 giờ 1 phút).
• Session thứ hai có phải chờ session thứ nhất hoàn thành thì mới thực hiện được hay không?

Đáp:

• Trong trường hợp trên, session thứ hai hoàn toàn không phải đợi session thứ nhất
• Session thứ nhất khi thực hiện sẽ LOCK bản ghi có ROW_ID =11
• Session thứ hai muốn thực hiện chỉnh sửa trên bản ghi có ROW_ID=10, nên hoàn toàn không bị ảnh hưởng với session thứ nhất
• Hình ảnh dẫn chứng khi 2 session thực hiện trong Database như sau

Ví dụ 2

Chúng ta có 2 session cùng kết nối vào Database Test của Wecommit.

Hai session này đều muốn cập nhật dữ liệu của bảng EMPLOYEES.

Hai session này đều muốn cập nhật dữ liệu của bảng EMPLOYEES.

Session thứ nhất muốn chỉnh sửa lương của nhân viên có mã 11 (EMP_ID=11)

SQL> update employees set salary=salary + 10 where emp_id=11;

Session thứ hai muốn chỉnh sửa lương của nhân viên có mã 11 (EMP_ID=11)

SQL> update employees set salary=salary * 2 where emp_id=11;

Hỏi rằng:

• Biết rằng Session thứ nhất thực hiện lệnh UPDATE lúc 9h, còn session thứ hai thực hiện lệnh sau session thứ nhất 1 phút (lúc 9 giờ 1 phút).
• Session thứ hai có phải chờ session thứ nhất hoàn thành thì mới thực hiện được hay không?

Đáp:

• Session thứ hai sẽ phải chờ Session thứ nhất hoàn thành thì mới có thể thực hiện.
• Session thứ hai sẽ có cảm giác bị “TREO”, bản chất việc “TREO” này là do bản ghi EMP_ID=11 đang bị “KHÓA” bởi Session thứ nhất.

Một số câu hỏi suy ngẫm sâu hơn

Hãy phân tích tình huống sau và trả lời câu hỏi

Chúng ta có 2 session cùng kết nối vào Database Test của Wecommit.

Hai session này đều muốn cập nhật dữ liệu của bảng EMPLOYEES.

Session thứ nhất muốn chỉnh sửa lương của nhân viên có mã nhỏ hơn 11 (EMP_ID < 11)

SQL> update employees set salary=salary + 10 where emp_id < 11;

Session thứ nhất muốn chỉnh sửa lương của nhân viên có mã nhỏ là 3 (EMP_ID=3)

SQL> update employees set salary=salary * 2 where emp_id=3;

Hỏi rằng:

• Biết rằng Session thứ nhất thực hiện lệnh UPDATE lúc 9h, còn session thứ hai thực hiện lệnh sau session thứ nhất 1 phút (lúc 9 giờ 1 phút).
• Session thứ hai có phải chờ session thứ nhất hoàn thành thì mới thực hiện được hay không?

Khi một Transaction đang lock bản ghi EMP_ID=11 để thực hiện chỉnh sửa, Transaction khác muốn SELECT giá trị EMP_ID = 11 có được hay không?

Hãy thử Demo hiện tượng Lock Conflict với câu lệnh Insert và Delete

Ghi chú của chuyên gia dành cho bạn đọc

Khi một câu lệnh SQL bị chậm, không nhất thiết là do câu lệnh ấy viết “tệ” hoặc câu lệnh tiêu tốn quá nhiều tài nguyên (CPU, I/O, RAM), đôi khi hiện tượng “chậm”, “treo” đó là do câu lệnh đang cố gắng sửa đổi một bản ghi bị “LOCK”, và phải chờ cho transaction giữ “LOCK” giải phóng.