Giáo Trình SQL bài 12

1
TRANSACTION
2
Nguyên Tắc Hoạt Ðộng Của Transaction Log
Transaction log file trong SQL Server dùng để ghi lại các thay đổi xảy ra trong database. Quá trình này diễn ra như sau:
đầu tiên khi có một sự thay đổi data như Insert, Update, Delete được yêu cầu từ các ứng dụng, SQL Server sẽ tải (load) data page tương ứng lên memory (vùng bộ nhớ này gọi là data cache),
sau đó data trong data cache được thay đổi (những trang bị thay đổi còn gọi là dirty-page).
Tiếp theo mọi sự thay đổi đều được ghi vào transaction log file cho nên người ta gọi là write-ahead log.
Cuối cùng thì một quá trình gọi là Check Point Process sẽ kiểm tra và viết tất cả những transaction đã được commited (hoàn tất) vào dĩa cứng (flushing the page).
3
4
1. Khái niệm cơ bản về Transaction:
Một giao tác (transaction) là một chuỗi một hoặc nhiều câu lệnh SQL được kết hợp lại với nhau thành một khối công việc.
Các câu lệnh SQL xuất hiện trong giao tác thường có mối quan hệ tương đối mật thiết với nhau và thực hiện các thao tác độc lập.
Việc kết hợp các câu lệnh lại với nhau trong một giao tác nhằm đảm bảo tính toàn vẹn dữ liệu và khả năng phục hồi dữ liệu.
Trong một giao tác, các câu lệnh có thể độc lập với nhau nhưng tất cả các câu lệnh trong một giao tác đòi hỏi hoặc phải thực thi trọn vẹn hoặc không một câu lệnh nào được thực thi.
5
Xét một ví dụ kinh điển về transaction: Ta muốn chuyển một số tiền $500 từ account A sang account B như vậy công việc này cần làm các bước sau:
Trừ $500 từ account A - Cộng $500 vào account B
Tuy nhiên việc chuyển tiền trên phải được thực hiện dưới dạng một transaction nghĩa là giao dịch chỉ được xem là hoàn tất (commited) khi cả hai bước trên đều thực hiện thành công.
Nếu vì một lý do nào đó ta chỉ có thể thực hiện được bước 1 (chẳng hạn như vừa xong bước 1 thì điện cúp hay máy bị treo) thì xem như giao dịch không hoàn tất và cần phải được phục hồi lại trạng thái ban đầu (roll back)
6
7
Giao tác SQL được định nghĩa trên các câu lệnh sau:
BEGIN TRANSACTION: Bắt đầu một giao tác
SAVE TRANSACTION: Đánh dấu một vị trí trong giao tác (gọi là điểm đánh dấu)
ROLLBACK TRANSACTION: Quay lui trở lại đầu giao tác hoặc một điểm đánh dấu trước đó trong giao tác
COMMIT TRANSACTION: Đánh dấu điểm kết thúc một giao tác. Khi câu lệnh này thực thi cũng có nghĩa là giao tác đã thực hiện thành công
ROLLBACK [WORK]: Quay lui trở lại đầu giao tác
COMMIT [WORK]: Đánh dấu kết thúc giao tác
8
VD: Giao tác dưới đây kết thúc do lệnh ROLLBACK TRANSACTION và mọi thay đổi vể mặt dữ liệu mà giao tác đã thực hiện (Update) đều không có tác dụng.

BEGIN TRANSACTION giaotac1
Update monhoc Set sodvht=4
Where sodvht=3;

Update diemthi Set diemlan2=0
Where diemlan2 Is Null
ROLLBACK TRANSACTION giaotac1
9
VD: giao tác dưới đây kết thúc bởi lệnh COMMIT và thực hiện thành công việc cập nhật dữ liệu trên các bảng MONHOC và DIEMTHI.

BEGIN TRANSACTION giaotac2
Update monhoc Set sodvht=4
Where sodvht=3;

Update diemthi Set diemlan2=0
Where diemlan2 Is Null
COMMIT TRANSACTION giaotac2
10
SAVE TRANSACTION
được sử dụng để đánh dấu một vị trí trong giao tác. Khi câu lệnh này được thực thi, trạng thái của cơ sở dữ liệu tại thời điểm đó sẽ được ghi lại trong nhật ký giao tác.

