Commit in SQL

Commit and Rollback are SQL commands frequently used when implementing transactional statements that enable the user to complete or undo the SQL transaction. Before understanding the functioning of the Commit command in SQL, we must first understand what transaction means. A transaction is a logical term that constitutes arranged instructions or queries to complete a transaction. Each transaction begins with a specific objective. It starts with performing a specific task and specific groups of tasks to ensure that the objective is completed.

If any task required to perform the transaction fails, the entire transaction is considered a failure. All the queries and tasks should be executed entirely for a transaction to be complete in SQL. The steps taken to perform any transaction are as follows:

1. Start the transaction
2. Set the transaction
3. Commit the Transaction
4. Rollback and savepoint of the transaction

In this tutorial, we will only discuss the implementation of the Commit command in SQL.

What is Commit in SQL?

A commit is an SQL command that enables the user to permanently save the current transactions or database statements in the relational database or the transaction table. It is necessary to ensure the transaction is successfully executed and modifications are applied to the database. Once the Commit command is executed in the transactions, the changes that occurred during the transaction become permanent to all users with access to the database. Since the changes are permanently saved in the database, it is impossible to go back to the previous states in which the database began before the transaction began in the system.

Syntax for Commit

The syntax to commit the data in the database is as follows:

Implementation of Commit

Consider an Employee table that stores Employee ID, name, age, and address. The table is as follows:

Add the data in the following table:

Output:

Consider a situation where the user want to remove the record of the employee with first name Varun. After performing the deletion operation in the table, the user can implement the Commit statement to permanently save the changes in the database for all the users that can access it.The query to implement the above operations are as follows:

To ensure that the changes are committed permanently on the database. The user can implement the Select query to fetch all the remaining entries from the database. The query is as follows:

Output:

In the above table, when the COMMIT query is executed, it permanently saves the EMPLOYEES table changes, which are visible to all database users.

How Does COMMIT Work in SQL?

The COMMIT is the fundamental command when performing transactions, enabling users to save the modifications performed by the specific transaction in a DBMS. It ensures that all the modifications performed before committing the database changes are permanent. It also ensures that all of the modifications can be rolled back.

Traditional commands such as COMMIT and ROLLBACK follow the ACID properties in MySQL. The ACID properties include:

Atomicity: This property ensures whether the transactions are completed and executed. The transactions cannot be partially implemented. For instance, when the user sends money from one account to another. All the tasks in the transaction must be completed in full. It means the money will be debited from the first account and credited to the second, or the transfer will not happen. That is, neither will the money be debited nor credited from the accounts.

Consistency: Transactions should maintain consistency in the database. It means if a change is performed in a database table, it should also be performed on the associated table. For example, if the user has booked a train ticket online, then the details necessary for the reservation should be updated in all the associated tables in the database. It ensures that the information stored is consistent throughout the database.

Isolation: It ensures that changes performed by one transaction do not affect other transactions.

Durability: The changes are permanent and durable once a transaction is committed. For example, adding a new record to a database will remain even if the system crashes.

How to Use COMMIT in SQL Server?

The syntax to implement the COMMIT statement in SQL Server differs from the syntax used in SQL.

Syntax For COMMIT in SQL Server

The syntax to implement COMMIT in SQL Server is as follows:

The above syntax starts with implementing the BEGIN TRANSACTION SQL statement, as it enables the user to start a new transaction. The next step is to use a set of SQL statements. These statements determine the task performed in the transactions. Once all the SQL statements are executed, the next step is to save the transactions. The last statement executed in the syntax is the COMMIT transaction, which ensures that all the changes after the transaction are permanently saved in the database.

Implementation of SQL COMMIT Statement

Before implementing the COMMIT statement, the user will need a table to implement SQL commands. Execute the following SQL statements to create an Employee table in the database.

Add Data in the above Employee table:

Implementing COMMIT statement With DELETE command

Implementing COMMIT statement With INSERT command

Output: