Giao Tác 1 Nội dung 1 Giới thiệu 2 Giao tác 3 Điều khiển đồng thời 2 www.com Giới thiệu Ví dụ 1: TaiKhoan (MaTK, HoTen, SoDu) Xét store procedure spRutTien ◦ Bước 1: Đọc số dư tài khoản ◦ Bước 2: Kiểm tra số dư tài khoản ◦ Bước 3: Nếu đủ tiền ◦ Bước 3.1: Cập nhật tài khoản với số dư mới ◦ Bước 3.2: Trả tiền ra máy ATM ◦ Bước 4: Nếu không đủ tiền thì kết thúc 3 www.com Giới thiệu Ví dụ 1: Nhận xét ◦ Nếu bước 3.1 thực hiện được và 3.2 bị lỗi è cập nhật mà không trả tiền ◦ Nếu bước 3.2 thực hiện được mà 3.1 bị lỗi è trả tiền mà không cập nhật tài khoản Ø Dữ liệu bị sai Mong muốn ◦ Bước 3.2 phải được thực hiện hết hoặc không thực hiện được bước nào.com Giới thiệu Ví dụ 2: Xét store procedure spChuyenTien ◦ Tham số @tk1, @tk2, @sotien ◦ Bước 1: Đọc số dư của @tk1 è @sodu1 ◦ Bước 2: Cập nhật số dư của tài khoản 1 UPDATE TaiKhoan SET SoDu = @sodu1 - @sotien ◦ Bước 3: Đọc số dư của tài khoản 2 UPDATE TaiKhoan SET SoDu = @sodu2 + @sotien ◦ Bước 4: Thông báo thành công 5 www.com Giới thiệu Ví dụ 2: Nhận xét ◦ Nếu bước 2 thành công, bước 3 bị lỗi è SoDu của tài khỏan @tk1 bị trừ nhưng SoDu của @tk2 không được tăng lên. Ø Bị lỗi Mong muốn ◦ Bước 2 và bước 3 phải được thực hiện hết hoặc không có bước nào được thực hiện.com Giới thiệu Ví dụ 3: DocGia(MaDG, HoTen, NgaySinh) NgLon(MaDG, SoNha, Dng, Phng, Quan) • Xét store procedure: spThemDGNguoiLon ◦ Bước 1: Xác định mã đọc giả ◦ Bước 2: Insert vào bảng DocGia ◦ Bước 3: Kiểm tra tuổi của đọc giả ◦ Bước 4: Nếu không đủ tuổi thì thông báo lỗi và kết thúc ◦ Bước 5: Ngược lại thì Insert vào bảng NgLon 7 www.com Giới thiệu Ví dụ 3: Nhận xét: ◦ Giả sử gọi spThemNguoiLon để thêm một đọc giả mà nhỏ hơn 18 tuổi Giải quyết à Có 1 bộ thêm vào bảng DocGia ??? à Có 0 bộ thêm vào bảng NgLon Ø Dữ liệu bị saiKhai báo các bước muốn có đặc Mong muốn: điểm như trên vào trong 1 giao tác ◦ Bước 2 và bước 5 phải được thực hiện hết, hoặc không thực hiện bước nào hết.com Nội dung 1 Giới thiệu 2 Giao tác 3 Điều khiển đồng thời 9 www.com Giao tác • Giao tác là một tập các lệnh có truy xuất đến CSDL • Giao tác phải đảm bảo tính ACID Giao tác 10 www.com Tính ACID Mọi thao tác phải Tất cả dữ liệu phải ở được thực hiện, trạng thái nhất quán sau hoặc đều bị hủy khi giao tác kết thúc Atomicity Consistency (nguyên tố) (nhất quán) Durability Isolation (bền vững) (cô lập) Khi giao tác thực hiện Một giao tác thực hiện thành công thì mọi thay đồng thời với giao tác đổi của nó phải được lưu khác thì không bị ảnh bền vững trong CSDL hưởng bởi giao tác khác 11 www.com Khai báo giao tác • Một số từ khóa: ◦ Begin tran: Bắt đầu giao tác ◦ Commit : Kết thúc giao tác (thành công) q Dữ liệu sẽ được xác nhận trên CSDL ◦ Rollback: Kết thúc giao tác (thất bại) q Dữ liệu sẽ được khôi phục về trạng thái ban đầu • Khi nào thì rollback ? ◦ Khi có một lệnh nào đó trong giao tác bị lỗi và cần phải khôi phục lại dữ liệu.com Xác định lỗi • Lỗi do hệ thống: Lỗi do những câu lệnh INSERT, UPDATE, DELETE ◦ Dựa vào biến @@error [0: thành công, != 0: mã lỗi] IF @@error != 0 BEGIN -- Các câu lệnh xử lý khi bị lỗi END ◦ Lưu ý: Sau mỗi câu lệnh Select, Insert, Update, Delete thì biến @@error chứa trạng thái (thành công/ thất bại) của việc thực thi câu lệnh.com Xác định lỗi • Lỗi do người dùng: ◦ Đọc giả thêm vào nhỏ hơn 18 tuổi ◦ Xác định lỗi dựa vào đoạn code do người dùng viết. ◦ Ví dụ: IF @tuoi < 18 BEGIN -- Các câu lệnh xử lý khi bị lỗi END 14 www.com Cú pháp khai báo Bước 1: Bổ sung từ khóa khai báo bắt đầu (begin tran), kết thúc giao tác (commit). Bước 2: Sau mỗi câu lệnh Select, Insert, Update, Delete è Kiểm tra lỗi hệ thống è Nếu có lỗi thì có những xử lý phù hợp.
IF @@error != 0 BEGIN raiserror (‘Lỗi rồi !!!’, 16, 1) rollback –- một cách hợp lý END 15 www.com Cú pháp khai báo • Bước 3: Sau mỗi lần kiểm tra lỗi của người dùng è Nếu có lỗi thì có những xử lý phù hợp. IF [điều kiện gây ra lỗi thỏa mãn] BEGIN raiserror ('Lỗi rồi !!!' 16, 1) rollback –- một cách hợp lý END 16 www.com CSDL Quản lý thư viện • DocGia (ma_DocGia, ho, tenlot, ten, ngaysinh) • Nguoilon (ma_DocGia, sonha, duong, quan, dienthoai, han_sd) • TreEm (ma_DocGia, ma_DocGia_nguoilon) • Tuasach (ma_tuasach, tuasach, tacgia, tomtat) • Dausach (isbn, ma_tuasach, ngonngu, bia, trangthai) • Cuonsach (isbn, ma_cuonsach, tinhtrang) • DangKy (isbn, ma_DocGia, ngay_dk, ghichu) • Muon (isbn, ma_cuonsach, ma_DocGia, ngay_muon, ngay_hethan) • QuaTrinhMuon(isbn, ma_cuonsach, ngay_muon, ma_DocGia, ngay_hethan, ngay_tra, tien_muon, tien_datra, tien_datcoc, ghichu) 17 www.com CSDL Quản lý thư viện 18 www.com Nội dung 1 Giới thiệu 2 Giao tác 3 Điều khiển đồng thời 19 www.com Bối cảnh Website bàn hàng 20 www.com Bối cảnh Database trước giao tác Đọc DS SP Đọc DS SP Thêm SP vào giỏ Thêm SP vào giỏ Thêm PMH Thêm PMH Thêm CT PMH Thêm CT PMH Cập nhật số SP Cập nhật số SP Database sau giao tác 21 www.com Bối cảnh vMô hình ứng dụng đa người dùng Một CSDL lưu tại Server và nhiều clients đồng thời truy cập và thao tác trên cùng CSDL vGiao tác Ti của client X đang thao tác trên CSDL, trong khi đó một giao tác Tj của client Y cũng đang thực hiện truy xuất và thay đổi CSDL. ðCác Ti và Tj có thể xung đột, tranh chấp lẫn nhau.com Giao tác lồng nhau STT T1 T2 1 Begin tran 2 Begin tran 3 read (A) 4 write (A) 5 Commit tran …. n read (A) n+1 Commit tran 23 www.com Giao tác lồng nhau v Tham số @@trancount cho biết số transaction đang thực thi.
v Khi khai báo transaction tường minh, phải rollback hoặc commit tường minh để: § Giải phóng tài nguyên transaction đang chiếm giữ. §Tránh cản trở việc thực hiện của các transaction khác.com Vấn đề truy xuất đồng thời Dirty read Đọc dữ liệu rác Unrepeatable read Không thể đọc lại dữ liệu Phantom Bóng ma Lost update Mất dữ liệu đã cập nhật 25 www.com Dirty read STT T1 T2 1 Begin tran 2 Begin tran 3 write (A) //Insert | Update 4 read (A) 5 If (Lỗi) 6 Rollback tran 7 Commit tran 8 Commit tran ð T2 đọc dữ liệu (rác) đã bị T1 hủy.com Unrepeatable read STT T1 T2 1 Begin tran 2 Begin tran 3 Read(A) 4 Write(A) //Update | Delete 5 Read(A) 6 Commit tran 7 Commit tran ð T1 đọc dữ liệu giá trị A khác nhau ở 2 lần đọc.com Phantom STT T1 T2 1 Begin tran 2 Begin tran 3 Read(A) 4 write(B) //Insert | Update 5 Read(A, B) 6 Commit tran 7 Commit tran ð T1 đọc tập dữ liệu 2 lần khác nhau 28 www.com Lost update STT T1 T2 1 Begin tran 2 Begin tran 3 Read(A) 4 Read(A) 5 Write(A’) 6 Write(A’’) 7 Commit tran 8 Commit tran ð Dữ liệu được ghi bởi T2 đã bị ghi đè bởi T1 29 www.com Lỗi giao tác v Không có quyền truy cập trên đối tượng (table, stored procedure,…) v Deadlock.com Lỗi giao tác v 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 vGiao 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.com Lỗi giao tác Create proc sp_ThemDG rollback tran @ Ten… return as end --buoc 1 : xác định mã độc giả -- buoc 2 : insert vao bang docgia declare @madg insert into DocGia values(…) set @madg = 1 if ( @@error <>0 ) begin transaction begin while exists (select * from rollback tran DocGia where ma_docgia = return @madg) end set @madg = @madg +1 … if ( @@error <>0 ) commit transaction begin www.com 32 Bài tập STT T1 T2 1 Begin tran 2 Begin tran 3 SELECT * FROM SACH 4 INSERT SACH (MaSach) Values (‘S003’) 5 SET @Msach = (SELECT Top 1 MaSach FROM SACH ORDER BY MaSach DESC) 6 Rollback 7 … 8 Commit 33 www.com Bài tập 2 STT T1 T2 1 Begin tran 2 Begin tran 3 SELECT * FROM SACH 4 INSERT SACH (MaSach) Values (‘S003’) 5 SET @Msach = (SELECT count (*) FROM SACH ) 6 Commit 7 … 8 Commit 34 www.com Bài tập 3 STT T1 T2 1 Begin tran 2 Begin tran 3 SELECT * FROM SACH 4 UPDATE SACH Set TenSach = ‘ABC’ WHERE MaSach = ‘S003’ 5 SELECT * FROM SACH 6 Commit 7 … 8 Commit 35 www.com Bài tập 4 STT T1 T2 1 Begin tran 2 Begin tran 3 SELECT * FROM PHIEUDATHANG 4 SELECT * FROM PHIEUDATHANG 5 INSERT PHIEUDATHANG (MaPD, MaKH) VALUES (‘PD004’,’KH003’) 6 INSERT PHIEUDATHANG (MaPD, MaKH) VALUES (‘PD004’,’KH002’) 7 Commit 8 Commit 36 www.com Bài tập 5 STT T1 T2 1 Begin tran 2 Begin tran 3 SELECT * FROM PHIEUDATHANG WHERE MaKH = ‘KH002’ 4 INSERT PHIEUDATHANG (MaPD, MaKH) VALUES (‘PD004’,’KH002’) 5 SELECT * FROM PHIEUDATHANG 6 INSERT PHIEUDATHANG (MaPD, MaKH) VALUES (‘PD005’,’KH002’) 7 Commit 8 Commit 37 www.com Bài tập 6 STT T1 T2 1 Begin tran 2 Begin tran 3 INSERT PHIEUDATHANG (MaPD, MaKH) VALUES (‘PD005’,’KH002’) SELECT * FROM PHIEUDATHANG WHERE MaKH = ‘KH002’ 4 SELECT * FROM PHIEUDATHANG WHERE MaKH = ‘KH002’ 5 UPDATE PHIEUDATHANG SET MaKH = ‘KH003’ WHERE MaPD = ‘PD005’ 6 SELECT * FROM PHIEUDATHANG WHERE MaKH = ‘KH002’ 7 Commit 38 8 Commit www.com Xử lý tranh chấp v Cơ chế xử lí tranh chấp đồng thời của SQL Server § Kỹ thuật khóa : các giao tác muốn đọc/ghi trên các đơn vị dữ liệu phải phát ra yêu cầu xin khóa trên đơn vị dữ liệu đó.
§ Mức cô lập : là các thiết lập trong giao tác quy định việc xin khóa/giữ khóa của những thao tác đọc/ghi lên đơn vị dữ liệu. § Khóa trực tiếp trong từng câu lệnh 39 www.com Kỹ thuật khóa vKhông đặt khóa (Nolock) vKhóa chia sẻ (shared lock) : § Còn gọi là khóa đọc (read lock). Gọi tắt : Khóa S § Khi đọc một đơn vị dự liệu SQL tự thiết lập shared lock trên đơn vị dữ liệu đó. § Shared lock có thể được thiết lập trên 1 trang, 1 bảng, hay một dòng dữ liệu.com Kỹ thuật khóa vKhóa cập nhật (update lock) § Còn gọi là Intend to write lock.