SỬ DỤNG TRANSACTION

Khái niệm transaction :
Giao tác (transaction) là 1 tập hợp có thứ tự các thao tác (statement) truy xuất dữ liệu trên CSDL thành 1 đơn vị công việc logic (xem là 1 thao tác nguyên tố), chuyển CSDL từ trạng thái nhất quán này sang trạng thái nhất quán khác.
Ví dụ : Ngân hàng thực hiện chuyển tiền từ tài khoản A sang tài khoản B, cần thực hiện hai công việc : trừ tiền của A, tăng tiền của B. Hai công việc này hoặc cả hai thành công hoặc không có công việc nào thành công (nếu một công việc vì lý do nào đó không thực hiện thành công thì trạng thái ban đầu trước khi chuyển tiền phải được khôi phục).Khi đó việc chuyển tiền cần được đặt vào một giao tác.
Chú ý : khi ta viết một thao tác (statement) trong SQL Server, nếu không có chỉ thị nào khác, thao tác này là một transaction.

Tại sao phải dùng transaction ?
Dùng khái niệm giao tác khi xử lý các vấn đề liên quan đến truy xuất dữ liệu đồng thời
Có những xử lý trên CSDL được thực hiện bằng nhiều thao tác liên tiếp nhau, tập hợp các thao tác này phải được xem là một thao tác nguyên tố để đảm bảo tính nhất quán của dữ liệu sau khi thực hiện, nghĩa là, hoặc tất cả được thực hiện thành công, hoặc không có thao tác nào được thực hiệnà tập hợp các thao tác này được viết thành một transaction.
Ví dụ
Stored procedure thực hiện việc thêm một học sinh vào lớp
--Bước 1
Insert into HocSinh (MaHS, HoTen, MaLop)
values (‘hs01’,’Nguyen V A’,1)
--Bước 2
Update Lop
Set SiSo = SiSo +1

Nếu bước 2 của stored proc thực hiện không thành công thì dữ liệu trong CSDL có còn nhất quán không?

Khai báo và sử dụng transaction :
Các lệnh liên quan :
Bắt đầu transaction : begin tran / begin transaction
Hoàn tất transaction : commit/ commit tran / commit transaction
Quay lui transaction : rollback / rollback tran / rollback transaction
Đánh dấu savepoint trong transaction : save transaction tên_savepoint
Biến @@trancount : cho biết số transaction hiện đang thực hiện (chưa được kết thúc với rollback hay commit) trong connection hiện hành.
Ghi chú :
lệnh rollback tran + tên_savepoint có tác dụng quay lui giao tác đến vị trí đặt savepoint tương ứng (không có tác dụng kết thúc transaction), các khóa (lock) được đặt khi thực hiện các thao tác nằm trong phần bị rollback sẽ được mở ra. (xem bài đọc thêm về rollback một phần transaction)
Khi khai báo transaction tường minh, phải đảm bảo rằng sau đó nó được rollback hoặc commit tường minh, nếu không, transaction sẽ tiếp tục tồn tại và chiếm giữ tài nguyên, ngăn trở sự thực hiện của các transaction khác.
Lệnh rollback chỉ có tác dụng quay lui các thao tác trên CSDL (thêm, xóa, sửa). Các câu lệnh khác chẳng hạn lệnh gán lệnh rollback sẽ không có tác dụng.
Các ví dụ : Cho bảng dữ liệu bên dưới. Hãy cho biết giá trị tại các ô màu vàng sau khi thực hiện (độc lập) các transaction trong các ví dụ sau .
MaTS TuaSach Tacgia
1 aaa ABC
2 bbb DEF
3 ccc GHI

Vd1 :
@@trancount = 0
Begin tran --(T1)
@@Trancount = ?
update Tuasach set Tacgia = xxx where MaTS = 1
update TuaSach set TacGia = yyy where MaTS =2

update TuaSach set TacGia = zzz where MaTS =3

Commit tran --(T1)
@@trancount =?

Vd 2:
declare @x int
set @x=3
@@trancount = 0
Begin tran --(T1)
@@trancount =?
update Tuasach set Tacgia = xxx where MaTS = 1
update TuaSach set TacGia = yyy where MaTS =2

set @x =7
update TuaSach set TacGia = zzz where MaTS =3

Rollback --(tran)
@@trancount =?
@x =?

Vd 3 :
declare @x int
set @x=3
@@trancount =0
Begin tran --(T1)
@@trancount =?
update Tuasach set Tacgia = xxx where MaTS = 1
update TuaSach set TacGia = yyy where MaTS =2

Save tran S
set @x =7
update TuaSach set TacGia = zzz where MaTS =3

Rollback tran S
@@trancount =?
@x=?
Commit tran --(T1)
@@trancount =?
@x =?

Transaction lồng nhau :
Các transaction có thể thực hiện lồng nhau, mục đích chủ yếu là để cho phép các procedure có chứa transaction có thể được gọi từ những tiến trình đã nằm bên trong một transaction hoặc từ những tiến trình không nằm bên trong một transaction nào.
Vd:
create proc sp_test
as
begin tran –T1
--do something
commit tran
---------------------------
begin tran --T2
exec sp_test
--do something
commit tran

==> proc sp_test được gọi từ một tiến trình nằm bên trong một transaction (T2), do đó, transaction T1 trong sp_test được xem là lồng bên trong transaction T2.

SQL đưa ra các qui định sau nhằm đảm bảo việc thực hiện của các transaction lồng nhau không làm vi phạm các tính chất của giao tác :

