Giới thiệu về Transaction

Có nhiều hoàn cảnh người dùng yêu cầu cần thay đổi dữ liệu trong nhiều bảng trong database. Trong nhiều tình huống, dữ liệu sẽ bị mất tính nhất quán khi thực thi riêng biệt nhau.

Giả sử nếu câu lệnh đầu tiên được thực thi chính xác nhưng câu lệnh tiếp theo thất bại vì dữ liệu không chính xác.

Ví dụ, một tình huống cụ thể là hoạt động chuyển tiền trong hệ thống ngân hàng. Việc chuyển tiền sẽ cần một câu lệnh INSERT và 2 câu lệnh UPDATE:

• Người dùng cần giảm số dư ở tài khoản nguồn.
• Sau đó, cần tăng số dư trong tài khoản ở hệ thống ngân hàng ở bản ghi tài khoản đích.

User sẽ cần phải kiểm tra rằng giao dịch này được cam kết (committed) và liệu các thay đổi tương tự có được thực hiện đối với tài khoản nguồn và tài khoản đích hay không.

Định nghĩa transaction

Một đơn vị công việc hợp lý phải thể hiện bốn thuộc tính, được gọi là thuộc tính Nguyên tử, tính nhất quán, cô lập và tính bền vững (ACID), để đủ điều kiện là một transaction:

Atomicity: Nếu giao dịch có nhiều thao tác thì tất cả nên được cam kết. Nếu bất kỳ hoạt động nào trong nhóm không thành công thì nó sẽ được khôi phục lại.

Consistency: Tuần tự các thao tác cần thích hợp

Isolation: Các hoạt động được thực hiện phải được cách ly vĩnh viễn với các hoạt động khác trên cùng một cơ sở dữ liệu máy chủ

Durability: Các hoạt động được thực hiện trên cơ sở dữ liệu phải được lưu và lưu trữ vĩnh viễn trong cơ sở dữ liệu.

Implimenting transaction (Thực hiện giao dịch)

SQL Server hỗ trợ transaction với một vài chế đô như sau:

• Autocomit Transactions: (tự động cam kết) Mỗi một dòng lệnh đơn được tự động cam kết khi nó thành công. Trong chế độ này, không cần viết bất kỳ câu lệnh xác định nào để bắt đầu và kết thúc transaction. Đây là chế độ mặc định của SQL Server
• Expicit Transactions: (tường minh) Mỗi transaction explicit (tường minh) bắt đầu với câu lệnh BEGIN TRANSACTION và kết thúc bằng ROLLBACK hoặc COMMIT transaction.
• Implicit transactions: (ngầm định) một transaction được tự động bắt đauà khi một transaction trước đó hoàn thành và mỗi transaction hooàn thành bằng cách sử dụng cú pháp ROLLBACK hoặc COMMIT
• Batch-scoped transactions: (phạm vi hàng loạt) các transaction này liên quan tới khái niệm Multiple Active results set (MARS). Và mỗi transaction implicit hoặc explicit bắt đầu với một viên MARS đưojc gọi là batch-scoped transaction.
• Distributed transactions: (transaction phân tán) nó được trải dài trên 2 hoặc nhiều server được hiểu là resource managers. Việc quản trị transaction phải được định vị giữa resource manager bằng thành phần server gọi là transaction manager. Mỗi instance trong SQL Server có thể hoạt động như một resource manager trong distributed transactions, được định vị bởi transaction manager, như là Microsoft Distributed Transaction Coordinator (MS DTC)

Transaction extending batches

Các câu lệnh transaction xác định khối lệnh thành công hoặc thất bại và cung cấp cơ sở noiw database có thể rollback các thao tác.

Lỗi được bắt gặp trong quá trình thực thi của một batch đơn giản có có khả năng thành công một phần, đây không phải là kết quả mong muốn khi sử dụng transaction.

Vấn đề này sẽ dẫn đến mâu thuẫn logic giữa các bảng trong CSDL.

