MySQL – Transactions

Table of content

The MySQL Transactions

The MySQL transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction. This will ensure no data losses or failed executions of SQL queries.

Properties of Transactions

There are four standard properties of transactions, often referred to by the acronym ACID −

Atomicity − This ensures that all operations within a transaction are treated as a single unit. Either all the operations within the transaction are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database is left in its original state.
Consistency − This ensures that the database properly changes states upon a successfully committed transaction.
Isolation − This enables transactions to operate independently and transparent to each other.
Durability − This ensures that once a transaction is committed, its effects on the database are permanent and will survive system failures (e.g., power outages, hardware failures).

Transactional Statements in MySQL

In MySQL, the transactions begin with either START TRANSACTION, BEGIN or BEGIN WORK statements, and end with either a COMMIT or a ROLLBACK statement. The MySQL commands executed between the beginning and ending statements forms the main body of the transaction.

To enable or disable the auto-commit option in a transaction, you can use the SET AUTOCOMMIT command. To enable auto-commit, set the command to ”1” or ”ON,” and to disable it, set the command to ”0” or ”OFF.”

The COMMIT Command

The COMMIT command is a transaction control command in MySQL. When issued, it finalizes the changes made to a database table up to that point in a transaction, making those changes permanent. As a result, these changes become visible to other active sessions in MySQL.

Syntax

Following is the syntax to execute the COMMIT command in MySQL −

COMMIT;

Example

Let us create a table names CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

We are inserting some records into the above-created table −

INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The CUSTOMERS table displayed is as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Using the following query, start a transaction and delete the records from the CUSTOMERS table whose AGE is 25, then COMMIT the changes in the database −

START TRANSACTION;
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

Verification

Two rows from the table would be deleted. To verify, display the modified CUSTOMERS table using the following SELECT statement −

SELECT * FROM CUSTOMERS;

Following is the output obtained −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	4500.00
7	Muffy	24	Indore	10000.00

The AUTOCOMMIT Command

You can control the behaviour of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes.

When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.

The ROLLBACK Command

The ROLLBACK command is a transactional command used to undo changes made in a transaction that have not been saved (committed) to the database. This command can only reverse the effects of transactions made since the last COMMIT or ROLLBACK statement was executed.

Syntax

Following is the syntax for ROLLBACK command in MySQL −

ROLLBACK;

Example

Using the following query, delete the records from the CUSTOMERS table whose AGE is 25, then ROLLBACK the changes in the database −

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

Verification

The table will not be affected. To verify, display the modified CUSTOMERS table using the following SELECT statement −

SELECT * FROM CUSTOMERS;

Following is the table obtained −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

You must remember that ROLLBACK only works inside a transaction. If you try to execute it without starting a transaction, the changes would not be revoked.

The SAVEPOINT Command

A SAVEPOINT is a logical rollback point within a transaction in MySQL.

When you execute the ROLLBACK command, it reverts the changes made in the transaction back to the last COMMIT or the beginning of the transaction if there haven”t been any COMMITs. However, by creating save points within the transaction, you can establish specific points to which you can partially roll back the transaction. You can create multiple save points within a transaction to have multiple rollback options between two commits.

Syntax

The syntax for creating a SAVEPOINT command among transactions is as shown below −

SAVEPOINT SAVEPOINT_NAME;

The syntax for rolling back to the SAVEPOINT created is as follows −

ROLLBACK TO SAVEPOINT_NAME;

Example

In the following example, you plan to delete three different records from the CUSTOMERS table while creating SAVEPOINTs before each delete. This allows you to ROLLBACK to any SAVEPOINT at any time to restore the appropriate data to its original state −

SAVEPOINT SP1;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=1;
Query OK, 1 row affected (0.01 sec)

SAVEPOINT SP2;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=2;
Query OK, 0 rows affected (0.00 sec)

SAVEPOINT SP3;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=3;
Query OK, 1 row affected (0.01 sec)

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT identified as SP2. Since SP2 was created after the first deletion, this action undoes the last two deletions −

