Tham khảo trước: Cách SET lại Isolation

Đề bài

Câu 1: Tạo transaction tường minh để thăm dò trước khi tạo 1 đợt khuyến mãi như sau:

  • Tính tổng trị giá toàn bộ kho hàng theo giá hiện tại ( ListPrice) của sản phẩm.
  • Thử giảm giá tất cả sản phẩm xe đạp xuống 10%, sau đó kiểm tra xem tổng số tiền giảm có nhỏ hơn 5% tổng trị giá kho hàng hay không? ➡️ Nếu nhỏ hơn chấp nhận việc giảm giá này trong thời hạn 10 ngày kể từ ngày hiện hành (có nghĩa là tạo 1 đợt khuyến mãi mới). Ngược lại thì hủy bỏ việc giảm giá này

Câu 2: Kết nối vào SQL Server với 2 login ID khác nhau để giả lập 2 user cùng truy xuất dữ liệu trong CSDL AdventureWork2008.

Thực hiện theo trình tự sau để xem tác dụng các loại khóa:

  • User1 tạo 1 transaction tường minh gồm 2 lệnh chèn thêm 1 số điện thoại mới cho nhân viên bán hàng có mã là 274, và xem toàn bộ số điện thoại của nhân viên bán hàng nhưng chưa commit.
  • User2 thực hiện lệnh truy vấn vào bảng [Person].[PersonPhone].

Khi nào thì User2 xem được kết quả lệnh?

Câu 3: Tạo CSDL Test có 1 bảng T1 chỉ có 1 cột là col1 kiểu int. Nhập 1 vài hàng vào bảng T1 ➡️ Dùng lệnh DBCC useroptions để kiểm tra isolation level hiện hành.

Để thực hành mức isolation là read_uncommitted, lần lượt thực hiện các thao tác sau:

  1. Hai user cùng truy xuất vào CSDL Test.
  2. Tại user1, tạo 1 transaction tường minh thêm 3 hàng mới vào bảng T1 nhưng không commit.
  3. Tại user2, dùng lệnh SELECT xem nội dung bảng T1. Vì mức isolation mặc định là READ COMMITTED nên lệnh SELECT sẽ phải đợi cho đến khi user1 thực hiện lệnh commit transaction.
  4. Chuyển sang user1, tạo 1 transaction tường minh mới thêm 3 hàng khác vào bảng T1 nhưng không commit.
  5. Tại user2, thay đổi mức isolation thành Uncommitted Read, dùng lệnh SELECT để xem nội dung bảng T1. Lệnh được thực hiện ngay cho xem cả 3 hàng mới nhập.
  6. Tại user1, dùng lệnh ROLLBACK để hủy bỏ transaction. Trở lại user2, chạy lại lệnh SELECT, kết quả lệnh sẽ không còn 3 hàng cuối vừa nhập. ➡️ Kết quả này là minh họa cho bài toán dirty read

Để xem tác dụng của isolation level ở mức Repeatable Read, lần lượt thực hiện các thao tác sau:

  1. Tại user2, đặt isolation level về lại mức mặc định (Committed Read).
  2. Tạo 1 transaction với lệnh đầu tiên là SELECT để xem nội dung bảng T1, nhưng không commit.
  3. Tại user1 cập nhật 1 vài hàng bất kỳ trong bảng T1 và commit.
  4. Tại user2, thực hiện lại lệnh SELECT, kết quả lệnh sẽ khác với lệnh trước tuy trong cùng 1 transaction ➡️ Hiện tượng này được gọi là phantom read.
  5. Cũng tại user2, kết thúc transaction. Đặt isolation level sang mức Repeatable Read. Bắt đầu 1 transac tion mới với câu lệnh SELECT để xem nội dung bảng T1 nhưng không commit
  6. Trở lại user1 cập nhật vài hàng bất kỳ trong bảng T1 và commit.
  7. Tại user2, thực hiện lại lệnh SELECT, kết quả lệnh vẫn như trước, không bị hiện tượng phantom read.
  8. Chuyển sang mode implicit transaction và tạo 2 transaction trong mode này, sau đó chuyển về lại mode autocommit
  9. Thực hiện transaction sau và kiểm tra tình trạng transaction bẳng hàm XACT_STATE()