ROLLBACK TRANSACTION
Trong trường hợp này, những thay đổi về mặt dữ liệu mà giao tác đã thực hiện từ điểm đánh dấu đến trước khi câu lệnh ROLLBACK được triệu gọi sẽ bị huỷ bỏ. Giao tác sẽ được tiếp tục với trạng thái cơ sở dữ liệu có được tại điểm đánh dấu.
Nhưng nếu câu lệnh ROLLBACK này được sử dụng để quay lui lại đầu giao tác (tức là huỷ bỏ giao tác), giao tác sẽ kết thúc và do đó câu lệnh COMMIT TRANSACTION trong trường hợp này sẽ gặp lỗi.
11
12
Ví dụ: Câu lệnh COMMIT TRANSACTION trong giao tác dưới đây kết thúc thành công

BEGIN TRANSACTION giaotac3
Update diemthi Set diemlan2=0
Where diemlan2 Is Null
SAVE TRANSACTION a
Update monhoc Set sodvht=4
Where sodvht=3
ROLLBACK TRANSACTION a
Update monhoc Set sodvht=2
Where sodvht=3
COMMIT TRANSACTION giaotac3
13
Ví dụ: về câu lệnh COMMIT TRANSACTION gặp lỗi:

BEGIN TRANSACTION giaotac4
Update diemthi Set diemlan2=0
Where diemlan2 Is Null
SAVE TRANSACTION a
Update monhoc Set sodvht=4
Where sodvht=3
ROLLBACK TRANSACTION giaotac4
Update monhoc Set sodvht=2
Where sodvht=3
COMMIT TRANSACTION giaotac4
14
Giao tác lồng nhau:
Các giao tác trong SQL có thể được lồng vào nhau theo từng cấp. Điều này thường gặp đối với các giao tác trong các thủ tục lưu trữ được gọi hoặc từ một tiến trình trong một giao tác khác.

Create Proc sp_ThemMonHoc
@maMH nvarchar(10), @tenMH nvarchar(50),
@sodvht smallint
As
BEGIN TRANSACTION T1
Insert Into MonHoc
Values(@maMH, @ tenMH, @sodvht);
COMMIT TRANSACTION T1
15
Lời gọi đến thủ tuch sp_ThemMonHoc c giao tác lồng:
BEGIN TRANSACTION T2
EXEC sp_ThemMonHoc `TI-005`, `CSDL`, 5
ROLLBACK TRANSACTION T2

Trong giao tác trên, câu lệnh Rollback Transaction T2 huỷ bỏ giao tác và do đó tác dụng của lời gọi thủ tục trong giao tác không còn tác dụng, tức là không có dòng dữ liệu nào mới được bổ sung vào bảng T (cho dù giao tác T1 trong thủ tục sp_tranex đã thực hiện thành công với lệnh Commit Transaction T1).
16
Giải sử table MonHoc chưa có dữ liệu ta xét VD sau:
BEGIN TRANSACTION
EXEC sp_ThemMonHoc `T-01`, `ASP.NET`, 4
SAVE TRANSACTION a
EXEC sp_ThemMonHoc `T-02`, ‘CSDL`, 5
ROLLBACK TRANSACTION a
EXEC sp_ThemMonHoc `T-03`, ‘SQL Server`, 5
COMMIT TRANSACTION
Dữ liệu trong bảng MonHoc sau khi thực hiện là:
MaMH TenMH SoDVHT
T-01 ASP.NET 4
T-03 SQL Server 5
Lời gọi thủ tục cho môn `CSDL` trong giao tác đã bị huỷ bỏ bởi lệnh Rollback Transaction trong giao tác.
17
Như đã thấy trong ví dụ trên, khi các giao tác SQL được lồng vào nhau, giao tác ngoài cùng nhất là giao tác có vai trò quyết định. Nếu giao tác ngoài cùng nhất được uỷ thác (COMMIT) thì các giao tác được lồng bên trong cũng đồng thời uỷ thác
Và nếu giao tác ngoài cùng nhất thực hiện lệnh ROLLBACK thì những giao tác lồng bên trong cũng chịu tác động của câu lệnh này (cho dù những giao tác lồng bên trong đã thực hiện lệnh COMMIT TRANSACTION)