• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL – How to undo (rollback) a set of SQL statements

by admin

What is Conducting a Transaction?

If you have a series of SQL statements that you want to run which involve adding, deleting, or changing data contained in InnoDB or BDB tables, but want to be sure that all SQL statements or transactions are completed successfully before committing them, there is a set of MySQL statements that you can use to do this. This is known as conducting a transaction (i.e., the set of SQL statements to execute) and being able to perform a rollback. Currently, you can only rollback transactions on InnoDB and BDB tables in MySQL.

Disabling autocommit

1. The first step for performing a transaction that you may want to rollback is to disable autocommit mode, so that you are always operating within a transaction, or to explicitly begin a transaction.

2. Autocommit mode is enabled by default. To change the autocommit just for the current connection, enter the following SQL statement:

SET SESSION autocommit = 0;

Disabling autocommit like this will only affect the current client connection and not other client connections. It will last until you enable it again by setting the variable to 1 or when you close the current session.

3. If you want to explicitly begin a transaction, which you must do if you do not disable autocommit mode, you can use the following simple statement:

START TRANSACTION;

4. Now you may enter a series of SQL statements that collectively will make up the transaction. At any point, you may undo all of the statements for the transaction by entering the following SQL statements:

ROLLBACK;

5. If you don’t want to reverse a transaction and decide to commit the SQL statements executed, enter the following SQL statement:

COMMIT;

This ends the transaction. Note that some statements are not transactional and may not be rolled back, such as data definition statements CREATE TABLE, CREATE DATABASE, ALTER TABLE, DROP TABLE, and DROP DATABASE, for example. Some of these statements, in fact, implicitly commit the current transaction.

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to use mysqlsh to execute addInstance in silent mode
  2. Understanding MySQL Query Cache
  3. Backup and Restore Of Group Replication Node ( MySQL 8.0 )
  4. MySQL Server Error – “Can’t Create A New Thread (errno 11)”
  5. Changing the value of lower_case_table_names in MySQL 8
  6. Granting All Privileges On All databases Except One Specific Table in MySQL
  7. “Another MySQL daemon already running with the same unix socket” – error while starting MYSQL
  8. What is the purpose of “mysql.sys@localhost” user
  9. What are the various types of locking used in the MySQL Server
  10. Understanding mysqlcheck and myisamchk utilities

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright