What is COMMIT in SQL?

The COMMIT command is an essential part of SQL for saving database changes. It's executed after a DML (Data Manipulation Language) statement, such as INSERT, DELETE, or UPDATE, to ensure permanent changes. Here's how it works:

• Transactions are a set of SQL commands executed as a single unit of work. Without the COMMIT command, any DML operations performed are only visible to you.
• When you execute the COMMIT command, the changes made in the database are saved and become visible to all users.
• You can use a SELECT statement to check the updated record or data from the table that is modified.

Thus, by using the COMMIT command, you can ensure that your SQL transactions are complete and the changes made to the database are permanent.

Syntax of COMMIT command in SQL:

It is important to note that the COMMIT command can only be used in conjunction with transactions. It cannot be used in isolation to save changes to the database. Transactions can be started using the BEGIN TRANSACTION or START TRANSACTION command, and can be rolled back using the ROLLBACK command.

Here's the syntax demonstrating the use of the COMMIT command with a transaction in MySQL:

The parameters used in the syntax are:

• START TRANSACTION: It is used for marking the beginning of changes or operations in a transaction.
• {a set of SQL statements}: It is used for mentioning the task that is supposed to be completed.
• COMMIT: It is used to save transactional changes made by SQL statements.

How Does COMMIT Work in SQL?

The COMMIT command is a fundamental transactional command used to save changes made by a particular transaction in a database management system. It ensures that all changes made before committing are permanent and cannot be rolled back. Transactional commands like COMMIT and ROLLBACK follow the ACID properties of transactions in SQL, which are:

• Atomicity: Transactions are executed fully or not at all. For example, when you transfer money from one bank account to another, the transaction must be completed in full. Otherwise, the transfer is not executed, and the money stays in the original account.
• Consistency: Transactions ensure consistency in the database. For instance, if you book a flight ticket online, the details regarding the booking of a ticket will be updated on all the related tables in the database. This ensures that the data is consistent across the database.
• Isolation: Transactions are executed in isolation, meaning that they are not visible to other users until they are committed. For example, if you are editing a record in a database, other users cannot see the changes until you commit the transaction.
• Durability: Once a transaction is committed, the changes made are permanent and durable. For example, when you add a new record to a database, it will remain in the database even if the system crashes.

How to COMMIT in SQL Server?

The syntax for COMMIT in SQL server is:

In the above syntax, BEGIN TRANSACTION is used for starting the transaction after which set of SQL statements are written. After statements are written, the transaction is commited. This transaction is then saved permanently into the database.

Examples of SQL COMMIT Command

Before starting, create a table name 'employee' which looks as follows:

Example 1: Using COMMIT with DELETE Command

Example 2: Using COMMIT with INSERT Command

Example 3: Using COMMIT with UPDATE Command