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