TRANSACTION

MySQL

Example

START TRANSACTION;

INSERT INTO Students (Name, Class, Mark) VALUES ('John Doe', '10th', 85);

INSERT INTO Students (Name, Class, Mark) VALUES ('Jane Smith', '10th', 90);

COMMIT;

Output

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 0 row affected (0.01 sec)

Explanation

The example demonstrates a transaction in MySQL. The START TRANSACTION command is used to initiate a new transaction. Two INSERT statements follow, each adding a new row to the Students table. The COMMIT command concludes the transaction, applying all changes made since START TRANSACTION. If any command within the transaction encounters an error, all changes made during the transaction would be discarded in order to maintain the database’s consistency.

PostgreSQL

Example

BEGIN;
  INSERT INTO Customers (CustomerName, ContactName, City, Country)
  VALUES ('Cardinal', 'Tom B. Erichsen', 'Stavanger', 'Norway');
  INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)
  VALUES ((SELECT customerid FROM Customers WHERE CustomerName = 'Cardinal'), 5, date('2020-11-18'));
COMMIT;

Output

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT

Explanation

The given code is a simple SQL transaction. A transaction starts with BEGIN and ends with COMMIT. In this transaction, a new customer ‘Cardinal’ is inserted into the ‘Customers’ table, and an order for ‘Cardinal’ is inserted into the ‘Orders’ table. If there is an error in any step between BEGIN and COMMIT, the transaction will not be finalized.

SQL Server

Example

BEGIN TRAN;

UPDATE Employees
SET salary = salary + 500
WHERE id = 101;

COMMIT;

Output

(1 row(s) affected)

Explanation

In the given example, a SQL Server transaction is started with the BEGIN TRAN; command. A UPDATE operation is then performed on the Employees table, increasing the salary by 500 for the employee with the id of 101. The COMMIT; command is used to end the transaction and make the changes permanent. If there was an error in the execution, the changes would not be saved to the database.

SQLite

Example

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS test (
  id INTEGER PRIMARY KEY,
  name TEXT
);

INSERT INTO test (name) VALUES ('John');

COMMIT;

Output

Query OK, 0 rows affected
Query OK, 1 row affected

Explanation

This example demonstrates a SQL transaction in SQLite. Initialization of the transaction is done using BEGIN TRANSACTION;. Within the transaction, a table test is created if it does not exist and a row is inserted into the test table. The transaction is then committed using COMMIT;. If any error occurred during the transaction, the changes would be rolled back and not committed. Otherwise, the changes are committed and will be permanent in the database.