• Lệnh commit transaction sẽ được xem như thuộc về transaction bắt đầu sau nhất (bên trong nhất) chưa commit , cho dù nó được đi kèm với tên của transaction bắt đầu trước (cấp ngoài hơn).
• Lệnh commit transaction của transaction con chỉ giảm @@trancount đi 1, không có tác dụng yêu cầu hệ quản trị ghi nhận chắc chắn những thay đổi trên CSDL mà transaction này đã làm.
• Chỉ có lệnh commit transaction của transaction ngoài cùng mới thực sự có tác dụng này (như vậy nếu có n transaction lồng nhau thì lệnh commit transaction thứ n mới thực sự commit toàn bộ giao tác).
• Chỉ cần có một lệnh rollback tran (ở bất cứ cấp nào) là toàn bộ giao tác sẽ bị rollback. Lý do có quy luật này đó là nhằm bảo đảm ý nghĩa một đơn vị công việc lôgic bởi khi có lệnh gọi rollback từ một cấp transaction nào đó đồng nghĩa với việc không thực hiện thành công tại vị trí đó. Trong một giao tác đang xét, nếu thực hiện không thành công ở bất kỳ điểm nào ở bất kỳ cấp giao tác nào, giao tác ngoài cùng cần được khôi phục bởi đã trong nó đã tồn tại một ví trí “bị lỗi”.
• Rollback tran + tran_name chỉ hợp lệ khi “tran_name” là tên của transaction ngoài cùng, nếu tran_name là tên của transaction bên trong, lệnh này sẽ bị lỗi.

Giao tác không được lồng nhau quá 32 cấp.
Các transaction lồng nhau không tranh chấp nhau về tài nguyên (có thể chia sẻ với nhau các khoá trên đơn vị dữ liệu được đọc/ghi ). Điều này có thể hiểu từ việc transaction lồng nhau chỉ xảy ra trên cùng một connection. Mà trên cùng một connection như vậy thì dĩ nhiên, không có tranh chấp.

Ghi chú : Nếu có hai transaction T1, T2 thực hiện trong cùng một connection thì có hai trường hợp :

• T2 thực hiện sau khi T1 kết thúc, lúc đó T1 và T2 là độc lập nhau;
• T2 thực hiện khi T1 chưa kết thúc, khi đó T2 là giao tác con lồng bên trong T1. Nói cách khác, không có trường hợp nào xảy ra tranh chấp giữa hai transaction trên cùng connection.

Ví dụ 1:

@@trancount =0
Begin tran --(T1)
@@trancount =?
update Tuasach set Tacgia = xxx where MaTS = 1
update TuaSach set TacGia = yyy where MaTS =2

Begin tran --(T2)
update TuaSach set TacGia = zzz where MaTS =3

commit tran T2
@@trancount =?
rollback tran --(T1)
@@trancount =?

Ví dụ 2 :

@@trancount =0
Begin tran --(T1)
@@trancount =?
update Tuasach set Tacgia = xxx where MaTS = 1
update TuaSach set TacGia = yyy where MaTS =2

Begin tran --(T2)
update TuaSach set TacGia = zzz where MaTS =3
@@trancount =?
rollback tran T2
Lỗi

Ví dụ 3 :

@@trancount =0
Begin tran --(T1)
@@trancount =?
update Tuasach set Tacgia = xxx where MaTS = 1
update TuaSach set TacGia = yyy where MaTS =2

Begin tran --(T2)
update TuaSach set TacGia = zzz where MaTS =3
@@trancount =?
rollback tran
@@trancount = ?
Commit /rollback tran
Lỗi, tại sao?

Kiểm tra lỗi khi xây dựng transaction
Một số lỗi thường gặp sau khi thực hiện 1 câu lệnh trong giao tác:

• Không có quyền truy cập trên 1 đối tượng (table, stored procedure,…)
• Vi phạm ràng buộc toàn vẹn (primary key, foreign key, check, rule, các ràng buộc được kiểm tra bằng trigger,…).
• Deadlock.
  …

SQL Server trả giá trị lỗi về trong biến toàn cục @@error.

• @@error= 0: không xảy ra lỗi
• @@error <> 0: xảy ra lỗi với mã lỗi là @@error

Giao tác không thể tự động rollback khi gặp những lỗi phát sinh trong quá trình thực hiện 1 câu lệnh thành phần trong giao tác. Vì vậy cần kiểm tra giá trị của biến @@error sau mỗi câu lệnh thành phần trong giao tác và cần xử lý những lỗi (nếu có): yêu cầu giao tác rollback một cách tường minh bằng lệnh rollback transaction.
Ví dụ :
Xây dựng giao tác thực hiện việc thêm độc giả người lớn (theo đúng mô tả câu 4.9, bài tập quản lý thư viện). Giao tác này nằm trong procedure sp_ThemDocGiaNguoiLon
Create proc sp_ThemDocGiaNguoiLon
@ Ten…
…
as
--buoc 1 : xác định mã độc giả
declare @madg
set @madg = 1
begin transaction
while exists (select * from DocGia where ma_docgia = @madg)
set @madg = @madg +1
if ( @@error <>0 )
begin
rollback tran
return
end
-- buoc 2 : insert vao bang docgia
insert into DocGia values(…)
if ( @@error <>0 )
begin
rollback tran
return
end
-- buoc 3 : kiem tra tuoi
if datediff(yy, @ngaysinh, getdate()) <18
begin
raiserror(‘Tuoi nho hon 18’,16,1)
rollback tran
return
end
…
commit transaction