Partially Roll Back With SAVEPOINT

Mark a named checkpoint inside an open transaction so you can roll back to that point without aborting the whole transaction.

0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Insert two rows, roll back only the second, and commit the first

The transaction inserts action_a, then sets a savepoint. It inserts action_b, then rolls back to the savepoint — undoing action_b only. The transaction is then committed, making action_a permanent. The pre-existing session_start row is unaffected because it was inserted before the transaction began. The final table contains only session_start and action_a.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE audit_log (id INT, event VARCHAR(50));

INSERT INTO
  audit_log (id, event)
VALUES
  (1, 'session_start');

Validated query

SQL

START TRANSACTION;

INSERT INTO
  audit_log (id, event)
VALUES
  (2, 'action_a');

SAVEPOINT after_action_a;

INSERT INTO
  audit_log (id, event)
VALUES
  (3, 'action_b');

ROLLBACK TO SAVEPOINT after_action_a;

COMMIT;

SELECT
  id,
  event
FROM
  audit_log
ORDER BY
  id;

Expected result

id	event
1	session_start
2	action_a

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE audit_log (id INT, event VARCHAR(50));

INSERT INTO
  audit_log (id, event)
VALUES
  (1, 'session_start');

Validated query

SQL

BEGIN TRANSACTION;

INSERT INTO
  audit_log (id, event)
VALUES
  (2, 'action_a');

SAVE TRANSACTION after_action_a;

INSERT INTO
  audit_log (id, event)
VALUES
  (3, 'action_b');

ROLLBACK TRANSACTION after_action_a;

COMMIT;

SELECT
  id,
  event
FROM
  audit_log
ORDER BY
  id;

Expected result

id	event
1	session_start
2	action_a

PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE audit_log (id INT, event VARCHAR(50));

INSERT INTO
  audit_log (id, event)
VALUES
  (1, 'session_start');

Validated query

SQL

BEGIN;

INSERT INTO
  audit_log (id, event)
VALUES
  (2, 'action_a');

SAVEPOINT after_action_a;

INSERT INTO
  audit_log (id, event)
VALUES
  (3, 'action_b');

ROLLBACK TO SAVEPOINT after_action_a;

COMMIT;

SELECT
  id,
  event
FROM
  audit_log
ORDER BY
  id;

Expected result

id	event
1	session_start
2	action_a

MySQL, MariaDB, PostgreSQL, and SQLite use SAVEPOINT and ROLLBACK TO SAVEPOINT. SQL Server uses SAVE TRANSACTION and ROLLBACK TRANSACTION with the same savepoint name. The resulting table state is identical: action_b was rolled back, action_a was committed.

Useful when

Where this command helps.

retrying one step of a multi-step write without rolling back earlier completed steps
implementing nested error recovery within a stored procedure or application transaction

Explanation

What the command is doing.

SAVEPOINT name creates a named marker inside an active transaction. ROLLBACK TO SAVEPOINT name undoes all work done after that marker while keeping the transaction open and preserving everything before it. RELEASE SAVEPOINT name removes the marker without undoing anything. This lets you handle recoverable errors in one part of a larger unit of work without discarding the entire transaction. MySQL, MariaDB, PostgreSQL, and SQLite all support this syntax natively. SQL Server uses SAVE TRANSACTION name and ROLLBACK TRANSACTION name instead.

Partially Roll Back With SAVEPOINT

Insert two rows, roll back only the second, and commit the first

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.