> ## Documentation Index
> Fetch the complete documentation index at: https://docs.maia.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Transactions
export const maia = "Maia";
A database transaction is a series of one or more operations, such as reading, writing, or updating data, that are performed as a single unit of work. Transactions are a core concept in databases, designed to maintain data integrity and consistency. They comply with the following **ACID** properties:
* **Atomicity:** A transaction is all-or-nothing. Either all operations within the transaction are completed successfully, or none are. If any operation fails, the transaction is rolled back to its initial state.
* **Consistency:** A transaction ensures that the database transitions from one valid state to another, preserving the defined rules and constraints.
* **Isolation:** Transactions are executed independently, and the intermediate states of one transaction are invisible to others. This prevents conflicts in concurrent transactions.
* **Durability:** Once a transaction is successfully committed, its results are permanent, even in the event of a system failure.
A database transaction, initiated using the `BEGIN` statement, encapsulates multiple queries. The transaction is only committed when all the queries within it are executed successfully. If any query fails, the transaction is rolled back to ensure data integrity.
Below is an example of a database transaction:
```sql theme={null}
-- Start the transaction
BEGIN;
-- Deduct £100 from Account A
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';
-- Add £100 to Account B
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';
-- Commit the transaction to save changes permanently
COMMIT;
```
For more information about the core concepts of database transactions, read [Database transaction](https://en.wikipedia.org/wiki/Database_transaction#:~:text=A%20database%20transaction%20symbolizes%20a,way%20independent%20of%20other%20transactions).
***
## Video example
Expand this box to watch our video about Database transactions.
***
## Transactions in Maia
By using the [Begin](/docs/components/begin), [Rollback](/docs/components/rollback), and [Commit](/docs/components/commit) components, a user can create a database transaction. Within a transaction, the user can include the [SQL Script](/docs/components/sql-script) component to execute SQL commands and the [Run Transformation](/docs/components/run-transformation) component to run a transformation pipeline. After executing these steps, the user can use the **Commit** component to finalize the transaction, or the **Rollback** component to end the transaction, and undo any changes made since the latest **Begin** component was executed.
* Database transactions are available in {maia} for Snowflake and Amazon Redshift.
* Databricks doesn't use transactions but relies on Delta tables in a similar concept. As a result, database transactions won't be supported for Databricks.
***
## Notes on transactions
### Auto commit
**Auto Commit** is the default behavior in a pipeline. Any database changes made during execution are automatically committed as soon as each component completes. For more information, read [Implicit and explicit transactions](#implicit-and-explicit-transactions).
However, if transactional control is required, you can use the **Begin**, **Commit**, and **Rollback** components to explicitly manage database transactions. When these components are used, **Auto Commit** mode is temporarily disabled during the transaction. Changes made within the transaction are not committed until a **Commit** component is executed. Once the transaction is completed using either a **Commit** or **Rollback** component, the pipeline returns to **Auto Commit** mode until another **Begin** component is triggered.
### Implicit and explicit transactions
Implicit transactions and explicit transactions refer to the ways in which transactions are initiated and managed in a database system. Both are used to maintain data integrity and consistency, but they differ in how they are controlled by the user or system.
When using the **Begin** component in {maia}, this is initiating an explicit transaction.
#### Implicit transactions
Single DML (Data Manipulation Language) statements, such as `INSERT`, `DELETE`, and `UPDATE`, are auto committed when executed outside an explicit transaction.
#### Explicit transactions
Use `BEGIN`, `COMMIT`, and `ROLLBACK` to manage transactions explicitly.
```sql theme={null}
BEGIN;
INSERT INTO table_name VALUES ('data');
COMMIT;
```
***
## DML and DDL
### Data Manipulation Language (DML)
**DML** refers to commands used to manipulate data within a database. These commands primarily handle retrieving, inserting, updating, and deleting records in database tables, such as `INSERT`, `DELETE`, and `UPDATE`.
### Data Definition Language (DDL)
In Snowflake, **DDL** statements such as `CREATE`, `DROP`, and `ALTER` are automatically committed and can't be rolled back.
**DDL** consists of commands used to define and manage the structure of a database, including creating, modifying, or deleting tables and schemas. Examples include `CREATE`, `ALTER`, `DROP`, and `TRUNCATE`.
#### Snowflake components using DDL
The following Snowflake components use DDL with the `ALTER