MySQL - 事務

要透過 JavaScript 程式執行事務，我們需要使用 **mysql2** 庫的 **query()** 函式執行三個語句：“START TRANSACTION”、“COMMIT”和“ROLLBACK”，如下所示：

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

要透過 Java 程式執行事務，我們需要使用 **JDBC** 函式 **execute()** 執行三個語句：“START TRANSACTION”、“COMMIT”和“ROLLBACK”，如下所示：

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

要透過 Python 程式執行事務，我們需要使用 **MySQL Connector/Python** 的 **execute()** 函式執行三個語句：“START TRANSACTION”、“COMMIT”和“ROLLBACK”，如下所示：

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

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);
    });
});   

輸出

獲得的輸出如下所示：

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://: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();
      }
   }
}   

輸出

獲得的輸出如下所示：

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() 

輸出

獲得的輸出如下所示：

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'))