Ngời dùng có thể thêm đoạn mã kiểm soát lỗi để roll back transaction về trạng thái cũ trong trường hợp gặp lỗi.

Error-handling code sẽ hooàn tác lại toàn bộ thay đổi trước khi bắp gặp lỗi.

Điều khiển transaction

Transactions có thể được điều khiển thông qua ứng dụng bằng cách định nghĩa bắt đầu và kết thúc cho một transaction.

Transaction đưojc quản trị bằng các tầng kết nối theo mặc định.

Khi transaction bắt đầu một kết nối, tất cả các câu lệnh T-SQL được thực thi trên cùng một kết nối và là một phần của kết nối cho tới khi transaction kết thúc.

BEGIN TRANSACTION

Câu lệnh BEGIN TRANSACTION đánh dấu điểm đầu của một transaction explicit (tường minh).

Ví dụ:

USE AdventureWorks2019;
GO
DECLARE @TranName VARCHAR(30);
SELECT @TranName = 'FirstTransaction';
BEGIN TRANSACTION @TranName;
DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13;

COMMIT TRANSACTION

Câu lệnh COMMIT TRANSACTION đánh dấu điểm cuối, là một cam kết báo hiệu kết thúc một implicit hoặc explicit transaction.

COMMIT [TRAN | TRANSACTION] [transaction_name | @tran_name_variable]] [;]

Ví dụ:

BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11;
GO
COMMIT TRANSACTION;
GO

COMMIT WORK

Câu lệnh COMMIT WORK đánh dấu điểm cuối transaction.

Cú pháp:

COMMIT [WORK] [;]

COMMIT TRANSACTION và COMMIT WORK giống hệt nhau, ngoại trừ COMMIT TRANSACTION nhận vào một transaction name do người dùng định nghĩa.

Tạo transaction với cam kết (commit):

BEGIN TRANSACTION DeleteCandidate
WITH MARK N'Deleting a Job Candidate';
GO
DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11;
GO
COMMIT TRANSACTION DeleteCandidate;

ROLLBACK TRANSACTION

Transaction có thể hủy bỏ và quay trở lại điểm ban đầu hoặc savepoint trong transaction.

Nó được sử dụng để xóa toàn bộ dữ liệu đã sửa đổi được tạo từ khi bắt đầu transaction hoặc tới savepoint. Nó cũng giải phóng các tài nguyên đang nắm giữ bởi transaction.

SAVE TRANSACTION

Câu lệnh SAVE TRANSACTION sẽ đặt ra savepoint bên trong transaction.

Cú pháp:

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable} [;]

Ví dụ:

CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION ProcedureSave;
ELSE
BEGIN TRANSACTION;
DELETE HumanResources.JobCandidate
WHERE JobCandidateID = @InputCandidateID;
IF @TranCounter = 0
COMMIT TRANSACTION;
IF @tranCounter = 1
ROLLBACK TRANSACTION ProcedureSave;
GO

Trong đoạn code trên,savepoint transaction đưojc tạo ra bên trong procedure. Nó sẽ được sử dụng để roll back chỉ khi dữ liệu thay đổi được tạo ra bởi stored procedure nếu một transaction có hiệu lực bắt đầu trước khi procedure thực thi.

@@TRANCOUNT trong transaction

@@TRANCOUNT là một hàm hệ thống trả về giá trị số của câu lệnh transaction, xảy ra trong kết nối hiện tại.

Ví dụ:

PRINT @@TRANCOUNT BEGIN TRAN
PRINT @@TRANCOUNT BEGIN TRAN
PRINT @@TRANCOUNT COMMIT
PRINT @@TRANCOUNT COMMIT
PRINT @@TRANCOUNT

Kết quả:

Ví dụ sử dụng @@TRANCOUNT với ROLLBACK

PRINT @@TRANCOUNT BEGIN TRAN
PRINT @@TRANCOUNT BEGIN TRAN
PRINT @@TRANCOUNT
ROLLBACK
PRINT @@TRANCOUNT

Kết quả:

Đánh dấu transaction

