1. 什么是事務(wù)

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

比如說有兩個(gè)人A和B，A有 1000， B有1000，

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

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

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

回滾：

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

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

當(dāng)然會(huì)，因?yàn)镸ySQL在執(zhí)行事務(wù)的時(shí)候，會(huì)記錄一個(gè)日志，來記錄當(dāng)前事務(wù)執(zhí)行到那步了，當(dāng)恢復(fù)電后再進(jìn)入MySQL，MySQL會(huì)根據(jù)日志對之前執(zhí)行的操作進(jìn)行回滾，回滾完成后刪除該日志，如果事務(wù)成功執(zhí)行完也會(huì)刪除該日志。

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

2. 事務(wù)的四個(gè)特征

2.1 原子性

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

2.2 一致性

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

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

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

有時(shí)候，事務(wù)回滾到中間某個(gè)SQL語句，此時(shí)也有可能遵循一致性。

2.3 持久性

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

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

2.4 隔離性

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

2.4.1 臟讀問題

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

如何解決臟讀問題：

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

2.4.2 不可重復(fù)讀問題

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

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

圖中的事務(wù)1就存在不可重復(fù)讀問題。

如何解決不可重復(fù)讀問題：

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

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

2.4.3 幻讀問題

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

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

如何解決幻讀問題：

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

2.5 事務(wù)的使用

2.5.1創(chuàng)建事務(wù)

#開始一個(gè)事務(wù)
start transaction;
或者
begin;

#結(jié)束一個(gè)事務(wù)
commit;

#回滾當(dāng)前事務(wù)
bollback;

2.5.2 保存點(diǎn)

保存點(diǎn)是我們在創(chuàng)建事務(wù)的時(shí)候設(shè)置的，可以在回滾的時(shí)候，指定保存點(diǎn)進(jìn)行回滾。使用

bollback to 保存點(diǎn)名字，回滾到保存點(diǎn)。

例如：

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

#開始事務(wù)
begin;

select * from student;

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

select * from student;

#設(shè)置保存點(diǎn)
savepoint tmp1;

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

select * from student;

#回滾到保存點(diǎn)
rollback to tmp1;

select * from student;

#結(jié)束事務(wù)
commit;

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

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

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

2.6.1 修改隔離級(jí)別

方法一：

通過SQL語句進(jìn)行修改：

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

#隔離級(jí)別
read uncommintted 
read committed
repeatable read
serializable

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

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

這種使用SQL語句修改的方式只是臨時(shí)修改，重啟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 ，這個(gè)文件位置就是當(dāng)前我電腦上的MySQL的配置文件，在里面可以修改隔離等級(jí)。

2.6.2 查看隔離級(jí)別

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

查看全局會(huì)話隔離級(jí)別：

select @@global.transaction_isolation;

查看當(dāng)前會(huì)話的隔離級(jí)別：

select @@global.transaction_isolation;

2.5.3讀未提交（read uncommitted）

這種級(jí)別包含臟讀問題，不可重復(fù)包含問題和幻讀問題。

臟讀問題：

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

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

#修改當(dāng)前會(huì)話的隔離等級(jí)為讀未完成
set session transaction isolation level read uncommitted;

#查詢當(dāng)前會(huì)話的隔離等級(jí)
select @@session.transaction_isolation;

#事務(wù)1
begin;#1

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

commit;

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

#修改當(dāng)前會(huì)話的隔離等級(jí)為讀未完成
set session transaction isolation level read uncommitted;

#查詢當(dāng)前會(huì)話的隔離等級(jí)
select @@session.transaction_isolation;

#事務(wù)2
begin; #2

select * from student where id = 1; #2

commit; #2

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

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

2.5.4讀已提交（read committed）

該隔離等級(jí)解決了臟讀問題，包含不可重復(fù)讀問題和幻讀問題。

不可重復(fù)讀問題：

第一個(gè)連接包含事務(wù)1：

#事務(wù)1
begin; #1

select * from student where id = 1; #3

select * from student where id = 1; #5

commit; #6

第二個(gè)連接包含事務(wù)2和事務(wù)3：

#事務(wù)2
begin; #2

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

commit; #2


#事務(wù)3
begin; #4

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

commit; #4

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

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

2.5.5可重復(fù)讀（repeatable read）

該隔離等級(jí)解決了臟讀問題，不可重復(fù)讀問題，還包含著幻讀問題。

幻讀問題：

第一個(gè)連接包含事務(wù)1：

#事務(wù)1
begin; #1

select * from student; #1

select * from student; #3

commit; #4

第二個(gè)連接包含事務(wù)2：

#事務(wù)2
begin; #2

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

commit; #2

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

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

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

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

第一個(gè)連接包含事務(wù)1：

#事務(wù)1
begin; #1

select * from student; #1

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

commit; #4

第二個(gè)連接包含事務(wù)2：

#事務(wù)2
begin; #2

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

commit; #2

此時(shí)先執(zhí)行事務(wù)1中的查詢，沒有王五，再執(zhí)行事務(wù)2后，此時(shí)再執(zhí)行事務(wù)一的查詢沒有王五，但是在執(zhí)行插入操作時(shí)候，會(huì)報(bào)錯(cuò)說明王五數(shù)據(jù)已經(jīng)插入其中，因?yàn)樵诓迦肭埃琈ySQL會(huì)查詢該表是否有此元素，此時(shí)就產(chǎn)生幻讀錯(cuò)誤。

下面是事務(wù)1報(bào)錯(cuò)信息，這里事務(wù)2已經(jīng)執(zhí)行，表中已有元素事務(wù)1執(zhí)行就會(huì)報(bào)錯(cuò)：

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

下面是事務(wù)2的報(bào)錯(cuò)，這里事務(wù)2等到事務(wù)1執(zhí)行完后才執(zhí)行，此時(shí)執(zhí)行事務(wù)2就會(huì)報(bào)錯(cuò)：

2.5.6串行化（serializable）

該隔離級(jí)別是解決了臟讀問題，不可重復(fù)讀問題和幻讀問題。

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

3. 視圖

3.1 什么是視圖

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

3.2 創(chuàng)建視圖

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

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

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

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)建一個(gè)視圖：

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

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

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

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í)的表的數(shù)據(jù)，根據(jù)這個(gè)表創(chuàng)建的視圖的數(shù)據(jù)也會(huì)被修改。

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

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

3.3.2 修改視圖

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

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

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

3.3.3 視圖不可修改

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

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

3.4 刪除視圖

drop view student_class_id1;

總結(jié)

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

最新評(píng)論