Bài giảng SQL Server 2008: Managing Transactions, locks

Managing Transactions, Locks

Nội dung

ị

ộ

ị

ề ồ

ờ

 Đ nh nghĩa Transactions ủ  Thu c tính c a Transactions ạ  Các lo i Transaction ụ  Công d ng Transaction log  Đ nh nghĩa Lock ấ  Các v n đ đ ng th i (Concurrency

problem).

ể  Các ki u Lock

Transaction

ặ

ộ ỗ ồ ộ là m t chu i g m m t ho c ế ợ ạ ớ i v i nhau thành c k t h p l

ộ ề ộ ệ ố

• M t giao tác (transaction) ượ ệ nhi u câu l nh SQL đ 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ó ự ố ệ t v i nhau và th c hi n ng đ i m t thi m i quan h t ạ ớ ệ ệ i v i các thao tác đ c l p. Vi c k t h p các câu l nh l ữ ẹ 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 ộ ấ ả ỏ ư t c các câu l nh trong m t giao tác đòi h i nh ng t ặ ự ả ặ ho c ph i th c thi tr n v n ho c không m t câu l nh ự c th c thi. nào đ

Transaction

ể ả ừ ả VD: giao d ch chuy n kho n 50$ t tài kho n A sang tài

ị kho n Bả

INSERT UPDATE DELETE

UPDATE

read(A) A := A – 50 write(A) read(B) B := B + 50 write(B) INSERT

Thuộc tính của Transaction

 Tính nguyên tử (Atomicity): Là một công việc đơn tử. Hoặc toàn bộ các hiệu chỉnh dữ liệu được thực hiện hoặc là tất cả chúng đều không được thực hiện.

 Tính nhất quán (Consistency): Tính nhất quán đòi hỏi sau khi giao tác kết thúc, cho dù là thành công hay bị lỗi, tất cả dữ liệu phải ở trạng thái nhất quán (tức là sự toàn vẹn dữ liệu phải luôn được bảo toàn)

 Tính cô lập (Isolation): Dữ liệu khi hiệu chỉnh được thực hiện bởi các Transaction phải độc lập với các hiệu chỉnh khác của các Transaction đồng thời khác.

 Tính bền vững (Durability): Sau khi một giao dịch thực hiện thành công, các thay đổi đã được tạo ra đối với CSDL vẫn còn ngay cả khi xảy ra sự cố hệ thống.

Transaction Properties

m icit y

Isolation

A t o

b ilit y

r a

nsistency

Transaction

Quản lý các trạng thái Transaction

 Active : Trạng thái khởi đầu, giao dịch giữ trong

 Partially committed: Sau khi lệnh cuối cùng được

trạng thái này trong khi nó đang thực hiện

 Failed: Sau khi phát hiện rằng sự thực hiện không

thực hiện.

 Aborted: Sau khi giao dịch đã bị “roll back” và CSDL đã phục hồi lại trạng thái của nó trước khi khởi động giao dịch.

 Committed: Sau khi thực hiện thành công giao dịch.

thể tiếp tục được nữa.

Quản lý các Transaction -trạng thái

Mô hình Transaction trong SQL

 Giao tác SQL được định nghĩa dựa trên các câu lệnh

