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

Chương 8

GV Phi Loan - FIT - UIH

Nội dung

• Transaction là gì? • Tính chất của Transaction • Thực thi transaction tường minh • Bài toán đồng thời và cơ chế quản l{ khóa

– Các loại khóa – Cơ chế quản l{ khóa – Giải quyết tình huống nghẽn khóa

GV Phi Loan - FIT - UIH

Khái quát về Transaction

• A transaction is a sequence of operations performed as a single logical unit of work. • Nếu 1 transaction thành công, tất cả các chỉnh

sửa dữ liệu trong transaction đó sẽ được công nhận (commit). Nếu có lỗi khi thực thi transaction, tất cả mọi sửa đổi dữ liệu sẽ bị xóa và CSDL trở về lại tình trạng ban đầu trước khi thực thi transaction đó.

GV Phi Loan - FIT - UIH

Bốn thuộc tính cơ bản (ACID) của transaction • Tí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.

• Tính nhất quán (Consistency): một giao tác phải chuyển CSDL từ trạng thái nhất quán này sang trạng thái nhất quán khác.

• Tí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.

• Tính bền vững (Durability): những thay đổi củ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 có lỗi xảy ra sau đó.

GV Phi Loan - FIT - UIH

Phân loại Transaction

• Giao tác tường minh (explicit transaction): được khai

báo bằng lệnh BEGIN TRANSACTION

• Giao tác ngầm định (implicit transaction): giao tác

mới sẽ tự động bắt đầu ngay khi giao tác trước đó hoàn

tất, nhưng mỗi transaction được kết thúc tường minh bằng

lệnh COMMIT hay ROLLBACK

• Giao tác tự động chuyển giao (autocommit

transaction): mỗi một lệnh được xem như 1 transaction.

GV Phi Loan - FIT - UIH

Transaction trong SQL server

• Để hoàn thành các yêu cầu của 4 tính chất

ACID trên, SQL Server cung cấp các chức năng sau: – Quản lý Transaction (Transaction

management)

– Quản l{ Khoá (Locking Management) – Ghi nhật ký (Logging)

GV Phi Loan - FIT - UIH

QUẢN LÝ TRANSACTION

GV Phi Loan - FIT - UIH

Định nghĩa transaction tường minh

• BEGIN TRAN[SACTION] [transaction_name]  Dùng để đánh dấu việc bắt đầu của 1

transaction

• COMMIT [TRAN[SACTION]

[transaction_name]

Hay COMMIT WORK  Dùng để đánh dấu việc kết thúc của 1

transaction tường minh

GV Phi Loan - FIT - UIH

Ví dụ

GV Phi Loan - FIT - UIH

Làm thế nào để quay về lại trước những thay đổi

ROLLBACK [TRAN[SACTION] [transaction_name |savepoint_name ]

Dùng để quay ngược một transaction tường

minh hay ngầm định về lại điểm bắt đầu, hay về điểm dừng (save-point) bên trong 1 transaction

GV Phi Loan - FIT - UIH

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

Tạo điểm dừng cho 1 TRANSACTION

• Lệnh SAVE TRANSACTION dùng để đặt 1 điểm dừng (save point) bên trong 1 transaction. Điểm dừng chia transaction thành 1 các phần khác nhau sao cho transaction có thể quay về lại điểm dừng này nếu 1 phần của transaction bị loại bỏ có điều kiện.

• Cú pháp

SAVE TRAN[SACTION] {savepoint_name }

GV Phi Loan - FIT - UIH

Thực thi một transaction với điể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

Thực thi một transaction với điể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

Ví dụ: dùng transaction và cơ chế quản lý lỗi BEGIN TRY;

BEGIN TRANSACTION;

UPDATE Production.ProductInventory SET Quantity -= 100 WHERE ProductID = 527

AND LocationID = 6 -- misc storage AND Shelf = ‘B’ AND Bin = 4;

UPDATE Production.ProductInventory SET Quantity += 100 WHERE ProductID = 527 AND LocationID = 50 AND Shelf = ‘F’ AND Bin = 11;

COMMIT TRANSACTION;

GV Phi Loan - FIT - UIH

Ví dụ: dùng transaction và cơ chế quản lý lỗi END TRY BEGIN CATCH;

ROLLBACK TRANSACTION; RAISERROR(’Inventory Transaction Error’, 16, 1); RETURN;

END CATCH;

GV Phi Loan - FIT - UIH

Hàm XACT_STATE()

• Chỉ ra yêu cầu đang chạy hiện thời có transaction

người dùng nào đang hoạt động không và transaction đó có thể được commit hay không?

1 Yêu cầu hiện thời có 1 transaction người dùng đang hoạt động có thể commit được Không có transaction nào đang hoạt động

0 -1 Yêu cầu hiện thời có 1 transaction người dùng đang hoạt

động nhưng có lỗi nên transaction được xem là uncommittable, không thể commit hay rollback về điểm dừng . Yêu cầu không thể “write” được chon đến khi transaction được rollback hoàn toàn.

GV Phi Loan - FIT - UIH

Ví dụ hà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

state.‘ + ' Rolling back transaction.'

ROLLBACK TRANSACTION;

END;

GV Phi Loan - FIT - UIH

Ví dụ hàm Xact_State()

IF (XACT_STATE()) = 1

BEGIN

PRINT 'The transaction is committable.' +

‘ Committing transaction.'

COMMIT TRANSACTION;

END;

END CATCH; GO

GV Phi Loan - FIT - UIH

Các lệnh không hợp lệ trong transaction

• Rollback phải có khả năng “undo”, vì vậy các

lệnh sau không được dùng: – 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

QUẢN LÝ KHÓA

SQL Server uses a dynamic, cooperative locking mechanism to protect the consistency of the data in the database.

GV Phi Loan - FIT - UIH

Nội dung

• Các mode khóa – Khóa Intent

• Tương thích giữa các khóa

GV Phi Loan - FIT - UIH

Transaction và cơ chế khóa

• Trước khi transaction đọc hay chỉnh sửa dữ liệu, nó cần được bảo vệ tránh ảnh hưởng của các transaction khác đang chỉnh sửa cùng dữ liệu.

• Transaction yêu cầu khóa trên dữ liệu đang

dùng.

• Có nhiều mode khóa khác nhau phụ thuộc vào mức độ phụ thuộc dữ liệu của transaction.

GV Phi Loan - FIT - UIH

Transaction và cơ chế khóa

• Nếu transaction yêu cầu 1 mode khóa xung đột Database Engine sẽ bắt transaction này dừng (pause) cho đến khi khóa trước đó được giải phóng.

• Tất cả các khóa sẽ được giải phóng khi

transaction hoàn thành (bằng commit hay roll back)

GV Phi Loan - FIT - UIH

Lock Granularity and Hierarchies

• Multigranular locking : cho phép khóa các tài nguyên trong 1 transaction ở các mức khác nhau. – Mục đích: giảm thiểu chi phí khóa.

• Khóa ở mức nhỏ hơn như mức row sẽ làm tăng tính đồng thời nhưng tăng chi phí vì cần nhiều khóa nếu có nhiều hàng cần khóa.

• Khóa ở mức cao hơn như mức table, sẽ hạn chế truy xuất đến các phần khác của bảng mà transaction hiện hành không dùng đến, nhưng giảm được chi phí.

GV Phi Loan - FIT - UIH

Các mode khóa

GV Phi Loan - FIT - UIH

Intent locks

• SQL Server không chỉ khóa theo yêu cầu

mà còn thông báo intent của khóa ở mức cao hơn.

• Xét 2 transaction: T1 và T2, mỗi

transaction sẽ modify các hàng khác nhau. Để modify 1 hàng, SQL Server cần phải thực hiện khóa exclusive trên hàng có { định modify.

GV Phi Loan - FIT - UIH

Intent locks

• Nếu SQL Server chỉ thực hiện 2 khóa

exclusive cho T1 trên R1 và T2 trên R2, SQL Server sẽ phải khảo sát cả cấu trúc cây để xác định hàng nào bị khóa? Để giảm chi phí SQL Server sẽ tuyến bố intent của nó lên mức cao hơn của cây bằng cách đặt khóa intent các hàng phía trên và ngược về gốc.

GV Phi Loan - FIT - UIH

Intent locks

• Bằng cách khảo sát khóa intent, SQL

Server có thể nhanh chóng xác định là hiện có 1 intent-exclusive lock trên bảng nhưng các hàng T1 nằm ở phần khác của cây, nên SQL Server có thể vẫn có được khóa mà T2 yêu cầu.

GV Phi Loan - FIT - UIH

Sự tương thích khóa Lock compatibility • Tương thích khóa dùng để kiểm soát các transaction có chiếm các khóa trên cùng tài nguyên cùng lúc hay không?

• Nếu tài nguyên đã bị khóa bởi 1 transaction, một yêu cầu khóa mới được cấp chỉ khi mode của khóa được yêu cầu tương thích với mode của khóa hiện có. – Nếu không tương thích với khóa hiện có,

transaction yêu cầu khóa mới sẽ đợi cho đến khi khóa hiện tại được giải phóng hay hết thời gian đợi.

GV Phi Loan - FIT - UIH

Sự tương thích khóa Lock compatibility

• Không có mode khoá nào tương thích với

khóa exclusive (X). Trong khi đang có khóa X thì không transaction nào có thể chiếm được bất kz loại khóa nào (trên tài nguyên đó cho đến khi khóa X được giải phóng.

GV Phi Loan - FIT - UIH

Sự tương thích khóa Lock compatibility

• Nếu khóa shared (S) đang được dùng trên 1

tài nguyên, các transaction khác có thể chiếm các khóa shared hay khóa update (U) ngay trên tài nguyên đó ngay cả khi transaction đầu chưa hoàn tất. Tuy nhiên các transaction không thể có được khóa exclusive cho đến khi khóa shared được giải phóng

GV Phi Loan - FIT - UIH

Lock Compatibility Matrix

GV Phi Loan - FIT - UIH

Quiz

• Nếu transaction T1 hiện đang giữ khóa

update (U) trên 1 hàng. Transaction T2 yêu cầu cấp khóa shared ngay trên hàng đó. T2 có được cấp khóa không???

• Tại sao khóa IX hiện hành có thể tích hợp

được với 1 khóa IX khác?? – Because the intent locks are never held at the actual resource level ; they are at least one level above in the tree.

GV Phi Loan - FIT - UIH

Concurrency Effects

Các bài toán đồng thời

GV Phi Loan - FIT - UIH

Tính cô lập và bài toán đồng thời Isolation and Concurrency Problems

• Khi người dùng truy xuất tài nguyên cùng lúc thì được gọi là truy xuất tài nguyên đồng thời (resource concurrently).

• Do dữ liệu bị truy xuất đồng thời nên cần có

cơ chế bảo vệ khi nhiều người dùng cùng sửa đổi tài nguyên.

GV Phi Loan - FIT - UIH

Dynamic Lock Management

• SQL Server sử dụng cơ chế quản l{ khóa động để tăng tính đồng thời trong CSDL. – Nếu transaction chỉnh sửa 1 hàng trong bảng, SQL Server sẽ cấp 1 lock hàng (row lock).

– Nếu bảng có nhiều khóa hàng, SQL server sẽ

cấp khóa bảng hay khóa trang thay thế.

GV Phi Loan - FIT - UIH

Các mức cô lập transaction (transaction isolation level) • Nên chọn mức cô lập cho transaction không làm ảnh hưởng đến các khóa đang có để tránh dữ liệu bị sửa đổi.

• Một transaction luôn nhận khóa độc quyền trên bất kz dữ liệu nào mà nó sửa đổi và sẽ giữ khóa cho đến khi transaction hoàn tất.

• Mức cô lập càng thấp thì người dùng càng có nhiều khả năng truy xuất dữ liệu đồng thời, nhưng cũng gây ra nhiều ảnh hưởng tương tranh và ngược lại..

GV Phi Loan - FIT - UIH

Các mức cô lập

• Để chọn mức cô lập thích hợp thì phải cân đối giữa yêu cầu bảo toàn dữ liệu của ứng dụng với chi phí của mỗi mức cô lập. • Mức cô lập cao nhất là tuần tự hóa

(serializable)

• Mức cô lập thấp nhất là cho phép đọc dữ liệu

chưa được commit (read uncommitted).

GV Phi Loan - FIT - UIH

4 1

Bốn mức cô lập theo chuẩn ISO

1. Read uncommitted: mức thấp nhất, transaction bị cô lập chỉ đủ để bảo đảm các dữ liệu bị lỗi vật lý không được đọc mà thôi

2. Read committed: mức mặc định của Database

Engine

3. Repeatable read 4. Serializable: mức cao nhất, các transaction hoàn

toàn bị cô lập khỏi các transaction khác

GV Phi Loan - FIT - UIH

Các mức cô lập của SQL server DB Engine

1. READ UNCOMMITTED: các lệnh có thể đọc các hàng bị chỉnh sửa bởi các transaction khác dù chưa được commit

2. READ COMMITTED: các lệnh không thể đọc dữ liệu

đã bị sửa đổi nhưng chưa commit bởi các transaction khác.

3. REPEATABLE READ: các lệnh không thể đọc dữ liệu đã bị sửa đổi bởi các transaction khác và không có transaction nào có thể sửa đổi dữ liệu đã được đọc bởi transaction hiện hành cho đến khi transaction hiện hành hoàn tất.

GV Phi Loan - FIT - UIH

Các mức cô lập của SQL server DB Engine

4. SNAPSHOT: dữ liệu được đọc bởi bất kz lệnh nào trong 1 transaction thì sẽ đuợc giữ giống như lúc bắt đầu transaction.

5. SERIALIZABLE

GV Phi Loan - FIT - UIH

Các mức cô lập và ảnh hưởng của tính đồng thời

GV Phi Loan - FIT - UIH

Nonstandard Isolation Level: snapshot

• Có thể cải tiến tính đồng thời và có đặc tính tương tự như mức serializable. – Dữ liệu được đọc bởi 1 lệnh trong

transaction sẽ ở trạng thái giống như lúc bắt đầu transaction.

– The statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction

GV Phi Loan - FIT - UIH

Nonstandard Isolation Level

• Để sử dụng mức isolation này, cần phải

thay đổi tùy chọn của database để có thể dùng thêm tài nguyên trong tempdb. • Khi bắt đầu transaction, phải xác định

snapshot isolation level.

GV Phi Loan - FIT - UIH

Ví dụ

USE [AdventureWorks2008] GO ALTER DATABASE AdventureWorks2008

SET ALLOW_SNAPSHOT_ISOLATION ON

GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; GO BEGIN TRANSACTION

SELECT * FROM HumanResources.EmployeePayHistory; SELECT * FROM HumanResources.Department;

GO COMMIT TRANSACTION; GO

GV Phi Loan - FIT - UIH

Lock Duration by Transaction Isolation Level

GV Phi Loan - FIT - UIH

Cách sử dụng khóa

• Mặc định của transaction isolation là read

committed, có nghĩa là SQL Server bảo đảm là chỉ có dữ liệu nào đã commit thì mới được đọc. Trong khi 1 hàng đang được cập nhật, dữ liệu chưa được commit, SQL Server sẽ buộc các transactions muốn đọc dữ liệu phải đợi, cho đến khi dữ liệu được commit.

GV Phi Loan - FIT - UIH

Ví dụ về sử dụng khoá

• User1 đang thực hiện các lệnh sau để cập nhật điểm

và ngày thi cho ứng viên có mã là ‘000002’ trong bảng ExternalCandidate. BEGIN TRANSACTION

UPDATE ExternalCandidate SET siTestScore = 90 WHERE cCandidateCode='000002' UPDATE ExternalCandidate SET dTestDate = getdate() WHERE cCandidateCode = '000002'

GV Phi Loan - FIT - UIH

Ví dụ về sử dụng khoá

• Trong khi transaction trên đang thực hiện, User2 muốn lập lịch phỏng vấn cho các ứng viên, nhưng không thể xem chi tiết của các ứng viên có điểm thi trên 80. User2 đang sử dụng các lệnh sau : BEGIN TRANSACTION

SELECT * from ExternalCandidate WHERE siTestScore > 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore > 80

Hãy xác định tại sao user2 không thể thực thi transaction

GV Phi Loan - FIT - UIH

Ví dụ về sử dụng khoá

• Các bảng sẽ bị khoá khi transaction trên máy 1

đang thực hiện.

• Khi transaction trên máy 1 kết thúc bằng cách

dùng lệnh sau: COMMIT TRANSACTION

Thì transaction trên máy 2 mới được thực hiện.

GV Phi Loan - FIT - UIH

Lệnh DBCC USEROPTIONS statement

• Để xác định mức cô lập hiện hành, dùng lệnh DBCC

USEROPTIONS

USE AdventureWorks; GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; GO DBCC USEROPTIONS; GO

GV Phi Loan - FIT - UIH

Phạm vi của mức cô lập

• Khi mức cô lập được xác định, khóa dùng tất cả lệnh DML trong phiên làm việc đó sẽ theo mức co lập này.

• Mức cô lập này duy trì cho đến khi phiên làm việc kết thúc hay mức cô lập được cài đặt mức mới.

GV Phi Loan - FIT - UIH

Lệnh thay đổi mức cô lập

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE

}

[ ; ]

GV Phi Loan - FIT - UIH

5 6

Ví dụ

SET TRANSACTION ISOLATION LEVEL

REPEATABLE READ

GO BEGIN TRANSACTION SELECT * FROM publishers SELECT * FROM authors ... COMMIT TRANSACTION

GV Phi Loan - FIT - UIH

Nghẽn khóa - Deadlock

• A deadlock occurs when two or more tasks

permanently block each other because each task has a lock on a resource that the other task(s) are trying to lock

• Deadlock còn được gọi là deadly embrace

GV Phi Loan - FIT - UIH

Deadlocks

• Khi bị nghẽn khóa, các CTUD không thể giải

quyết bế tắc này mà DBMS phải phát hiện và giải quyết nghẽn khóa. – Chỉ có 1 cách là hủy bỏ một hay nhiều giao tác để

giải quyết bế tắc.

– Người dùng không nhận thấy được sự xuất hiện của tình trạng nghẽn khóa, DBMS phải tự động giải quyết tình trạng nghẽn khóa

GV Phi Loan - FIT - UIH

Phân loại deadlock

• Conversion • Writer-writer • Reader-writer (thường xuyên nhất) • Cascading.

GV Phi Loan - FIT - UIH

Phân loại deadlock

• Conversion deadlocks xảy ra khi cả 2 kết nối đều đang giữ khóa shared chung trên 1 tài nguyên và cả hai đang cố chuyển thành khóa exclusive lock cho riêng mình.

• Writer-writer deadlocks do tài nguyên yêu cầu để bảo đảm tất cả mã truy xuất tài nguyên phải theo đúng thứ tự

• Reader-writer deadlocks xảy ra khi 2 kết nối

đêu giữ khóa exclusive locks trên các tài nguyên khác nhau và kết nối này yêu cầu khóa shared trên tài nguyên của kết nối kia

GV Phi Loan - FIT - UIH

Phân loại deadlock

• Cascading deadlocks xảy ra khi có nhiều hơn 2 kết nối liên quan đến deadlock. – Ngay cả khi SQL Server chọn 1 victim cho deadlock, các deadlock khác vẫn đang tồn tại. SQL Server lại tiếp tục chọn victim khác cho đến khi tất cả deadlock đều được giải quyết.

GV Phi Loan - FIT - UIH

Cách giải quyết deadlock

• SQL Server dành riêng 1 thread chỉ đề dò tìm

deadlock.

• Mặc định thread này hoạt động sau mỗi 5 second. Nếu phát hiện có deadlock, thread hoạt động thường xuyên hơn, trường hợp nếu có nhiều deadlock, thread sẽ hoạt động sau mỗi 100 milliseconds.

GV Phi Loan - FIT - UIH