1. 什么是事務

事務就是把多條SQL語句打包成一個整體，里面的sql語句要么全部執(zhí)行成功，要么都不執(zhí)行，這里的都不執(zhí)行，不是說真沒執(zhí)行，而是發(fā)生了回滾操作。

比如說有兩個人A和B，A有 1000， B有1000，

• 語句一：A給B轉(zhuǎn)賬500，
• 語句二：B接收轉(zhuǎn)過來的500。

這兩個sql語句需要都執(zhí)行，不能中斷，要成功都成功。

如果執(zhí)行完語句一后，沒有執(zhí)行語句二就會出現(xiàn)回滾現(xiàn)象，執(zhí)行回滾的SQL語句，返回到語句一執(zhí)行前的狀態(tài)。

回滾：

就是事務中的SQL語句執(zhí)行一部分，后面沒執(zhí)行，此時MySQL會執(zhí)行回滾的sql語句將數(shù)據(jù)恢復到該事務執(zhí)行之前的狀態(tài)。

如果執(zhí)行事務執(zhí)行一般斷電了，還會發(fā)生回滾嗎？

當然會，因為MySQL在執(zhí)行事務的時候，會記錄一個日志，來記錄當前事務執(zhí)行到那步了，當恢復電后再進入MySQL，MySQL會根據(jù)日志對之前執(zhí)行的操作進行回滾，回滾完成后刪除該日志，如果事務成功執(zhí)行完也會刪除該日志。

引入事務可以很好的解決原子性問題。

2. 事務的四個特征

2.1 原子性

原子性就是說事務在執(zhí)行SQL語句時候，要么全部執(zhí)行，要么都不執(zhí)行，不存在部分執(zhí)行，部分沒有執(zhí)行的情況，如果存在會觸發(fā)回滾操作。

2.2 一致性

一致性是說事務在執(zhí)行前和執(zhí)行后的數(shù)據(jù)都要符合該業(yè)務的規(guī)則和約束條件。

比如說：銀行轉(zhuǎn)賬業(yè)務中，余額在執(zhí)行完事務后不能是負數(shù)等。

事務在執(zhí)行前是符合一致性的，在執(zhí)行一部分后中斷，發(fā)生回滾回到事務執(zhí)行開始，也是符合一致性的。

有時候，事務回滾到中間某個SQL語句，此時也有可能遵循一致性。

2.3 持久性

持久性是說事務對數(shù)據(jù)做出的修改，應該持久的生效，那么就應該寫入到硬盤中，加上數(shù)據(jù)庫本身存儲數(shù)據(jù)也是存儲在硬盤上的。

硬盤也有壞的風險，我們需要對關(guān)鍵數(shù)據(jù)進行備份。

2.4 隔離性

隔離性主要是談到事務在并發(fā)情況下出現(xiàn)的問題：

2.4.1 臟讀問題

事務1 對某個數(shù)據(jù)進行了修改，但是該事務還沒結(jié)束，如果事務1發(fā)生回滾，事務2 對該數(shù)據(jù)進行訪問，訪問的是修改后的數(shù)據(jù)，但實際數(shù)據(jù)是沒有修改的，此時讀到的這個數(shù)據(jù)就是臟數(shù)據(jù)。

如何解決臟讀問題：

• 我們需要約束事務1執(zhí)行完之后，才允許事務2讀取到修改后的值。
• 此時如果事務1沒有執(zhí)行完，事務2讀到的數(shù)據(jù)就是修改前的數(shù)據(jù)。

2.4.2 不可重復讀問題

事務1中多次讀取某個值的時候，每次讀取同一個數(shù)據(jù)的值都不一樣，就是不可重復讀。

同一個事務多次讀取一個數(shù)據(jù)，得到的結(jié)果相同就是可重復讀。

圖中的事務1就存在不可重復讀問題。

如何解決不可重復讀問題：

