MySQL事務
事務：一個或一組SQL語句組成的一個執(zhí)行單元，要么全部成功，要么全部失敗。
注：mysql中只有innodb支持事務
事務的ACID特性
- 原子性(Atomicity) 指事務是一個不可分割的工作單位，事務中的操作要么都發(fā)生，要么都不發(fā)生
- 一致性(Consistency) 指事務必須使數(shù)據(jù)庫從一個一致性狀態(tài)變成另一個一致性狀態(tài)
- 隔離性(Isolation) 指一個事務的執(zhí)行不能被另一個事務干擾
- 持久性(Durability) 指事務一旦被提交，對數(shù)據(jù)庫中數(shù)據(jù)的改變是永久性的
事務的創(chuàng)建
默認情況下mysql的事務是自動提交的
show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
#需要手動的禁用一下事務自動提交
set autocommit = 0;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
事務的隔離級別
查看數(shù)據(jù)庫的隔離級別
#mysql8中查看隔離級別
select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
#mysql8之前查看隔離級別
select @@tx_isolation;
修改數(shù)據(jù)庫的隔離級別
#修改全局隔離級別
set global transaction isolation level read committed;
#修改會話隔離級別
set session transaction isolation level read committed;
事務并發(fā)出現(xiàn)的問題
- 臟讀 對于兩個事務T1,T2，T1讀取了已經(jīng)被T2更新但還沒有被提交的字段之后，若T2回滾，T1讀取到的內容就是臨時無效的
- 不可重復讀 對于兩個事務T1,T2，T1讀取了一個字段，T2更新了該字段，之后T1再次讀取同一個字段，值就不同了(描述的是修改操作)
- 幻讀 對于兩個事務T1,T2，T1從一個表中讀取了一個字段，然后T2在該表中插入了一些新的行，T1再次讀取同一個表，就會多出幾行（描述的是插入操作）
臟讀和幻讀相似，臟讀屬于修改操作，幻讀屬于插入操作
隔離級別
- READ UNCOMMITED(讀未提交的數(shù)據(jù)) 允許事務讀取未被其他事務提交的變更，臟讀、不可重復讀、幻讀問題都存在
- READ COMMITED(讀已提交的數(shù)據(jù)) 只允許事務讀取已被其他事務提交的數(shù)據(jù)，可以避免臟讀，但是不可重復讀和幻讀仍存在
- REPEATABLE READ(可重復讀) 確保事務可以多次從一個字段中讀取相同的值，在這個事務持續(xù)期間，禁止其他事務對這個字段進行更新，可以避免臟讀和不可重復讀，但幻讀仍存在(Mysql默認的事務隔離級別)
- SERIALIZABLE(串行化) 確保事務可以從一個表中讀取相同的行，在這個事務持續(xù)期間，禁止其他事務對該表執(zhí)行插入、更新和刪除操作，所有并發(fā)問題都可以避免，但是性能低下
測試各個隔離級別
開啟兩個mysql連接，為mysql1和mysql2
原始數(shù)據(jù)為
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修改數(shù)據(jù)但未提交
select * from users where id =3;#② mysql1 查詢數(shù)據(jù) 為李四
select * from users where id =3;#③ mysql2 查詢數(shù)據(jù) 也為李四 查詢到了mysql1中未提交的數(shù)據(jù) 出現(xiàn)了臟讀
insert into users (u_name) values ('劉亦菲');#④mysql1 插入數(shù)據(jù)但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該數(shù)據(jù)
select * from users where u_name = '劉亦菲';#⑥mysql2 查詢到該數(shù)據(jù) 出現(xiàn)了幻讀
rollback;#⑦mysql1 回滾之前的操作 此時mysql2在查詢發(fā)現(xiàn)之前查詢到的數(shù)據(jù)都變了 出現(xiàn)了不可重復讀
測試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修改數(shù)據(jù)但未提交
select * from users where id =3;#② mysql1 查詢數(shù)據(jù) 為李四
select * from users where id =3;#③ mysql2 查詢數(shù)據(jù) 為張三0 沒有出現(xiàn)臟讀
insert into users (u_name) values ('劉亦菲');#④mysql1 插入數(shù)據(jù)但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該數(shù)據(jù)
select * from users where u_name = '劉亦菲';#⑥mysql2 沒有查詢到該數(shù)據(jù)
commit;#⑦mysql1 提交之前的操作 此時mysql2在查詢發(fā)現(xiàn)之前查詢到的數(shù)據(jù)都變了 出現(xiàn)了不可重復讀
測試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修改數(shù)據(jù)但未提交
select * from users where id =3;#② mysql1 查詢數(shù)據(jù) 為李四
select * from users where id =3;#③ mysql2 查詢數(shù)據(jù) 為張三0 沒有出現(xiàn)臟讀
insert into users (u_name) values ('劉亦菲');#④mysql1 插入數(shù)據(jù)但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該數(shù)據(jù)
select * from users where u_name = '劉亦菲';#⑥mysql2 沒有查詢到該數(shù)據(jù)
commit;#⑦mysql1 提交之前的操作 此時mysql2在查詢發(fā)現(xiàn)之前查詢到的數(shù)據(jù)都變了 出現(xiàn)了不可重復讀
測試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修改數(shù)據(jù)但未提交
select * from users where id =3;#② mysql1 查詢數(shù)據(jù) 為李四
select * from users where id =3;#③ mysql2 查詢數(shù)據(jù) 為張三0 沒有出現(xiàn)臟讀
insert into users (u_name) values ('劉亦菲');#④mysql1 插入數(shù)據(jù)但未提交
select * from users where u_name = '劉亦菲';#⑤mysql1 查詢到該數(shù)據(jù)
select * from users where u_name = '劉亦菲';#⑥mysql2 沒有查詢到該數(shù)據(jù)
commit;#⑦mysql1 提交之前的操作 此時mysql2還是沒有數(shù)據(jù)
保存點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
