How do transactions and savepoints work in SQL?
How do transactions and savepoints work in SQL?
A transaction groups statements so they commit or fail together. A savepoint marks a point inside a transaction to which you can roll back without discarding the whole transaction.
| Action | PostgreSQL, MySQL, standard | SQL Server | Oracle |
|---|---|---|---|
| Start | BEGIN or START TRANSACTION |
BEGIN TRAN |
implicit with the first DML |
| Commit | COMMIT |
COMMIT |
COMMIT |
| Undo | ROLLBACK |
ROLLBACK |
ROLLBACK |
BEGIN;
INSERT INTO orders (id, customer_id, total) VALUES (1, 42, 100);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 999, 1); -- fails: unknown product
ROLLBACK TO SAVEPOINT before_items; -- undoes only the item insert
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 7, 1);
RELEASE SAVEPOINT before_items; -- optional cleanup
COMMIT;
SQL Server spells it SAVE TRANSACTION name and ROLLBACK TRANSACTION name.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.