> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/sql-fundamentals/sql-commands/tcl-transaction-control-language.md).

# TCL (Transaction Control Language)

TCL (Transaction Control Language) commands are used to manage transactions, which are a series of database operations treated as a single unit. These commands ensure data integrity by controlling how changes are committed or rolled back.

## COMMIT

Makes all changes performed within a transaction permanent. This saves the changes to the database.

* Syntax

```
COMMIT;
```

* Example

```
-- Insert data into two tables within a transaction
BEGIN
  INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (2023051401, 1001, SYSDATE);
  INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (2023051401, 1234, 2);
  COMMIT;
END;
```

## ROLLBACK

Undoes all changes made within a transaction, reverting the database to its state before the transaction began.

* Syntax

```
ROLLBACK;
```

* Example

```
BEGIN
  INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (2023051402, 1002, SYSDATE);
  INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (2023051402, 5678, 10);
  ROLLBACK;
END;
```

{% hint style="info" %}
In Oracle and most relational database systems, transactions do function as atomic units. This means all changes within a transaction are treated as a single entity. If any statement within the transaction fails due to an exception, the entire transaction automatically rolls back, undoing all the changes attempted within that transaction block.
{% endhint %}

## SAVEPOINT

Creates a named point within a transaction. We can optionally roll back to a specific savepoint instead of the entire transaction.

* Syntax

```
SAVEPOINT savepoint_name;
```

* Example

```
-- Start a transaction
BEGIN
  -- Update first_name for employee with ID 1001
  UPDATE employees SET first_name = 'John' WHERE employee_id = 1001;

  -- Create a savepoint
  SAVEPOINT before_update_1002;

  -- Update first_name for employee with ID 1002
  UPDATE employees SET first_name = 'Jane' WHERE employee_id = 1002;

  -- Commit the transaction
  COMMIT;
EXCEPTION
  -- Handle exceptions
  WHEN OTHERS THEN
    -- Rollback to savepoint if an error occurs
    ROLLBACK TO before_update_1002;
    -- Handle the error (print error message, log, etc.)
    DBMS_OUTPUT.PUT_LINE('An error occurred. Rolling back to savepoint.');
END;
```

## SET TRANSACTION

Defines isolation level and other transaction characteristics. Used for advanced control over transaction behavior.

* Syntax

```
SET TRANSACTION [isolation_level] [other_parameters];
```

* Example

```
-- Setting transaction isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/sql-fundamentals/sql-commands/tcl-transaction-control-language.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.