COMMIT

Example

START TRANSACTION;

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

COMMIT;

Output

Query OK, 1 row affected (0.02 sec)

Explanation

In the provided example, a transaction is initiated by START TRANSACTION;. Then an INSERT statement is executed to add new records into the employees table. At the end, the COMMIT; statement is used to save all modifications made since the start of the transaction. The output corresponds to MySQL’s acknowledgment to the successful insertion of data.

Example

BEGIN;
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
COMMIT;

Output

INSERT 0 1

Explanation

In this example, a new record is inserted into the ‘employees’ table with ‘John’ as ‘first_name’ and ‘Doe’ as ‘last_name’. The COMMIT statement is used for making the changes permanent in the database. Here the insert operation is enclosed between BEGIN; and COMMIT; which represent a transaction. After the commit, the result ‘INSERT 0 1’ indicates that one record was successfully inserted.

Example

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 50000 WHERE ID = 1;
COMMIT;

Output

No explicit output is generated by the COMMIT command. However, the result of above query would be the change in Salary for the employee with ID = 1.

Explanation

In the provided example, first a transaction is started with BEGIN TRANSACTION, then an UPDATE command is used to change the salary of the employee with ID = 1. Finally, the COMMIT statement is used to save these changes permanently in the database. If an error occurred during the transaction, COMMIT would not be executed, and all changes within the transaction would be rolled back. Thus, COMMIT ensures that only successful transactions are saved in the database.

Example

UPDATE employees
SET salary = salary + 500
WHERE employee_id = 100;
COMMIT;

Output

Commit complete.

Explanation

The COMMIT command in the example saves the changes made by the UPDATE statement. The UPDATE statement increases the salary of an employee with employee_id = 100 by 500. After executing the COMMIT command, the UPDATE command’s changes are permanently stored in the database. If COMMIT wasn’t executed, the changes could be rolled back.

Example

BEGIN TRANSACTION;

UPDATE Employees SET Salary = 50000 WHERE EmployeeId = 1;

COMMIT;

Output

Query OK, 1 row affected (0.01 sec)

Explanation

In this example, a SQL transaction begins with BEGIN TRANSACTION, and an UPDATE statement is executed to modify the salary of an employee whose ID is 1 within the transaction. If the UPDATE statement is executed successfully, the COMMIT statement is used to save the changes. The output indicates that one row has been affected by the update operation, meaning the transaction was successful.