Locks and Transactions

name: portada
layout: true
class: portada-slide
---
# Locks and Transactions
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Transactions

"An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. 
That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data."

— The SQL Standard

.center[[MariaDB Transactions](https://mariadb.com/kb/en/transactions/)]

---
# Transactions: Start
The `START TRANSACTION` or `BEGIN` statement begins a new transaction.

By default, MariaDB runs with autocommit mode enabled. This means that as soon as you execute a statement 
that updates (modifies) a table, MariaDB stores the update on disk to make it permanent. To disable autocommit mode
use `SET autocommit = 0;`

.center[[MariaDB Start Transaction](https://mariadb.com/kb/en/start-transaction/)]

```
SET autocommit = 0;

START TRANSACTION;

-- SQL statements
-- inside a transaction
```

---
# Transactions: Commit
The `COMMIT` statement ends a transaction, saving any changes to the data on the disk and makes it permanent.

.center[[MariaDB Commit](https://mariadb.com/kb/en/commit/)]

```
SET autocommit = 0;

START TRANSACTION;

-- SQL statements
-- inside a transaction

COMMIT; -- Saves the changes and ends the transaction.
```

---
# Transactions: Rollback

The `ROLLBACK` statement rolls back (ends) a transaction, destroying any changes to SQL-data so that they never
are written to disk.

.center[[MariaDB Rollback](https://mariadb.com/kb/en/rollback/)]

```
SET autocommit = 0;

START TRANSACTION;

-- SQL statements
-- inside a transaction

ROLLBACK; -- Discards the changes and ends the transaction.
```

---
# Transactions: Savepoints
A save point is a logical rollback point within a transaction. 
When you set a save point, whenever an error occurs past a save point, 
you can undo the events you have done up to the save point using the rollback.

InnoDB supports the SQL statements `SAVEPOINT`, `ROLLBACK TO SAVEPOINT` and `RELEASE SAVEPOINT`.

Normally `ROLLBACK` undoes the changes performed by the whole transaction. 
When used with the TO clause, it undoes the changes performed after the specified savepoint, 
and erases all subsequent savepoints.
`RELEASE SAVEPOINT` does not rollback or commit any changes, but removes the specified savepoint.

```
SET autocommit = 0;
START TRANSACTION;

-- SQL statements inside a transaction

SAVEPOINT s1; -- Creates a savepoint named "s1"

-- SQL statements inside a transaction

ROLLBACK TO s1; -- Discards the changes made after "s1" savepoint.
RELEASE SAVEPOINT s1; -- Deletes the savepoint "s1" (but doesn't discard the changes)
ROLLBACK; -- Discards ALL the changes and ends the transaction.
```
---
# Transactions: example

We'll use a simple database with a single table simulating a bank with some accounts.
```
create database bank;
use bank;
create table account (
    id int primary key auto_increment,
    balance int not null default 0
);
insert into account (balance) values (100), (200), (300);
```

---
# Transactions: example
Using two different sessions:

.columns[
.column[

```
set autocommit = 0;
start transaction;

update account
set balance = balance - 100
where id = 1;

savepoint money_retrieved;

update account
set balance = balance + 100
where id = 2;

savepoint money_deposited;

commit;
```
]
.column[
```
-- At the beggining of the transaction.
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |     100 |
|  2 |     200 |
+----+---------+
-- At the money_retrieved savepoint.
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |     100 |
|  2 |     200 |
+----+---------+
-- After the commit;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |       0 |
|  2 |     300 |
+----+---------+
```
]
]

---
# Locks: example

Using two different sessions:

.columns[
.column[
- Account `1` transfers 100€ to account `2`.

```
set autocommit = 0;
start transaction;

update account -- 1
set balance = balance - 100
where id = 1;

update account -- 2
set balance = balance + 100
where id = 2;

commit; -- 3
```
]

.column[
- Account `3` transfers 50€ to account `1`.

```
set autocommit = 0;
start transaction;

update account -- 4
set balance = balance - 50
where id = 3;

update account -- 5
set balance = balance + 50
where id = 1;

commit; -- 6
```
]
]

What would happen if the execution order is `1 2 4 5 3 6`?
- Solution: Lock the account table.

---
# Lock tables

.center[[MariaDB Lock Tables](https://mariadb.com/kb/en/lock-tables/)]
.center[[MariaDB Unlock Tables](https://mariadb.com/kb/en/transactions-unlock-tables/)]

```
lock table table_name [READ | WRITE];
unlock tables;
```

Two main type of table locks:
- `READ`: Read lock, no writes allowed
- `WRITE`: Exclusive write lock. No other connections can read or write to this table.

---
# Lock records

Instead of locking an entire table (may be inefficient), a set of records (rows)
of a table can be locked using the `SELECT` statemenet.

```
SELECT * FROM account WHERE id in (1, 2) [FOR UPDATE | LOCK IN SHARE MODE];
```
.center[[MariaDB LOCK IN SHARE MODE](https://mariadb.com/kb/en/lock-in-share-mode/)]
.center[[MariaDB FOR UPDATE](https://mariadb.com/kb/en/for-update/)]

- `LOCK IN SHARE MODE`: MariaDB will wait until all transactions that have modified the rows are committed. 
  Then, a write lock is acquired. All transactions can read the rows, 
  but if they want to modify them, they have to wait until your transaction is committed.
- `FOR UPDATE`: A lock is acquired on the rows, and other transactions are prevented
  from writing the rows, acquire locks, and from reading them.
  - UPDATE statements get a FOR UPDATE lock automatically.

---
# Advanced stuff
.center[[MariaDB Isolation Levels](https://mariadb.com/kb/en/mariadb-transactions-and-isolation-levels-for-sql-server-users/)]
.center[[MariaDB Transactions Overview](https://mariadb.com/kb/en/mariadb-transactions-and-isolation-levels-for-sql-server-users/)]
.center[[MariaDB Implicit Commit](https://mariadb.com/kb/en/sql-statements-that-cause-an-implicit-commit/)]