Làm cách nào để hoàn tác thao tác cuối cùng trong MySQL?

Rollback là một hoạt động, thay đổi trạng thái hiện tại của giao dịch về trạng thái trước đó. Nhật ký hoàn tác thường được yêu cầu nếu chúng tôi muốn khôi phục bất kỳ giao dịch nào chưa được cam kết và nó đóng vai trò chính trong Cô lập

Đối với bất kỳ thay đổi nào được thực hiện trong một giao dịch, nó phải được lưu trữ trước, bởi vì chúng được yêu cầu nếu chúng tôi chọn khôi phục giao dịch

Các mục được thực hiện trong nhật ký hoàn tác khi sửa đổi dữ liệu được thực hiện. Nếu một giao dịch sửa đổi dữ liệu bằng các lệnh SQL, Nó sẽ tạo các bản ghi hoàn tác rời rạc cho từng thao tác. Khi một giao dịch được cam kết, MySQL có thể tự do xóa các bản ghi hoàn tác được tạo trong giao dịch đó.  

Để biết thêm về nhật ký hoàn tác, bạn có thể kiểm tra các blog trước đây của chúng tôi về tổng quan để hoàn tác nhật ký

Thông thường, quá trình Rollback sẽ mất nhiều thời gian hơn so với thao tác ban đầu. Bởi vì nó là một quá trình đơn luồng

Sơ đồ luồng giao dịch

Hãy xem xét một bảng sbtest1 với 100 triệu bản ghi. Tôi đang xóa 30 triệu bản ghi dựa trên điều kiện id<=30000000. Vì vậy, ở giữa, tôi buộc phải chấm dứt thao tác xóa

mysql> hiển thị danh sách quy trình;
+—-+—————–+———–+———+———+——+———— . ID. Người sử dụng. Chủ nhà. db. Chỉ huy. Thời gian. Tiểu bang. Thông tin.
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+————— . 5. event_scheduler. máy chủ cục bộ. VÔ GIÁ TRỊ. yêu tinh. 6669. Chờ đợi trên hàng đợi trống. VÔ GIÁ TRỊ.
|  5 | event_scheduler | localhost | NULL    | Daemon  | 6669 | Waiting on empty queue | NULL                                   |
. 16. nguồn gốc. máy chủ cục bộ. sb_test. Truy vấn. 0. trong đó. hiển thị danh sách quy trình.
. 17. nguồn gốc. máy chủ cục bộ. sb_test. Truy vấn. 669. cập nhật. xóa khỏi sbtest1 nơi id
+—-+—————–+———–+———+———+——+————————+————— . 01 giây)
3 rows in set (0.01 sec)

mysql> kill 17;
Truy vấn OK, 0 hàng bị ảnh hưởng (0. 26 giây)

Như chúng ta có thể thấy trong danh sách quy trình, giao dịch bị hủy hiện đang ở trạng thái khôi phục. Thao tác khôi phục được thực hiện với sự trợ giúp của nhật ký hoàn tác.

mysql> hiển thị danh sách quy trình;
+—-+—————–+———–+———+———+——+———— . ID. Người sử dụng. Chủ nhà. db. Chỉ huy. Thời gian. Tiểu bang. Thông tin.
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+————— . 5. event_scheduler. máy chủ cục bộ. VÔ GIÁ TRỊ. yêu tinh. 7306. Chờ đợi trên hàng đợi trống. VÔ GIÁ TRỊ.
|  5 | event_scheduler | localhost | NULL    | Daemon  | 7306 | Waiting on empty queue | NULL                                   |
. 17. nguồn gốc. máy chủ cục bộ. sb_test. bị giết. 704. kết thúc truy vấn. xóa khỏi sbtest1 nơi id
. 18. nguồn gốc. máy chủ cục bộ. sb_test. Ngủ. 626.                         . VÔ GIÁ TRỊ.
. 19. nguồn gốc. máy chủ cục bộ. VÔ GIÁ TRỊ. Truy vấn. 0. trong đó. hiển thị danh sách quy trình.
+—-+—————–+———–+———+———+——+————————+————— . 00 giây)
4 rows in set (0.00 sec)

Bây giờ, để tính toán ước tính của quy trình khôi phục, tôi đã tìm nạp các mục nhật ký hoàn tác và hủy giao dịch của id chuỗi từ information_schema. đầu ra bảng innodb_trx với khoảng thời gian 60 giây. Khoảng thời gian 5 giây cài đặt tối thiểu sẽ hữu ích để tính toán thời gian ước tính chính xác của việc khôi phục.

