DB2 9.7: Các giao dịch tự quản

DB2 9.7: Các giao dịch tự quản Tìm hiểu một chút nền tảng về các giao dịch Các giao dịch là các thực thể thế giới thực có thể được biểu diễn dưới dạng văn bản chữ, số hoặc cả hai, để xử lý một hệ thống quản lý cơ sở dữ liệu. Chúng có ý nghĩa như là các hành động dựa vào cơ sở dữ liệu và chúng phải được thực hiện như một nhóm. Ví dụ, một yêu cầu chuyển số tiền X từ tài khoản của người dùng A đến tài khoản của người dùng B là một giao dịch đơn giản. Có thể chia giao dịch này thành hai câu lệnh SQL, như trong Liệt kê 1. Liệt kê 1. Ví dụ về giao dịch đơn giản Update table AccountInfo set CurrentBalance = CurrentBalance - X, where UserName=A Update table AccountInfo set CurrentBalance = CurrentBalance + X, where UserName=B Giao dịch này chỉ có thể được coi là thành công nếu cả hai câu lệnh SQL có thể cập nhật bảng này thành công. Để chắc chắn rằng cả hai câu lệnh có hiệu lực hoặc chẳng có câu lệnh nào có hiệu lực, hãy chạy các ứng dụng theo cách sao cho không có thay đổi cơ sở dữ liệu nào được thực hiện cho đến khi một câu lệnh COMMIT (Cam kết) xảy ra. Khi gặp câu lệnh COMMIT, tất cả các câu lệnh còn chưa được cam kết (các câu lệnh sau COMMIT cuối cùng) đều có hiệu lực cùng một lúc, đảm bảo tính toàn vẹn dữ liệu. Điều này tương tự như việc TẮT (OFF) hành vi AUTO COMMIT của bộ xử lý dòng lệnh (CLP), rồi phát ra một tập các câu lệnh và sau đó tự tay đưa ra một COMMIT khi đã thực hiện xong. Một câu lệnh ROLLBACK (Hủy giao dịch) hủy bỏ tất cả thay đổi chưa được cam kết. Vì vậy, các câu lệnh COMMIT và ROLLBACK là các khối xây dựng quan trọng để triển khai thực hiện giao dịch. Về đầu trang Giới thiệu một giao dịch tự quản Một giao dịch tự quản có phạm vi COMMIT và ROLLBACK riêng của mình để đảm bảo rằng kết quả của nó không ảnh hưởng đến những thay đổi chưa được cam kết của người gọi. Ngoài ra, các COMMIT và các ROLLBACK trong phiên đang gọi không ảnh hưởng đến các thay đổi đã được hoàn thành vào lúc kết thúc của chính giao dịch tự quản này. Lưu ý rằng phiên đang gọi bị treo cho đến khi phiên được gọi trả về quyền điều khiển. Hỗ trợ cho các giao dịch tự quản hoàn toàn không nên được xem như là sự hỗ trợ để có các phiên thực hiện song song. Về đầu trang Tạo ra một giao dịch tự quản Trong DB2, các giao dịch tự quản được triển khai thực hiện thông qua các thủ tục tự quản. Một thủ tục lưu sẵn cung cấp một phương tiện tự nhiên để bó các câu lệnh lại thành một khối. Để tạo một thủ tục tự quản, từ khoá AUTONOMOUS (Tự quản) được chỉ rõ trên câu lệnh CREATE PROCEDURE (Tạo thủ tục), như trong Liệt kê 2. Liệt kê 2. Ví dụ về câu lệnh CREATE PROCEDURE CREATE OR REPLACE your_procedure_name LANGUAGE SQL AUTONOMOUS BEGIN do autonomous work ; END Khi một thủ tục tự quản được gọi, nó được thực hiện bên trong phiên riêng của mình để tạo ra tính độc lập cần thiết của giao dịch. Các thủ tục tự quản thành công cam kết hoàn toàn, trong khi các thủ tục tự quản không thành công lại hủy giao dịch. Trong cả hai trường hợp, các giao dịch đang gọi không bị động chạm đến. Về đầu trang Xem một ví dụ sử dụng thực tế Ngân hàng B muốn bảo đảm mỗi truy vấn đến một bảng chứa dữ liệu khách hàng nhạy cảm được ghi nhật ký đúng. Để đạt được mục tiêu này, người ta giao cho các nhà phát triển ứng dụng tại Ngân hàng B một tập các giao diện có thể dùng để truy cập dữ liệu nhạy cảm. Mỗi giao diện được triển khai thực hiện như một thủ tục đã lưu sẵn. Các thủ tục đã lưu trả về thông tin cần thiết từ bảng này, trong cùng lúc đó ghi lại mã định danh (ID) người dùng của nhân viên đang thực hiện truy vấn và số tài khoản của bản ghi khách hàng đang được truy vấn kèm theo ngày tháng và thời gian. Các giả định SQL giả định tất cả như sau:  Đã có một kết nối cơ sở dữ liệu.  Tắt cam kết tự động.  Ký tự kết thúc câu lệnh được thiết lập là %, bằng cách nhập vào câu lệnh CLP của DB2 là db2 +c -td% khi bắt đầu một phiên CLP mới của DB2. Bắt đầu Trước tiên tạo ra các bảng cần thiết. Bạn sẽ cần một bảng để giữ thông tin khách hàng nhạy cảm và một bảng khác để giữ thông tin được ghi nhật ký mỗi lần truy cập thông tin nhạy cảm. Liệt kê 3 cho thấy một ví dụ. Liệt kê 3. Tạo hai bảng ví dụ DROP TABLE customerSensitiveInfo % CREATE TABLE customerSensitiveInfo(customerAccountNumber integer, amountOverdue integer) % DROP TABLE log_table % CREATE TABLE log_table(queryingEmployeeID varchar(100), customerAccNumber integer, when timestamp) % COMMIT % Tiếp theo, tạo một thủ tục để viết vào log_table khi truy cập thông tin nhạy cảm, như trong Liệt kê 4. Liệt kê 4. Viết vào bảng log CREATE OR REPLACE PROCEDURE log_query (in queryingEmployee varchar(100), in accNumber integer, in when timestamp) LANGUAGE SQL BEGIN insert into log_table values (queryingEmployee, accNumber, when); END % COMMIT % Một trong những giao diện mà Ngân hàng B cung cấp cho các nhà phát triển ứng dụng của mình là một phương tiện để truy vấn xem một tài khoản cụ thể đã nợ quá hạn bao nhiêu. Đầu tiên thủ tục get_AmountOverdue thực hiện cuộc gọi đến thủ tục log_query để ghi lại rằng nó sẽ truy cập dữ liệu nhạy cảm. Rồi nó thực hiện một select từ bảng customerSensitiveInfo để lấy ra số tiền nợ quá hạn của số tài khoản cụ thể. Liệt kê 5 cho thấy một ví dụ. Liệt kê 5. Thủ tục get_AmountOverdue CREATE OR REPLACE PROCEDURE get_AmountOverdue(in accountNumber integer, out overdue integer) LANGUAGE SQL BEGIN DECLARE due integer; DECLARE currentTime timestamp; SET currentTime= CURRENT TIMESTAMP; CALL log_query(CURRENT USER, accountNumber, currentTime ); SELECT amountOverdue INTO due FROM customerSensitiveInfo WHERE customerAccountNumber= accountNumber; SET overdue=due; END % COMMIT % Sau khi bạn tạo giao diện get_AmountOverdue, hãy thêm một vài dữ liệu khách hàng vào bảng customerSensitiveInfo. Tiếp theo, thực hiện câu lệnh trong Liệt kê 6 để tạo bảng này. Liệt kê 6. Câu lệnh để tạo ra bảng ví dụ INSERT INTO customerSensitiveInfo VALUES(12345, 10000),(12346,20000) % COMMIT % Bảng kết quả có chứa các thông tin như trong Bảng 1. Bảng 1. CustomerSensitiveInfo Số tài khoản của khách hàng Số tiền nợ quá hạn 12345 10,000 12346 20,000 Bây giờ với bảng này đã điền dữ liệu và một phương tiện để truy cập vào nó, hãy lấy số tiền nợ quá hạn từ tài khoản 12345. Vì bạn chỉ quan tâm xem dữ liệu, nên bạn muốn làm như vậy một cách ẩn danh, hãy đưa ra câu lệnh rollback ngay sau cuộc gọi để che dấu vết tích của bạn, như trong Liệt kê 7. Liệt kê 7. Thêm một câu lệnh rollback vào mã get_AmountOverdue CALL get_AmountOverdue(12345, ?) % Value of output parameters -------------------------- Parameter Name : OVERDUE Parameter Value : 10,000 return Status = 0 ROLLBACK % Kiểm tra trạng thái của log_table, nó trông giống như Liệt kê 8. Liệt kê 8. log_table SELECT * FROM log_table % QUERYINGEMPLOYEEID CUSTOMERACCNUMBER WHEN 0 record(s) selected. Theo dự kiến, bảng log là rỗng vì giao dịch có chứa cả hai câu lệnh truy cập thực tế vào bảng này và câu lệnh chèn vào bảng log đã hủy giao dịch. Đây chắc chắn không phải là hành vi mong muốn. Hãy thêm từ khóa AUTONOMOUS vào thủ tục log_query, như trong Liệt kê 9. Liệt kê 9. Thủ tục log_query với câu lệnh AUTONOMOUS CREATE OR REPLACE PROCEDURE log_query (in queryingEmployee varchar(100), in accNumber integer, in when timestamp) LANGUAGE SQL AUTONOMOUS BEGIN INSERT INTO log_table values (queryingEmployee, accNumber, when); END % COMMIT % Bây giờ lấy ra số tiền nợ quá hạn hạn từ tài khoản 12345 và hủy giao dịch này sau khi làm như vậy một lần nữa, như trong Liệt kê 10. Liệt kê 10. Mã get_AmountOverdue với câu lệnh rollback CALL get_AmountOverdue(12345, ?) % Value of output parameters -------------------------- Parameter Name : OVERDUE Parameter Value : 10,000 return Status = 0 ROLLBACK % Một lần nữa, hãy kiểm tra trạng thái của log_table, nó bây giờ trông giống như Liệt kê 11. Liệt kê 11. log_table sau khi thêm câu lệnh AUTONOMOUS SELECT * FROM log_table % QUERYINGEMPLOYEEID CUSTOMERACCNUMBER WHEN 98765 12345 2009:05:25:12.00.00.000000 1 record(s) selected. Lúc này, có được kết quả mong muốn. Mặc dù giao dịch đọc những thông tin nhạy cảm đã tự hủy giao dịch, các mục nhập vào log_table được cam kết. Bằng cách này, bạn có thể duy trì lịch sử của những người đã truy cập vào dữ liệu đó, ngay cả khi bản thân việc truy cập đã không được cam kết. Về đầu trang Kết luận Bài viết này đã giới thiệu khái niệm về các giao dịch tự quản. Bây giờ bạn sẽ hiểu một giao dịch tự quản là gì cũng như cách tạo và sử dụng một giao dịch tự quản trong DB2.