ROLLBACK TO SP2;

Verification

If you display the CUSTOMERS table using the following SELECT statement, you will notice that only the first deletion took place since you rolled back to SP2 −

SELECT * FROM CUSTOMERS;

The result obtained is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Transaction-Safe Table Types in MySQL

In MySQL, not all table types natively support transactions. To use transactions effectively, you should create your tables in a specific way. Although various table types are available, the most commonly used transaction-safe table type is InnoDB.

To enable InnoDB table support, you may need a specific compilation parameter during the MySQL source compilation. If your MySQL version doesn”t include InnoDB support, you can request your Internet Service Provider (ISP) to provide a MySQL version with InnoDB support, or you can download and install MySQL-Max Binary Distribution for Windows or Linux/UNIX to work with InnoDB tables in a development environment.

If your MySQL installation supports InnoDB tables, you can create an InnoDB table as shown below −

CREATE TABLE tcount_tbl (
   tutorial_author varchar(40) NOT NULL,
   tutorial_count  INT
) ENGINE = InnoDB;

Following is the output obtained −

Query OK, 0 rows affected (0.05 sec)

You can also use other table types like GEMINI or BDB, but it depends on your installation, whether it supports these two table types or not.

Transactions Using a Client Program

We can also perform transactions using the client program.

Syntax

To perform the transactions through a PHP program, we need to execute three statements: “START TRANSACTION”, “COMMIT”, and “ROLLBACK” using the mysqli function query() as follows −

$sql = "START TRANSACTION";
$mysqli->query($sql);
...
$sql = "COMMIT";
$mysqli->query($sql);
...
$sql = "ROLLBACK";   
$mysqli->query($sql);
...

To perform the transactions through a JavaScript program, we need to execute three statements “START TRANSACTION”, “COMMIT”, and “ROLLBACK” using the query() function of mysql2 library as follows −

sql = "START TRANSACTION";
con.query(sql);
...
sql = "COMMIT";
con.query(sql);
...
sql = "ROLLBACK";   
con.query(sql);
...

To perform the transactions through a Java program, we need to execute three statements “START TRANSACTION”, “COMMIT”, and “ROLLBACK” using the JDBC function execute() as follows −

String sql = "START TRANSACTION";
statement.execute(sql);
...
String sql = "COMMIT";
statement.execute(sql);
...
String sql = "ROLLBACK";   
statement.execute(sql);
...

To perform the transactions through a Python program, we need to execute three statements “START TRANSACTION”, “COMMIT”, and “ROLLBACK” using the execute() function of the MySQL Connector/Python as follows: −

connection.start_transaction()
...
connection.commit()
...   
connection.rollback()
...

Example

Following are the programs −

 $dbhost = ''localhost
 $dbuser = ''root
 $dbpass = ''password
 $db = ''TUTORIALS
 $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
 if ($mysqli->connect_errno) {
     printf("Connect failed: %s
", $mysqli->connect_error);
     exit();
 }
 //printf(''Connected successfully.
'');
 //start transaction
 $sql = "START TRANSACTION";
if($mysqli->query($sql)){
 printf("Transaction started....!n");
}
//print table record
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     printf("Table records after transaction...!n");
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row[''ID''],
         $row[''NAME''],
         $row[''AGE''],
         $row[''ADDRESS''],
         $row[''SALARY'']);
         printf("n");
     }
 }
 //let''s delete some records
 $sql = "DELETE FROM CUSTOMERS WHERE AGE  = 25";
 if($mysqli->query($sql)){
     printf("Records with age = 25 are deleted successfully....!n");
 }
 //lets delete some more records..
 $sql = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
 if($mysqli->query($sql)){
     printf("Records with salary = 2000 are deleted successfully....!n");
 }
 printf("Table data after second delete (before rollback)...!n");
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row[''ID''],
         $row[''NAME''],
         $row[''AGE''],
         $row[''ADDRESS''],
         $row[''SALARY'']);
         printf("n");
     }
 }
 $sql = "ROLLBACK";
 if($mysqli->query($sql)){
     printf("Transaction rollbacked successfully..!n");
 }
 printf("Table data after rollback: n");
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row[''ID''],
         $row[''NAME''],
         $row[''AGE''],
         $row[''ADDRESS''],
         $row[''SALARY'']);
         printf("n");
     }
 }
 if($mysqli->error){
     printf("Error message: ", $mysqli->error);
 }
 $mysqli->close();

