Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 8: Transacion và bài toán đông thời
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (876.11 KB, 64 trang )
Ch
GV Phi Loan - FIT - UIH
ng 8
1
N i dung
•
•
•
•
T
T ̀
T
̀
B ̀
–C ̀
–C ̀
–G ̀
GV Phi Loan - FIT - UIH
̀ ̀ ?
̀ a Transaction
̀
̀
ng minh
̀
̀
̀ ̀ ̀ ̀
̀ ̀
̀
̀
a
̀ ̀
̀
̀
a
a
̀
̀
a
2
Khái quát v Transaction
• A transaction is a sequence of operations
performed as a single logical unit of work.
• N ̀ ̀
̀
̀
̀ ̀ ̀ ̀
̀
̀ ̀ ̀
̀
̀ ̀ ̀
̀
̀
̀
̀N ̀ ̀ ̀ ̀
̀ ̀
̀ ̀ ̀
̀
̀ ̀ ̀ ̀ ̀ ̀
̀ ̀C“DL̀ ̀ ̀ ̀
̀
̀
̀
̀
̀ ̀
̀ ̀
̀ .
GV Phi Loan - FIT - UIH
3
B
transaction
ACID
a
• T́nh nguyên t (Atomicity): m
t transaction ph i
là 1 đ n v công vi c nguyên t ; ho c t t c các s a đ i d
li u đ u đ c th c thi ho c không 1 s a đ i nào đ c th c
thi.
• T́nh nh t qún (Consistency): m
t giao tác ph i
chuy n CSDL t tr ng thái nh t quán này sang tr ng thái
nh t qn khác.
• T́nh cơ l p (Isolation) : Nh
ng ch nh s a đ c làm
b i transaction hi n hành ph i đ c cô l p kh i nh ng ch nh
s a đ c làm b i các transaction hi n hành khác.
• T́nh b n v ng (Durability): nh
ng thay đ i c a
CSDL do giao tác th c hi n thành công là b n v ng, không b
m t đi k c khi ć l i x y ra sau đ́.
GV Phi Loan - FIT - UIH
4
P
i Transaction
• Giao t́c t
ng minh (explicit transaction): đ
c khai
báo b ng l nh BEGIN TRANSACTION
• Giao t́c ng m đ nh (implicit transaction): giao tác
m i s t đ ng b t đ u ngay khi giao tác tr
c đ́ hồn
t t, nh ng m i
̀
̀
̀
̀
̀
̀
̀
nh COMMIT hay ROLLBACK
• Giao t́c t
đ ng chuy n giao (autocommit
transaction): m i m t l nh đ
GV Phi Loan - FIT - UIH
c xem nh 1 transaction.
5
Transaction trong SQL server
• Đ hồn thành các u c u c a 4 tính ch t
ACID trên, SQL Server cung c p các ch ̀
sau:
– Qu n lý Transaction (Transaction
management)
–Q
̀ ̀Khoá (Locking Management)
– Ghi nh t ký (Logging)
GV Phi Loan - FIT - UIH
̀
6
QUAN Ĺ TRANSACTION
GV Phi Loan - FIT - UIH
7
Đ nh
transaction
ng minh
• BEGIN TRAN[SACTION] [transaction_name]
Dùng đ đánh d u vi c b t đ u c a 1
transaction
• COMMIT [TRAN[SACTION]
[transaction_name]
Hay
COMMIT WORK
Dùng đ đánh d u vi c k t thúc c a 1
transaction t ng minh
GV Phi Loan - FIT - UIH
8
Ví d
GV Phi Loan - FIT - UIH
9
Làm th
quay v l i tr
c nh ng
i
ROLLBACK [TRAN[SACTION]
[transaction_name |savepoint_name ]
D
̀ quay ng c m t transaction t ng
minh hay ng ̀ nh v l ̀ m b ̀ u, hay
v
m d ng (save-point) bên trong 1
transaction
GV Phi Loan - FIT - UIH
10
Ví d
BEGIN TRANSACTION
USE Pubs
UPDATE Titles
SET Royalty = Royalty + 20
WHERE type LIKE 'busin%'
IF (SELECT MAX(Royalty) FROM Titles WHERE Type LIKE 'busin%')
>$25
BEGIN
ROLLBACK TRANSACTION
PRINT 'Transaction Rolled back'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'Transaction Committed'
END
GV Phi Loan - FIT - UIH
11
T
m d ng cho 1 TRANSACTION
• L
̀“́VÈT‘́N“́CTIOǸ
̀
t1
m d ng
(save point) bên trong 1
̀Đ m d ng chia
transaction thành 1 các ph n khác nhau sao cho
transaction có th quay v l ̀ m d ng này n u 1
ph n c a transaction b lo i b
̀ u ki n.
• Cú pháp
SAVE TRAN[SACTION]
{savepoint_name }
GV Phi Loan - FIT - UIH
12
Th c thi m t transaction v
m d ng
BEGIN TRANSACTION
UPDATE Employee
SET cCurrentPosition = '0015'
WHERE cEmployeeCode = '000002
UPDATE Position
SET iCurrentStrength = iCurrentStrength + 1
WHERE cPositionCode = '0015'
SAVE TRANSACTION trnTransaction1
UPDATE Requisition
SET siNoOfVacancy=siNoOfVacancy - 10
WHERE cRequisitionCode='000004'
UPDATE Position
SET iCurrentStrength=iCurrentStrength+10
WHERE cPositionCode='0015
GV Phi Loan - FIT - UIH
13
Th c thi m t transaction v
m d ng
IF (SELECT iBudgetedStrength-iCurrentStrength
FROM Position WHERE cPositionCode = '0015') <0
BEGIN
PRINT 'Transaction 1 has been committed
but transaction 2 has not been committed.'
ROLLBACK TRANSACTION trnTransaction1
END
ELSE
BEGIN
PRINT 'Both the transactions have been committed.'
COMMIT TRANSACTION
END
GV Phi Loan - FIT - UIH
14
V
i
BEGIN TRY;
BEGIN TRANSACTION;
UPDATE Production.ProductInventory
SET Quantity -= 100
WHERE ProductID = 527
AND LocationID = 6 -- misc storage
́ND̀“
̀ ̀ B ̀́ND̀B ̀ ̀4;
UPDATE Production.ProductInventory
SET Quantity += 100
WHERE ProductID = 527 AND LocationID = 50
́ND̀“
̀ ̀ F ̀́ND̀B ̀ ̀11;
COMMIT TRANSACTION;
GV Phi Loan - FIT - UIH
15
V
i
END TRY
BEGIN CATCH;
ROLLBACK TRANSACTION;
‘́I“E‘‘O‘ I
̀T
RETURN;
END CATCH;
GV Phi Loan - FIT - UIH
̀E
̀16, 1);
16
H m XACT_STATE()
• C ̀ ̀
̀
̀
̀
̀
̀
1
̀
̀
̀ ̀
̀
̀
̀
̀
̀ ̀
̀
̀
̀
̀ ̀
c commit hay không?
Yêu
c
0
Không̀ ̀
-1 Yêù
̀
̀
̀
̀ ̀ ̀
̀
̀ ̀ ̀
̀
̀
̀ ̀
̀ ̀Y ̀ ̀
̀ ̀
̀
̀
̀
GV Phi Loan - FIT - UIH
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
ng
̀
̀
̀
̀
̀ ̀
̀ ̀
̀
̀
̀ ̀
n.
17
V
m Xact_State()
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Production.Product
WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable
̀̀̀̀ ̀ ̀‘
̀
̀
̀
ROLLBACK TRANSACTION;
END;
GV Phi Loan - FIT - UIH
18
V
m Xact_State()
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
̀C
̀
̀
COMMIT TRANSACTION;
END;
END CATCH;
GO
GV Phi Loan - FIT - UIH
19
Các l nh khơng h p l trong transaction
• Rollback ph i có kh
̀
l
̀ ̀
̀
c dùng:
̀ ̀
y các
– CREATE DATABASE, ALTER DATABASE
– CREATE TABLE, ALTER TABLE, TRUNCATE TABLE
– CREATE INDEX
– T t c l nh DROP
– SELECT...INTO
– GRANT or REVOKE
– DISK INIT, RECONFIGURE, LOAD DATABASE,
LOAD TRANSACTION
GV Phi Loan - FIT - UIH
20
QUAN Ĺ KH́A
SQL Server uses a dynamic, cooperative locking
mechanism to protect the consistency of the data in
the database.
GV Phi Loan - FIT - UIH
21
N i dung
•C ̀
–K
̀
a
a Intent
•T
GV Phi Loan - FIT - UIH
̀
̀
̀
̀
a
22
T
• T
a
̀
̀
̀
̀ ̀
̀ u.
• T
ng.
• C ̀
̀
̀ ̀
GV Phi Loan - FIT - UIH
̀
̀
̀
̀
̀ ̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀
̀ a transaction.
̀
̀
̀
23
T
a
• N
̀
̀D
̀
̀
• T ̀ ̀
̀
̀
̀ ̀
̀
̀
̀
̀
̀E
̀ ̀ ̀
̀
̀ ̀
̀ ̀
̀
̀ ̀
̀
ng.
̀
̀ ̀
̀ ̀
̀ ̀
̀
̀
̀ ng commit hay roll
back)
GV Phi Loan - FIT - UIH
24
GV Phi Loan - FIT - UIH
25
