Hướng dẫn sử dụng Transaction Trong SQL Server

Transaction (giao dịch) được dùng để đảm bảo tính toàn vẹn dữ liệu khi xảy ra cập nhật (cập nhật xin được hiểu theo nghĩa rộng là các hành động sửa đổi dữ
liệu, như INSERT, UPDATE, DELETE…). Khi một transaction bao gồm nhiều lệnh cập nhật, nó đảm bảo tất cả các cập nhật đều được thực hiện thành công, hoặc trong trường hợp một lệnh gặp sự cố toàn bộ transaction bị hủy bỏ. Khi đó dữ liệu trở về trạng thái như trước khi xảy ra transaction. Nói cách khác transaction ngăn chặn tình huống dữ liệu được cập nhật nửa chừng, trong đó một phần được cập nhật còn một phần bị bỏ qua.

Một ví dụ kinh điển về transaction là khi bạn cần thực hiện một giao dịch chuyển tiền giữa hai tài khoản ngân hàng. Giả sử bạn có hai tài khoản A và B với số tiền tương ứng là 8 tỷ và 1 tỷ; nay bạn cần chuyển bớt 2 tỷ từ tài khoản A sang tài khoản B. Sẽ có hai phép cập nhật như sau:

– trừ số tiền hiện có của tài khoản A đi 2 tỷ

– cộng thêm số tiền hiện có của tài khoản B lên 2 tỷ

Nếu hai lệnh cập nhật trên diễn ra độc lập (không nằm trong một transaction), và vì một lý do nào đó lệnh thứ hai bị lỗi, tài khoản A sẽ còn 6 tỷ và tài khoản B vẫn giữ nguyên 1 tỷ. Điều này không thể chấp nhận được vì 2 tỷ bỗng dưng biến mất! Khi thực hiện hai lệnh trên trong một transaction, nó sẽ đảm bảo:

– hoặc cả hai lệnh update đều được thực hiện thành công. Cả hai tài khoản được cập nhật với số tiền tương ứng.

– hoặc trong trường hợp giao dịch bị lỗi cả hai lệnh đều không được thực hiện. Hai tài khoản giữ nguyên số tiền như trước khi thực hiện transaction.

Trong SQL Server một transaction có đoạn code ở dạng đơn giản như sau (Bạn nên tham khảo mẫu code sử dụng transaction hoàn chỉnh ở phần dưới):

BEGIN TRAN
   -- lệnh 1
   -- lệnh 2
   -- ...
COMMIT

Bạn mở transaction bằng lệnh BEGIN TRAN và kết thúc bằng lệnh COMMIT – sau lệnh này những cập nhật dữ liệu sẽ được xác nhận vào trong database, transaction được đóng lại và các khóa (lock) trên các bảng được cập nhật được thả ra.

Về tùy chọn XACT_ABORT

Câu chuyện sẽ rất đơn giản nếu không có sự xuất hiện của tùy chọn XACT_ABORT. Đây là tùy chọn ở mức kết nối, chỉ có tác dụng trong phạm vi kết nối của bạn.

XACT_ABORT nhận hai giá trị ON và OFF (OFF là giá trị mặc định). Khi tùy chọn này được đặt là OFF, SQL Server sẽ chỉ hủy bỏ lệnh gây ra lỗi trong transaction và vẫn cho các lệnh khác thực hiện tiếp, nếu lỗi xảy ra được đánh giá là không nghiêm trọng. Còn khi XACT_ABORT được đặt thành ON, SQL Server mới cư xử đúng như mong đợi – khi gặp bất kỳ lỗi nào nó hủy bỏ toàn bộ transaction và quay lui trở lại như lúc ban đầu. Ví dụ:

-- tạo bảng với ràng buộc cột i không được chứa giá trị 2
CREATE TABLE #t1(i INT, CONSTRAINT ck1 CHECK (i<>2) )
 
