Transaction trong SQL

Posted on by mtoplistChuyên mục:Top List

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 để đ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 các 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  
[ ; ]

Để 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 (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.Thêm đoạn dữ liệu giả như sau: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.Dữ liệu Customer ko bị xóa nếu dùng lệnh Rollback transactionSQLServer 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:Để nhận thông tin lỗi, ta có các hàm: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:Và bạn thực hiện hàm:Bạn có kết quả: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.Nếu bạn thực hiện 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 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: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 các bạn thành công!

mtoplist