START TRANSACTION; -- ---------------------------- -- Select, Update,... ở đây -- ----------------------------
Thành công thì dùng lệnh COMMIT, thất bại dùng lệnh ROLLBACK để đóng transaction.
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; -- ---------------------------- -- Records of users -- ---------------------------- BEGIN; INSERT INTO `users` VALUES (1, 'TungNT'); INSERT INTO `users` VALUES (2, 'VienLD'); INSERT INTO `users` VALUES (3, 'CuongPT'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
Mở 2 tiến trình mysql test_transaction_1 & test_transaction_2, thực hiện như sau:
mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.01 sec) mysql> UPDATE users SET `name` = 'TungNT001' WHERE id = 1; Query OK, 1 ROW affected (0.02 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> DELETE FROM users WHERE id = 3; Query OK, 1 ROW affected (0.02 sec) mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | +----+-----------+ 2 ROWS IN SET (0.02 sec)
mysql> SELECT * FROM users; +----+---------+ | id | name | +----+---------+ | 1 | TungNT | | 2 | VienLD | | 3 | CuongPT | +----+---------+ 3 ROWS IN SET (0.01 sec)
Qua ví dụ trên cho thấy khi transaction 1 chưa commit hoặc rollback thì transaction 2 chưa được ghi nhận dữ liệu thay đổi từ transaction 1 ⇒ Hai transaction là riêng biệt, độc lập với nhau.
Isolation levels là các mức cô lập dữ liệu. Mỗi transaction được chỉ định 1 isolation level để chỉ định mức độ mà nó phải được cách ly khỏi các sự sửa đổi dữ liệu được thực hiện bởi các transaction khác.
SQL cung cấp các mức isolation levels sau xếp theo thứ tự tăng dần của mức độ cô lập của dữ liệu: Read Uncommitted, Read Commited, Repeatable Read, Serializable
Cách kiểm tra level isolation MySQL đang sử dụng:
mysql> SHOW VARIABLES LIKE '%iso%'; +-----------------------+--------------+ | Variable_name | VALUE | +-----------------------+--------------+ | transaction_isolation | SERIALIZABLE | +-----------------------+--------------+ 1 ROW IN SET (0.03 sec)
Khi transaction 1 update thông tin nhưng chưa COMMIT. Nhưng transaction 2 vẫn ghi nhận dữ liệu mới đã được cập nhật từ transaction 1. Với cơ chế này, hiệu suất rất cao nhưng chế độ nhất quán của database là không có.
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 ROWS affected (0.09 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.00 sec) mysql> UPDATE users SET `name` = 'TungNT001' WHERE id = 1; Query OK, 1 ROW affected (0.03 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 ROWS affected (0.02 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.00 sec) mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.00 sec)
Khi transaction 1 update thông tin và phải COMMIT thì transaction 2 mới ghi nhận dữ liệu mới đã được cập nhật từ transaction 1. Với cơ chế này, chế độ nhất quán của database cao nhưng hiệu suất kém vì transaction 2 phải chờ hết giao dịch của các transaction trước đó rồi mới được thực hiện.
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 ROWS affected (0.04 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.00 sec) mysql> UPDATE users SET `name` = 'TungNT001' WHERE id = 1; Query OK, 1 ROW affected (0.06 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.02 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 ROWS affected (0.01 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.00 sec) mysql> SELECT * FROM users; +----+---------+ | id | name | +----+---------+ | 1 | TungNT | | 2 | VienLD | | 3 | CuongPT | +----+---------+ 3 ROWS IN SET (0.01 sec)
Khi transaction 1 update thông tin và COMMIT nhưng transaction 2 vẫn không ghi nhận dữ liệu mới, cho đến khi transaction 2 COMMIT.
Khi transaction 1 chưa commit:
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 ROWS affected (0.00 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.01 sec) mysql> UPDATE users SET `name` = 'TungNT001' WHERE id = 1; Query OK, 1 ROW affected (0.01 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 ROWS affected (0.04 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.02 sec) mysql> SELECT * FROM users; +----+---------+ | id | name | +----+---------+ | 1 | TungNT | | 2 | VienLD | | 3 | CuongPT | +----+---------+ 3 ROWS IN SET (0.08 sec)
Khi transaction 1 commit:
mysql> COMMIT; Query OK, 0 ROWS affected (0.05 sec) mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.03 sec)
mysql> SELECT * FROM users; +----+---------+ | id | name | +----+---------+ | 1 | TungNT | | 2 | VienLD | | 3 | CuongPT | +----+---------+ 3 ROWS IN SET (0.01 sec)
Khi transaction 2 commit:
mysql> COMMIT; Query OK, 0 ROWS affected (0.03 sec) mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.02 sec)
Khi transaction 1 cập nhật thông tin, chưa COMMIT/ROLLBACK thì transaction 2 sẽ không đọc được dữ liệu và bị lock lại, cho đến khi transaction 1 COMMIT/ROLLBACK thì mới được mở lock và đọc được thông tin.
Khi transaction 1 chưa COMMIT/ROLLBACK:
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 ROWS affected (0.04 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.00 sec) mysql> UPDATE users SET `name` = 'TungNT001' WHERE id = 1; Query OK, 1 ROW affected (0.17 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.04 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 ROWS affected (0.03 sec) mysql> START TRANSACTION; Query OK, 0 ROWS affected (0.02 sec) mysql> SELECT * FROM users WHERE id = 1; -- Câu select bị lock, treo lại chờ các transaction trước đó xử lý xong. Quá thời gian timeout thì bị báo lỗi. ERROR 1205 (HY000): LOCK wait timeout exceeded; try restarting TRANSACTION
Khi transaction 1 COMMIT/ROLLBACK:
mysql> COMMIT; Query OK, 0 ROWS affected (0.01 sec) mysql> SELECT * FROM users; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | | 2 | VienLD | | 3 | CuongPT | +----+-----------+ 3 ROWS IN SET (0.02 sec)
mysql> SELECT * FROM users WHERE id = 1; +----+-----------+ | id | name | +----+-----------+ | 1 | TungNT001 | +----+-----------+ 1 ROW IN SET (5.74 sec)