Khôi phục Mysql sau khi cam kết

Giới thiệu về giao dịch

Một giao dịch là một đơn vị logic của công việc chứa một hoặc nhiều câu lệnh SQL. Giao dịch là đơn vị nguyên tử của công việc có thể được cam kết hoặc khôi phục. Khi một giao dịch thực hiện nhiều thay đổi đối với cơ sở dữ liệu, tất cả các thay đổi đều thành công khi giao dịch được thực hiện hoặc tất cả các thay đổi sẽ được hoàn tác khi giao dịch được khôi phục

Một giao dịch bắt đầu với câu lệnh SQL thực thi đầu tiên. Một giao dịch kết thúc khi nó được cam kết hoặc khôi phục, rõ ràng bằng câu lệnh CAM KẾT hoặc ROLLBACK hoặc hoàn toàn khi sử dụng DDL (Ngôn ngữ định nghĩa dữ liệu (DDL) để quản lý cấu trúc bảng và chỉ mục cũng như các câu lệnh CREATE, ALTER, RENAME, DROP và TRUNCATE

nội dung

• Tìm hiểu khái niệm giao dịch
• MySQL và Mô hình ACID
• Giao dịch MySQL
• Các câu lệnh MySQL không thể Roll Back và các câu lệnh gây ra Cam kết ngầm
• SAVEPOINT, QUAY LẠI VỀ SAVEPOINT và PHÁT HÀNH SAVEPOINT
• Bảng KHÓA và MỞ KHÓA
• ĐẶT Cú pháp GIAO DỊCH

Tìm hiểu khái niệm giao dịch

Để hiểu khái niệm về giao dịch, hãy xem xét cơ sở dữ liệu ngân hàng. Giả sử một khách hàng của ngân hàng chuyển tiền từ tài khoản tiết kiệm (SB a/c) sang tài khoản hiện tại (CA a/c), sao kê sẽ được chia thành bốn khối

• Nợ SB a/c
• Tín dụng CA a / c
• Ghi vào Nhật ký giao dịch
• Kết thúc giao dịch

Câu lệnh SQL để ghi nợ SB a/c như sau

CẬP NHẬT sb_accounts
SET số dư = số dư - 1000
WHERE account_no = 932656 ;

Câu lệnh SQL để ghi có OD a/c như sau

CẬP NHẬT ca_accounts
SET số dư = số dư + 1000
WHERE account_no = 933456 ;

Câu lệnh SQL để ghi vào nhật ký giao dịch như sau

CHÈN VÀO GIÁ TRỊ tạp chí
(100896, 'Tansaction on Benjamin Hampshair a/c', '26-AUG-08' 932656, 933456, 1000);

Câu lệnh SQL cho Giao dịch kết thúc như sau

CAM KẾT CÔNG VIỆC;

MySQL và Mô hình ACID

ACID (Nguyên tử, Tính nhất quán, Cách ly, Độ bền) là một tập hợp các thuộc tính đảm bảo rằng các giao dịch cơ sở dữ liệu được xử lý một cách đáng tin cậy. Trong MySQL, công cụ lưu trữ InnoDB hỗ trợ các tính năng tuân thủ ACID. Các phần sau thảo luận về cách các tính năng của MySQL, cụ thể là công cụ lưu trữ InnoDB, tương tác với các danh mục của mô hình ACID

nguyên tử. Khía cạnh nguyên tử của mô hình ACID chủ yếu liên quan đến các giao dịch InnoDB. Các tính năng MySQL liên quan bao gồm

• Cài đặt tự động cam kết
• câu lệnh CAM KẾT
• câu lệnh QUAY LẠI
• Dữ liệu hoạt động từ các bảng INFORMATION_SCHEMA

Tính nhất quán. Khía cạnh tính nhất quán của mô hình ACID chủ yếu liên quan đến quá trình xử lý InnoDB nội bộ để bảo vệ dữ liệu khỏi sự cố. Các tính năng MySQL liên quan bao gồm

• Bộ đệm ghi kép InnoDB
• Khôi phục sự cố InnoDB

Sự cách ly. Khía cạnh cách ly của mô hình ACID chủ yếu liên quan đến các giao dịch InnoDB, cụ thể là mức độ cách ly áp dụng cho từng giao dịch. Các tính năng MySQL liên quan bao gồm

• Cài đặt tự động cam kết
• Câu lệnh SET ISOLATION LEVEL
• Chi tiết cấp thấp về khóa InnoDB. Trong quá trình điều chỉnh hiệu suất, bạn sẽ thấy những chi tiết này thông qua bảng INFORMATION_SCHEMA

Độ bền. Khía cạnh độ bền của mô hình ACID liên quan đến các tính năng phần mềm MySQL tương tác với cấu hình phần cứng cụ thể của bạn. Do có nhiều khả năng tùy thuộc vào khả năng của CPU, mạng và thiết bị lưu trữ của bạn, khía cạnh này là phức tạp nhất để cung cấp hướng dẫn cụ thể cho. Các tính năng MySQL liên quan bao gồm

• Bật và tắt bộ đệm ghi kép InnoDB bằng tùy chọn cấu hình innodb_doublewrite
• Tùy chọn cấu hình innodb_flush_log_at_trx_commit
• Tùy chọn cấu hình sync_binlog
• Tùy chọn cấu hình innodb_file_per_table
• Bộ đệm ghi trong thiết bị lưu trữ, chẳng hạn như ổ đĩa, SSD hoặc mảng RAID
• Bộ nhớ đệm được hỗ trợ bằng pin trong thiết bị lưu trữ
• Hệ điều hành được sử dụng để chạy MySQL, cụ thể là hỗ trợ lệnh gọi hệ thống fsync()
• Nguồn cung cấp điện liên tục (UPS) bảo vệ nguồn điện cho tất cả các máy chủ và thiết bị lưu trữ chạy máy chủ MySQL và lưu trữ dữ liệu MySQL
• Chiến lược sao lưu của bạn, chẳng hạn như tần suất và loại sao lưu cũng như thời gian lưu giữ sao lưu
• Đối với các ứng dụng dữ liệu được lưu trữ hoặc phân tán, các đặc điểm cụ thể của trung tâm dữ liệu nơi đặt phần cứng cho máy chủ MySQL và kết nối mạng giữa các trung tâm dữ liệu

Giao dịch MySQL

MySQL (ở đây chúng tôi duy trì phiên bản 5. 6) hỗ trợ các giao dịch cục bộ (trong một phiên khách hàng nhất định) thông qua các câu lệnh như SET tự động cam kết, BẮT ĐẦU GIAO DỊCH, CAM KẾT và ROLLBACK. Đây là cú pháp BẮT ĐẦU GIAO DỊCH, CAM KẾT và ROLLBACK

START TRANSACTION
   transaction_characteristic [, transaction_characteristic] ...] 

