SQL COMMIT

Introduction to SQL COMMIT

A COMMIT command in Structured Query Language(SQL) is a transaction command that is used to save all changes made by a particular transaction in a relational database management system since the last COMMIT or ROLLBACK command. It signifies the end of a successful transaction. All the data or system modifications made by the COMMIT command since the beginning of transactions are permanent in nature and they cannot be undone or rollbacked, as a successful COMMIT operation frees all the involved transaction resources. Therefore, it should be the prime responsibility of a database developer that he or she performs a COMMIT operation only in cases when the operation is sequentially and logically correct, otherwise, he or she might end up losing the major relationships in the database.

Syntax and parameters:

The basic syntax for using a COMMIT command in SQL SERVER is as follows :

BEGIN TRANSACTION;
{a set of SQL statements};
COMMIT TRANSACTION;

A more simplified version of syntax for other relational databases like MYSQL is as follows :

{a set of SQL statements};
COMMIT;

The parameters used in the above syntax are:

1. BEGIN TRANSACTION: This marks the beginning of operations or changes for the transaction.

2. {a set of SQL statements}: This is the section where you mention the task that has to be committed.

3. COMMIT: COMMIT is a SQL transaction statement that is used to save the changes made by the SQL statements in the previous step permanently in the database.

How does COMMIT work in SQL?

A COMMIT command in SQL is an essential command that is used after Data Manipulation Language (DML) operations like INSERT, DELETE and UPDATE transactions. Transactions in SQL are a set of SQL statements.When you perform a DML operation without a COMMIT statement, the changes are visible only to you. You can use a SELECT statement and check the updated records from the modified data. But once you use a COMMIT command after a transaction, the changes in the table or database are visible to other database users.

All the transaction commands like ROLLBACK and COMMIT in SQL obeys the basic principles of ACID properties.

Given below are the basic properties:

• Atomicity: Either the entire transaction will be performed or nothing from that transaction is performed. It means there’s nothing like partial transactions.
• Consistency: It ensures consistency in the database. For example, if you have returned a book to a library, then the details of the book will be updated in all the related tables across databases.
• Isolation: The results of a partially completed transaction are not visible to other users.
• Durability: All the changes made by a COMMIT transaction are permanent in nature.

Here we will take the employees table( this contains the personal details of all the employees).

The data in the “employees” table is as follows:

employeeid	lastname	firstname	departmentid	gender	city	salary	create_at
10028	Becker	Todd	4001	Male	Manhattan	15000	2007-01-03
10026	Sharma	Deepak	4002	Male	New Delhi	12876	2007-12-03
10027	Tobby	Riya	4002	Female	Manhattan	13456	2006-01-03
10024	Krishna	Lina	4001	Female	Oslo	12000	2006-01-02
10022	Mayers	David	4003	Male	Manhattan	15000	2002-01-31
10023	Jackson	David	4002	Male	Manhattan	16543	2001-12-31

Examples of SQL COMMIT

Given below are the examples of COMMIT transaction command:

Example #1

Program to illustrate the use of COMMIT command on a DELETE statement.

Code:

BEGIN TRANSACTION;
DELETE FROM employees
WHERE employeeid = 10022;
COMMIT TRANSACTION;

Output:

Example #2

Program to illustrate the use of COMMIT command on an INSERT statement.

Code:

BEGIN TRANSACTION;
INSERT INTO employees(employeeid,lastname,firstname,gender,salary,city,create_at)
VALUES(10030,'Woods','Charles','Male','14567','New Delhi','2005-12-31');
COMMIT TRANSACTION;

Output:

For the above example, we can check if the insert operation has been successfully completed or not, using a select query. We can see that a new row has been created for employee id ‘10030’.

Code:

select * from employees;

Output:

Example #3

Program to illustrate the use of COMMIT command on an UPDATE statement.

Code:

BEGIN TRANSACTION;
UPDATE employees
SET departmentid = '4002'
WHERE employeeid = 10030
COMMIT TRANSACTION;

Output:

For the above example, we can check if the update operation has been successfully completed or not, using a select query. We can clearly see that the department id has been updated.

Code:

select * from employees;

Output:

Example #4

Program to illustrate the use of COMMIT command on a nested transaction.

Code:

--Starting of transaction 1
BEGIN TRANSACTION
INSERT INTO employees
(employeeid,departmentid,city,salary,gender,create_at,firstname,lastname)
VALUES
(10031,'4003','Manhattan','14325','Female','2012-03-03','Priyanka','M')
--Starting of transaction 2
BEGIN TRANSACTION
INSERT INTO employees
(employeeid,departmentid,city,salary,gender,create_at,firstname,lastname)
VALUES
(10034,'4001','Manhattan','12325','Male','2015-08-03','Lucas','Martin')
COMMIT TRANSACTION
--Committing/Saving of transaction 2
COMMIT TRANSACTION
--Committing/Saving of transaction 1

Output:

In this example, we tried to illustrate commit on nested transactions. Using a SELECT query we can check that two rows have been created corresponding to the employee id 10031 and 10034.

Code:

select * from employees;

Output:

Example #5

SQL program to illustrate the use of COMMIT command on a nested transaction consisting of multiple tables.

Code:

--Starting of transaction 1
BEGIN TRANSACTION
UPDATE employees
SET departmentid = 4004
WHERE departmentid = 4003
--Starting of transaction 2
BEGIN TRANSACTION
DELETE FROM department
WHERE departmentid = 4003
COMMIT TRANSACTION
--Committing/Saving of transaction 2
--Starting of transaction 3
BEGIN TRANSACTION
INSERT INTO employees
(employeeid,departmentid,city,salary,gender,create_at,firstname,lastname)
VALUES
(10032,'4003','Manhattan','13425','Female','2014-02-03','Kristina','Saffiz')
COMMIT TRANSACTION
--Committing/Saving of transaction 3
COMMIT TRANSACTION
--Committing/Saving of transaction 1

Output:

From the screenshots below, we can see that all the required changes have been made in the desired order.

Code:

select departmentid, departmentname, head from department;

Output:

Code:

select * from employees;

Output:

Conclusion

COMMIT is a transaction command in SQL used primarily to save data manipulation changes(INSERT, DELETE and UPDATE) permanently. Once committed it makes changes visible to other users also. COMMIT should always be done with care as changes made once cannot be undone.

This is a guide to SQL COMMIT. Here we discuss the introduction, how does COMMIT work in SQL? and examples. You may also have a look at the following articles to learn more –

1. Cursors in PostgreSQL
2. T-SQL Commands
3. MySQL Queries
4. Uses of SQL
5. Guide to PostgreSQL COMMIT
6. Guide to PostgreSQL ROLLUP