Bà
i
th
ự
c
hàn
h
ô
n
H
ệ
q
u
ả
n
t
r
ị
C
S
DL
1
--
B
ướ
c
1
Insert into
Ho
cS
inh
(
MaHS
,
HoT
en
,
Ma
Lop)
values
(‘
hs
01’,
’N
guyen
V A
’
,
1)
--
B
ướ
c
2
Up
date
Lop
Se
t
SiSo
= SiSo +1
Hướng dẫn thực hành môn Hệquản trị
CSDL
Chủđề2:Transaction
1. Sử dụng Transaction
1.1. 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 đểbảo toàn dữliệu). 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.
1.2. 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ựchiện thành công, hoặc không có thao tác nào được thực hiệntậ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
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?
1.3. Khai báo và sử dụng transaction
Các lệnh liên quan :
- Bắt đầu transaction :
obegin tran / begin transaction
- Hoàn tất transaction :
ocommit/ commit tran / commit transaction
- Quay lui transaction :
orollback / 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_savepointcó tác dụng quay lui giao tác đến vịtrí đặt savepoint tương ứng
Bà
i
th
ự
c
hàn
h
ô
n
H
ệ
q
u
ả
n
t
r
ị
C
S
DL
2
create proc sp_te
st
begin tran
--T2
as exec
s
p_tes
t
begin
tra
n
--T1 --do
s
ometh
ing
--do
s
ome
thing
comm
it
tran
commit
tr
an
(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 ROLLBACKMỘ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, sẽkhông bị ảnh hưởng bởi lệnh rollback.
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 =?
1.4. 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 stored 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.
Ví dụ:
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.
Bà
i
th
ự
c
hàn
h
ô
n
H
ệ
q
u
ả
n
t
r
ị
C
S
DL
3
Chú ý trước câu lệnh gọi sp_test SQL Server tựđộng ghi nhớgiá trịbiến hệthống
@@tranCount, giảsửlưu vào biến @trc. Ngay sau câu lệnh này, biến @trc và
@@tranCount sẽđược so sánh giá trị, nếu khác nhau thì SQL Server sẽphát sinh lỗi giúp biết được
lệnh gọi stored proc có bảo toàn các cấp lồng nhau vềtransaction hay không. Đặc biệt khi sp_test gọi
rollback thì chắc chắn T2 sẽcó lỗi và toàn bộtransaction đãđược rollback; lúc này người dùng được
thông báo đểtựquyết định thực hiện tiếp hoặc
kết thúc
tiến trình đang thực hiện.
- 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
được save tran trước đó.
-
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 (bởi các thao
tác sẽđược thực hiện tuần tự).
Ghi chú : Nếu có hai transaction T1, T2 thực hiện trong cùng một connectionthì 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.
1.5. 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:
o Không có quyền truy cập trên 1 đối tượng (table, stored procedure,…)
o 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,…).
o Deadlock.
o …
SQL Server trảgiá trịlỗi vềtrong biến toàn cục@@error.
o @@error= 0: không xảy ra lỗi
o @@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ó) và
yêu cầu rollback giao tác một cách tường minh bằng lệnh rollback/rollback transaction.
2. Rollback một phần transaction
2.1. Đặt vấn đề
Thông thường khi chúng ta rollback một transaction thì toàn bộnhững thao tác đã thực hiện trong
transaction đóđều bịhủy bỏ. Tuy nhiên trong một sốtrường hợp nhấtđịnh chúng ta có nhu cầu chỉhủy
bỏmột sốthao tác nào đó mà thôi (các thao tác còn lại không bịhủy bỏ). Tuy nhu cầu này ít khi phát