What is a database transaction in SQL ?

---

What is a transaction

A transaction in a DBMS database is a logical unit of work containing a set of SQL statements. It relies on the atomic property meaning that this set of SQL statements is considered as one single instruction. Indeed, if only 1 statement out of a set of 20 SQL statements fails, we consider the transaction to be failed. The transaction will then take care of cancelling the effects of the other succesfull statements.

Let’s take for example a bank using a database. In fact, the transaction name comes from the fact that originally this concept was mainly used by banking database operation. Let’s say someone wants to transfer money to another bank account, the following process will happen :

• 1) The user selects the amount of money he wants to transfer
• 2) The bank checks that this user possess at least this amount of money
• 3) The bank substract this amount from this user account
• 4) The bank adds this amount in the destination account

The main point here is that we can’t allow one of those step to fail. Imagine if the 4th step failed, then the original user still lost a certain amount of money (step 3). We can’t allow this. If you convert those steps into SQL statements that’s basically what a transaction is. A transaction uses and apply the ACID database properties (Atomic, Consistent, Isolated and Durable).

---

How to use a transaction in SQL

There are rules for the type of SQL statements involved in the transaction. You can’t CREATE a table and INSERT data in a single transaction.

• DDL (Data Definition Language) commands like CREATE or DROP there is only one such command in a transaction.
• DCL (Data Control Language) commands like GRANT or REVOKE there is also only one such command in a transaction.
• DML (Data Manipulation Language) commands like INSERT or UPDATE there can be a non-restricted number of such commands in a transaction.

The following snippets can represent transactions in SQL :

// DDL
DROP TABLE Bankaccounts;
COMMIT;

// DCL
GRANT SELECT ON Employees TO user1
COMMIT;

// DML
UPDATE Bankaccounts SET Money = Money - 500 WHERE User = 'user1' ;
UPDATE Bankaccounts SET Money = Money + 500 WHERE User = 'user2' ;
COMMIT;

As you can see we are using the COMMIT command to confirm and execute a transaction. Until the COMMIT command all the previous SQL statements are done temporarily. The 2 UPDATE statements in the last code snippet will not really change the data until the COMMIT statement.

Another command named ROLLBACK is used to undo previous statements to the last executed COMMIT.