Transaction trong SQL Server - Web888 chia sẻ kiến thức lập trình, kinh doanh, mmo

Giới thiệu về Transaction

Có nhiều thực trạng người dùng nhu yếu cần đổi khác tài liệu trong nhiều bảng trong database. Trong nhiều trường hợp, tài liệu sẽ bị mất tính đồng điệu khi thực thi riêng không liên quan gì đến nhau nhau .
Giả sử nếu câu lệnh tiên phong được thực thi đúng chuẩn nhưng câu lệnh tiếp theo thất bại vì tài liệu không đúng mực .
Ví dụ, một trường hợp đơn cử là hoạt động giải trí chuyển tiền trong mạng lưới hệ thống ngân hàng nhà nước. 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.

Bạn đang đọc: Transaction trong SQL Server – Web888 chia sẻ kiến thức lập trình, kinh doanh, mmo

Định nghĩa transaction

Một đơn vị chức năng việc làm hài hòa và hợp lý phải bộc lộ bốn thuộc tính, được gọi là thuộc tính Nguyên tử, tính đồng điệu, cô lập và tính vững chắc ( ACID ), để đủ điều kiện kèm theo 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 tương hỗ 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 lập khối lệnh thành công xuất sắc hoặc thất bại và phân phối cơ sở noiw database hoàn toàn có thể rollback những thao tác .
Lỗi được phát hiện trong quy trình thực thi của một batch đơn thuần có có năng lực thành công xuất sắc một phần, đây không phải là hiệu quả mong ước khi sử dụng transaction .
Vấn đề này sẽ dẫn đến xích míc logic giữa những bảng trong CSDL .
Ngời dùng hoàn toàn có thể thêm đoạn mã trấn áp 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 hàng loạt đổi khác trước khi bắp gặp lỗi .

Điều khiển transaction

Transactions hoàn toàn có thể được tinh chỉnh và điều khiển trải qua ứng dụng bằng cách định nghĩa mở màn và kết thúc cho một transaction .
Transaction đưojc quản trị bằng những tầng liên kết theo mặc định .
Khi transaction khởi đầu một liên kết, tổng thể những câu lệnh T-SQL được thực thi trên cùng một liên kết và là một phần của liên kết cho tới khi transaction kết thúc .

BEGIN TRANSACTION

Câu lệnh BEGIN TRANSACTION ghi lại đ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 ghi lại đ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 ghi lại đ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 hoàn toàn có thể hủy bỏ và quay trở lại điểm khởi đầu hoặc savepoint trong transaction .
Nó được sử dụng để xóa hàng loạt tài liệu đã sửa đổi được tạo từ khi khởi đầu transaction hoặc tới savepoint. Nó cũng giải phóng những 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 tài liệu đổi khác được tạo ra bởi stored procedure nếu một transaction có hiệu lực thực thi hiện hành mở màn trước khi procedure thực thi .

@@TRANCOUNT trong transaction

@ @ TRANCOUNT là một hàm mạng lưới hệ thống trả về giá trị số của câu lệnh transaction, xảy ra trong liên kết 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.

Xem thêm: Lập trình tân binh | 2.6. Lớp và con trỏ

Việc lưu lại transactions trên địa thế căn cứ lịch trình trong mọi cơ sở tài liệu tương quan đơn lẻ tạo ra một chuỗi những điểm Phục hồi chung trong cơ sở tài liệu

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

Một transaction mark sẽ tiêu thụ khoảng trống vật lý, chỉ sử dụng chúng cho transaction có vai trò quan trọng trong kế hoạch hồi sinh cơ sở tài 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 những server khác nhau, hoặc cùng server, marks cần phải được ghi vào hồ sơ của tổng thể những cơ sở tài liệu bị ảnh hưởng tác động .

Create Marked Transaction

Để tạo một marked transaction, người dùng hoàn toàn 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 thanh toán giao dịch phải được tách biệt sửa đổi tài liệu hoặc tài nguyên được triển khai bởi những thanh toán giao dịch khác .
Các mức độ cách ly được xác lập trong điều kiện kèm theo được cho phép hiệu ứng đồng thời như dirty reads .
Transaction isolation level tinh chỉnh và điều khiển những yếu tố 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 nhu yếu một khóa duy nhất mọi lúc trên mỗi tài liệu mà nó kiểm soát và đ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 thanh toán 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 những kiểu locks thông dụng 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 trường hợp bế tắc ( deadlock ). Nó tiếp nối đuôi nhau những transaction, transaction sẽ đọc tài liệu, có được một khóa được san sẻ trên hàng hoặc một trang, và sửa đổi tài liệu nhu yếu quy đổi lock thành exclusive lock .

Shared Locks

Các locks này được cho phép những thanh toán giao dịch song song để đọc một tài nguyên dưới sự trấn áp đồng thời .
Shared locks gỉai phóng tài nguyên một khi thao tác đọc hoàn thành xong, ngoại trừ tầng cách ly được gán thành hành vi đọc lăp lại lại hoặc cao hơn .

Exclusisve Locks

Các locks này ngăn ngừa truy vấn 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 hoàn toàn có thể biến hóa tài liệu và thao tác đọc sẽ được sắp xếp chỉ khi trải qua tầng cách ly chưa cam kết hoặc chính sách NOLOCK .
Câu lệnh DML như INSERT, UPDATE, DELETE sử dụng để sửa đổi tài 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 miêu 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 tài liệu được copy vào bảng. Các locks này sẽ được cho phép nhều luồng cùng chạy để tải tài 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 triển khai những thao tác DDL như là xóa bảng hoặc cột .
Các khóa không thay đổi của lược đồ được database engine sử dụng trong khi biên dịch và thực thi những truy vấn .

Key-Range Locks

Kiểu locks này sẽ bảo vệ list những bản ghi được trình diễn trong tập bản ghi .
Key-range locks ngăn ngừa phantom reads ( rủi ro đáng tiếc xảy ra với lệnh read có điều kiện kèm theo. Ví dụ : giả sử transaction A đọc một tập hợp những tài liệu phân phối một số ít điều kiện kèm theo tìm kiếm, transaction B tạo ra một tài liệu mới khớp với điều kiện kèm theo được tìm kiếm cho transaction A. Nếu A triển khai lại với điều kiện kèm theo như vậy thì nó sẽ nhận được một tập hợp những tài liệu là không giống hệt. )

Quản lý transaction

SQL Server tiến hành những trasaction với những khoanh vùng phạm vi khác nhau bảo vệ những thuộc tính ACID của những transaction này .
Trong trong thực tiễn, 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 những traansaction với nhau .

Transaction Log

Nhật ký thanh toán giao dịch ( transaction log ) là một thành phần quan trọng của cơ sở tài liệu, nếu mạng lưới hệ thống gặp sự cố, transaction log sẽ bảo vệ việc hồi sinh tài liệu về trạng thái tương thích .
Không nên xóa hoặc chuyển dời 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:

Xem thêm: Ép kiểu dữ liệu trong lập trình C/C++

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 hữu bởi log file để liên tục ghi log. Log sẽ được tự động hóa cắt bớt khi gặp những 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

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

admin

final-blade.com