Stack Overflow
Stack Overflow

Questions tagged [savepoints]

A **savepoint** is a way of implementing subtransactions (also known as nested transactions) within a relational database management system by indicating a point within a transaction that can be "rolled back to" without affecting any work done in the transaction before the savepoint was created.

A savepoint is a way of implementing subtransaction (also known as nested transaction) concept.

A savepoint is a point withing a transaction (withing a relational database management system) to which the transaction can be "rolled back" without affecting any work done before the savepoint was created.

A savepoint is a way to create a single subtransaction, or multiple subtransaction nested in one another, and it does not enable creating 2 paralell 'subtransaction', one of which would be rolled back and the second commited.

Savepoints on specific RDBMS

ORACLE and PostgreSQL SQL dialects have the same syntax for creating and rolling back to savepoint

SAVEPOINT savepointName;

creates a named savepoint

ROLLBACK TO savepointName;

invalidates all SQL queries done before creating savepoint and now.

When using ORACLE, note that any DDL operation issues implicit commit.

74 questions
4
votes
3 answers

ERROR 1305 (42000): SAVEPOINT ... does not exist

I have this SQL in my MYSQL DB (sproc with empty body so I guess no implicit commits ?). DROP PROCEDURE IF EXISTS doOrder; DELIMITER $$ CREATE PROCEDURE doOrder(IN orderUUID VARCHAR(40)) BEGIN SAVEPOINT sp_doOrder; BEGIN DECLARE…
uthomas
  • 677
  • 2
  • 9
  • 17
3
votes
1 answer

Savepoint using Hibernate

The issue is i m using save or update in hibernate on temporary base... now when i finally fire update it will store it in DB permanently otherwise rollback it.. how to make save point using hibernate base is spring. like HibernateTemplate…
Ruchi
  • 5,032
  • 13
  • 59
  • 84
3
votes
1 answer

Usage ROLLBACK TO SAVEPOINT with condition

Is it possible to ROLLBACK TO SAVEPOINT with CASE? My query is BEGIN; SAVEPOINT my_savepoint; INSERT INTO DPoint (uuid) VALUES ('5547f4b7-00b3-4aac-8ceb-c9ca163a0214') ON CONFLICT (uuid) DO NOTHING; WITH ins1 AS (INSERT INTO Point (latitude,…
Jane
  • 269
  • 3
  • 15
3
votes
2 answers

How to use Hibernate Session.doWork(...) for savepoints / nested transactions?

I'm using JavaEE/JPA managed transactions with Oracle DB und Hibernate and need to achieve some kind of nested transaction. As far as I've learned such thing is not supported out of the box but I should be able to use savepoints for that purpose. As…
Daniel Bleisteiner
  • 3,190
  • 1
  • 33
  • 47
3
votes
2 answers

Savepoint on JDBC

I have a JDBC code where there are multiple Savepoints present; something like this: 1st insert statement 2nd insert statement savepoint = conn.setSavepoint("S1"); 1st insert statement 2nd update statement savepoint = conn.setSavepoint("S2"); 1st…
user182944
  • 7,897
  • 33
  • 108
  • 174
3
votes
1 answer

How do I check whether a savepoint has been established before issuing a rollback?

I have an Oracle package which loops through a list of procedures and calls them dynamically. Prior to each procedure call, a SAVEPOINT is created, and if an exception is raised, it issues a rollback and logs the problem. A bug was recently…
ninesided
  • 23,085
  • 14
  • 83
  • 107
3
votes
1 answer

Save point never created error

I'm trying to create a savepoint in Oracle 11g. ALTER TABLE ORDERS DISABLE CONSTRAINT ORDERS_C_ID_FK; DELETE FROM CUSTOMER; SELECT * FROM CUSTOMER; ROLLBACK; SELECT * FROM CUSTOMER; SAVEPOINT SAVEPOINT1; ALTER TABLE ORDERS DISABLE CONSTRAINT…
Antarr Byrd
  • 24,863
  • 33
  • 100
  • 188
2
votes
2 answers

Identify illegal commit in oracle procedure

I want to find out, if there is an illegal commit in my Oracle PL/SQL code, perhaps nested deeply. Thus, I executed "alter session disable commit in procedure". With this, an error will be raised if that happens. But.. It can be that there are…
Wolfgang
  • 21
  • 3
2
votes
0 answers

Get a list of savepoints in MySQL InnoDB

How do I get a list of savepoints in InnoDB? I need it because in my database I'm running a function which may or may not have made a number of savepoints.
Simon Baars
  • 1,877
  • 21
  • 38
2
votes
0 answers

Event sourcing in Flink

I have a Flink application that was implemented following the event-sourcing paradigm. Both events and commands are stored in several Kafka topics. The application has two startup modes: recovery and production. First, the recovery mode is used to…
user2108278
  • 391
  • 5
  • 17
2
votes
1 answer

How to perform Savepoint and rollback in Hibernate?

I am making Test of My classes so I am inserting so many data for to test my code. So I am thinking to make some mechanism of savepoint and rollback in DB. I am using postgresql as DB sever. Following is my code for test…
user3145373 ツ
  • 7,858
  • 6
  • 43
  • 62
2
votes
0 answers

Rollback to savepoint doesn't release locks

I think I have a misunderstanding about how to use Savepoints. Perhaps someone can clear it up for me. I present my example as what I am trying to do, and what I have experienced. My app is doing a certain procedure. Before that procedure (and…
svaens
  • 649
  • 7
  • 23
1
vote
1 answer

"Cannot map checkpoint/savepoint state for operator" when using fromChangelogStream

I want to use the savepoint mechanism to move existing jobs from one version of Flink to another, by: Stopping a job with a savepoint Creating a new job from the savepoint, on the new version. Until Flink 1.14 I have no problem, but in Flink…
Colin Smetz
  • 131
  • 7
1
vote
1 answer

In SQL, what happens if you try to rollback to a savepoint from another user?

If USER1 does some work, adds a savepoint SP1 but does not commit. Then, USER2 does some other work, and tries to rollback to SP1. Would there be an error? Thanks
Simon Tran
  • 1,461
  • 1
  • 11
  • 28
1
vote
1 answer

How to get all savepoint states list in mysql

I am learning SQL. I am trying to get all savepoint list. So which command is used to get all savepoint list in a database/table? I have tried SHOW SAVEPOINTS but it's not working.
MdFarzan
  • 320
  • 4
  • 11
1
2
3 4 5