-- dùng giá trị mặc định XACT_ABORT = OFF
-- SET XACT_ABORT OFF
 
BEGIN TRAN
   INSERT #t1 SELECT 1
   INSERT #t1 SELECT 2 -- vi phạm ràng buộc
   INSERT #t1 SELECT 3   
COMMIT
 
SELECT * FROM #t1
 
i
---
1
3
(2 ROW(s) affected)

Như vậy trong transaction trên, lệnh insert thứ hai gây ra lỗi nhưng lệnh thứ ba vẫn tiếp tục được thực hiện, và transaction vẫn kết thúc thành công. Kết quả là bảng vẫn có hai bản ghi từ lệnh insert thứ nhất và thứ ba. Nay hãy đặt XACT_ABORT thành ON:

-- tạo bảng với ràng buộc cột i không được chứa giá trị 2
CREATE TABLE #t2(i INT, CONSTRAINT ck2 CHECK (i<>2) )
 
SET XACT_ABORT ON
 
BEGIN TRAN
   INSERT #t2 SELECT 1
   INSERT #t2 SELECT 2 -- vi phạm ràng buộc
   INSERT #t2 SELECT 3   
COMMIT
 
SELECT * FROM #t2
 
i
---
(0 ROW(s) affected)

Và bây giờ bảng không có bản ghi nào vì toàn bộ transaction đã bị hủy bỏ. Chính xác ra là các lệnh phía sau lệnh gây ra lỗi không được thực hiện tiếp, còn các
lệnh thực hiện trước đó bị quay lui (ROLLBACK) trở lại. Nói chung SET XACT_ABORT ON tránh được rất nhiều rắc rối khi dùng transaction, nó xử lý gọn ghẽ các ngoại lệ kể cả các lỗi như connection timeout hay khi user hủy bỏ thực hiện. Bản thân tôi không lý giải được tại sao Microsoft không đặt giá trị mặc định cho XACT_ABORT là ON. Thậm chí không lý giải được việc đưa ra tùy chọn này để làm gì. Tóm lại bạn luôn nên đặt SET XACT_ABORT ON vào đầu thủ
tục nếu cần dùng transaction.

Mẫu code sử dụng transaction

Từ SQL Server bản 2005 trở lên bạn có thể dùng đoạn code sau:

SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
   -- lệnh 1
   -- lệnh 2
  -- ...
COMMIT
END TRY
BEGIN CATCH
   ROLLBACK
   DECLARE @ErrorMessage VARCHAR(2000)
   SELECT @ErrorMessage = 'Lỗi: ' + ERROR_MESSAGE()
   RAISERROR(@ErrorMessage, 16, 1)
END CATCH

Đoạn lệnh trên kết hợp transaction với xử lý lỗi.

– Nó bắt đầu bằng việc đặt lựa chọn XACT_ABORT là ON để đảm bảo transaction hoạt động đúng như mong muốn.

– Sau đó là BEGIN TRAN để mở transaction.

– Tiếp đến là BEGIN TRY để mở ra khối try block (giống như try block trong C#)

– Khối try block sẽ chứa các lệnh cần thực hiện trong transaction

– Rồi đến COMMIT để kết thúc transaction và END TRY để kết thúc khối try block

– Sau đó là BEGIN CATCH (giống như catch block trong C#). Đây là phần chứa đoạn lệnh sẽ được thực hiện khi có lỗi trong phần try block.

– Trong phần catch lệnh đầu tiên là ROLLBACK để quay lui transaction.

– Sau đó dùng một biến để chứa thông báo lỗi. Bạn cũng có thể thêm các bước như lưu thông tin về lỗi vào một bảng audit, hoặc gửi email cho DBA…

– Kết thúc là RAISERROR để báo cho ứng dụng biết thủ tục đã gây ra lỗi và truyền thông báo lỗi cho ứng dụng.

Phiên bản áp dụng: SQL Server 2005 trở lên