Transaction trong SQL

Tóm Tắt

Transaction là gì?

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.
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.
Cú pháp :


BEGIN TRAN
   -- command 1
   -- command 2
   -- ...
COMMIT

Transaction Control

Các lệnh sau đây dùng để tinh chỉnh và điều khiển transaction :

COMMIT − 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.
ROLLBACK – trả lại dữ liệu trước khi thay đổi.
SAVEPOINT – Tạo ra savepoint được dùng để Rollback.
SET TRANSACTION – Đặt tên cho 1 transaction.

Giả sử bạn có 1 bảng Customer với những field : ID, Name, Age, Address, Salary

CREATE TABLE [dbo].[Customers](
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [nvarchar](500) NOT NULL,
 [Age] [int] NOT NULL,
 [Address] [nvarchar](max) NULL,
 [Salary] [decimal](18, 0) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[Customers] ADD  CONSTRAINT [DF_Customers_Id]  DEFAULT (newid()) FOR [Id]

Thêm đoạn dữ liệu giả như sau:

INSERT INTO [dbo].[Customers] ([Id], [Name], [Age], [Address], [Salary])
     VALUES( newid(), 'Mohamed Salah', 18, 'Dubai', 2000)

INSERT INTO [dbo].[Customers] ([Id], [Name], [Age], [Address], [Salary])
     VALUES( newid(), 'Angela Merkel', 52, 'Germany', 2000)

INSERT INTO [dbo].[Customers] ([Id], [Name], [Age], [Address], [Salary])
     VALUES( newid(), 'Victoria', 20, 'England', 2000)

INSERT INTO [dbo].[Customers] ([Id], [Name], [Age], [Address], [Salary])
     VALUES( newid(), 'David', 18, 'America', 2000)

INSERT INTO [dbo].[Customers] ([Id], [Name], [Age], [Address], [Salary])
     VALUES( newid(), 'Tim', 18, 'United Stated', 2000)

Lệnh ROLLBACK

Lệnh ROLLBACK là lệnh giao dịch được sử dụng để hoàn tác các giao dịch chưa được lưu vào cơ sở dữ liệu. Lệnh này chỉ có thể được sử dụng để hoàn tác các giao dịch kể từ khi lệnh COMMIT hoặc ROLLBACK cuối cùng được phát hành.


BEGIN TRANSACTION;

DELETE FROM CUSTOMERS WHERE AGE = 18;

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

Dữ liệu Customer ko bị xóa nếu dùng lệnh Rollback transaction

Hàm Try … catch

SQLServer Transact-SQL cung cấp cơ chế kiểm soát lỗi bằng TRY … CATCH như trong các ngôn ngữ lập trình phổ dụng hiện nay (Java, C, PHP).
Cú pháp:


-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
BEGIN TRY  
     { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
     [ { sql_statement | statement_block } ]  
END CATCH  
[ ; ]  
Xem thêm: Khởi tạo, xóa, sửa Table trong SQL Server | How Kteam 
Xem thêm: Sự khác biệt giữa biến và tham số trong SQL là gì – Sự Khác BiệT GiữA – 2022
Bạn đang đọc: Transaction trong SQL

Để nhận thông tin lỗi, ta có các hàm:

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Lưu ý: các hàm trên chỉ được gọi trong phạm vi catch block.
Ví dụ:
Bạn tạo 1 stored procedure để lấy thông tin error:


-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage;  
GO

Và bạn thực hiện hàm:


BEGIN TRY  
    -- Generate divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  
END CATCH;

Bạn có kết quả:

Sử dụng Try..catch với transaction

Trường hợp xuất hiện lỗi, bạn sử dụng lệnh Rollback để revert lại data. Nếu không xuất hiện lỗi, bạn dùng lệnh commit để lưu data.


BEGIN TRANSACTION;  

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    select 1/0
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  

    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO

Nếu bạn thực hiện nhiều lệnh trong try… catch, bạn phải sử dụng option: XACT_ABORT

Mặc định giá trị này = OFF
XACT_ABORT = 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.
XACT_ABORT = ON: 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.
Ta có template:


SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
   -- command 1
   -- command 2
  -- ...
COMMIT
END TRY
BEGIN CATCH
   ROLLBACK
   DECLARE @ErrorMessage VARCHAR(2000)
   SELECT @ErrorMessage = 'Error: ' + ERROR_MESSAGE()
   RAISERROR(@ErrorMessage, 16, 1)
END CATCH

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.
VD:

-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  

BEGIN TRY  
    BEGIN TRANSACTION;   
        Select 1/0 

    -- If the DELETE statement succeeds, commit the transaction.  
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  

    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  

    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  

    -- Test whether the transaction is committable.  
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;

Tham khảo

http://www.sqlviet.com/blog/su-dung-transaction-trong-sql-server
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

Chúc các bạn thành công!
Nhatkyhoctap’s blog
Transaction ( thanh toán giao dịch ) được dùng để bảo vệ tính toàn vẹn tài liệu khi xảy ra update. Khi một transaction gồm có nhiều lệnh update, nó bảo vệ tổng thể những update đều được thực thi thành công xuất sắc, hoặc trong trường hợp một lệnh gặp sự cố hàng loạt transaction bị hủy bỏ. Khi đó tài liệu quay trở lại trạng thái như trước khi xảy ra transaction. Nói cách khác transaction ngăn ngừa trường hợp tài liệu được update nửa chừng, trong đó một phần được update còn một phần bị bỏ lỡ. Thêm đoạn tài liệu giả như sau : Lệnh ROLLBACK là lệnh thanh toán giao dịch được sử dụng để hoàn tác những thanh toán giao dịch chưa được lưu vào cơ sở tài liệu. Lệnh này chỉ hoàn toàn có thể được sử dụng để hoàn tác những thanh toán giao dịch kể từ khi lệnh COMMIT hoặc ROLLBACK ở đầu cuối được phát hành. Dữ liệu Customer ko bị xóa nếu dùng lệnh Rollback transactionSQLServer Transact-SQL phân phối chính sách trấn áp lỗi bằng TRY … CATCH như trong những ngôn từ lập trình phổ dụng lúc bấy giờ ( Java, C, PHP ). Cú pháp : Để nhận thông tin lỗi, ta có những hàm : Lưu ý : những hàm trên chỉ được gọi trong khoanh vùng phạm vi catch block. Ví dụ : Bạn tạo 1 stored procedure để lấy thông tin error : Và bạn triển khai hàm : Bạn có hiệu quả : Trường hợp Open lỗi, bạn sử dụng lệnh Rollback để revert lại data. Nếu không Open lỗi, bạn dùng lệnh commit để lưu data. Nếu bạn thực thi nhiều lệnh trong try … catch, bạn phải sử dụng option : XACT_ABORTMặc định giá trị này = OFFXACT_ABORT = OFF : SQL Server sẽ chỉ hủy bỏ lệnh gây ra lỗi trong transaction và vẫn cho những lệnh khác thực thi tiếp, nếu lỗi xảy ra được nhìn nhận là không nghiêm trọng. XACT_ABORT = ON : khi gặp bất kể lỗi nào nó hủy bỏ hàng loạt transaction và quay lui trở lại như lúc bắt đầu. Ta có template : 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. VD : Chúc những bạn thành công xuất sắc !

Source: https://final-blade.com
Category: Kiến thức Internet

admin

final-blade.com