Output

The output obtained is as shown below −

Transaction started....!
Table records after transaction...!
ID 1, NAME Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID 2, NAME Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID 3, NAME kaushik, AGE 23, ADDRESS Kota, SALARY 2000.000000
ID 4, NAME Chaitali, AGE 25, ADDRESS Mumbai, SALARY 6500.000000
ID 5, NAME Hardik, AGE 27, ADDRESS Bhopal, SALARY 8500.000000
ID 6, NAME Komal, AGE 22, ADDRESS MP, SALARY 4500.000000
ID 7, NAME Muffy, AGE 24, ADDRESS Indore, SALARY 10000.000000
Records with age = 25 are deleted successfully....!
Records with salary = 2000 are deleted successfully....!
Table data after second delete (before rollback)...!
ID 5, NAME Hardik, AGE 27, ADDRESS Bhopal, SALARY 8500.000000
ID 6, NAME Komal, AGE 22, ADDRESS MP, SALARY 4500.000000
ID 7, NAME Muffy, AGE 24, ADDRESS Indore, SALARY 10000.000000
Transaction rollbacked successfully..!
Table data after rollback:
ID 1, NAME Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID 2, NAME Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID 3, NAME kaushik, AGE 23, ADDRESS Kota, SALARY 2000.000000
ID 4, NAME Chaitali, AGE 25, ADDRESS Mumbai, SALARY 6500.000000
ID 5, NAME Hardik, AGE 27, ADDRESS Bhopal, SALARY 8500.000000
ID 6, NAME Komal, AGE 22, ADDRESS MP, SALARY 4500.000000
ID 7, NAME Muffy, AGE 24, ADDRESS Indore, SALARY 10000.000000

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "START TRANSACTION";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Transaction started....!");
});
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
    console.log("Table records after transaction...!");
    if (err) throw err;
    console.log(result);
    });
//delete record
sql = "DELETE FROM CUSTOMERS WHERE AGE  = 25";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Records with age = 25 are deleted successfully....!");
});
//now lets delete more records
sql = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Records with salary = 2000 are deleted successfully....!");
});
//print table records before rollback;
sql = "SELECT * FROM CUSTOMERS";
con.query(sql, function(err, result){
   console.log("Table Data After Second Delete (Before Rollback)");
   if (err) throw err;
   console.log(result);
   });
//rollback the transaction
sql = "ROLLBACK";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Transaction rollbacked successfully..!");
});
//print table data after rollback;
sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
    console.log("Table records after rollback...!");
    if (err) throw err;
    console.log(result);
    });
});

Output

The output obtained is as shown below −