Đánh dấu transaction hữu dụng chỉ khi người dùng sẵn sàng mất các giao dịch đã cam kết gần đây hoặc đang kiểm tra cơ sở dữ liệu liên quan.

Việc đánh dấu transactions trên căn cứ lịch trình trong mọi cơ sở dữ liệu liên quan đơn lẻ tạo ra một chuỗi các điểm khôi phục chung trong cơ sở dữ liệu

Mối quan tâm khi sử dụng Marked Transaction:

Một transaction mark sẽ tiêu thụ không gian vật lý, chỉ sử dụng chúng cho transaction có vai trò quan trọng trong chiến lược phục hồi cơ sở dữ liệu.

Khi marked transaction đưojc cam kết, row sẽ thêm vào bảng logmarkhistory trên bảng msdb.

Nếu marked transaction trải rộng trên nhiềều database trên các server khác nhau, hoặc cùng server, marks cần phải được ghi vào hồ sơ của tất cả các cơ sở dữ liệu bị ảnh hưởng.

Create Marked Transaction

Để tạo một marked transaction, người dùng có thể sử dụng cú pháp câu lệnh BEGIN TRANSACTION đi kèm với mệnh đề WITH MARK [DESCRIPTION]

Transaction ghi lại mark description, name, user, database , thông tin datetime và Log Sequence Number (LSN).

Các bước để tạo marked transaction trong một tập databases:

• Tên của transaction trong câu lệnh BEGIN TRAN và sử dụng mệnh đề WITH MARK.
• Thực thi một cập nhật đối với tất cả các cơ sở dữ liệu trong tập hợp.

Ví dụ:

USE AdventureWorks2019;
GO
BEGIN TRANSACTION ListPriceUpdate
WITH MARK 'UPDATE Product List prices';
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 1.20 WHERE ProductNumber LIKE 'BK-%';
GO
COMMIT TRANSACTION ListPriceUpdate;
GO

Khác biệt Implicit và Explicit Transaction

Implicit	Explicit
Transaction được duy trì bởi SQL Server cho mỗi câu lệnh DDL và DML	Transaction định nghĩa bởi lập trình biên
Câu lệnh DML và DDL thực thi dưới transaction implicit	Bao gồm Câu lệnh DML, và thực thi như một đơn vị truy vấn.
SQL server sẽ rollback toàn bộ câu lệnh	Không bao gồm câu lệnh SELECT vì chúng không sửa đổi dữ liệu.

Isolation Level (tầng cách ly)

Transaction xác dịnh tầng cácch ly mà định nghĩa mức độ mà một giao dịch phải được tách biệt sửa đổi dữ liệu hoặc tài nguyên được thực hiện bởi các giao dịch khác.

Các mức độ cách ly được xác định trong điều kiện cho phép hiệu ứng đồng thời như dirty reads.

Transaction isolation level điều khiển những vấn đề sau:

• Khi dữ liệu được đọc, liệu có cơ chế khóa (locks) nào đang chiếm và kiểu locks được yêu cầu là gì
• Khoảng thời gian mà các khóa đọc được giữ là bao nhiêu
• Nếu một thao tác đọc tham chiếu tới một hàng được sửa đổi bởi transaction khác thì một trong các tình huống sau xảy ra:
  • Chặn cho đến khi cơ chế khóa duy nhất trên hàng được mở.
  • Truy xuất phiên bản đã commit của hàng tồn tại tại thời điểm bắt đầu transaction hoặc câu lệnh.
  • Đọc dữ liệu chưa được comit sửa đổi

Giao dịch yêu cầu một khóa duy nhất mọi lúc trên mỗi dữ liệu mà nó điều chỉnh. Sau đó, nó giữ khóa đó cho đến khi giao dịch hoàn tất, bất kể mức độ cách ly được đặt cho giao dịch đó.

Các isolation level:

Isolation Level	Dirty Read	NonRepeatable Read
Read commited	No	Yes
Read uncommited	Yes	No
Snapshot	No	No
Repeatable Read	No	No
Serializable	No	No

