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
12
votes
3 answers

How to rollback to savepoint nested transactions using Hibernate

I have a JavaEE application using Hibernate to connect to the database. In some part of my application I have calls to method which have a @Transactional annotation. In some of these cases, I want to rollback the whole transaction (the…
hfm
  • 567
  • 2
  • 7
  • 24
7
votes
3 answers

Django + MySQL - Admin Site - Add User - OperationalError - SAVEPOINT does not exist

We're trying to have a custom User model and behaviors, but then we noticed that even the default Django installation has issue when adding a new User via the Django Admin: The issue happens even in other Django versions (tried it in Django 1.8,…
Ranel Padon
  • 525
  • 6
  • 13
7
votes
2 answers

Jdbc check for capability - savepoint release

I have generic jdbc code that works with all kinds of databases. I have some apis that work with transactions and savepoints. The problem is that some databases require you to release the savepoint manually conn.releaseSavepoint(savepoint1) and…
Matei Alexandru Bogdan
  • 191
  • 6
6
votes
1 answer

sqlite transition from transactions to savepoints

My SQLite-based application currently uses transactions - both for being able to rollback and for improving performance. I'm considering replacing all transactions with savepoints. The reason is that the application is multi-threaded (yes, sqlite is…
noamtm
  • 12,435
  • 15
  • 71
  • 107
6
votes
1 answer

Savepoint in PostgreSql Function

I want to use savepoint feature inside a function in PostgreSQL. I read that savepoint cannot be used inside functions in Postgres. But while I rollback, I want to rollback to a specific point because of which I want to use savepoint. What is the…
Subhasree Mitra
  • 65
  • 1
  • 5
6
votes
3 answers

How to use savepoints in oracle procedure

I have multiple updates and insert statements in a procedure. Please refer below example: Procedure Example --code Update 1 insert 1 Update 2 Update 3 --Suppose exception occurs Now i want to rollback to before 1st update statement means no update…
Tajinder
  • 2,248
  • 4
  • 33
  • 54
6
votes
2 answers

ora-01086 : save point was not established or invalid

Ora-01086 : save point was not established or invalid. KRD_UPD_BORCTAHSILATYAP_SP this SP throws errors . When I test this loop below, I get the error: ora-01086 Normally it works without calling an external sp, I test it with an inline error and I…
Bilgin Kılıç
  • 8,707
  • 14
  • 41
  • 67
6
votes
2 answers

Can I substitute savepoints for starting new transactions in Oracle?

Right now the process that we're using for inserting sets of records is something like this: (and note that "set of records" means something like a person's record along with their addresses, phone numbers, or any other joined tables). Start a…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
5
votes
1 answer

SAVEPOINT mechanism in SQLite

I am trying to understand Savepoints and Transactions in SQLite. I had the following commands on a Table/Database and I am using Savepoints. SAVEPOINT aaa; RELEASE aaa; BEGIN; Now, if I execute all the above statements at once, its throwing an…
Programmerzzz
  • 1,237
  • 21
  • 48
5
votes
1 answer

How can to set savepoint in integration tests (with hsqldb in-memory)?

I have integration tests with hsqldb (in memory), now I need to set savepoint in my tests, in BaseTest class, How can to set savepoint in tests (hsqldb (in-memory))? BaseTest: @ContextConfiguration(classes = {TestConfig.class}) public class BaseTest…
Paushchyk Julia
  • 516
  • 3
  • 8
  • 24
5
votes
2 answers

Achieving NHibernate Nested Transactions Behavior

I'm trying to achieve some kind of nested transaction behavior using NHibernate's transaction control and FlushMode options, but things got a little bit confusing after too much reading, so any confirmation about the facts I list below will be very…
jfneis
  • 2,139
  • 18
  • 31
5
votes
2 answers

Using savepoints in python sqlite3

I'm attempting to use savepoints with the sqlite3 module built into python 2.6. Every time I try to release or rollback a savepoint, I always recieve an OperationalError: no such savepoint. What am I missing? python version: 2.6.4 (r264:75821M,…
Shane Holloway
  • 7,550
  • 4
  • 29
  • 37
4
votes
2 answers

How to retry transaction after exception in postgreSQL

In different parts of my code I have to retry transactions after exceptions. But I cant figure out how to do it. Here is my test function: CREATE OR REPLACE FUNCTION f() RETURNS VOID AS $$ DECLARE user_cur CURSOR FOR SELECT * FROM "user" WHERE…
n0nSmoker
  • 832
  • 11
  • 26
4
votes
2 answers

ActiveRecord, MySQL, and nested transactions -- what's the behavior?

Rails uses savepoints to achieve nested transactions with MySQL, and to my understanding, the semantics of this are identical to actual nested transactions, in terms of atomic data changes. Is this true? What about calling "save" at arbitrary times…
John Bachir
  • 22,495
  • 29
  • 154
  • 227
4
votes
1 answer

Using SAVE TRANSACTION SavePointName in a Stored Procedure

It is unclear to me if I need to used a different save point names for each SP I use SAVE TRANSACTION. Could I always use e.g. SAVE TRANSACTION ProcedureSavePoint and ROLLBACK TRANSACTION ProcedureSavePoint even if a higher level transaction used…
zig
  • 4,524
  • 1
  • 24
  • 68
1
2 3 4 5