Transaction started....!
Table records after transaction...!
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
Records with age = 25 are deleted successfully....!
Records with salary = 2000 are deleted successfully....!
Table Data After Second Delete (Before Rollback)
[
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
Transaction rollbacked successfully..!
Table records after rollback...!
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Transaction {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //start transaction
            String sql = "START TRANSACTION";
            st.execute(sql);
            System.out.println("Transaction started....!");
            //print customers record after starting transaction
            String sql1 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql1);
            System.out.println("Table records after starting transaction: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String age = rs.getString("age");
               String address = rs.getString("address");
               String salary = rs.getString("salary");
               System.out.println("Id: " + id + ", Name: " + name +   ", Age: " + age +   ", Address: " + address +  ", Salary: " + salary);
            }
            //lets delete some records
            String sql2 = "DELETE FROM CUSTOMERS WHERE AGE = 25";
            st.execute(sql2);
            System.out.println("Customer with age 25 deleted successfully...!");
            String sql4 = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
            st.execute(sql4);
            System.out.println("Customer with age 2000 deleted successfully...!");
            String sql5 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql5);
            System.out.println("Table records before rollback: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String age = rs.getString("age");
               String address = rs.getString("address");
               String salary = rs.getString("salary");
               System.out.println("Id: " + id + ", Name: " + name +   ", Age: " + age +   ", Address: " + address +  ", Salary: " + salary);
            }
            //lets roll-back the transaction
            String r = "ROLLBACK";
            st.execute(r);
            System.out.println("Transaction rollbacked successfully...!");
            String sql6 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("Table records after rollback: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String age = rs.getString("age");
               String address = rs.getString("address");
               String salary = rs.getString("salary");
               System.out.println("Id: " + id + ", Name: " + name +   ", Age: " + age +   ", Address: " + address +  ", Salary: " + salary);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

Transaction started....!
Table records after starting transaction: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Customer with age 25 deleted successfully...!
Customer with age 2000 deleted successfully...!
Table records before rollback: 
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Transaction rollbacked successfully...!
Table records after rollback: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Start the transaction
connection.start_transaction()
# Displaying the table before deleting
select_query = "SELECT * FROM customers"
cursorObj.execute(select_query)
print("Table Data after starting Transaction:")
for row in cursorObj.fetchall():
    print(row)
# Execute the DELETE statement
delete_query = "DELETE FROM customers WHERE AGE = 25"
cursorObj.execute(delete_query)
print("Rows with AGE = 25 are deleted.")
# Commit the transaction
connection.commit()
print("Transaction committed successfully.")
# Displaying the table after deleting (changes are permanent)
cursorObj.execute(select_query)
print("Table Data After Transaction:")
for row in cursorObj.fetchall():
    print(row)
# Now, let us delete more records
delete_query1 = "DELETE FROM customers WHERE SALARY = 2000"
cursorObj.execute(delete_query1)
print("Rows with SALARY = 2000 are deleted.")
# Display the table after the second delete operation (changes are not committed yet)
cursorObj.execute(select_query)
print("Table Data After Second Delete (Before Rollback):")
for row in cursorObj.fetchall():
    print(row)
# Rollback the transaction
connection.rollback()
print("Transaction rollbacked successfully.")
# Displaying the table after rollback (changes are reverted)
cursorObj.execute(select_query)
print("Table Data After Rollback:")
for row in cursorObj.fetchall():
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −

Table Data after starting Transaction:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(2, ''Khilan'', 25, ''Delhi'', Decimal(''1500.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(4, ''Chaitali'', 25, ''Mumbai'', Decimal(''6500.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Rows with AGE = 25 are deleted.
Transaction committed successfully.
Table Data After Transaction:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Rows with SALARY = 2000 are deleted.
Table Data After Second Delete (Before Rollback):
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Transaction rollbacked successfully.
Table Data After Rollback:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

Sign up for Newsletter

mysql

Khóa học miễn phí MySQL – Transactions nhận dự án làm có lương

MySQL – Transactions

The MySQL Transactions

Properties of Transactions

Transactional Statements in MySQL

The COMMIT Command

Syntax

Example

Verification

The AUTOCOMMIT Command

The ROLLBACK Command

Syntax

Example

Verification

The SAVEPOINT Command

Syntax

Example

Verification

Transaction-Safe Table Types in MySQL

Transactions Using a Client Program

Syntax

Example

Output

Output

Output

Output

user

Sign up for Newsletter

MySQL – Transactions

The MySQL Transactions

Properties of Transactions

Transactional Statements in MySQL

The COMMIT Command

Syntax

Example

Verification

The AUTOCOMMIT Command

The ROLLBACK Command

Syntax

Example

Verification

The SAVEPOINT Command

Syntax

Example

Verification

Transaction-Safe Table Types in MySQL

Transactions Using a Client Program

Syntax

Example

Output

Output

Output

Output

user

Login

Register