MySQL事務-有解無憂

原文鏈接http://zhhll.icu/2021/01/02/%E6%95%B0%E6%8D%AE%E5%BA%93/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93/MySQL/MySQL%E4%BA%8B%E5%8A%A1/

MySQL事務

事務：一個或一組SQL陳述句組成的一個執行單元，要么全部成功，要么全部失敗，

注：mysql中只有innodb支持事務

事務的ACID特性

原子性(Atomicity) 指事務是一個不可分割的作業單位，事務中的操作要么都發生，要么都不發生
一致性(Consistency) 指事務必須使資料庫從一個一致性狀態變成另一個一致性狀態
隔離性(Isolation) 指一個事務的執行不能被另一個事務干擾
持久性(Durability) 指事務一旦被提交，對資料庫中資料的改變是永久性的

事務的創建

默認情況下mysql的事務是自動提交的

show variables like '%autocommit%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

#需要手動的禁用一下事務自動提交
set autocommit = 0;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

事務的隔離級別

查看資料庫的隔離級別

#mysql8中查看隔離級別
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

#mysql8之前查看隔離級別
select @@tx_isolation;

修改資料庫的隔離級別

#修改全域隔離級別
set global transaction isolation level read committed;
#修改會話隔離級別
set session transaction isolation level read committed;

事務并發出現的問題

臟讀對于兩個事務T1,T2，T1讀取了已經被T2更新但還沒有被提交的欄位之后，若T2回滾，T1讀取到的內容就是臨時無效的
不可重復讀對于兩個事務T1,T2，T1讀取了一個欄位，T2更新了該欄位，之后T1再次讀取同一個欄位，值就不同了(描述的是修改操作)
幻讀對于兩個事務T1,T2，T1從一個表中讀取了一個欄位，然后T2在該表中插入了一些新的行，T1再次讀取同一個表，就會多出幾行（描述的是插入操作）

臟讀和幻讀相似，臟讀屬于修改操作，幻讀屬于插入操作

隔離級別

READ UNCOMMITED(讀未提交的資料) 允許事務讀取未被其他事務提交的變更，臟讀、不可重復讀、幻讀問題都存在
READ COMMITED(讀已提交的資料) 只允許事務讀取已被其他事務提交的資料，可以避免臟讀，但是不可重復讀和幻讀仍存在
REPEATABLE READ(可重復讀) 確保事務可以多次從一個欄位中讀取相同的值，在這個事務持續期間，禁止其他事務對這個欄位進行更新，可以避免臟讀和不可重復讀，但幻讀仍存在(Mysql默認的事務隔離級別)
SERIALIZABLE(串行化) 確保事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其他事務對該表執行插入、更新和洗掉操作，所有并發問題都可以避免，但是性能低下

測驗各個隔離級別

開啟兩個mysql連接，為mysql1和mysql2

原始資料為

select * from users;
 +----+---------+
| id | u_name  |
+----+---------+
|  2 | 張三    |
|  3 | 張三0   |
|  4 | 張三1   |
|  5 | 張三2   |
+----+---------+

測驗READ UNCOMMITTED

 #首先設定mysql1和mysql2隔離級別為read uncommited
 set session transaction isolation level read uncommitted;
 #禁用mysql1 mysql2的自動提交
 set autocommit = 0;
 
 update users set u_name='李四' where id = 3;#①  mysql1修改資料但未提交
 select * from users where id =3;#② mysql1 查詢資料 為李四
 select * from users where id =3;#③ mysql2 查詢資料 也為李四 查詢到了mysql1中未提交的資料 出現了臟讀
 
insert into users (u_name) values ('劉亦菲');#④mysql1 插入資料但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該資料
select * from users where u_name = '劉亦菲';#⑥mysql2 查詢到該資料  出現了幻讀

rollback;#⑦mysql1 回滾之前的操作  此時mysql2在查詢發現之前查詢到的資料都變了 出現了不可重復讀

測驗read commited

#首先設定mysql1和mysql2隔離級別為read commited
 set session transaction isolation level read committed;
 #禁用mysql1 mysql2的自動提交
 set autocommit = 0;
 
 update users set u_name='李四' where id = 3;#①  mysql1修改資料但未提交
 select * from users where id =3;#② mysql1 查詢資料 為李四
 select * from users where id =3;#③ mysql2 查詢資料 為張三0 沒有出現臟讀
 
insert into users (u_name) values ('劉亦菲');#④mysql1 插入資料但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該資料
select * from users where u_name = '劉亦菲';#⑥mysql2 沒有查詢到該資料

commit;#⑦mysql1 提交之前的操作  此時mysql2在查詢發現之前查詢到的資料都變了 出現了不可重復讀

測驗read commited

#首先設定mysql1和mysql2隔離級別為read commited
 set session transaction isolation level read committed;
 #禁用mysql1 mysql2的自動提交
 set autocommit = 0;
 
 update users set u_name='李四' where id = 3;#①  mysql1修改資料但未提交
 select * from users where id =3;#② mysql1 查詢資料 為李四
 select * from users where id =3;#③ mysql2 查詢資料 為張三0 沒有出現臟讀
 
insert into users (u_name) values ('劉亦菲');#④mysql1 插入資料但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該資料
select * from users where u_name = '劉亦菲';#⑥mysql2 沒有查詢到該資料

commit;#⑦mysql1 提交之前的操作  此時mysql2在查詢發現之前查詢到的資料都變了 出現了不可重復讀

測驗repeatable read

#首先設定mysql1和mysql2隔離級別為repeatable read
 set session transaction isolation level repeatable read;
 #禁用mysql1 mysql2的自動提交
 set autocommit = 0;
 
 update users set u_name='李四' where id = 3;#①  mysql1修改資料但未提交
 select * from users where id =3;#② mysql1 查詢資料 為李四
 select * from users where id =3;#③ mysql2 查詢資料 為張三0 沒有出現臟讀
 
insert into users (u_name) values ('劉亦菲');#④mysql1 插入資料但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該資料
select * from users where u_name = '劉亦菲';#⑥mysql2 沒有查詢到該資料

commit;#⑦mysql1 提交之前的操作  此時mysql2還是沒有資料

保存點savepoint

在一個事務中可以設定保存點，和rollback搭配使用

測驗保存點

select * from users;

+------+-----------+
| id   | u_name    |
+------+-----------+
|    2 | 張三      |
|    3 | 李四      |
|    4 | 張三1     |
|    5 | 張三2     |
| 1006 | 劉亦菲    |
+------+-----------+

delete from users where id = 4;
savepoint a;#設定保存點
delete from users where id = 1006;
rollback to a;#回滾到保存點的位置


select * from users;

+------+-----------+
| id   | u_name    |
+------+-----------+
|    2 | 張三      |
|    3 | 李四      |
|    5 | 張三2     |
| 1006 | 劉亦菲    |
+------+-----------+

由于本身的博客百度沒有收錄，博客地址http://zhhll.icu

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/260262.html

標籤：MySQL

上一篇：sql server想問問 with nolock的使用

下一篇：MySQL事務