Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [rollback]

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

Rollbacks can be either explicit or implicit and undo the changes that have occurred in the transaction since the transaction began or since the last save point if a specific rollback point is specified.

132 questions
27
votes
2 answers

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database alterations in a transaction locally (that was then…
sennett
  • 370
  • 4
  • 7
25
votes
4 answers

Can I change table structure in a transaction and then roll it back if there is an error?

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements if something goes wrong. Is this possible, or is…
Piers Karsenbarg
  • 959
  • 2
  • 12
  • 23
22
votes
4 answers

Why won't some DBMS's allow rollback for certain DDL statements?

Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it.. Are there technical reasons for not supporting…
Joril
  • 441
  • 1
  • 5
  • 10
21
votes
5 answers

Is ROLLBACK a fast operation?

Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
garik
  • 6,782
  • 10
  • 44
  • 56
18
votes
2 answers

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 which grows till the disk is full. This time it was a…
HoneyBadger
  • 361
  • 4
  • 10
14
votes
1 answer

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 | @tran_name_variable | savepoint_name |…
Just a learner
  • 2,082
  • 7
  • 36
  • 57
13
votes
2 answers

ROLLBACK doesn't work after INSERT INTO newly created destination table

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers). Before inserting contents of CSV-file into the mysql table I am first backing up the original customers table. I am wrapping whole import process…
Dimitry K
  • 235
  • 2
  • 4
  • 8
13
votes
2 answers

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 ALTER statement, but this make sure that the LDF…
ToC
  • 727
  • 1
  • 8
  • 20
11
votes
1 answer

Investigating rollbacks in Postgres

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have tried searching the Postgres log file but it doesn't…
aco
  • 211
  • 2
  • 3
11
votes
2 answers

MongoDB Replica Set SECONDARY Stuck in `ROLLBACK` State

During a recent automated update of our mongodb PRIMARY, when the PRIMARY stepped down it permanently went into a ROLLBACK state. After several hours in the ROLLBACK state, there was still no rollback .bson file in the rollback directory in the…
Chris W.
  • 591
  • 3
  • 6
  • 17
10
votes
2 answers

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a huge data set (300M rows) into an InnoDB table. I did…
dasup
  • 223
  • 1
  • 2
  • 7
8
votes
4 answers

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 constraint which is not enabled for checking and I…
David Woakes
  • 183
  • 1
  • 4
8
votes
3 answers

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) One or more SQL statements COMMIT or ROLLBACK As…
Manngo
  • 3,065
  • 10
  • 38
  • 61
7
votes
2 answers

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or corrupted table if something fails. This is how I planned…
Clarkey
  • 425
  • 2
  • 5
  • 6
7
votes
1 answer

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 good at first, but realize it's a dumb…
Andy DesRosiers
  • 173
  • 1
  • 4
1
2 3
…
8 9