Explicit Transactions

Let us take a look at the code below and break it down:


START TRANSACTION;
----SET TRANSACTION NAME 'NAME_OF_TRANSACTION';--- EITHER THIS OF THE PREVIOUS ONE MAY BE USED

SELECT * FROM tbl_employee WHERE employee_number = 123;

UPDATE TBL_EMPLOYEE SET employee_number = 122 WHERE employee_number = 123;

SELECT * FROM tbl_employee WHERE employee_number = 123;

ROLLBACK

This is an explanation of the code above.

 

  • START TRANSACTION;
    • This statement marks the beginning of an explicit transaction. It signifies that a series of SQL statements will be treated as a single unit of work, and changes made during this transaction can be either committed or rolled back.
  • SET TRANSACTION NAME 'NAME_OF_TRANSACTION';
    • This statement sets a name for the transaction, but it is optional. It provides a way to label the transaction for easier identification.
  • SELECT * FROM tbl_employee WHERE employee_number = 123;
    • This is a SELECT statement within the transaction, retrieving data from the tbl_employee table based on the condition employee_number = 123. This is a read operation and does not modify any data.
  • UPDATE TBL_EMPLOYEE SET employee_number = 122 WHERE employee_number = 123;
    • This is an UPDATE statement within the transaction. It modifies the value of employee_number from 123 to 122 in the TBL_EMPLOYEE table where the condition employee_number = 123 is met. This is a write operation.
  • SELECT * FROM tbl_employee WHERE employee_number = 123;
    • Another SELECT statement within the transaction, checking the current state of the tbl_employee table after the update. This is a read operation.
  • ROLLBACK;
    • This statement rolls back the entire transaction, undoing any changes made since the START TRANSACTION; statement. It discards the modifications made by the UPDATE statement, effectively canceling the transaction.

In summary, the code begins with the initiation of an explicit transaction, involves a couple of read and write operations on the tbl_employee table, and then concludes by rolling back the transaction, ensuring that no changes are permanently applied to the database.

 

it is very important to note that if you want the changes that you made with with a DML to stay, you MUST include a COMMIT; statement to make certain that the changes are saved.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top