transaction_characteristic:     
    WITH CONSISTENT SNAPSHOT    
    | READ WRITE    
    | READ ONLY    

BEGIN [WORK]  
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]  
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]  
SET autocommit = {0 | 1}

Những tuyên bố này cung cấp quyền kiểm soát đối với việc sử dụng các giao dịch

• Câu lệnh START TRANSACTION hoặc BEGIN bắt đầu một giao dịch mới
• CAM KẾT cam kết giao dịch hiện tại, khiến các thay đổi của giao dịch đó trở thành vĩnh viễn.  
• ROLLBACK khôi phục giao dịch hiện tại, hủy các thay đổi của nó
• Câu lệnh SETautocommit vô hiệu hóa hoặc bật chế độ tự động cam kết mặc định cho phiên hiện tại

Theo mặc định, MySQL chạy với chế độ autocommit được bật. Điều này có nghĩa là ngay khi bạn thực hiện một câu lệnh cập nhật (sửa đổi) một bảng, MySQL sẽ lưu trữ bản cập nhật trên đĩa để biến nó thành vĩnh viễn. Thay đổi không thể được khôi phục

Hiện tại (theo mặc định), MySQL chạy với chế độ autocommit được bật

mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        9 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.09 sec)

Hãy thực hiện một lệnh cập nhật

mysql>mysql> UPDATE STUDENT_MAST SET ST_CLASS=8 WHERE STUDENT_ID=2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        8 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

Bây giờ hãy thực hiện lệnh ROLLBACK để quay lại giai đoạn trước

mysql>mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)

mysql>mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        8 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)
 

Không có khôi phục khi MySQL chạy với chế độ tự động xác nhận được bật

Để tắt chế độ tự động cam kết, hãy sử dụng câu lệnh BẮT ĐẦU GIAO DỊCH. Xem ví dụ sau

mysql>mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE STUDENT_MAST SET ST_CLASS=10 WHERE STUDENT_ID=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |       10 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from student_mast;
+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        8 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

Các câu lệnh MySQL không thể Roll Back và các câu lệnh gây ra Cam kết ngầm

Trong MySQL, một số câu lệnh không thể được khôi phục. Các câu lệnh DDL chẳng hạn như cơ sở dữ liệu CREATE hoặc DROP, các bảng CREATE, ALTER hoặc DROP hoặc các thủ tục được lưu trữ. Bạn nên thiết kế một giao dịch không có những câu lệnh này

Các câu lệnh được liệt kê trong phần này (và bất kỳ từ đồng nghĩa nào với chúng) hoàn toàn kết thúc bất kỳ giao dịch nào đang hoạt động trong phiên hiện tại, như thể bạn đã thực hiện CAM KẾT trước khi thực hiện câu lệnh

