How can you use the COMMIT and ROLLBACK statements in SQL?

In SQL, the COMMIT and ROLLBACK statements are used to manage transactions, ensuring the ACID (Atomicity, Consistency, Isolation, Durability) properties of database operations. These statements are used to confirm or cancel the changes made within a transaction.

Here's how you can use the COMMIT and ROLLBACK statements:

1. 1.COMMIT Statement:

   • 
     

• The COMMIT statement is used to permanently save the changes made during a transaction. It marks the end of a successful transaction and makes all the changes within the transaction permanent.
• Once you issue a COMMIT statement, there's no way to undo the changes made in the transaction.
• It is typically used when a transaction has executed successfully without any errors, and you want to ensure that the changes are saved permanently.

1. Syntax:

   COMMIT;
   

   Example:

   BEGIN TRANSACTION;
   -- SQL statements here
   COMMIT;
   

2. 2.ROLLBACK Statement:

   • 
     

• The ROLLBACK statement is used to undo or cancel all the changes made during a transaction. It effectively rolls back the transaction to its starting point.
• ROLLBACK is used when a transaction encounters an error or doesn't complete as expected. It ensures that no changes are saved to the database.

1. Syntax:

   ROLLBACK;
   

   Example:

   BEGIN TRANSACTION;
   -- SQL statements here
   -- An error occurs
   ROLLBACK;
   

Using COMMIT and ROLLBACK:

To use the COMMIT and ROLLBACK statements effectively, follow these guidelines:

1. Begin a transaction with BEGIN TRANSACTION or START TRANSACTION before executing the SQL statements within the transaction.

2. Execute the necessary SQL statements within the transaction.

3. If the transaction completes successfully without errors, issue a COMMIT statement to make all changes permanent.

4. If any error occurs during the transaction or if you decide to cancel the changes, issue a ROLLBACK statement to undo the changes made within the transaction.

Here's a simplified example of a SQL transaction using COMMIT and ROLLBACK:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
INSERT INTO transaction_history (account_id, transaction_type, amount) VALUES (123, 'Withdrawal', 100);
-- Check for error condition 
IF some_condition THEN 
ROLLBACK; -- Cancel the transaction

ELSE 
COMMIT;   -- Save the transaction

END IF;

In this example, if some_condition evaluates to TRUE, the transaction is rolled back, and the changes are not saved. If it's FALSE, the transaction is committed, and the changes become permanent.