What is the SQL COMMIT command?

Overview

In Relational Database Management, when a DMLData manipulation language query operation that causes a change in a database is carried out, you can simply say that a transaction has occurred. These types of transactions regularly occur on relational databases.

There are also instances where a particular record is accessed simultaneously by different users. RDBMs have a way to keep changes performed by a particular user only visible to them until they are done with their transaction in the database.

If an action is performed during a particular transaction and it needs to reflect in all ongoing transactions on the same database, you can use the SQL COMMIT command.

The COMMIT command

The COMMIT command saves all the transactions that take place since the last COMMIT or ROLLBACK command to the database. With this command, every copy of the database record will get this change as the new state of the database.

Basic syntax

COMMIT

Example

Let’s use the garments table to see a use case for the COMMIT command.

Say a user makes a change to this table with a query like the one below:

DELETE FROM garments WHERE size ='XL';

The table will now look as follows:

It’s possible for this change to only be visible to the transaction that made it, and not available to those that have an ongoing transaction. To avoid such situations, we can modify the DELETE query from above as follows:

DELETE FROM garments WHERE size ='XL';
COMMIT;

This modification to the code will make sure that every ongoing transaction gets the new update made to the database.