Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [rollback]

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

1
vote
4answers
46 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
19 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
51 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 ...
0
votes
0answers
31 views

MSSQL All table data gone

What are some of the ways of knowing how the 100 M rows of record table got wiped out. Database is in Full recovery mode. I am not sure how doing daily backups are happening but before the truncate ...
2
votes
2answers
63 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
62 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
592 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 -...
0
votes
0answers
46 views

Availability group database and large rollback

I wonder if anyone have experience removing a database from an SQL Server 2016 AG during a huge rollback, currently at 30% and 25 000 seconds left to go. I think it would work and cause no harm but ...
2
votes
2answers
50 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
0answers
236 views

SQLite Concurrent threads - Using prepared statement and commit

I'm trying to understand a bit how SQLite handles concurrent access from multiple threads from a java application. I have a java application that when executed crunches some data and writes the ...
0
votes
2answers
576 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 ...
3
votes
2answers
4k 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
255 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
57 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
50 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 ...
4
votes
2answers
562 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
62 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
356 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
3k 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 ...
4
votes
2answers
6k 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 ...
5
votes
3answers
2k 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) ...
0
votes
1answer
525 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
72 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
812 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
516 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
495 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
1k 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
432 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 ...
1
vote
2answers
950 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
116 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
427 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
547 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 @...
4
votes
1answer
236 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
300 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
87 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
849 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 ...
0
votes
1answer
303 views

How to quantify rollback events in SQL Server between 2 dates

I need to quantify rollback events and find theirs reasons, without change the application code. There is any way to collect this data? The reason to check the rollback occurrences have need to ...
1
vote
1answer
152 views

Prepare MySQL for big UPDATE query on production server

Surprisingly I have not found anything relevant on this subject. What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details ...
6
votes
1answer
3k views

SQL Server Rollback being performed - how to stop it?

I have a query that takes 60 million rows, copies them in a new table, does some updates on them, then inserts them into another new table. It's all wrapped in a transaction. I thought this would be ...
1
vote
1answer
112 views

Report rollbacks to log file

There are several reasons for SQL transactions to rollback. I want to log all of them to the SQL Server Log File so that I can find information about them at a later time in the SQL Server Log File ...
5
votes
2answers
1k views

What are all the ways an open transaction will rollback?

When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions ...
2
votes
0answers
122 views

Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up. I have started the database with innodb_force_recovery=3 ...
13
votes
2answers
468 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
-1
votes
1answer
202 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
1
vote
0answers
51 views

Taking Backup of a database in mysql

I have database but somehow it has been deleted. Can i take the back-up of deleted database. Please Help. Thanks In Advance
1
vote
1answer
55 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
2
votes
2answers
8k views

Rollback Committed transaction in SQL Server

Is it possible to rollback a committed transaction? I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit ...
2
votes
2answers
443 views

Keep local variable after rollback in mysql

I wish to keep local variable from stored procedure even if rollback occurres. In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as arguments....
1
vote
0answers
1k views

MySQL Transaction with rollback upon @@error

I have done several transactions with MySQL but mainly using php's libraries as the handle. Using php you would have something as: $pdo->beginTransaction(); $pdo->execute(' query1; ...
0
votes
1answer
134 views

Copy a Large INNODB table in MySQL

I have an INNODB table that's > 93 million rows. A lot of the data is considered "temp" data and is governed by an "is_active" flag of 1/0. When a user updates a form, new data is written with an "...