During a longer write process, a transaction can use savepoints to create more than one recovery point. Normal transactions already let several changes finish as one unit, but they treat the whole group as the part to keep or cancel. That can be fine for smaller write flows, while longer flows sometimes need a way to recover from one failed step without losing every earlier change. During checkout, the database could create an order, reserve inventory, apply a discount, write an audit row, and update the order status. Some steps are required, while others can be retried, skipped, or replaced with fallback data. SAVEPOINT gives the transaction a named marker before one of those recoverable steps. If a later statement fails, ROLLBACK TO SAVEPOINT can remove the changes made after that marker while the earlier changes stay inside the open transaction. From there, the transaction can continue, write a fallback value, skip the failed step, or still cancel the whole unit with a full ROLLBACK.
Savepoints Inside a Transaction
Inside an open transaction, a savepoint acts as a named recovery marker, while the outer transaction still controls the final result. The word save can make the feature sound permanent, so the distinction is important. Nothing has been made permanent just because SAVEPOINT exists. The marker only records a point in the transaction history. If the transaction later commits, the pending changes that remain become permanent. If the transaction fully rolls back, every pending change in that transaction is canceled, including changes made before the savepoint.
Transaction Markers
Named markers give the database a place to return to without ending the transaction. The usual transaction boundary starts with BEGIN or START TRANSACTION, depending on the database. After that, statements such as INSERT, UPDATE, and DELETE become part of the same pending unit until COMMIT or ROLLBACK ends it.
SAVEPOINT adds a named marker inside that unit. The marker records the position after the statements that already ran. Later statements can still be undone back to that marker, while earlier statements stay pending in the open transaction.
BEGIN;
UPDATE store_credit
SET balance = balance - 25.00
WHERE customer_id = 108;
SAVEPOINT after_credit_deduction;
INSERT INTO store_credit_log (customer_id, amount, reason)
VALUES (108, -25.00, 'Checkout credit applied');
COMMIT;The marker named after_credit_deduction is placed after the balance update. At that point, the deduction is not permanent yet. It is only part of the open transaction. The log insert happens after the marker, so a rollback to that savepoint would remove the log insert but keep the credit deduction pending.
That distinction gives savepoints their value. They don’t split the write into separate committed units. The transaction still has one final outcome. Savepoints only give the transaction named places inside that outcome while it is still open.
Databases commonly let more than one savepoint exist in the same transaction. The names should be tied to the recovery point, not just numbered markers, because the name tells the reader what will be preserved if the transaction returns there.
BEGIN;
INSERT INTO invoices (invoice_id, customer_id, status)
VALUES (501, 108, 'OPEN');
SAVEPOINT after_invoice_header;
INSERT INTO invoice_notes (invoice_id, note)
VALUES (501, 'Header created before line totals were checked');
SAVEPOINT after_invoice_note;
UPDATE invoices
SET status = 'REVIEW_REQUIRED'
WHERE invoice_id = 501;
COMMIT;By the time the second savepoint is created, the transaction has two named positions. Rolling back to after_invoice_note would only remove changes that happened after that marker. Rolling back to after_invoice_header would remove the note and any later changes. The earlier invoice row would still be part of the open transaction unless the whole transaction is rolled back.
Savepoint names are not table names, column names, or permanent database objects. They belong to the current transaction. After the transaction ends, those markers are gone. The same name can be used again in a later transaction because the old marker no longer exists.
RELEASE SAVEPOINT removes a marker that is no longer needed. Releasing a savepoint does not undo any data changes, and it does not commit the transaction. It only removes that named return point.
BEGIN;
INSERT INTO payment_attempts (payment_id, status)
VALUES (7005, 'STARTED');
SAVEPOINT before_gateway_note;
INSERT INTO payment_notes (payment_id, note)
VALUES (7005, 'Gateway note written');
RELEASE SAVEPOINT before_gateway_note;
UPDATE payment_attempts
SET status = 'READY_FOR_CAPTURE'
WHERE payment_id = 7005;
COMMIT;After the release, the note still remains pending inside the transaction. The transaction can still be fully rolled back later, which would remove the payment row, the note, and the status update. Releasing the savepoint only removes the ability to roll back specifically to before_gateway_note.
What Rollback Keeps
Rolling back to a savepoint cancels the changes made after that marker, then leaves the transaction open. This is different from a full ROLLBACK, which cancels the entire transaction and ends it. With ROLLBACK TO SAVEPOINT, the earlier changes are still waiting for the final COMMIT or full ROLLBACK.
BEGIN;
INSERT INTO shipment_batches (batch_id, status)
VALUES (3001, 'CREATED');
SAVEPOINT before_label_print;
INSERT INTO shipping_labels (batch_id, carrier, label_status)
VALUES (3001, 'USPS', 'REQUESTED');
UPDATE shipment_batches
SET status = 'LABEL_REQUESTED'
WHERE batch_id = 3001;
ROLLBACK TO SAVEPOINT before_label_print;
UPDATE shipment_batches
SET status = 'CREATED_WITHOUT_LABEL'
WHERE batch_id = 3001;
COMMIT;The batch row is inserted before before_label_print, so it remains pending after the savepoint rollback. The label insert and the status update to LABEL_REQUESTED happened after the marker, so they are removed from the transaction. The later status update writes a different result before the final commit.
This is why the rollback target matters. The database does not look for one table to undo or one business step to undo. It returns the transaction to the named marker. Any data changes after that marker are removed, regardless of which table they touched.
Plain ROLLBACK behaves differently because it cancels the full transaction:
BEGIN;
INSERT INTO refund_requests (refund_id, order_id, status)
VALUES (810, 4402, 'OPEN');
SAVEPOINT before_refund_note;
INSERT INTO refund_notes (refund_id, note)
VALUES (810, 'Customer requested store credit');
ROLLBACK;The full rollback cancels both inserts. The savepoint does not protect the first insert from the full rollback because the savepoint is only a marker inside the transaction, not a committed checkpoint. Rolling back to a savepoint also does not end the transaction. New statements can still run afterward, and the transaction can still commit if the remaining pending changes form a valid final state.
BEGIN;
INSERT INTO account_reviews (review_id, account_id, status)
VALUES (615, 901, 'OPEN');
SAVEPOINT before_score_update;
UPDATE account_scores
SET score = score + 5
WHERE account_id = 901;
ROLLBACK TO SAVEPOINT before_score_update;
INSERT INTO account_review_notes (review_id, note)
VALUES (615, 'Score update skipped during review');
COMMIT;The score update is removed, but the review row remains pending. The note insert happens after the rollback and becomes part of the same transaction. When the transaction commits, the review row and note are accepted, while the score update is not.
Savepoint rollback is still bound by the database’s transaction rules. If the remaining statements violate a constraint, the transaction cannot safely commit until the problem is fixed or the transaction is rolled back. Savepoints help control what gets undone, but they do not make invalid data valid.
Locks, error states, and constraint timing can vary by database engine and isolation level, so the safest way to read a savepoint example is through the data changes it keeps and removes. Changes before the named marker remain pending. Changes after the named marker are reversed. The outer transaction still decides the final outcome.
Partial Recovery During Multi Step Writes
Longer write flows can contain steps with different levels of importance. Some changes need to stay in the open transaction, while other changes can be retried, skipped, or replaced with fallback data. Savepoints help with that split because we can mark a recovery point before a risky step, then return to that marker if the later SQL should not stay. The outer transaction still controls the final result, so the remaining pending changes only become permanent after COMMIT.
Basic Command Flow
The usual flow starts with a transaction, runs the required statements first, creates a savepoint before a recoverable step, then decides what to keep based on the result. If the recoverable step succeeds, we can keep moving toward COMMIT. If that step fails or gives the application a result it cannot accept, ROLLBACK TO SAVEPOINT removes the changes made after the marker while leaving earlier changes in the transaction.
Think about an order flow where the order row is required, but the discount step can be removed if the code has already expired. We still want the order to stay pending, while the failed discount should not stay attached to it:
BEGIN;
INSERT INTO orders (order_id, customer_id, status)
VALUES (1201, 88, 'PENDING');
SAVEPOINT before_discount;
UPDATE discount_codes
SET redeemed_count = redeemed_count + 1
WHERE code = 'SPRING25'
AND redeemed_count < max_redemptions;
INSERT INTO order_discounts (order_id, code)
VALUES (1201, 'SPRING25');
ROLLBACK TO SAVEPOINT before_discount;
INSERT INTO order_notes (order_id, note)
VALUES (1201, 'Discount was not applied');
UPDATE orders
SET status = 'READY_TO_REVIEW'
WHERE order_id = 1201;
COMMIT;After we roll back to before_discount, the order row remains pending because we inserted it before the marker. The discount counter update and discount row are removed because both happened after the marker. Then we write a note and update the order status before committing the transaction.
Application code usually decides when that rollback should run. The code may check the affected row count from the discount update, catch a database error, or read a validation result before returning to the savepoint. The SQL command is the recovery step, while the application decides when that recovery step fits the current write flow.
Optional audit or note records can use the same idea. The main write does not always need to fail just because a supporting row cannot stay in the transaction:
BEGIN;
UPDATE inventory_items
SET reserved_quantity = reserved_quantity + 1
WHERE sku = 'KB-110'
AND available_quantity - reserved_quantity >= 1;
SAVEPOINT before_reservation_note;
INSERT INTO inventory_notes (sku, note)
VALUES ('KB-110', 'Reserved during checkout');
ROLLBACK TO SAVEPOINT before_reservation_note;
COMMIT;The inventory reservation remains pending after the rollback, while the note insert is removed. That does not mean every optional write should be ignored. It means we can place a rollback target around a recoverable part when the surrounding transaction can still finish with valid data.
Nested Savepoints
Several savepoints can exist in the same transaction, which lets us mark more than one recovery point during a longer write flow. The later marker is closer to the current transaction position, while earlier markers reach farther back. If we roll back to an earlier marker, the transaction removes changes after that point, including changes that came after later savepoints.
BEGIN;
INSERT INTO invoices (invoice_id, customer_id, status)
VALUES (7001, 44, 'DRAFT');
SAVEPOINT before_line_items;
INSERT INTO invoice_items (invoice_id, item_code, quantity, unit_price)
VALUES (7001, 'SUPPORT-HOUR', 2, 95.00);
SAVEPOINT before_tax;
INSERT INTO invoice_taxes (invoice_id, tax_code, tax_amount)
VALUES (7001, 'WI-SALES', 10.45);
ROLLBACK TO SAVEPOINT before_line_items;
UPDATE invoices
SET status = 'DRAFT_NEEDS_ITEMS'
WHERE invoice_id = 7001;
COMMIT;When we roll back to before_line_items, the line item and tax row are removed because both happened after that marker. The invoice header remains pending because we inserted it before the marker. Then we update the invoice status so the remaining pending data still matches the transaction result.
Nested savepoints need careful placement, typically you want to place a marker right before a step with a meaningful recovery boundary. If we place it too early, a rollback removes more than the failed part. If we place it too late, the transaction may keep data that should have been removed with the failed step. Rolling back to a savepoint also affects markers created after that target in databases that follow the common savepoint model. After we return to an earlier point, later markers no longer match the active transaction history. That is why nested savepoints should use names tied to the recovery point rather than vague labels.
Name Handling
Names affect the mechanics because the rollback command targets a specific marker. A name like before_discount or before_tax tells us what the transaction is about to try and what will be removed if we roll back. Names such as sp1 and sp2 can run, but they are harder to read when the transaction grows.
BEGIN;
INSERT INTO returns (return_id, order_id, status)
VALUES (8801, 3409, 'OPEN');
SAVEPOINT before_restock;
UPDATE product_inventory
SET available_quantity = available_quantity + 1
WHERE sku = 'HD-550';
ROLLBACK TO SAVEPOINT before_restock;
UPDATE returns
SET status = 'OPEN_RESTOCK_PENDING'
WHERE return_id = 8801;
COMMIT;The name before_restock points to the part we plan to undo if restocking cannot stay in the transaction. The return row remains pending, while the inventory update is removed.
Savepoint name reuse depends on the database engine. Some engines replace or hide an older savepoint with the same name. SQL Server allows duplicate savepoint names inside a transaction and rolls back to the most recent marker with that name. Because behavior differs across engines, distinct names are safer when SQL may move between databases later.
RELEASE SAVEPOINT has a separate meaning from rollback. Releasing a savepoint removes the marker, but it does not undo the changes made after that marker. Those changes stay pending inside the transaction and still depend on the final COMMIT or full ROLLBACK.
BEGIN;
INSERT INTO billing_events (event_id, account_id, event_type)
VALUES (9401, 501, 'MONTHLY_CHARGE_CREATED');
SAVEPOINT before_billing_note;
INSERT INTO billing_notes (event_id, note)
VALUES (9401, 'Monthly charge note added');
RELEASE SAVEPOINT before_billing_note;
UPDATE billing_events
SET event_type = 'MONTHLY_CHARGE_READY'
WHERE event_id = 9401;
COMMIT;After RELEASE SAVEPOINT before_billing_note, the note remains part of the open transaction. The release only removes that rollback marker. If the transaction later runs a full ROLLBACK, the billing event, note, and status change are still canceled.
Database Differences
SQL databases share the same general savepoint idea, but the command names are not identical everywhere. PostgreSQL, MySQL, and SQLite use the familiar SAVEPOINT name, ROLLBACK TO SAVEPOINT name, and RELEASE SAVEPOINT name form. SQL Server uses SAVE TRANSACTION name to create a savepoint and ROLLBACK TRANSACTION name to return to it.
For PostgreSQL, MySQL, and SQLite, we can write the flow like this:
BEGIN;
INSERT INTO approval_requests (request_id, status)
VALUES (3005, 'OPEN');
SAVEPOINT before_optional_comment;
INSERT INTO approval_comments (request_id, comment_text)
VALUES (3005, 'Optional comment added');
ROLLBACK TO SAVEPOINT before_optional_comment;
UPDATE approval_requests
SET status = 'OPEN_WITHOUT_COMMENT'
WHERE request_id = 3005;
COMMIT;The outer transaction begins, we create a marker, we remove later changes by returning to that marker, and we commit the remaining pending changes.
SQL Server uses different command names for the same recovery idea:
BEGIN TRANSACTION;
INSERT INTO approval_requests (request_id, status)
VALUES (3006, 'OPEN');
SAVE TRANSACTION before_optional_comment;
INSERT INTO approval_comments (request_id, comment_text)
VALUES (3006, 'Optional comment added');
ROLLBACK TRANSACTION before_optional_comment;
UPDATE approval_requests
SET status = 'OPEN_WITHOUT_COMMENT'
WHERE request_id = 3006;
COMMIT TRANSACTION;The flow reads the same way, but the syntax changes. SAVE TRANSACTION creates the marker, and ROLLBACK TRANSACTION before_optional_comment returns to it. SQL Server does not have the same RELEASE SAVEPOINT command used by PostgreSQL, MySQL, and SQLite.
Transaction start behavior also varies by engine. MySQL commonly runs with autocommit enabled, so a multi-statement savepoint flow should start with START TRANSACTION or BEGIN. PostgreSQL commonly uses BEGIN for an explicit transaction block. SQLite allows SAVEPOINT to start a transaction-like nested unit by itself, although teaching examples commonly use an outer transaction so the final COMMIT or ROLLBACK stays easy to follow. Schema statements need more care than ordinary data changes becasue some engines treat certain DDL statements as implicit commit boundaries, which can break the transaction flow a savepoint example is trying to teach. For savepoint logic, ordinary transactional writes such as INSERT, UPDATE, and DELETE are the best place to learn the behavior before adding database-specific DDL rules.
Conclusion
Savepoints give a transaction a named place to return to while the full transaction stays open. SAVEPOINT marks the recovery point, ROLLBACK TO SAVEPOINT removes later changes, and COMMIT accepts the remaining pending changes only after the transaction reaches a valid final state. This mechanic is useful for multi-step writes where one recoverable step can be undone without canceling every earlier change.