Toogle 
USE AdventureWorks2019;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    -- A FOREIGN KEY constraint exists on this table. This statement will generate a constraint violation error.
    DELETE
    FROM Production.Product
    WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-----
-- Test XACT_STATE for 0, 1, or -1.
-- If 1:  the transaction is committable.
-- If -1: the transaction is uncommittable and should be rolled back.
-- If 0:  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 ('The transaction is in an uncommittable state. Rolling back transaction.');
            ROLLBACK TRANSACTION;
        END;
-- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
        BEGIN
            PRINT ('The transaction is committable. Committing transaction');
            COMMIT TRANSACTION;
        END;
END CATCH;
GO

Bài giải

Câu 1:

--Tạo Transaction cho đợt giảm giá 10 ngày
BEGIN TRAN
DECLARE @magiamgia INT,
    @tongtienkhotruocgiam MONEY,
    @tongtiensaugiam MONEY

--Tạo đợt giảm giá mới (Rate 10%) - SpecialOfferID: Auto increment column
INSERT [Sales].[SpecialOffer] ([Description], [DiscountPct], [Type], [Category], [StartDate], [EndDate], [MinQty])
VALUES (N'Đợt Khuyến Mãi Mới', 0.1, 'Volume Discount', 'Sale', GETDATE(), DATEADD(DD, 10, GETDATE()), 0)

-- a. Tính tổng giá trị toàn bộ kho hàng - Cho đối tượng áp mã là Bike
SELECT @tongtienkhotruocgiam = SUM(I.[Quantity] * P.[ListPrice])
FROM [Production].[ProductInventory] I
         JOIN [Production].[Product] P ON P.ProductID = I.ProductID
         JOIN [Production].[ProductSubcategory] PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID
         JOIN [production].[ProductCategory] PC ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Bikes'

--b. Lấy mã giảm giá mới vừa tạo
SELECT @magiamgia = MAX([SpecialOfferID])
FROM [Sales].[SpecialOffer]

-- Giảm giá Áp dụng cho tất cả các sản phẩm là xe đạp -  ModifiedDate: getdate(); rowguid: newid()
INSERT [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
SELECT @magiamgia, [ProductID]
FROM [Production].[Product] P
         JOIN [Production].[ProductSubcategory] PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID
         JOIN [production].[ProductCategory] PC ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Bikes'

--Kiểm tra tổng tiền sau giảm có nhỏ hơn trước giảm 5% tổng giá trị kho hay không?
SELECT @tongtiensaugiam = SUM(I.[Quantity] * P.[ListPrice] * SO.DiscountPct)
FROM [Production].[ProductInventory] I
         JOIN [Production].[Product] P ON P.ProductID = I.ProductID
         JOIN [Sales].[SpecialOfferProduct] SOP ON SOP.ProductID = P.ProductID
         JOIN [Sales].[SpecialOffer] SO ON SO.SpecialOfferID = SOP.SpecialOfferID
WHERE SO.[SpecialOfferID] = @magiamgia

IF @tongtiensaugiam <=
   0.05 * @tongtienkhotruocgiam
    BEGIN
        RAISERROR ('CHAP NHAN DOT GIAM GIA NAY',1,16)
        COMMIT TRAN
    END
ELSE
    BEGIN
        RAISERROR ('KHONG THE TAO DOT GIAM GIA NAY DUOC...',1,16)
        ROLLBACK TRAN
    END

Câu 2:

DBCC USEROPTIONS

BEGIN TRAN
SELECT *
FROM [Person].[PersonPhone]
WHERE [BusinessEntityID] = 247

UPDATE [Person].[PersonPhone]
SET [PhoneNumber] = '393-555-0186'
WHERE [BusinessEntityID] = 247

SELECT *
FROM [Person].[PersonPhone]
WHERE [BusinessEntityID] = 247
ROLLBACK TRAN