Questions tagged [rollback]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
37 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 ...
0
votes
2answers
24 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.
0
votes
1answer
30 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; -...
2
votes
1answer
38 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]...
7
votes
4answers
928 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 ...
1
vote
1answer
22 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 ...
1
vote
1answer
47 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 ...
0
votes
1answer
28 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 ...
1
vote
1answer
36 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) ...
0
votes
1answer
77 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
2answers
343 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 * ...
0
votes
2answers
496 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
0answers
20 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 ...
4
votes
1answer
124 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 ...
2
votes
1answer
216 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 ...
18
votes
2answers
2k 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 ...
1
vote
2answers
572 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....
1
vote
4answers
4k 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 ...
0
votes
2answers
60 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 ...
0
votes
1answer
607 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 ...
3
votes
2answers
94 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, ...
1
vote
1answer
590 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 ...
1
vote
1answer
681 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 ...
2
votes
2answers
245 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? ...
0
votes
2answers
5k 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 ...
4
votes
2answers
13k 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 ...
2
votes
1answer
801 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 ...
1
vote
0answers
94 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 ...
0
votes
3answers
100 views

after executing a back up cmd (via query) how to kill it

I have a big database (150 GB) that needs a back up and I don't have space in current machine, so I am pointing it to the different box which has space. After executing the cmd, it is executing the ...
5
votes
2answers
895 views

Why did a SQL Server Restart roll-back seemingly committed transactions?

I am using SQL Server 2014 Express It seems that restarting SQL Server when an event such as a Data Lock happens is not (always) such a good idea. http://www.sqlpassion.at/archive/2016/08/08/...
1
vote
1answer
90 views

Full backup of Simple recovery model - how much log is backed up if there is an open transaction

I have a situation right now. There is a database in SIMPLE recovery model. Note: 1. Compression is not enabled which will be enabled pretty soon. 2. SQL Server 2012 SP3 BackupSize: 8GB (Usually)&...
4
votes
2answers
593 views

ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES

Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode? I frequently add new filegroups/files to a 24/7 production database ...
1
vote
2answers
6k views

Mysql rollback and triggers

How does mysql triggers and rollback working together? I need to know how this is working together? I can rollback if i proceed with more than one sql statement because it is in same query but it ...
3
votes
2answers
13k views

Resume a suspended task

I was applying an index to a database with archived data. I knew this would take quite a while. It was at 14 hours running when I left it last night. I came back to it this morning and enterprise ...
6
votes
3answers
7k views

SQL Transactions: When do I Roll Back?

This is probably a simplistic question, but I would like to check on the mechanics of rolling back a transaction. As far as I understand, the steps are as follows: BEGIN TRANSACTION (or equivalent) ...
1
vote
1answer
2k views

Find history of rollbacks on SQL Server 2012 [duplicate]

Is there a way to find the history of any and all rollbacks that happened on a SQL Server database? Specifically, I would like to know the time and the user. Thanks.
0
votes
1answer
103 views

Exclude procedure call from transaction rollback

How can I exclude a statement in a transaction rollback? Begin Insert into table a ... ... call proc_1() ... ... Rollback How can I rollback all the commands except/prevent proc_1() from rolling ...
6
votes
2answers
2k views

Tracking down a transaction rollback

I have a very busy database and I've been asked to look for transactions that rollback. I used SQL Server Profiler to monitor the TM: Rollback Tran completed event, which shows me that dozens of ...
0
votes
2answers
688 views

Is dirty-read a problem in an always-commit database?

Is this statement true: If a DB engine disallows rollback and always uses a redo log to enforce commits, then dirty-reads never happen (because all changes will be commited finally). If so, are ...
1
vote
2answers
889 views

Transaction Error in TRY CATCH block

The following code returns this message in case something goes wrong: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current ...
1
vote
1answer
3k views

Why does a rollback of a CREATE INDEX statement take longer than expected?

Recently, I needed to apply a clustered index to a table. I first performed the operation in a Staging environment. The operation completed in about 20 minutes, so we planned for PROD to take ...
1
vote
1answer
769 views

Can I avoid Rollback with drop table?

I had a very long query running that was causing everything to get stuck. (was running for around 24 hours before being killed) INSERT INTO tbl_new SELECT * FROM tbl_old So I killed it. Now I don't ...
2
votes
3answers
3k views

Truncate temporary table commits transaction?

Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction? ...
0
votes
1answer
278 views

Dependent Transaction in separate Database-Connections

I am quite new to database and I am in the following situation. I have two tables defining a 1:n relation, let's say Company and Employee which I access in two separated database connections in ...
1
vote
1answer
560 views

SQL Server Rollback nested Stored procedures and linked servers

I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to ...
2
votes
1answer
1k views

How costly are InnoDB rollbacks?

I recently heard a fellow co-worker mention that InnoDB rollbacks are costly and negatively impact performance. How true is this statement? To give a context, we use spring+hibernate and set @...
5
votes
1answer
628 views

During rollback, is lock escalation reversed

Generally, SQL Server will take locks during updates. It also supports Lock Escalation: Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing ...
0
votes
1answer
1k views

How to recover auto committed transaction in Oracle with normal privilage

I have updated the table & need to rollback that changes. I have tried Rollback work command but data still not rollback. I have also tried Flashback command but it is not working because "row ...
3
votes
2answers
128 views

SQL Server - What access do I have during a transaction rollback?

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...
3
votes
0answers
1k views

InnoDB crashes on trx rollback

I have InnoDB assertion failure during the trx rollback 160316 10:47:31 InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo 160316 10:47:31 InnoDB: Assertion failure in thread ...