Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [rollback]

Ending a transaction by reverting to the changes made by the transaction.

Filter by
Sorted by
Tagged with
-2 votes
1 answer
30 views

In PolarDB, during multiple SQL writes, one fails; rollback attempted, but didn't succeed [closed]

In PolarDB, while executing multiple SQL insert statements, upon one statement failure, a transaction rollback was initiated but didn't fully revert changes. Despite this, the business code didn't ...
user24266786's user avatar
1 vote
1 answer
71 views

how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?

I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header I had lots of problems of locking and blocking as this ...
Marcello Miorelli's user avatar
1 vote
1 answer
63 views

Why does creating an index on MySQL(InnoDB) and then crashing deliberately in between not result in a rollback?

I created this table USERS having `10 million records mysql> desc users; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-...
catsarethebest's user avatar
0 votes
1 answer
41 views

Rollback/forward DiskIO ratio

I tried an alter table X add column C, but it was taking too long and rolled back(actually killed it). When I did so, sp_who2 showed DiskIO = 5 mil. Is it meaningful to assume that it will take ...
George Menoutis's user avatar
-1 votes
1 answer
60 views

How to rollback data mart update if data quality checks are not passed?

Good afternoon! I am working with Oracle database. I have a PL/SQL procedure which is scheduled with DBMS job to update every night a table SOME_TABLE: CREATE OR REPLACE PROCEDURE MY_PROC IS ...
rsx's user avatar
  • 1
0 votes
1 answer
1k views

MariaDB writing transaction and rollback - novice

Currently I am learning how to write transactions for MariaDB (I got a version 10.6 running). I attempt to execute several commands and based on their success to commit or rollback. So far I have ...
JRB's user avatar
  • 3
0 votes
2 answers
196 views

Does any database track all inserts, updates, deletes so that you can simply undo any changes until you get back to a good state?

Sometimes people make inserts, updates, or deletes they did not intend to and want to revert back their changes (or undo them). Are there databases out there that make this easy to do (i.e. are there ...
user1068636's user avatar
0 votes
2 answers
2k views

Guaranteed rollback in SSIS

I'm trying to create a job in SSIS to truncate a table, read from a data source and rewrite the table, so only 2 steps (1 Truncate and 2 Select + Insert). I need to guarantee a Truncate's Rollback in ...
Giorgia Grossi's user avatar
2 votes
1 answer
3k views

Query stuck on 0% ROLLBACK for 2 days

kill with statusonly returns: transaction rollback in progress. Estimated rollback completion: 0%. The query creates a temp table and runs on local server without using xp_cmdshell. CPU & reads ...
Kati's user avatar
  • 23
0 votes
3 answers
685 views

Rollback a transaction using single thread/multi thread

Would it be possible to rollback a transaction using a single thread? I tried to find an answer to this question, but I couldn't find one that provided references. I read about rollback mechanisms. ...
Amirhossein's user avatar
0 votes
2 answers
1k views

How to find which transaction is being rolled back

My MariaDB server crashed earlier today and is still recovering. The error log shows InnoDB: To roll back: 1 transactions, 519345 rows From the log entries it appears that the rollback is going to ...
Joyce Babu's user avatar
-1 votes
1 answer
2k views

Rollback of ALTER TABLE ALTER COLUMN command taking forever

I have a very large table that needed a schema change to one column - I needed to expand a datatype from varchar(50) to varchar(100). I expected this to happen quickly since the column datatype is ...
Kevin Olree's user avatar
1 vote
1 answer
209 views

What could be the reason for running rollback after a commit?

I am trying to understand why there are so many rollback queries being issued by Jira and is equal to the number of commits. MySQL log reveals the following: 2021-04-29T14:55:58.364468Z 42059 ...
GP92's user avatar
  • 155
14 votes
1 answer
2k views

Is this an error in SQL Server's documentation for ROLLBACK?

This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows: ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @...
Just a learner's user avatar
0 votes
0 answers
430 views

How to find reason for rollback during rollback works in Oracle?

Working on Oracle 12.c, I have a situation, that a big transaction performs a rollback, which takes several hours. I see the status of the session e.g. in V$SESSION.MESSAGE "Transaction Rollback: ...
D. Lohrsträter's user avatar
1 vote
1 answer
660 views

MariaDB InnoDB does not rollback on timeout

I have the ff. sample queries in this sequence: START TRANSACTION UPDATE INSERT COMMIT Query #2 is timing out but I noticed that query #3 is not rolled back. I am also trying to manually rollback by ...
IMB's user avatar
  • 169
1 vote
2 answers
1k views

Trap exception in PostgreSQL script

I want to run a script inside a transaction in PostgreSQL. So I surround the SQL code with begin and commit statements. But I want to rollback on error. I don't see how to do that. BEGIN; UPDATE ...
Peter Marshall's user avatar
0 votes
1 answer
1k views

Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)

I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...
user3150146's user avatar
0 votes
1 answer
553 views

Killed SPID on xp_LogInfo not rolling back and growing tempdb [duplicate]

I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task. I checked the scheduled tasks and we had ...
Shayn Thomas's user avatar
0 votes
1 answer
147 views

Best practices for allowing rollback of individual customers on a shared database

Imagine having multiple customers (as an example) on a shared database, and needing a backup system so that a single customer can be rolled back without affecting the other customers. - It's not ...
Jarl's user avatar
  • 3
0 votes
1 answer
509 views

Com_commit and Com_rollback count are almost same. What doest it mean?

SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. When I compare Com_commit to Com_rollback, both are ...
GP92's user avatar
  • 155
0 votes
1 answer
592 views

Is it possible to make a trigger that does not allow INSERT?

I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during ...
Justanothermatt's user avatar
0 votes
1 answer
125 views

Install old Cumulative Update SQL Server 2016 Standard

We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster. Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since ...
Mrigank's user avatar
  • 43
1 vote
2 answers
75 views

Fast Backup and Restore of Growing Databases

I work in Tech Support and the some of the products I support are heavily database dependent (PostgreSQL and MySQL, but most of the big ones are MySQL). Before we do an upgrade, we always take a full ...
Naptha's user avatar
  • 136
1 vote
1 answer
2k views

What happens if transaction rollback fails?

So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original ...
Kishan Kumar's user avatar
0 votes
2 answers
1k views

MySQL- Best practices for using rollback [closed]

What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ? Any ROLLBACK, COMMIT mechanism ? FYI: I'm using InnoDB tables which complies with Atomicity.
Prateek Pande's user avatar
0 votes
1 answer
1k views

syntax error at or near TO (ROLLBACK TO SAVEPOINT)

The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT : for ptrEnt in entite loop -- positionnement du point de retour pour une entité savepoint rbk; -...
unknownUser's user avatar
2 votes
1 answer
5k views

ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER

I give an example to show my problem. I create a table as the following: CREATE TABLE a ( id INT ) I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a" CREATE TRIGGER [dbo]...
Dunguyen's user avatar
8 votes
4 answers
3k views

Delete statement not responding to Kill session command

Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size. delete from [table-name] This table does have a foreign key ...
David Woakes's user avatar
2 votes
1 answer
677 views

Stop rollback of anonymous DO code block?

I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it ...
Zeruno's user avatar
  • 497
2 votes
1 answer
1k views

If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping? DECLARE @LoopId INT = 1; WHILE (@LoopId < 100) BEGIN INSERT INTO Table2 ...
J.D.'s user avatar
  • 37.8k
0 votes
1 answer
58 views

Cancel Rollback and accept data loss

I have a SQL instance (2012 Standard Edition) which consumes all CPU resources because of a rollback-process. The instance has been restarted, so the database is currently In Recovery. I cannot see ...
MHeld's user avatar
  • 402
2 votes
1 answer
790 views

Rollback is not working if the storedproce call another storedproc

Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit? example: pseudo procedure proc1 create procedure proc1(in var1 int, out var2 int) ...
zer09's user avatar
  • 453
0 votes
1 answer
723 views

Implementation of Rollback in PostgreSQL

How is the rollback command implemented within the PostgreSQL database? I wanted to know the implementation details and what exactly happens underneath when a rollback is executed, i.e. state of ...
user196874's user avatar
3 votes
2 answers
2k views

Rolling back truncate table

I have a stored procedure that does the following: BEGIN TRANSACTION -- Code to delete updated records from production (dbo) table DELETE FROM [dbo].[factMyTable] WHERE exists (SELECT * ...
pmdci's user avatar
  • 691
0 votes
2 answers
6k views

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION [closed]

Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a ...
Dean Myerson's user avatar
0 votes
0 answers
31 views

Is it possible to create an SQL transaction that rollbacks on syntax errors?

In may day-to-day job, I often have to create SQL files containing minor changes (no DDL operation usually) in order to migrate those to production databases easily. The problem is, even with syntax ...
Zoltán Schmidt's user avatar
4 votes
1 answer
141 views

Inner procedure is rolled back but outer procedure is still committed

I have an outer procedure which calls 5 different inner procedures something like Parent procedure(Tran/try/catch) Inner sp1 (Tran/try/catch) ' ' Inner sp5 (Tran/try/catch) But in this case, if one ...
user186949's user avatar
2 votes
1 answer
465 views

Transaction rollback hanging from past two months(MSSQL 2016)

I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL ...
swchen's user avatar
  • 131
18 votes
2 answers
4k views

Disk space full during insert, what happens?

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb ...
HoneyBadger's user avatar
5 votes
2 answers
8k views

How a Transaction is atomic?

I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query....
user170514's user avatar
1 vote
4 answers
10k views

how to test query

I would like to run a query but just to test it and have the possibility to go back to previous state I saw rollback transaction. do I have just to run this command before ? delete * from myTables ...
Lika's user avatar
  • 21
0 votes
2 answers
435 views

Keep a history of transactions for easy rollback

Is there a database (either SQL or NoSQL) which allows you to keep a history or log of all past transactions so that you can easily rollback to any given point in time (similar to how git manages a ...
Cyker's user avatar
  • 125
2 votes
1 answer
3k views

How to immediately terminate a SPID with a very long rollback time estimated?

I have a script to restore a database from a backup. Prior to the restore command, it has: set single_user with rollback immediate The script seemed to be hanging here. I used sp_who2 to find out a ...
youcantryreachingme's user avatar
4 votes
2 answers
301 views

Can independent applications cause roll back to each other's transactions?

I have a database built on SQL Server Application-1 [Legacy App]: Written in C#, and runs transactions on the above database. Application-2 [New application being developed]: Written in Python, ...
Amit Tomar's user avatar
2 votes
1 answer
2k views

What will happen with MySQL transaction if one of the queries fails?

I have 500 independent insert queries. I put them in a transaction to run faster. I am runing queries with PHP. What will happen if one of queries fails? Will the transaction be rolled back ...
Davit Huroyan's user avatar
1 vote
1 answer
917 views

mssql revert changes made today without backup

A bug in an app made some changes now i need to rollback without a backup Can i use the logfile for this ? Is 'ROLLBACK' another option ? INFO: -My Recovery model is set to simple -AutoCommit hasnt ...
Mrparkin's user avatar
2 votes
2 answers
1k views

Cancelling the rebuilding of a partition with compression

Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already). What is the impact of cancelling the operation and letting it roll back? ...
Greg's user avatar
  • 574
0 votes
2 answers
14k views

Why am I getting a syntax error when trying to create a stored procedure in MySQL? [closed]

I would like to use a transaction to call upon a stored procedure on many rows. If it fails on a single row, I want to roll back everything. Otherwise I want to commit. I have the following so far ...
MSJ's user avatar
  • 1
4 votes
2 answers
22k views

ROLLBACK after COMMIT

I have multiple database sessions from which one has been blocking others because of a frozen application. There are transactions running on multiple sessions. I need to kill the head blocker because ...
Martin's user avatar
  • 187