### 事务的隔离级别
* 修改事务的隔离级别，可以使用以下命令：
```mysql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
```
事务的隔离级别分为以下四个级别：
1. **READ UNCOMMITTED** 未提交读
事务中的修改，即使没有提交，对别的事务也是可以见的。事务可以读取未提交的数据，这也被称之为**脏读**
```text
mysql> SET session transaction isolation level read UNCOMMITTED;
SET SESSION binlog_format = 'ROW';
mysql> begin; mysql> begin;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom |
| 4 | ha1 |
+
4 rows in set (0.00 sec)
mysql> UPDATE test_transaction set `name` = 'ha1-1' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom |
| 4 | ha1-1 |
+
4 rows in set (0.00 sec)
mysql> DELETE from test_transaction where id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom |
+
3 rows in set (0.00 sec)
mysql> commit; mysql> commit;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
```
2. **READ COMMITTED** 提交读
一个事务开始时，只能看到已经提交事务的修改。一个事务从开始直到提交之前，所做的任何修改对其它事务来说是不可见的。 这个级别的有时候也可以叫做
**不可重复读**，因为两次执行同样的查询，可能会得到不一样的结果。
```text
mysql> SET session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> begin; mysql> begin;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom |
+
3 rows in set (0.02 sec)
mysql> INSERT into test_transaction (`name`) VALUES ('bbc');
Query OK, 1 row affected (0.01 sec)
mysql> UPDATE test_transaction set `name` = 'bob-1' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom |
+
3 rows in set (0.00 sec)
mysql> INSERT into test_transaction (`name`) VALUES ('cctv');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom |
| 6 | cctv |
+
4 rows in set (0.00 sec)
mysql> UPDATE test_transaction set `name` = 'tom-bak' where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom-bak |
| 6 | cctv |
+
4 rows in set (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb |
| 3 | tom-bak |
| 6 | cctv |
+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb-1 |
| 3 | tom-bak |
| 5 | bbc |
| 6 | cctv |
+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
```
3. **REPEATABLE READ** 可重复读
在同一个事务中多次读取同样的数据的结果是一致的，在不同的事务中读取同样的数据，得到的结果也是同样的。
```text
mysql> SET session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb-1 |
| 3 | tom-bak |
| 5 | bbc |
| 6 | cctv |
+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test_transaction set `name` = 'cctv-1' where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb-1 |
| 3 | tom-bak |
| 5 | bbc |
| 6 | cctv |
+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT into test_transaction (`name`) VALUES ('usa');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb-1 |
| 3 | tom-bak |
| 5 | bbc |
| 6 | cctv |
+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test_transaction where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 1 | jjj |
| 2 | bbb-1 |
| 3 | tom-bak |
| 5 | bbc |
| 6 | cctv |
+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+
| id | name |
+
| 2 | bbb-1 |
| 3 | tom-bak |
| 5 | bbc |
| 6 | cctv-1 |
| 7 | usa |
+
5 rows in set (0.00 sec)
```
4. **SERIALIZABLE** 可串行化
最高的隔离级别，也是最慢的，但是它可以保证读取的数据是正确的。通过强制执行事务串行化，可以防止脏读和不可重复读。它在读取每一行的数据上都加锁，
从而阻止了插入和删除操作。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读
|
| READ UNCOMMITTED | Yes | Yes | Yes | No |
| READ COMMITTED | No | Yes | Yes | No |
| REPEATABLE READ | No | No | Yes | No |
| SERIALIZABLE | No | No | No | Yes |
### 死锁
**死锁**是指两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
1. 多个事务试图以不同顺序锁定资源时，就可能产生死锁
2. 多个事务同时锁定同一资源时，也可能会产生死锁
事务A开启事务，更新主键id=6的数据，事务A还没有提交事务，这时事务B也开启事务，更新id=6的数据，就会产生死锁。
```text
mysql> begin; mysql> begin;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test_transaction set `name` = 'cctv-2' where id = 6; mysql> UPDATE test_transaction set `name` = 'cctv-3' where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
```