我們需要約束，一個事務在進行讀操作的時候，對該數(shù)據(jù)進行加鎖，其他事務不能對該數(shù)據(jù)進行讀和修改的操作。當這個事務對該數(shù)據(jù)執(zhí)行完后，開鎖。

而SQL內(nèi)部處理原理是：事務在執(zhí)行前會創(chuàng)建一個“一致性讀視圖”（快照）(就是事務啟動前的數(shù)據(jù)狀態(tài)），該事務內(nèi)部都會參照這個圖去執(zhí)行語句，不受外部其他事務對數(shù)據(jù)操作的影響。

2.4.3 幻讀問題

同一個事務中，在針對某次查詢時候，前后兩個查詢結(jié)果不同。

在執(zhí)行事務1中第一次查詢時，有3條數(shù)據(jù)，再執(zhí)行事務2，在執(zhí)行事務1的第二條查詢，有4條數(shù)據(jù)，這就是幻讀問題。

如何解決幻讀問題：

我們就需要服務器進行串行化處理數(shù)據(jù)，也就是一個事務一個事務執(zhí)行，可以解決幻讀問題。

2.5 事務的使用

2.5.1創(chuàng)建事務

#開始一個事務
start transaction;
或者
begin;

#結(jié)束一個事務
commit;

#回滾當前事務
bollback;

2.5.2 保存點

保存點是我們在創(chuàng)建事務的時候設置的，可以在回滾的時候，指定保存點進行回滾。使用

bollback to 保存點名字，回滾到保存點。

例如：

create table student(id int primary key, name varchar(20), balance double);
insert into student values(1,'張三',1000), (2,'李四',1500);

#開始事務
begin;

select * from student;

update student set balance = balance + 100 where name = '張三';
update student set balance = balance - 100 where name = '李四';

select * from student;

#設置保存點
savepoint tmp1;

update student set balance = balance + 100 where name = '張三';
update student set balance = balance - 100 where name = '李四';

select * from student;

#回滾到保存點
rollback to tmp1;

select * from student;

#結(jié)束事務
commit;

2.6數(shù)據(jù)庫的隔離級別

MySQL中規(guī)定了四種隔離級別：

• read uncommitted，讀未提交，此時包含臟讀，不可重復讀，幻讀問題。
• read committed，讀已提交，此時包含不可重復讀，幻讀問題。
• repeatable read，可重復讀，這種是數(shù)據(jù)庫默認的級別，此時包含幻讀問題。
• serializable，串行化，這種就解決了所有的隔離性問題。

2.6.1 修改隔離級別

方法一：

通過SQL語句進行修改：

set [global | session] transaction isolation level  隔離級別 | 訪問模式;

#隔離級別
read uncommintted 
read committed
repeatable read
serializable

#訪問模式
read write #事務對數(shù)據(jù)進行讀寫
read only  #事務對數(shù)據(jù)只進行讀，不能讀寫。

• global：是對當前的連接以及后續(xù)連接服務器的客戶端都生效。
• session：只對當前連接服務器的客戶端生效。

這種使用SQL語句修改的方式只是臨時修改，重啟MySQL就失效了。

方法二：

通過配置文件修改：

我們可以找到自己電腦下載的MySQL所在的文件：

右鍵文件找到屬性，里面有一串地址如下：

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" "-uroot" "-p" "--default-character-set=utf8mb4"

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini ，這個文件位置就是當前我電腦上的MySQL的配置文件，在里面可以修改隔離等級。

2.6.2 查看隔離級別

我們還可以通過SQL語句查看數(shù)據(jù)庫的隔離級別：

查看全局會話隔離級別：

select @@global.transaction_isolation;

查看當前會話的隔離級別：

select @@global.transaction_isolation;

2.5.3讀未提交（read uncommitted）

這種級別包含臟讀問題，不可重復包含問題和幻讀問題。

臟讀問題：

我們創(chuàng)建第一個連接，SQL語句為，這里包含第一個事務：

create table student(id int primary key, name varchar(20), balance double);
insert into student values(1,'張三',1000), (2,'李四',1500);
select * from student;

#修改當前會話的隔離等級為讀未完成
set session transaction isolation level read uncommitted;

#查詢當前會話的隔離等級
select @@session.transaction_isolation;

#事務1
begin;#1

update student set balance = 2000 where id = 1;#1

commit;

創(chuàng)建第二個連接，SQL語句包含第二個事務：

#修改當前會話的隔離等級為讀未完成
set session transaction isolation level read uncommitted;

#查詢當前會話的隔離等級
select @@session.transaction_isolation;

#事務2
begin; #2

select * from student where id = 1; #2

commit; #2

此時兩個連接的隔離級別是 read uncommitted，此時事務1的SQL語句執(zhí)行前兩行，事務2的SQL語句全部執(zhí)行，事務2查詢的結(jié)果是2000。此時產(chǎn)生臟讀問題。當我們設置隔離等級為read committed就能解決臟讀問題。

我們修改隔離等級為read uncommitted后，再進行上面的步驟，查詢結(jié)果是1000，此時就解決了臟讀問題。

2.5.4讀已提交（read committed）

該隔離等級解決了臟讀問題，包含不可重復讀問題和幻讀問題。

不可重復讀問題：

第一個連接包含事務1：

#事務1
begin; #1

select * from student where id = 1; #3

select * from student where id = 1; #5

commit; #6

第二個連接包含事務2和事務3：

#事務2
begin; #2

update student set balance = 2000 where id = 1; #2

commit; #2


#事務3
begin; #4

update student set balance = 3000 where id = 1; #4

commit; #4

此時兩個連接的隔離級別是read committed，此時按照上面注釋的執(zhí)行順序，事務1中的第一次查詢結(jié)果是2000，第二次查詢結(jié)果是 3000，此時包含不可重復讀問題。

我們可以修改隔離等級為repeatable read ，此時再按照上面順序執(zhí)行，事務1第一次查詢結(jié)果是2000，第二次查詢結(jié)果是2000，解決了不可重復讀問題。

2.5.5可重復讀（repeatable read）

該隔離等級解決了臟讀問題，不可重復讀問題，還包含著幻讀問題。

幻讀問題：

第一個連接包含事務1：

#事務1
begin; #1

select * from student; #1

select * from student; #3

commit; #4

第二個連接包含事務2：

#事務2
begin; #2

insert into student values(3,'王五',2000); #2
select * from student; #2

commit; #2

此時的隔離等級是 repeatable read，事務1第一次查詢沒有王五，在執(zhí)行完事務2后再次查詢也沒有王五。

為什么此時沒有出現(xiàn)幻讀問題呢？

• 可重復讀問題是在一個事務中多次讀取某個數(shù)據(jù)，而該數(shù)據(jù)受到其他事務的修改而導致結(jié)果不一致（這是針對已有的數(shù)據(jù)進行修改）。
• 幻讀問題是在一個事務中多次相同條件的查詢，而結(jié)果因為其他事務的插入或者刪除操作發(fā)生了修改導致不一致（這是針對新數(shù)據(jù)的插入）。
• 而MySQL底層解決不可重復讀問題是利用一致性讀視圖（快照）來解決的，而其他事務的插入和刪除操作不會影響快照的內(nèi)容，產(chǎn)生的結(jié)果一致。

下面是產(chǎn)生幻讀問題的語句：

第一個連接包含事務1：

#事務1
begin; #1

select * from student; #1

insert into student values(3,'王五',2000); #3

commit; #4

第二個連接包含事務2：

#事務2
begin; #2

insert into student values(3,'王五',2000); #2
select * from student; #2

commit; #2

此時先執(zhí)行事務1中的查詢，沒有王五，再執(zhí)行事務2后，此時再執(zhí)行事務一的查詢沒有王五，但是在執(zhí)行插入操作時候，會報錯說明王五數(shù)據(jù)已經(jīng)插入其中，因為在插入前，MySQL會查詢該表是否有此元素，此時就產(chǎn)生幻讀錯誤。

下面是事務1報錯信息，這里事務2已經(jīng)執(zhí)行，表中已有元素事務1執(zhí)行就會報錯：

此時我們可以修改隔離級別為serializable，此時在按照上面執(zhí)行的話，就不會存在幻讀問題了。此時事務之間一個一個執(zhí)行。

下面是事務2的報錯，這里事務2等到事務1執(zhí)行完后才執(zhí)行，此時執(zhí)行事務2就會報錯：

2.5.6串行化（serializable）

該隔離級別是解決了臟讀問題，不可重復讀問題和幻讀問題。

隔離級別越高，執(zhí)行效率越低，并發(fā)性越低，數(shù)據(jù)準確性越高。

3. 視圖

3.1 什么是視圖

視圖就是一個虛假的表，不存在于硬盤上，根據(jù)其他表或者視圖的查詢結(jié)果作為數(shù)據(jù)生成的一張“類似于表”的數(shù)據(jù)集。

3.2 創(chuàng)建視圖

create view  視圖名字  as  查詢語句;

這里的查詢語句是指從其他的表或者視圖中查詢的結(jié)果。

我們先創(chuàng)建一個學生表：

create table student(id int primary key, name varchar(20), sno varchar(20), age int, gender varchar(10), class_id int);
insert into student values(1,'張三','1001',20,'男',1), (2,'李四','1002',20,'男',1), (3,'王五','1003',20,'男',2),(4,'周六','1004',20,'男',2);

創(chuàng)建一個視圖：

create view student_class_id as select * from student where class_id = 1;
select * from student_class_id;

該視圖查詢出來結(jié)果包含的就是class_id=1的學生的數(shù)據(jù)。

我們在創(chuàng)建視圖的時候可以給列起指定的名字：

create view student_class_id1 (student_id,student_name) as select id,name from student where class_id = 1;
select * from student_class_id1;

查詢視圖結(jié)果為：

3.3 修改視圖

3.3.1 修改表

修改真實的表的數(shù)據(jù)，根據(jù)這個表創(chuàng)建的視圖的數(shù)據(jù)也會被修改。

update student set age = 50 where name = '張三';
select * from student_class_id;

查詢創(chuàng)建的視圖，張三的年齡也被修改成50。

3.3.2 修改視圖

修改視圖的數(shù)據(jù)也會影響到表中的數(shù)據(jù)。

update student_class_id set age = 100 where name = '李四';
select * from student;

查詢表中李四的年齡變成了100。

3.3.3 視圖不可修改

有些情況創(chuàng)建的視圖不能夠修改：

• 創(chuàng)建視圖的時候使用了聚合函數(shù)的視圖。比如sum,avg等。
• 創(chuàng)建視圖的時候使用了distinct，對查詢結(jié)果進行了去重，此時修改不知道修改的是表中的那行數(shù)據(jù)。
• 創(chuàng)建視圖的時候使用了 group by 或者 having，對查詢結(jié)果進行了分組，此時視圖一行數(shù)據(jù)包含表中多條數(shù)據(jù)，修改視圖不知道修改表的那條數(shù)據(jù)。
• 創(chuàng)建視圖的時候使用了union或者union all，合并的查詢來自不同的表時候，不能確定修改視圖的行對應那個表的哪個行。
• 創(chuàng)建視圖的時候使用了子查詢，簡單的子查詢可以修改視圖，但是復雜的子查詢，子查詢的查詢語句中包含聚合函數(shù)group by之類的不能修改視圖。
• 創(chuàng)建視圖的時候查詢條件是一個表跟一個不可修改的視圖進行關(guān)聯(lián)查詢。

3.4 刪除視圖

drop view student_class_id1;

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論