Questions tagged [rollback]
Ending a transaction by reverting to the changes made by the transaction.
129
questions
0
votes
1
answer
38
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 ...
- 437
-1
votes
1
answer
45
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
...
- 1
0
votes
0
answers
81
views
Stuck in Rollback of restore operation
I have started a restore operation, in which backup file is in Backup Server. After an hour I cancelled the operation; in order to copy the file into the local server and start the operation again. ...
- 210
0
votes
1
answer
895
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 ...
- 3
0
votes
2
answers
89
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 ...
- 411
0
votes
2
answers
1k
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 ...
2
votes
1
answer
2k
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 ...
- 23
0
votes
3
answers
581
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. ...
- 216
0
votes
2
answers
930
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 ...
- 103
-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 ...
- 103
1
vote
1
answer
167
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 ...
- 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 | @...
- 2,602
0
votes
0
answers
353
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: ...
- 180
1
vote
1
answer
622
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 ...
- 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 ...
- 121
0
votes
1
answer
822
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 ...
0
votes
1
answer
498
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 ...
- 21
0
votes
1
answer
139
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 ...
- 3
0
votes
1
answer
446
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 ...
- 155
0
votes
1
answer
520
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 ...
0
votes
1
answer
122
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 ...
- 43
1
vote
2
answers
71
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 ...
- 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 ...
- 123
0
votes
2
answers
943
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.
- 157
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;
-...
- 29
2
votes
1
answer
4k
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]...
- 51
8
votes
4
answers
2k
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 ...
- 183
2
votes
1
answer
554
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 ...
- 467
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
...
- 35.2k
0
votes
1
answer
53
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 ...
- 382
2
votes
1
answer
713
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)
...
- 453
0
votes
1
answer
638
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 ...
3
votes
2
answers
1k
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 *
...
- 691
0
votes
2
answers
5k
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 ...
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 ...
- 101
4
votes
1
answer
138
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 ...
- 49
2
votes
1
answer
444
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 ...
- 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 ...
- 361
4
votes
2
answers
7k
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....
- 41
1
vote
4
answers
9k
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
...
- 21
0
votes
2
answers
394
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 ...
- 125
2
votes
1
answer
2k
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 ...
- 1,605
4
votes
2
answers
281
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, ...
- 183
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 ...
- 123
1
vote
1
answer
889
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 ...
- 63
2
votes
2
answers
972
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?
...
- 564
0
votes
2
answers
13k
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 ...
- 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 ...
- 187
2
votes
1
answer
2k
views
Set recovery mode in mysql database
I've been reading about different recovery modes you can set on sql databases, for example here: https://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/
So I've been trying to find ...
- 123
1
vote
0
answers
260
views
mariadb won't restart after storage space induced crash
I have a large mariadb instance running (400 gb or so).
Due to running out of storage space it crashed.
I went ahead and resized my volume (I am using AWS), and went to restart the service service ...
- 121