mysql> grep -e “trx_mysql_thread_id. 17” -e trx_rows_modified
PAGER được đặt thành ‘grep -e “trx_mysql_thread_id. 17” -e trx_rows_modified’
mysql> select * from information_schema. innodb_trx\G chọn chế độ ngủ (60); . innodb_trx\G
         trx_rows_modified. 0
       trx_mysql_thread_id. 17
         trx_rows_modified. 18460230
2 hàng trong tập hợp (0. 26 giây)

1 hàng trong bộ (1 phút 0. 31 giây)

       trx_mysql_thread_id. 17
         trx_rows_modified. 17169927
1 hàng trong tập hợp (0. 09 giây)

mysql> \n
PAGER được đặt thành thiết bị xuất chuẩn
mysql> select SEC_TO_TIME(round((17169927*60) . Thời gian ước tính của Rollback.
+—————————–+
| Estimation Time of Rollback |
+—————————–+
. 00. 13. 18.
+—————————–+
1 hàng trong tập hợp (0. 18 giây)

Từ nhật ký được chia sẻ ở trên, chúng tôi có thể thấy rằng sẽ mất thêm 13 phút 18 giây để hoàn tất thao tác khôi phục

Để làm cho quá trình ước tính trên trở nên đơn giản hơn, tôi đã tạo hàm RollbackTimeCalc

DELIMITER $$

CHỨC NĂNG TẠO RollbackTimeCalc(processID INT, timeInterval INT)
RETURNS VARCHAR(225)
. innodb_trx trong đó trx_mysql_thread_id = processID và trx_state = 'QUẢ LẠI';
BEGIN 
  DECLARE RollbackModifiedBeforeInterval INT; 
  DECLARE RollbackModifiedAfterInterval INT;
  DECLARE RollbackPendingRows INT; 
  DECLARE Result varchar(20);
      
      SELECT trx_rows_modified INTO RollbackModifiedBeforeInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;
      do sleep(timeInterval);
      SELECT trx_rows_modified INTO RollbackModifiedAfterInterval từ information_schema. innodb_trx trong đó trx_mysql_thread_id = processID và trx_state = 'QUAY LẠI';
  
      đặt Kết quả=SEC_TO_TIME(vòng((RollbackModifiedAfterInterval*timeInterval)/(RollbackModified BeforeInterval-RollbackModifiedAfterInterval)) . innodb_trx trong đó trx_mysql_thread_id = processID và trx_state = ‘QUAY LẠI’;
  
      SELECT trx_rows_modified INTO RollbackPendingRows from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;
  
      RETURN(CONCAT(‘Thời gian hoàn nguyên ước tính. ‘, Kết quả, ‘ Các hàng đang chờ khôi phục ‘, RollbackPendingRows));
END$$
DELIMITER ;

Chúng ta phải truyền hai tham số cho các chức năng

Chọn RollbackTimeCalc(x,y);

Ở đâu

x là id danh sách quy trình của giao dịch bị hủy,

y là khoảng thời gian hoàn tác các mục nhật ký

​​mysql> select RollbackTimeCalc(18,5);
+——————————————————————— . RollbackTimeCalc(18,5).
| RollbackTimeCalc(18,5)                                                   |
+————————————————————————————————————+
| Estimation Time of Rollback: 00:06:09 Pending rows to rollback 10341861 |
+————————————————————————————————————-
1 row in set (5.37 sec)

Với hàm đã tạo ở trên, chúng ta có thể dễ dàng ước tính thời gian gần đúng của các thao tác quay lui là 06 phút 09 giây.  

Ghi chú

1) Các giao dịch đã cam kết không thể được khôi phục.  

2) Chia các giao dịch thành các giao dịch nhỏ hơn. Các thao tác xóa và cập nhật nặng có thể được xử lý thông qua các công cụ như pt-archiver và oak-chunk-update

3) Tốt hơn là đặt autocommit=0 nếu chúng tôi đang thực hiện các thay đổi lớn trong cơ sở dữ liệu. và đừng quên thêm autocommit=1 vào cuối giao dịch

Làm cách nào để hoàn tác lệnh cuối cùng trong MySQL?

Có hoàn tác trong MySQL không?

Làm cách nào để ROLLBACK sau khi CAM KẾT trong MySQL?

Nhật ký hoàn tác trong MySQL là gì?