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
0
votes
1 answer

Oracle save points

I understand that when you roll back an Oracle DB to a savepoint, any savepoints marked after that original savepoint are erased, but are all committed transactions since that savepoint also rolled back? Is it a complete flashback of the database?…
Franco
  • 23
  • 1
  • 8
0
votes
1 answer

MySQL:START TRANSACTION - UPDATE - ROLLBACK : Non-Transactional Tables

I'm attempting to run these queries to perform a ROLLBACK, and I'm not too sure what I'm doing wrong, but I get a warning: Some non-transactional changed tables couldn't be rolled back. After a bit of research I found that the most likely cause…
Sierra
  • 327
  • 4
  • 11
0
votes
0 answers

DB2 savepoint in stored procedure is invalid

Im creating a DB2 stored procedure which goes like this create or replace procedure test() dynamic result sets 1 begin //declaration of variable goes here declare continue handler for sqlexception set errstate = sqlstate; savepoint…
Fuad Hanif
  • 33
  • 7
0
votes
2 answers

Savepoints in Oracle global temporary tables

I read that savepoints in Oracle global temporary tables delete all the data, but when I tested on Oracle 11g they worked like heap tables. Can anybody explain? insert into table_1 values('one'); insert into table_1 values('two'); savepoint…
Pankaj Pradhan
  • 9
  • 1
0
votes
1 answer

What is the right way to use multiple savepoints

I'm using transactions for the first time, so I might be asking a silly question. I want to insert data in 3 tables: Table1(p1,p2,p3) Table2(q1,q2) Table3(t3,fk1,fk2) For example, if something goes wrong and data cannot be inserted into Table2,…
user1012732
  • 181
  • 2
  • 14
0
votes
1 answer

Why arent my modifications being written to the disk using SAVEPOINT?

I have three operations, a read, a write, and another read, each operation is in a transaction started using SAVEPOINT. I read an value, aborted the transaction I used to read it, I set the value, commited the transaction I used to set it, and then…
André Puel
  • 8,741
  • 9
  • 52
  • 83
0
votes
1 answer

How to rollback each and every insertion if there is any validation is missing

I have to insert data from xlm to 10 tables, the hierarchy is as below. T1 has child table T2 T2 have child tables t3,t4,t5,t6 etc till t10 If any of the validation fails in t3 to t10 above t1, t2 tables insertion should rollback. I am using…
developer
  • 9,116
  • 29
  • 91
  • 150
0
votes
1 answer

sql server: managing transactions (begin, save, commit, rollback) across multiple stored procedures

In T-Sql, rollback transaction rolls back all transactions except specified a save point name. To roll back only part of modifications, we use rollback transaction @save_point_name. That is a save transaction @save_point_name has to be earlier…
joegomain
  • 536
  • 1
  • 5
  • 22
0
votes
1 answer

savepoint is not recognized in db2

I have following query - SAVEPOINT A ON ROLLBACK RETAIN CURSORS; select max(id) from testdb.table1; ROLLBACK TO SAVEPOINT A; commit I am getting following error ROLLBACK TO SAVEPOINT A DB21034E The command was processed as an SQL statement…
user2039445
  • 253
  • 1
  • 4
  • 17
0
votes
1 answer

how to manage nested transaction with try catch

--Drop Table Tab1 Begin Transaction TR1; Save Transaction TR1; Create Table Tab1(f1 decimal(10,0)); Begin Transaction TR2 Save Transaction TR2 insert into Tab1 values(1); Begin Transaction TR3; Save Transaction…
Haider Ali Wajihi
  • 2,756
  • 7
  • 51
  • 82
0
votes
2 answers

setting savepoints on hibernate session

I'm new to Hibernate. I needed the below scenario for my project. For example am inserting 20 records data of class TestHBDemo. hbsession = HibernateUtil.getCurrentSession(); tx = hbsession.beginTransaction(); for(int index = 0; index < 20;…
NamingException
  • 2,388
  • 1
  • 19
  • 41
0
votes
0 answers

How to get the status of savepoint() in jdbc3

I have three savepoint for three different database. I want to find out from which transaction, exception rose. So basically these transaction are running sequentially. If exception occurs in transaction 1 ,Then I have to rollback to savepoint 1. if…
arvin_codeHunk
  • 2,328
  • 8
  • 32
  • 47
-1
votes
1 answer

How to Create Rollback & Savepoints on Oracle apex?

Is there any way to Create a save-point and to Rollback on Oracle apex application I have tried several times on Application but several error occurs
Sharjeel Sohail
  • 5
  • 3
-5
votes
1 answer

Rollback Stored Procedure in Java

I got a requirement where I have to rollback a stored procedure based on a condition. First i call the stored procedure and later check a condition and if condition fails, have to rollback. Below is the code I have tried. public static void…
Kaushi
  • 198
  • 3
  • 8
  • 20
1 2 3 4
5