```mysql
##查看MySQL事務(wù)的提交方式發(fā)現(xiàn)是自動(dòng)提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

**使用set改變MySQL的事務(wù)提交方式**：
```sql
##將MySQL的事務(wù)提交方式修改為禁止自動(dòng)提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
##將MySQL的事務(wù)提交方式修改為開啟自動(dòng)提交
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
##修改默認(rèn)隔離級(jí)別生效
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

create table if not exists account(
id int primary key,
name varchar(50) not null default '',
blance decimal(10,2) not null default 0.0
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

mysql> start transaction; -- 開始一個(gè)事務(wù)begin也可以，推薦begin
Query OK, 0 rows affected (0.00 sec)
mysql> savepoint save1; -- 創(chuàng)建一個(gè)保存點(diǎn)save1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account values (1, '張三', 1030.00); -- 插入一條記錄
Query OK, 1 row affected (0.05 sec)
mysql> savepoint save2; -- 創(chuàng)建一個(gè)保存點(diǎn)save2
Query OK, 0 rows affected (0.01 sec)
mysql> insert into account values (2, '李四', 13000.00); -- 在插入一條記錄
Query OK, 1 row affected (0.00 sec)
mysql> select * from account; -- 兩條記錄都在了
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 1030.00 |
| 2 | 李四 | 13000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> rollback to save2; -- 回滾到保存點(diǎn)save2
Query OK, 0 rows affected (0.03 sec)
mysql> select * from account; -- 一條記錄沒有了
+----+--------+--------+
| id | name | blance |
+----+--------+--------+
| 1 | 張三 | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)
mysql> rollback; -- 直接rollback，回滾在最開始
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; -- 所有剛剛的記錄沒有了
Empty set (0.00 sec)

-- 終端A
mysql> select * from account; -- 當(dāng)前表內(nèi)無數(shù)據(jù)
Empty set (0.00 sec)
mysql> show variables like 'autocommit'; -- 依舊自動(dòng)提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> begin; -- 開啟事務(wù)
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account values (1, '張三', 100.30); -- 插入記錄
Query OK, 1 row affected (0.00 sec)
mysql> select * from account; -- 數(shù)據(jù)已經(jīng)存在，但沒有commit，此時(shí)同時(shí)查看
-- 終端B
+----+--------+--------+
| id | name | blance |
+----+--------+--------+
| 1 | 張三 | 100.30 |
+----+--------+--------+
1 row in set (0.00 sec)
mysql> Aborted -- ctrl + \ 異常終止MySQL
-- 終端B
mysql> select * from account; -- 終端A崩潰前
+----+--------+--------+
| id | name | blance |
+----+--------+--------+
| 1 | 張三 | 100.30 |
+----+--------+--------+
1 row in set (0.00 sec)
mysql> select * from account; -- 數(shù)據(jù)自動(dòng)回滾
Empty set (0.00 sec)

-- 查看
mysql> SELECT @@global.tx_isolation; --查看全局隔級(jí)別
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@session.tx_isolation; --查看會(huì)話(當(dāng)前)全局隔級(jí)別
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@tx_isolation; --默認(rèn)同上
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

-- 設(shè)置當(dāng)前會(huì)話 or 全局隔離級(jí)別語法
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE}

--設(shè)置當(dāng)前會(huì)話隔離性，另起一個(gè)會(huì)話，看不多，只影響當(dāng)前會(huì)話
mysql> set session transaction isolation level serializable; -- 串行化
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.tx_isolation; --全局隔離性還是RR
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@session.tx_isolation; --會(huì)話隔離性成為串行化
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE |
+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@tx_isolation; --同上
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> set global transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED |
+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
-- 注意，如果沒有現(xiàn)象，關(guān)閉mysql客戶端，重新連接。

--先設(shè)置當(dāng)前的全局隔離級(jí)別為讀未提交，再退出MySQL重新登錄一下讓其生效
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

-- 終端A
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
-- 重啟客戶端
mysql> select * from account; -- 查看當(dāng)前數(shù)據(jù)
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 123.00 |
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> begin; -- 手動(dòng)開啟事務(wù)，同步的開始終端B事務(wù)
Query OK, 0 rows affected (0.00 sec)
mysql> update account set blance=321.0 where id=1; -- 更新張三數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--切換終端到終端B，查看數(shù)據(jù)。
mysql> commit; --commit提交！
Query OK, 0 rows affected (0.01 sec)
-- 切換終端到終端B，再次查看數(shù)據(jù)。
-- 終端B
mysql> begin; -- 手動(dòng)開啟事務(wù)，和終端A一前一后
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; -- 終端A commit之前，查看不到
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 123.00 | -- 老的值
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
-- 終端A commit之后，看到了！
-- but，此時(shí)還在當(dāng)前事務(wù)中，并未commit，那么就造成了，同一個(gè)事務(wù)內(nèi)，同樣的讀取，在不同的時(shí)間段
##(依舊還在事務(wù)操作中！)，讀取到了不同的值，這種現(xiàn)象叫做不可重復(fù)讀(non reapeatable read)！！
##（這個(gè)是問題嗎？？）
mysql> select *from account;
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 321.00 | -- 新的值
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

-- 終端A
mysql> set global transaction isolation level repeatable read; -- 設(shè)置全局隔離級(jí)別
RR
Query OK, 0 rows affected (0.01 sec)
-- 關(guān)閉終端重啟
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ | -- 隔離級(jí)別RR
+-----------------+
1 row in set, 1 warning (0.00 sec)
-- 終端A
mysql> select *from account;
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 321.00 |
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> begin; -- 開啟事務(wù)，終端B同步開啟
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account (id,name,blance) values(3, '王五', 5432.0);
Query OK, 1 row affected (0.00 sec)
-- 切換到終端B，查看另一個(gè)事務(wù)是否能看到
mysql> commit; -- 提交事務(wù)
Query OK, 0 rows affected (0.00 sec)
-- 切換終端到終端B，查看數(shù)據(jù)。
mysql> select * from account;
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
| 3 | 王五 | 5432.00 |
+----+--------+----------+
3 rows in set (0.00 sec)
-- 終端B
mysql> begin; -- 開啟事務(wù)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; -- 終端A commit前 查看
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> select * from account; -- 終端A commit后 查看
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> select * from account;
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> commit; -- 結(jié)束事務(wù)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; -- 看到更新
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
| 3 | 王五 | 5432.00 |
+----+--------+----------+
3 rows in set (0.00 sec)

-- 對(duì)所有操作全部加鎖，進(jìn)行串行化，不會(huì)有問題，但是只要串行化，效率很低，幾乎完全不會(huì)被采用
-- 終端A
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> begin; -- 開啟事務(wù)，終端B同步開啟
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; -- 兩個(gè)讀取不會(huì)串行化，共享鎖
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
| 3 | 王五 | 5432.00 |
+----+--------+----------+
3 rows in set (0.00 sec)
mysql> update account set blance=1.00 where id=1; -- 終端A中有更新或者其他操作，會(huì)阻
##塞。直到終端B事務(wù)提交。
Query OK, 1 row affected (18.19 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 終端B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account; -- 兩個(gè)讀取不會(huì)串行化
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 張三 | 4321.00 |
| 2 | 李四 | 10000.00 |
| 3 | 王五 | 5432.00 |
+----+--------+----------+
3 rows in set (0.00 sec)
mysql> commit; -- 提交之后，終端A中的update才會(huì)提交。
Query OK, 0 rows affected (0.00 sec)