• Các câu lệnh ngôn ngữ định nghĩa dữ liệu (DDL) xác định hoặc sửa đổi các đối tượng cơ sở dữ liệu. THAY ĐỔI CƠ SỞ DỮ LIỆU. NÂNG CẤP TÊN THƯ MỤC DỮ LIỆU, THAY ĐỔI SỰ KIỆN, THAY ĐỔI THỦ TỤC, THAY ĐỔI MÁY CHỦ, THAY ĐỔI BẢNG, THAY ĐỔI VIEW, TẠO CƠ SỞ DỮ LIỆU, TẠO SỰ KIỆN, TẠO CHỈ MỤC, TẠO THỦ TỤC, TẠO MÁY CHỦ, TẠO BẢNG, TẠO KÍCH HOẠT, TẠO VIEW, DROP DATABASE, DROP EVENT,
• ALTER FUNCTION, CREATE FUNCTION và DROP FUNCTION cũng gây ra một cam kết ngầm định khi được sử dụng với các hàm được lưu trữ, nhưng không phải với UDF. (CHỨC NĂNG THAY ĐỔI chỉ có thể được sử dụng với các chức năng được lưu trữ. )
• ALTER TABLE, CREATE TABLE và DROP TABLE không thực hiện giao dịch nếu từ khóa TEMPORARY được sử dụng
• Các câu lệnh ngầm sử dụng hoặc sửa đổi các bảng trong cơ sở dữ liệu MySQL. TẠO NGƯỜI DÙNG, THẢ NGƯỜI DÙNG, CẤP, ĐỔI TÊN NGƯỜI DÙNG, THU HỒI, ĐẶT MẬT KHẨU
• Kiểm soát giao dịch và báo cáo khóa. BEGIN, LOCK TABLES, SET autocommit = 1 (nếu giá trị chưa phải là 1), BẮT ĐẦU GIAO DỊCH, MỞ KHÓA BẢNG
• Báo cáo tải dữ liệu. TẢI DỮ LIỆU VÀO TẬP TIN. LOAD DATA INFILE chỉ gây ra một cam kết ngầm cho các bảng sử dụng công cụ lưu trữ NDB
• Báo cáo hành chính. PHÂN TÍCH BẢNG, CHỈ MỤC BỘ NHỚ, BẢNG KIỂM TRA, TẢI CHỈ SỐ VÀO BỘ NHỚ, TỐI ƯU BẢNG, BẢNG SỬA CHỮA
• Báo cáo kiểm soát sao chép. Bắt đầu với MySQL 5. 6. 7. BẮT ĐẦU NÔ LỆ, DỪNG NÔ LỆ, THIẾT LẬP LẠI NÔ LỆ, THAY ĐỔI MASTER THÀNH
  

SAVEPOINT, QUAY LẠI VỀ SAVEPOINT và PHÁT HÀNH SAVEPOINT

InnoDB hỗ trợ các câu lệnh SQL SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT và từ khóa WORK tùy chọn cho ROLLBACK

Câu lệnh SAVEPOINT đặt điểm lưu trữ giao dịch được đặt tên với tên của mã định danh. Nếu giao dịch hiện tại có một điểm lưu trữ có cùng tên, thì điểm lưu trữ cũ sẽ bị xóa và một điểm lưu trữ mới được đặt

Câu lệnh ROLLBACK TO SAVEPOINT khôi phục giao dịch về điểm lưu trữ được đặt tên mà không chấm dứt giao dịch. Các sửa đổi mà giao dịch hiện tại được thực hiện đối với các hàng sau khi đặt điểm lưu trữ sẽ được hoàn tác trong quá trình khôi phục, nhưng InnoDB không giải phóng các khóa hàng được lưu trữ trong bộ nhớ sau điểm lưu trữ

Đây là cú pháp

Bảng KHÓA và MỞ KHÓA

MySQL cho phép các phiên máy khách có được các khóa bảng một cách rõ ràng nhằm mục đích hợp tác với các phiên khác để truy cập vào các bảng hoặc để ngăn các phiên khác sửa đổi các bảng trong các khoảng thời gian khi một phiên yêu cầu quyền truy cập độc quyền vào chúng. Một phiên chỉ có thể lấy hoặc giải phóng các khóa cho chính nó. Một phiên không thể lấy khóa cho phiên khác hoặc giải phóng khóa do phiên khác nắm giữ

LOCK TABLES có được các khóa bảng một cách rõ ràng cho phiên máy khách hiện tại. Khóa bảng có thể được mua cho các bảng cơ sở hoặc dạng xem. Bạn phải có đặc quyền LOCK TABLES và đặc quyền CHỌN cho từng đối tượng được khóa

UNLOCK TABLES giải phóng rõ ràng bất kỳ khóa bảng nào do phiên hiện tại nắm giữ. LOCK TABLES hoàn toàn giải phóng bất kỳ khóa bảng nào được giữ bởi phiên hiện tại trước khi có được các khóa mới

Chúng tôi có thể khôi phục sau khi cam kết không?

Làm cách nào để sử dụng cam kết và khôi phục trong MySQL?

Làm cách nào để khôi phục dữ liệu trong MySQL?

Chúng tôi có thể khôi phục sau khi cam kết trong SQL Server không?