Phạm vi và các kiểu khóa (locks)

Danh sách các kiểu locks phổ biến trong SQL Ser

Lock Mode	Description
Update	Sử dụng trong tài nguyên chuẩn bị update
Shared	Sử dụng để đọc thao tác mà không thay đổi dữ liệu như câu lệnh SELECT
Intend	Sử dụng để thành lập một locks phân tầng
Exclusive	Sử dụng cho các thao tác sử đổi dữ liệu như INSERT, UPDATE, DELETE.
BULK UPDATE	Sử dụng khi copy số lượng lớn dữ liệu vào bảng.
Schema	Sử dụng khi có thao tác phụ thuộc vào table schema

Update Locks

Những locks này tránh những tình huống bế tắc (deadlock). Nó nối tiếp các transaction, transaction sẽ đọc dữ liệu, có được một khóa được chia sẻ trên hàng hoặc một trang, và sửa đổi dữ liệu yêu cầu chuyển đổi lock thành exclusive lock.

Shared Locks

Các locks này cho phép các giao dịch song song để đọc một tài nguyên dưới sự kiểm soát đồng thời.

Shared locks gỉai phóng tài nguyên một khi thao tác đọc hoàn thành, ngoại trừ tầng cách ly được gán thành hành động đọc lăp lại lại hoặc cao hơn.

Exclusisve Locks

Các locks này ngăn chặn truy cập vào tài nguyên đồng thời trong transaction.

Bằng việc sử dụng exclusive lock, không transaction nào có thể thay đổi dữ liệu và thao tác đọc sẽ được sắp xếp chỉ khi thông qua tầng cách ly chưa cam kết hoặc chế độ NOLOCK.

Câu lệnh DML như INSERT,UPDATE, DELETE sử dụng để sửa đổi dữ liệu.

Intent Locks

Vai trò của Intend lock:

• Ngăn chặn các transaction khác thay đổi dữ liệu ở tài nguyên tầng cao hơn, theo cách sẽ làm mất hiệu lực của khóa ở cấp thấp hơn.
• để cải thiện hiệu quả của Database engine trong việc xác định các xung đột khóa ở mức độ chi tiết cao hơn.

Danh sách mô tả intent lock:

Lock Mode	Description
Intent Shared (IS)	Bảo vệ shared lock được yêu cầu trên một số tài nguyên có phân tầng thấp hơn.
Intent exclusive (IX)	Bảo vệ exclusisve lock được yêu cầu trong một số tài nguyên được phân tầng thấp hơn. IX là một superset (1 tập hợp tập bao gồm một tập hợp khác) của IS, bảo vệ các shared lock yêu cầu ở tầng tài nguyên thấp hơn.
Shared with Intent Exclusive (SIX)	Bảo vệ các shared lock được yêu cầu trên toàn bộ tài nguyên thấp honw trong hierarchy và intent exclusive locks trong một số tài nguyên phân tầng thấp honw. Concurrent IS locks (IS locks đồng thời) được cho phép trên tài nguyên cấp cao nhất.
Intent Update (IU)	Để bảo vệ những locks được yêu cầu trong tooàn bộ tài nguyên ở tầng thấp hơn. IU locks chỉ sử dụng trên tài nguyên page. IU locks được chuyển đổi thành IX locks nếu thao tác cập nhật được diễn ra.
Shared intent update (SIU)	Cung cấp kết hợợp của S và IU locks, như là một kết quả của việc thu được các lock riêng biệt và đồng thời nắm giữ cả 2 locks.
Update intent exclusive (UIX)	Cung cấp kết hợp của U và IX locks, như là kết quả của việc thu được locks riêng biệt và đồng thời nắm giữ cả 2 locks.

Bulk Update locks

Bulk update locks được sử dụng khi có một lượng lớn dữ liệu được copy vào bảng. Các locks này sẽ cho phép nhều luồng cùng chạy để tải dữ liệu số lượng lớn tuần tự trên một bảng.

Schema Locks

