Bài viết này tôi sẽ giúp anh em hiểu bản chất của SQL Lock Conflict, một hiện tượng kinh điển trong các hệ thống OLTP có nhiều transaction đồng thời làm việc.
1. 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 Huy và Tuân cùng có mặt trong tòa nhà Wecommit (Wecommit Building)
- Cả hai người đều muốn sử dụng máy chấm công được đặt ở cửa ra vào.
- Ông Huy là người đến lúc 9 giờ và Tuân đế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:
- Huy sẽ được sử dụng máy chấm công trước
- Tuân sẽ buộc phải đợi khi Huy 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 Huy 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.
2. Phân tích ví dụ về SQL Lock Conflict (Transaction Lock Conflict)
2.1. 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
2.2. 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.
3. Một số câu hỏi suy ngẫm sâu hơn
3.1. 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?
3.2. 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?
3.3. Hãy thử Demo hiện tượng Lock Conflict với câu lệnh Insert và Delete
4. 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.
5. Thông tin tác giả
Tác giả của bài viết này là Trần Quốc Huy – Founder & CEO Wecommit.
Facebook cá nhân của tác giả: https://www.facebook.com/tran.q.huy.71
Xem series Demo về tối ưu SQL trên kênh youtube của tác giả: Click tại đây
Zalo: 0888549190
Email: huy.tranquoc@wecommit.com.vn
6. Nếu bạn muốn truy cập hệ thống học về tối ưu SQL, tối ưu cơ sở dữ liệu – kiến thức độc quyền của Wecommit
Bạn có thể truy cập hệ thống học và bắt đầu xem các kiến thức mà không cần phải đăng ký tài khoản (HOÀN TOÀN MIỄN PHÍ) tại đây: Click vào đây để bắt đầu học ngay
7. Nếu bạn muốn được chia sẻ toàn bộ kiến thức, tư duy và kinh nghiệm tối ưu SQL, tối ưu cơ sở dữ liệu – chương trình có phí, giúp anh em DEV trở nên hoàn toàn khác biệt so với thị trường
Bạn sẽ biết được toàn bộ những kinh nghiệm mà chúng tôi thực hiện tối ưu cho các dự án tại HNX, FPT, VNDirect,… (Chi tiết toàn bộ các dự án bạn có thể xem tại đây: https://wecommit.com.vn/du-an/ )
Xem chi tiết chương trình cao cấp, có phí tại đây: https://wecommit.com.vn/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-wecommit/