> For the complete documentation index, see [llms.txt](https://ret2basic.gitbook.io/ctfnote/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ret2basic.gitbook.io/ctfnote/computer-science/databases/mysql/transaction.md).

# Transaction

## Motivation

Suppose we have a banking system and two clients Alice and Bob. Alice wants to send $100 to Bob. This action can be subdivided into two parts:

1. Deduct $100 from Alice's account.
2. Add $100 to Bob's account.

But here is the problem: if step 1 succeeds and step 2 fails, then Alice will lose $100 and Bob will get nothing. We need step 1 and step 2 to be an **atomic unit**: if step 2 fails, then step 1 must fail as well. To overcome this barrier, MySQL implemented **transaction**.

## What is Transaction

Transactions are atomic units of work that can be **committed** or **rolled back**. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

Since MySQL > 5.4, the default storage engine is InnoDB. InnoDB supports transaction.

## Lab

Let there be a table tb with the following entries:

![Table tb, before](https://3988450783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MWVjG_njKgBtvmnKaJh%2Fuploads%2FcVewcnEIQgYIAgw9SHBK%2Fimage.png?alt=media\&token=12eae0c1-3ef2-4d2f-aade-aeb13eb2c912)

Start a new transaction:

```sql
BEGIN;
```

Delete all entries from tb:

```sql
DELETE FROM tb;
```

Verify that all entries were deleted:

![Table tb, after](https://3988450783-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MWVjG_njKgBtvmnKaJh%2Fuploads%2Ft5YIuG8C3WxeOv9LnmEB%2Fimage.png?alt=media\&token=49a1d898-ba6f-4418-b886-942c331ac6c6)

Now, you can either revert the deletion:

```sql
ROLLBACK;
```

or make the deletion permanent:

```sql
COMMIT;
```

## Auto Commit

Auto commit is turned on by default. Turn it off:

```sql
SET AUTOCOMMIT=0
```

This is same as starting a transaction session. Or turn it back on:

```sql
SET AUTOCOMMIT=1
```