So sánh Postgres và MySQL: Sự khác biệt trong giao dịch DDL

Các thay đổi về cấu trúc cơ sở dữ liệu (database schema) là những thao tác quan trọng đòi hỏi phải được lên kế hoạch và thực hiện cẩn thận. Khả năng thực hiện các thay đổi này một cách an toàn và đáng tin cậy là yếu tố then chốt khi lựa chọn hệ quản trị cơ sở dữ liệu. Trong bài viết này, chúng ta sẽ so sánh cách PostgreSQL 17 và MySQL 8 xử lý các giao dịch ngôn ngữ định nghĩa dữ liệu (DDL - Data Definition Language), đặc biệt là về tính nguyên tử (atomicity) và khả năng rollback.

Tìm hiểu về Giao dịch DDL

Trước khi đi vào so sánh, hãy làm rõ khái niệm về giao dịch DDL. Các câu lệnh DDL có thể được nhóm lại và thực hiện commit như một đơn vị, hoặc rollback toàn bộ nếu có lỗi xảy ra.

Có hai khái niệm cần phân biệt:

• Transactional DDL: Khả năng đưa các câu lệnh DDL vào trong một khối giao dịch nhiều câu lệnh, với tùy chọn commit hoặc rollback tất cả cùng nhau.
• Atomic DDL: Đảm bảo rằng từng câu lệnh DDL riêng lẻ là nguyên tử (hoặc thành công hoàn toàn, hoặc không thực hiện gì), nhưng không nhất thiết phải hỗ trợ nhiều câu lệnh DDL trong cùng một giao dịch.

PostgreSQL 17: DDL giao dịch thực sự

Trong PostgreSQL 17, các thao tác DDL hoàn toàn giao dịch hóa, có nghĩa là:

• Các câu lệnh DDL có thể nằm trong khối giao dịch cùng với các câu lệnh DML.
• Nhiều thao tác DDL có thể được commit hoặc rollback như một đơn vị.
• Có thể sử dụng SAVEPOINT trong các giao dịch chứa DDL.
• Nếu giao dịch thất bại, tất cả thay đổi DDL sẽ được rollback, giữ nguyên trạng thái ban đầu của cơ sở dữ liệu.

Chỉ có một vài ngoại lệ: các thao tác trên database và tablespace (như CREATE DATABASE hoặc DROP TABLESPACE) thì không thể rollback. Tuy nhiên, hầu hết các thao tác khác đều có thể hoàn tác.

MySQL 8: DDL nguyên tử

Trước MySQL 8, các thao tác DDL trong MySQL không hề nguyên tử. Nếu một câu lệnh DDL bị lỗi khi đang thực hiện (ví dụ: ALTER TABLE thêm nhiều cột hoặc chỉ mục), cơ sở dữ liệu có thể rơi vào trạng thái không nhất quán.

Từ MySQL 8, một tính năng gọi là Atomic DDL được giới thiệu, mang lại cải tiến đáng kể nhưng vẫn khác biệt cơ bản so với PostgreSQL.

Trong MySQL 8: Các câu lệnh DDL là nguyên tử ở cấp độ câu lệnh, nghĩa là:

• Một câu lệnh DDL hoặc được thực hiện toàn bộ, hoặc không thực hiện gì.
• DDL sẽ ngầm commit bất kỳ giao dịch đang mở trước khi thực thi.
• Không thể nhóm nhiều câu lệnh DDL trong cùng một giao dịch rollback được.
• Khôi phục sau sự cố đảm bảo tính nguyên tử ở mức câu lệnh.

Atomic DDL trong MySQL được thực hiện thông qua bảng nội bộ DDL_LOG trong InnoDB, ghi lại quá trình tạo file và cấu trúc khi thực hiện DDL, nhằm đảm bảo dọn dẹp đầy đủ nếu xảy ra lỗi.

Lưu ý: Atomic DDL chỉ hỗ trợ trên InnoDB. Với các storage engine khác, có thể vẫn xảy ra cập nhật một phần.

Ví dụ minh họa

PostgreSQL 17

Ví dụ 1: Bọc toàn bộ DDL trong một giao dịch. Lệnh ROLLBACK sẽ xóa toàn bộ bảng và chỉ mục vừa tạo.

-- Start a transaction block
BEGIN; -- Create a simple table
CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL
); -- Add an index
CREATE INDEX idx_username ON users(username); -- Oops! We made a mistake and want to roll back all changes
ROLLBACK; -- Verify that the table was not created
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = 'users';
-- Should return no rows, as the transaction was rolled back

Ví dụ 2: Sử dụng SAVEPOINT để rollback một phần:

BEGIN; -- Create a table
CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL
); -- Create a savepoint
SAVEPOINT after_users_table; -- Alter the table to add a column
ALTER TABLE users ADD COLUMN email VARCHAR(100); -- Oops! We only want to roll back the column addition
ROLLBACK TO after_users_table; -- Add a different column instead
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT TRUE; -- Commit the transaction
COMMIT;

MySQL 8

Ví dụ sau không rollback được, vì mỗi DDL tự commit trước khi thực hiện:

-- Try to use a transaction block (note: this won't work as expected for DDL)
START TRANSACTION; -- Create a simple table
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL
); -- Add an index
CREATE INDEX idx_username ON users(username); -- Try to roll back all changes (won't work for DDL statements)
ROLLBACK; -- Verify that the table was created despite the ROLLBACK
SHOW TABLES;
-- Will show 'users' table

Tuy nhiên, MySQL 8 hỗ trợ atomic ở mức câu lệnh:

-- This will either create all users or none
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1', 'user2'@'localhost' IDENTIFIED BY 'password2'; -- This will either drop all tables or none
DROP TABLE IF EXISTS table1, table2, table3;

Kết luận

Bảng so sánh:

PostgreSQL cung cấp mức độ an toàn cao hơn cho các thao tác thay đổi schema phức tạp, bằng cách cho phép nhiều thay đổi được bọc trong một giao dịch duy nhất. Trong khi đó, MySQL 8 mang đến cải tiến với atomic DDL, nhưng vẫn không hỗ trợ giao dịch nhiều câu lệnh DDL, điều này hạn chế khả năng rollback khi có lỗi xảy ra.