Schema modification locks được sử dụng trên Database Engine trong khi thực hiện các thao tác DDL như là xóa bảng hoặc cột.

Các khóa ổn định của lược đồ được database engine sử dụng trong khi biên dịch và thực thi các truy vấn.

Key-Range Locks

Kiểu locks này sẽ bảo vệ danh sách các bản ghi được biểu diễn trong tập bản ghi.

Key-range locks ngăn chặn phantom reads ( rủi ro xảy ra với lệnh read có điều kiện. Ví dụ: giả sử transaction A đọc một tập hợp các dữ liệu đáp ứng một số điều kiện tìm kiếm, transaction B tạo ra một dữ liệu mới khớp với điều kiện được tìm kiếm cho transaction A. Nếu A thực hiện lại với điều kiện như vậy thì nó sẽ nhận được một tập hợp các dữ liệu là không đồng nhất. )

Quản lý transaction

SQL Server triển khai các trasaction với các phạm vi khác nhau đảm bảo các thuộc tính ACID của các transaction này.

Trong thực tế, có nghĩa là sử dụng locks để làm cơ cở traansaction truy vấn vào tài nguyên database được chi sẻ và ngăn chạn sự can thiệp giữa các traansaction với nhau.

Transaction Log

Nhật ký giao dịch (transaction log) là một thành phần quan trọng của cơ sở dữ liệu, nếu hệ thống gặp sự cố, transaction log sẽ đảm bảo việc phục hồi dữ liệu về trạng thái phù hợp.

Không nên xóa hoặc di chuyển transaction log cho đến khi người dùng hiểu được hậu quả của nó.

Các hoạt động được hỗ trợ bởi transaction log:

• Individual transactions recovery.
• Khôi phục giao dịch chưa hoàn thành khi SQL Server khởi động.
• Hỗ trợ nhân rộng giao dịch
• Giải pháp phục hồi sau thảm họa, hỗ trợ hệ thống yêu cầu hiện hoạt cao.
• Khôi phục tệp, cơ sở dữ liệu , nhóm tệp hoặc chuyển tiếp trang đến điểm bị lỗi.

Cắt bớt transaction log

Cắt bớt ransaction log sẽ giải phóng vùng nhớ chiếm dụng bởi log file để tiếp tục ghi log. Log sẽ được tự động cắt bớt khi gặp các sự kiện sau:

• Trong một mô hình phục hồi đơn giản sau (checkpoint) điểm kiểm tra.
• Một mô hình phục hồi số lượng lớn và phục hồi tooàn bộ, nếếu checkpoing( điểm kiểm tra) bắt gặp từ lần backup gần nhất.

Khi các nhật ký hooạt động trong một thời gian dài, việc transaction log sẽ bị chậm trễ và có thể khiến đầy bộ nhớ hệ thống. Log truncations có thể bị chậm vì nhiều lí do, người dùng có thể tìm hiểu xem bất kì điều gì ngăn cản việc cắt bớt trnassaction log bằng việc truy vấn tới cột log_reuse_wait_desc và log_reuse_wait trong sys.databases catalog view.

Mô tả giá trị 2 cột:

Log_reuse_wait	Log_reuse_wait_desc	Mô tả
0	NOTHING	Xác định rằng nó biểu diễn có nhiều honw 1 file log ảo có thể sử dụng lại
1	CHECKPOINT	Xác định không có checkpoint nào xuất hiện từ lần cắt bớt log cuối, hoặc tiêu đề của log nhật ký không di chuyển ra ngoài tệp nhật ký ảo
2	LOG_BACKUP	Xác định log backup được yêu cầu trức khi thực hiện cắt bớt log.
3	ACTIVE_BACKUP_OR_RESTORE	Xác định rằng quá trình backup hoặc restore đang diễn ra.
4	ACTIVE_TRANSACTION	Xác định transaction đang có hiệu lực.
5	DATABASE_MIRRORING	Xác định rằng database mirroring đang tạm dừng hoặc ở chế độ high-perfromance, mirror database là đằng sau cơ sở dữ liệu chính