(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.

xử lý giao tác sau đây:  BEGIN TRANSACTION: Bắt đầu một giao tác  SAVE TRANSACTION: Đánh dấu một vị trí trong giao tác

Mô hình Transaction trong SQL

SQL Statements

 Cú Pháp: BEGIN TRANSACTION COMMIT | ROLLBACK TRANSACTION

Mô hình Transaction trong SQL

Chúng ta có thể sử dụng TRY…CACTH hoặc IF cùng

với TRANSACTION

BEGIN TRY BEGIN TRAN

-- Code for your transaction

COMMIT TRAN END TRY BEGIN CATCH

-- output an error message

ROLLBACK TRAN END CATCH

Mô hình Transaction trong SQL

 Ví dụ 6.1: 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

Mô hình Transaction trong SQL

 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

Quản lý các Transaction -trạng thái

ạ ộ ủ ộ Ho t đ ng c a m t transaction

Mô hình Transaction trong SQL

Các loại Transaction

ườ

ng minh ườ

ng minh

 Explicit – T  Implicit – Không t  Auto commit transaction Giao tác t

ự

ộ đ ng chuy n giao

ể  Distributed Transactions

ạ

Các lo i Transaction

 Tường minh - Explicit  Không tường minh – Implicit  Giao tác tự động chuyển giao (auto commit transaction)  Distributed Transactions

Explicit Transaction_Transaction tường minh

ộ

ắ ầ

ế

ộ

ộ ả ị  Là m t Transaction ph i đ nh nghĩa b t đ u m t Transaction(Begin Transaction) và k t thúc m t transaction(Commit Transaction)  Use BEGIN TRANSACTION to start

BEGIN TRAN [SACTION](Transaction_Name|@Tran_Name_Variable]

 Use COMMIT TRANSACTION or COMMIT WORK

to successfully complete

COMMIT [TRAN [SACTION](Transaction_Name|@Tran_Name_Variable]

Explicit Transaction_Transaction tường minh

 Save Position TRANSACTION

SAVE TRAN [SACTION]{Transaction_Name|@Tran_Name_Variable}  Use ROLLBACK TRANSACTION or ROLLBACK ộ ủ WORK to cancel (h y m t Transaction) ROLLBACK TRAN [SACTION] [Transaction_Name| @Tran_Name_Variable | savepoint_Name |@savepoint_variable]

ả ề ố ứ ự @@TRANCOUNT : Tr v s th t

ở ố ượ ồ ồ ấ mà Transaction i đa l ng 32 c p, không nên l ng nhau. c m , t đ

Implicit Transactions(Ngầm định)

ộ

ế

ệ

ự ộ ặ

ầ

ạ

ỉ  B n không c n b t đ u m t transaction, b n ch

ộ ỗ

ộ ở  Khi m t Connection đang m trong ch đ ớ ộ ắ ầ Implicit, SQL Server b t đ u m t Transaction m i ộ m t cách t đ ng sau khi Transaction hi n hành ấ t ho c Roll back. hoàn t ắ ầ ạ ầ c n Commit hay Rolback m i transaction.

ế ộ

ộ

ỗ

 Ch đ Implicit transaction phát sinh m t chu i

các Transaction liên t c.ụ

Implicit Transactions(Ngầm định)

ế ộ ộ ế ố

 Sau khi ch đ Transaction implicit đã đ ự ộ

ượ ậ c b t ắ

ự

ộ

đ ng b t ON cho m t k t n i, SQL Server t ấ ỳ ầ đ u m t transaction khi nó th c thi b t k các ệ l nh sau:

 UPDATE  DROP  OPEN  FETCH  TRUNCATE



 ALTER TABLE  REVOKE  CREATE  SELECT  DELETE INSERT

TABLE  GRANT

Autocommit Transaction

ể ự ộ • Autocommit Transaction: Mô hình chuy n giao t đ ng

ặ ị ủ ả là mô hình qu n lý transaction m c đ nh c a SQL Server.

ộ ệ ượ ế ể • M t l nh (statement) đ c chuy n giao (committed) n u

ẽ ả ự ệ ầ nó th c hi n thành công hay s tr ng ượ ạ c l i ban đ u

ặ ỗ ế (roll back) n u nó g p l i.

Autocommit Transaction

ệ ượ ề • L nh BEGIN TRANSACTION v t quy n mode t ự

ặ ị ể ộ đ ng chuy n giao (autocommit) m c đ nh.

ở • SQL Server tr v ề ạ l i mode autocommit khi

ườ ượ ể transaction t ng mình đã đ c chuy n giao (commit)

ả ượ ề ầ hay tr ng c v đ u (roll back) hay khi mode

ị ắ ầ ị transaction ng m đ nh b t t.

Distributed Transactions

ộ ạ ư

ả ượ ề ả

ủ ữ ả

ọ ủ  Là m t lo i Explicip Transaction nh ng giao tác c a nó ế ợ ự c k t h p liên quan nhi u Server. S qu n lý ph i đ ề gi a các nhà qu n lý tài nguyên c a các server và đi u này g i là transaction manager.

ữ ộ

 Các Transaction trong m t server là nh ng tham chi u t th c ra cũng

ự ề ộ ế ừ là m t Distributed

nhi u Database, Transaction.

 Transaction log: dùng đ ngăn ch n ng

ặ ệ

ư ừ ưở ữ ệ ả ể ng t ườ i dùng hi u các transaction ch a hoàn

ỉ ch nh d li u nh h t.ấ t

TRANSACTION LOG

 Dùng để theo vết tất cả các giao dịch  Phục hồi dữ liệu  Một transaction log gồm:

 Một record đánh dấu bắt đầu 1 transaction  Thông tin về transaction

 Thao tác (cập nhật, xóa, chèn)  Tên các object ảnh hưởng bởi transaction  Giá trị trước và sau của các field được cập nhật.  Con trỏ trỏ đến dòng trước và sau trong cùng 1 transaction

 Một record đánh dấu kết thúc transaction

TRANSACTION LOG (tt)

Công dụng của Transation

ồ

ặ

ụ

ệ

 Ph c h i các Transaction đ c bi

ệ

ả

ậ

ấ ỳ ệ

ượ ố

ấ

t: Khi môt ư Application đ a ra l nh ROLL BACK hay SQL ể ộ ỗ c dùng đ i, thì b ng ghi log đ nh n ra m t l ỉ Roll Back b t k hi u ch nh trong su t quá trình t. Transaction ch a hoàn t

ư ụ ồ ấ ả

ấ

 Ph c h i t

t khi

t c các Transaction ch a hoàn t ượ ắ ầ

SQL Server đ

c b t đ u.

ị ỗ

ể

ộ

ờ i đ n m t th i đi m b l

i Database l ả

ả ạ ả

ẫ

ạ ế  Hoàn tr l i ằ : Nh m đ m b o không phát sinh mâu thu n khi ự ố có s c .

Các lệnh không hợp lệ trong Transactions

 LOAD

TRANSACTION

 DUMP

 ALTER DATABASE  DROP DATABASE  RESTORE

TRANSACTION

DATABASE

 CREATE DATABASE  DISK INIT  LOAD DATABASE

 BACKUP LOG  RECONFIGURE  RESTORE LOG  UPDATE STATISTICS

Các ví dụ của Transation

declare @tranname varchar(20) select @tranname ='MyTran' Begin tran @tranname use Northwind delete from [Order Details] where

OrderID=10248 Commit tran @tranname select * from [Order Details] where OrderID=10248 Go

Quay lại trước những thay đổi

Các ví dụ của Transation

use Northwind Update Products

set UnitPrice =UnitPrice +10 where ProductName like 'A%'

if(select MAX(unitprice) from Products where ProductName like 'A

%')>100

Begin

RollBack tran Print 'Transaction rolled back'

End Else Begin

Commit Tran print 'Transaction committed'

End

Begin Tran

Tạo điểm dừng cho một Transation

Các ví dụ của Transation

ự ừ ể ớ

use Northwind Update Products

set UnitsInStock =UnitsInStock+20 where ProductName like 'A%'

Update [Order Details]

set Discount =Discount+0.25 where ProductID in (3,7)

SAVE TRAN tran1

Th c thi 1 transaction v i đi m d ng select * from [Order Details] where ProductID in(3,7) Begin Tran

Các ví dụ của Transation

Update [Order Details]

set UnitPrice =UnitPrice +10 where ProductID in (3,7)

Update [Order Details]

set Discount =Discount+0.5 where ProductID in (3,7)

if (Select discount from [Order Details] where ProductID In(3,7))<1 Begin

print 'Transaction 1 has been committed but transaction 2 has been 2 has not been committed' RollBack tran tran1

End Else Begin

print 'Both the transactions have been committed' Commit Tran

End

select * from [Order Details] where ProductID in(3,7)

Các ví dụ của Transation

ơ ế ả Dùng Transaction và c ch qu n lý l ỗ i

Begin try

begin tran

Update products

set UnitsInstock =100 where ProductID in (3,7) update [order details] set quantity =Quantity +100 where ProductID in(3,7) Commit tran

end try

Các ví dụ của Transation

begin catch

rollback tran raiserror ('Transaction Error',16,1) return

end catch

Hàm XACT_STATE

BEGIN TRY

BEGIN TRAN

DELETE from Products where ProductID =100 commit tran

END TRY BEGIN CATCH

IF (XACT_STATE())=1 BEGIN

print 'The transaction is in an uncommitable state' +'Rolling back transaction' Rollback tran

END

END CATCH

Hàm XACT_STATE

IF (XACT_STATE())=1 BEGIN

print 'The transation is committable' +'Committing transaction' COMMIT TRaN

END IF (XACT_STATE())=0 BEGIN

ờ ng th i (tt)

 Lost Updates TRANSACTION

COMPUTATION

T1: cộng 0.5 điểm T2: trừ 3 điểm

mark = mark + 0.5 mark = mark -3

Time

Transaction

step

Stored valued

Đọc mark

Mark = mark +0.5

Ghi mark

6.5

Đọc mark

6.5

Mark = mark - 3

Ghi mark

3.5

Đ ề

ể đồ

i u khi n

ờ ng th i (tt)

 Lost Updates

Time

Transaction

step

Stored valued

Đọc mark

Mark = mark + 0.5

Mark = mark – 3

Ghi mark

6.5

Ghi mark

3.0

Đ ề

ể đồ

i u khi n

ờ ng th i (tt)

Uncommited data: mối quan hệ chưa được chuyển

 Xảy ra khi giao tác thứ 2 chọn 1 hàng đang được

giao:

cập nhật bởi 1 giao tác khác. Giao tác thứ 2 đọc

dữ liệu lúc chưa được công nhận và có thể bị

thay đổi bởi giao tác đang thực hiện việc cập

nhật.

ể đồ

ờ ng th i (tt)

Đ ề i u khi n  Uncommited data

TRANSACTION

COMPUTATION

T1: cộng 0.5 điểm T2: trừ 3 điểm

mark = mark + 0.5 mark = mark -3

Time

Transaction

step

Stored valued

Đọc mark

Mark = mark +0.5

Ghi mark

6.5

Rollback

Đọc mark

6.0

Mark = mark - 3

Ghi mark

3.0

Implementing RDBMS concepts using SQL Server 2000/ Session 17/ 45 of 22

Đ ề

ể đồ

i u khi n

ờ ng th i (tt)

 Uncommited data

Time

Transaction

step

Stored valued

Đọc mark

Mark = mark +0.5

Ghi mark

6.5

Đọc mark

6.5

Mark = mark – 3

3.5

Rollback

ờ ng th i (tt)

 Inconsistent retrievals

T1 T2

SELECT sum(mark) From enroll WHERE SID = ‘142’

UPDATE enroll SET mark = mark +3 WHERE SID= ‘142’ AND CID = ‘C01’

UPDATE enroll SET mark = mark - 3 WHERE SID= ‘142’ AND CID = ‘C02’

Locks

ặ

ơ ấ

ộ

ữ ệ

ể ọ

ỉ ở ộ ế

ữ ệ

ệ

 Là c c u ngăn ch n các xung đ t do các user ệ không th đ c hay hi u ch nh các d li u mà ử các d li u này hi n đang m m t ti n trình x lý khác.

ạ

ể

ữ

ẫ ụ ự

ươ

ể

ế ướ

 Tuy nhiên, b n v n có th thao tác trên nh ng ộ ố ượ ể đ i t ng còn ph thu c vào chuy n tác mà ẽ ệ ố ệ user khác đang th c hi n. Khi đó h th ng s ớ ủ ạ ng thích v i ki m soát ti n trình c a b n có t quá trình tr

c đó hay không.

Locking Problems

ề

ả

ậ

• Lost Update (c p nh p m t d li u):

ộ ữ ệ

ậ

ậ ọ ị

ấ ữ ệ x y ra khi 2 hay nhi u transaction ch n cùng m t d li u và sau đó c p nh p dòng ự d a trên giá tr cũ.

ọ ữ ệ

• Uncommitted Dependency(Dirty Read _đ c d li u sai) :

ộ

ứ

ẵ

ộ

ậ ở

•

ọ

ấ

ở ỗ ầ

ọ

ữ ệ ộ ữ ệ m i l n đ c.

x y ả ọ ra khi Transaction th hai ch n m t dòng mà đang s n sàng ậ c p nh t b i m t transaction. Inconsistent Analysis (Nonrepeatable Real_đ c d li u hai ứ ả ầ x y ra khi transaction th 2 truy xu t cùng m t d li u l n) : ữ ệ ữ ầ ớ v i vài l n đ c lên nh ng d li u khác nhau ẫ

ộ

• Phantom Reads(đ c các m u tin ma): ọ ả X y ra khi hành đông ữ ệ ộ ượ c thi hành trên m t dòng d li u mà nó Insert hay delete đ ọ ủ ữ ệ thu c vùng d li u đ c c a m t transaction khác.

ế

ổ ữ ư ệ

ữ ệ ỉ c dùng cho nh ng thao tác hi u ch nh

ể ậ

ế ừ ế ậ ộ

ả ộ ể t l p m t Lock k th a. ồ c dùng khi thao tác thu c vào gi n đ

ượ ự

ẻ

ữ ả ộ

ự ụ ợ ề ồ

Các loại Locks Share locks : đ c dùng cho nh ng thao tác mà không làm ậ ữ ệ ậ thay đ i hay c p nh t d li u nh l nh Select. Exclusive locks : đ ượ ư ữ ệ d li u nh Insert, Update, Delete. Update locks : dùng trên nh ng tài nguyên mà có th c p ữ nh t.ậ Insert Locks : Dùng đ thi Scheme llocks : đ ủ c a Table là đang th c thi. Bulk Update locks : Cho phép chia s cho Bulkcopy thi hành. Deadlock : x y ra khi có s ph thu c chu trình gi a hai ộ ậ hay nhi u lu ng cho m t t p h p tài nguyên nào đó

Scheduler

 Khái niệm: là 1 chương trình DBMS thiết lập thứ tự các thao tác trong những transaction đồng thời

 Các phương pháp:

 Locking  Time Stamping  Optimistic

ươ

 Khả tuần tự: two phase locking  Deadlock: phát hiện và ngăn chặn

ươ

Ph

ng pháp Locking (tt)

 Locking two-phase

 Giao

thức two-phase

 Giai đoạn growing: transaction lấy được tất cả các khoá cần thiết nhưng không khóa dữ liệu. Tất cả các khóa được đặt vào locked point.

 Giai đoạn shrinking: transaction giải phóng tất cả

locking xác định cách transaction đạt được và giải phóng, đảm bảo được tính khả tuần tự nhưng không tránh được deadlock serializability

các khoá và không lấy thêm khóa mới nào

ươ

Ph

ng pháp Locking (tt)

 Locking two-phase

 Qui tắc cho giao thức Two-Phase Locking

 Không có 2 transaction nào có khóa đụng độ  Trong cùng 1 transaction không có thao tác

 Không có dữ liệu nào bị ảnh hưởng cho đến khi

không khóa nào đi trước thao tác có khóa.

tất cả các khóa lấy được.

ươ

Ph

ng pháp Locking (tt)

 Locking two-phase

ươ

Ph

ng pháp Locking (tt)

Nghẽn khóa-Deadlock  Là một hoàn cảnh mà trong đó 2 user (hay transaction) có các khóa trên các đối tượng khác nhau và mỗi user đang chờ khóa trên đối tượng của người dùng khác

 Deadlock còn được gọi là deadly embrace

ươ

Ph

ng pháp Locking (tt)

Nghẽn khóa-Deadlock  Khi bị nghẽn khóa, các chương trình ứng dụng không thể giải quyết bế tắc này mà DBMS phải phát hiện thấy và phải giải quyết gỡ bỏ nghẽn khóa.

 Deadlock: 3 kỹ thuật để điều khiển Deadlocks:

 Chặn Deadlock: Một transaction sẽ bị từ bỏ nếu yêu cầu lock mới và và lock mới này có khả năng gây nên dealock. Sau đó transaction sẽ được khởi động lại

 Phát hiện Deadlock: DBMS định kỳ kiểm tra deadlocks. Nếu có deadlock, một trong những transaction phải bị từ bỏ để transaction kia tiếp tục

 Tránh Deadlock: Transaction phải lấy được tất cả

các khóa nó cần trước khi thực thi

ơ

ế

ề

ấ

C ch khóa nhi u c p (Multigranular locking)

 Cho phép transaction khóa các loại tài nguyên khác

 Để giảm chi phí khóa, Db engine khóa tự động tài

nhau.

 Khóa ở mức nhỏ hơn như hàng, làm tăng khả năng đồng thời nhưng lại làm tăng chi phí vì cần nhiều khóa hơn khi có nhiều hàng cần khóa.

 Khóa ở mức lớn hơn, như mức bảng, thực thi đồng thời sẽ khó khăn hơn vì khi cả bảng được khóa sẽ hạn chế việc truy xuất đến các phần của bảng của các transaction khác. Nhưng chi phí sẽ thấp vì cần dùng ít khóa hơn.

nguyên tùy theo cấp độ của nhiệm vụ.

Lock granularity

 DB Engine thường thực hiện nhiều mức khóa

khác nhau để bảo vệ đầy đủ tài nguyên.

 Khóa ở nhiều mức khác nhau được gọi là lock

hierarchy.

 Ví dụ: để bảo vệ đầy đủ việc đọc 1 index, DB Engine có thể phải chiếm các khóa share trên các hàng, và khóa intent share trên các trang và bảng

ể ị

Tài nguyên có th b khóa

ự ươ

S t

ng thích khóa Lock combatibility

 Tương thích khóa dùng để kiểm soát nhiều transaction có chiếm các khóa trên cùng tài nguyên cùng lúc hay không?

 Nếu tài nguyên đã bị khóa bởi 1 transaction, một yêu cầu khóa mới có thể được cấp chỉ khi mode của khóa được yêu cầu tương thích với mode của khóa hiện có.

 Nếu không tương thích với khóa hiện có, transaction yêu cầu khóa mới sẽ đợi cho đến khi khóa hiện tại được giải phóng hay hết thời gian đợi.

ự ươ

S t

ng thích khóa Lock combatibility

 Nếu khóa shared(S) đang được dùng trên tài nguyên, các transaction khác có thể chiếm các khóa shared hay khóa update(U) ngay trên tài nguyên đó ngay cả khi transaction đầu chưa hoàn tất. Tuy nhiên các transaction không thể có được khóa exclusive cho đến khi khóa shared được giải phóng.

Ví dụ: Sự tương thích khóa Lock combatibility:  Không có mode khóa nào tương thích với khóa exclusive. Trong khi đang có khóa exclusive(X) thì không transaction nào có thể chiếm được bất kỳ loại khóa nào(shared, update hay exclusive) trên tài nguyên đó cho đến khi khóa exclusive được giải phóng.