SAVEPOINT - MySQL

Overview

SAVEPOINT is a transactional control command in MySQL used to mark a specific point within a transaction, allowing you to later roll back or release changes made after that point. It helps manage complex transactions by providing checkpoints for error handling and data recovery.

Syntax

SAVEPOINT `savepoint_name`
  • savepoint_name: A user-defined name for the savepoint.

Options/Flags

SAVEPOINT does not take any options or flags.

Examples

Example 1: Creating a Savepoint

BEGIN;
SAVEPOINT my_savepoint;
-- Perform operations...

Example 2: Rolling Back to a Savepoint

-- If an error occurs...
ROLLBACK TO my_savepoint;

Example 3: Releasing a Savepoint

-- To delete the savepoint and continue the transaction
RELEASE SAVEPOINT my_savepoint;

Common Issues

  • Savepoint name conflict: Ensure savepoint_name is unique within the transaction.
  • Invalid savepoint: Savepoints can only be created within an active transaction.
  • Nested savepoints: MySQL does not support nested savepoints.

Integration

SAVEPOINT can be integrated with other transactional commands, such as:

  • BEGIN: Starts a transaction.
  • COMMIT: Ends the transaction and commits changes.
  • ROLLBACK: Ends the transaction and rolls back all changes.