sql之事務(wù)和并發(fā)
1、Transaction（事務(wù)）是什么：
?事務(wù)是作為單一工作單元而執(zhí)行的一系列操作。包括增刪查改。
2、事務(wù)的種類：
?事務(wù)分為顯示事務(wù)和隱式事務(wù)：
隱式事務(wù)：就是平常我們使用每一條sql 語句就是一個事務(wù)，只不過他們執(zhí)行完成之后事務(wù)就跟著結(jié)束了。
顯示事務(wù)：就是需要我們來手寫了，這個時候就可以進(jìn)行控制事務(wù)的開始和結(jié)束了。
1
--
顯式事務(wù)（對事物可以進(jìn)行控制）
2
3
--
開始事務(wù)
4
begin
transaction
;
5
update
[
Sales.Shippers
]
6
set
companyname
=
'
順豐
'
where
shipperid
=
5
;
7
8
select
*
from
[
Sales.Shippers
]
;
9
10
--
結(jié)束事務(wù)：
11
--
第一種：事務(wù)的回滾
12
rollback
;
13
14
--
第二種：事務(wù)的提交
15
commit
;
?
3、事務(wù)很重要的四個屬性：?
?1、 原子性 ：事務(wù)必須是原子工作單位?！谑聞?wù)中修改數(shù)據(jù)，要么全都執(zhí)行，要么全都不執(zhí)行。在事務(wù)執(zhí)行完成之前（調(diào)提交指令寫入到sql的事務(wù)日志之前），出現(xiàn)問題或重啟，sql server 會回滾所有的修改事務(wù)。 但是也有例外的錯誤不會回滾事務(wù)————例如：主鍵沖突和鎖超時等。 ?錯誤日志會 捕獲這些錯誤的指令，并記錄日志里面，然后執(zhí)行一些操作（例如：回滾事務(wù)）
?
2、 一致性 ：發(fā)生在同一進(jìn)程的事物里面的 修改和 查詢是不會產(chǎn)生沖突的。保持訪問的數(shù)據(jù)的一致性。
?
3、 隔離性 ：控制數(shù)據(jù)訪問的機(jī)制； 說明： 一個事務(wù)正在對一個表的數(shù)據(jù)正在修改， 還沒有執(zhí)行完成；；這時另一個事務(wù)，想要查詢里面的數(shù)據(jù)，是不能查到的，必須等到 修改的事務(wù)執(zhí)行完成。：sql server 采用的 “鎖”的機(jī)制，將正在修改的事務(wù) 處理的表的數(shù)據(jù) 鎖定。這樣是為了保證數(shù)據(jù)同步，數(shù)據(jù)的一致性。
?
4、 持久性 ： ?當(dāng)一個事務(wù)的指令 已經(jīng)提交到 事務(wù)日志里面，即使磁盤上的數(shù)據(jù)還沒有修改，這個時候數(shù)據(jù)庫的服務(wù)停止，在服務(wù)重啟的時候還會將事務(wù)日志里的指令執(zhí)行(進(jìn)行回復(fù)處理)。保證數(shù)據(jù)的持久性。
?
上面將基本的事務(wù)介紹了一下，下面開始介紹并發(fā)。所以必須要介紹就是事務(wù)的“鎖”。
4、事務(wù)中的鎖
?事務(wù)中都含有什么鎖呢？
最常用的鎖：排它鎖（獨占鎖）和共享鎖，還有其他的鎖，這里就不做介紹了，比如：更新鎖、架構(gòu)鎖、意向鎖等。
?
5、排它鎖和共享鎖
?排它鎖：
當(dāng)一個事務(wù)執(zhí)行更新修改操作的時候會申請排它鎖，主要是在寫操作里面使用。需要注意的兩點：1、一個事務(wù)含有排它鎖，就不能含有其他任何鎖。2、一條數(shù)據(jù)只能被一個排它鎖鎖住，就不能再被其他排他鎖鎖定。
共享鎖：
?主要是在讀操作中使用，并且多個事務(wù)可以同時對一條數(shù)據(jù)使用共享鎖。
?
排它鎖和共享鎖最重要的區(qū)別：排它鎖是不能被控制他的處理方式和時間，但是共享鎖是可以控制其隔離級別來控制其處理的時間。
1
begin
transaction
;
2
update
[
Sales.Shippers
]
set
companyname
=
'
順豐
'
where
shipperid
=
5
;
3
--
事務(wù)還沒有查詢完成，為這條數(shù)據(jù) 加上一個 排它鎖。這時這條數(shù)據(jù)就不能被其他進(jìn)程 訪問到
?
事務(wù)還沒有執(zhí)行完成，再開一個線程，執(zhí)行查詢操作
1
select
*
from
[
Sales.Shippers
]
where
shipperid
=
5
?
因為讀操作默認(rèn)使用的共享鎖，但是這個時候這條數(shù)據(jù)已經(jīng)被其他線程的排它鎖鎖住，所以會造成阻塞，直到排它鎖釋放。
?
6、隔離級別?
?首先要先明白三點：
1、用于控制并發(fā)用戶如何讀寫數(shù)據(jù)的操做。
2、讀操作默認(rèn)使用共享鎖；寫操作需要使用排它鎖。
3、讀操作能夠控制他的處理的方式，寫操作不能控制它的處理方式
?
隔離級別分為六種：
read uncommited（讀取未提交數(shù)據(jù)），read commited（讀取已提交數(shù)據(jù)）讀取的默認(rèn)方式，repeatable read（可重復(fù)讀），serializable（可序列化），snapshot（快照），read commited snapshot（已經(jīng)提交讀隔離）（后兩個是sql server 2005 里面 引入的）。隔離的強(qiáng)度依次遞增。
?
?1、read uncommitted:
1
select
*
from
[
Sales.Shippers
]
where
shipperid
=
3
;
?
?查詢結(jié)果：
在本線程內(nèi)執(zhí)行：
1
begin
transaction
;
2
update
[
Sales.Shippers
]
set
companyname
=
'
圓通
'
where
shipperid
=
3
;
?
在另外一個線程內(nèi) 使用 read uncommitted 隔離級別 查詢數(shù)據(jù)：
1
--
設(shè)置讀操作的隔離級別
2
set
transaction
isolation
level
read
uncommitted
;
3
select
*
from
[
Sales.Shippers
]
where
shipperid
=
3;
?查詢結(jié)果：
如果這個時候?qū)⒛莻€事務(wù)回滾，那么這個時候 ?查詢到的數(shù)據(jù)就是“臟數(shù)據(jù)”。
總結(jié)：
read uncommitted：最低的隔離級別：查詢的時候不會請求共享鎖，所以不會和排它鎖產(chǎn)生沖突（不會等待排它鎖執(zhí)行完），查詢效率非常高，速度飛快。但是缺點：會查到“臟數(shù)據(jù)”（排它鎖的事務(wù)已經(jīng)將數(shù)據(jù)修改，還沒提交，這個時候查詢到的數(shù)據(jù) 是已經(jīng)更改過的。如果事務(wù)回滾，就是“臟數(shù)據(jù)”）
優(yōu)點：查詢效率非常高，速度非?？臁?
缺點：會產(chǎn)生“臟數(shù)據(jù)”
適用性：
適用于 像聊天軟件的 聊天記錄，會是軟件的運行速度非?？臁?但是不適用于 商務(wù)軟件。尤其是銀行
?
2、read committed
?讀取的默認(rèn)隔離級別就是read committed 和上面正好相反。如果上面情況，采用read committed 隔離級別查詢的話查到的就是還沒有更改之前的數(shù)據(jù)。
?所以在這里就不再演示。
3、repeatable read：
?查詢的時候會加上共享鎖，但是查詢完成之后，共享鎖就會被撤銷。比如一些購票系統(tǒng)，如果查到票了，當(dāng)買的時候就沒有，這是不行的。所以要在查詢到數(shù)據(jù)之后做一些延遲共享鎖，進(jìn)而阻塞排它鎖來修改。
在查詢線程里面執(zhí)行sql語句：
1
set
transaction
isolation
level
repeatable
read
;
2
begin
transaction
;
3
select
*
from
[
Sales.Shippers
]
where
shipperid
=
4
;
?
然后在 另外一個線程內(nèi)執(zhí)行修改語句：
update
[
Sales.Shippers
]
set
companyname
=
'
shit
'
where
shipperid
=
4
;
?
這個時候會將更改的線程阻塞掉：
?
4、serializable（可序列化）
?更高級的 隔離。用戶解決“幻讀”。就是使用上面的 ?加上共享鎖 并不撤銷，如果鎖定的 一行數(shù)據(jù)，那么 其他的進(jìn)程 還可以對 其他的數(shù)據(jù)進(jìn)行操作，也可以 進(jìn)行新增和刪除的操作。 ? 所以如果想要在查詢的時候，不能對整張表進(jìn)行任何操作，那么就要 將表的結(jié)構(gòu)也 鎖定 ? ?（就需要使用 更強(qiáng)的 鎖定）
在查詢線程執(zhí)行sql語句：
1
set
transaction
isolation
level
serializable
;
2
3
begin
transaction
;
4
select
*
from
[
Sales.Shippers
]
where
shipperid
=
3
;
?
那么在另外一個線程執(zhí)行下面兩個語句，不論那一條語句都會阻塞?。?
update
[
Sales.Shippers
]
set
companyname
=
'
聯(lián)邦
'
where
shipperid
=
3
;
insert
into
[
Sales.Shippers
]
(companyname,phone)
values
(
'
韻達(dá)
'
,
'
12345678
'
)
?
總結(jié)：
可序列話 隔離讀操作：用戶 解決 幻影數(shù)據(jù)（將標(biāo)的數(shù)據(jù)和表的結(jié)構(gòu)都鎖定）,是并發(fā)降低...隔離級別越高,并發(fā)越低,但是效率越低,所以不是要確定使用 ?最好不要使用
?
下面兩種隔離級別是在 sql server 2005才出現(xiàn)的，隔離級別更高：
5、snapshot（快照）
?為數(shù)據(jù)產(chǎn)生一個臨時數(shù)據(jù)庫，當(dāng)sql server 數(shù)據(jù)更新之前將當(dāng)前數(shù)據(jù)庫復(fù)制到 tempdb數(shù)據(jù)庫里面，查詢就是從tempdb數(shù)據(jù)庫中查詢
--
設(shè)置數(shù)據(jù)庫支持快照隔離級別：
alter
database
ssdemo
set
allow_snapshot_isolation
on
;
--
這個時候會產(chǎn)生一個臨時數(shù)據(jù)庫（寫操作的排它鎖鎖定的是 現(xiàn)實存在的數(shù)據(jù)庫，，讀操作的讀取的是 臨時數(shù)據(jù)庫）
?
?在一個線程中執(zhí)行 更新操作，用排它鎖鎖定當(dāng)前數(shù)據(jù)
begin
transaction
;
--
使用 排它鎖（獨占鎖）X，鎖定 下面的那條數(shù)據(jù)
update
[
Sales.Shippers
]
set
companyname
=
'
飛鳳
'
where
shipperid
=
3
;
?
這個時候在在另外一個線程中查詢這條數(shù)據(jù)（默認(rèn)的隔離級別），就會將當(dāng)前線程阻塞。
如果使用 snapshot 隔離級別查詢就不會阻塞。
1
set
transaction
isolation
level
snapshot;
2
--
下面的就可以 從臨時數(shù)據(jù)庫中查詢到數(shù)據(jù)
3
begin
transaction
;
4
--
使用 共享鎖 S
5
select
*
from
[
Sales.Shippers
]
where
shipperid
=
3
;
--
查詢到的 是還沒有完成更新之前的數(shù)據(jù)
?
但是同時也會帶來兩個問題：
1、當(dāng) 另外一個事務(wù) ?已經(jīng)提交，但是這邊的查詢到數(shù)據(jù)還是沒有修改。因為 每次查詢到的快照是針對于 本次回話對應(yīng)的那個 transaction 的，因為在這個事務(wù)里面是沒有修改的，所以查詢到的數(shù)據(jù)是沒有修改的。
2、（更新問題）因為 那邊的數(shù)據(jù)已經(jīng)是 飛鳳公司了，但是這里還是 ? 聯(lián)邦,所以,在這個事務(wù)里面是不能對表進(jìn)行修改,因為訪問的是臨時數(shù)據(jù)庫,想要對 數(shù)據(jù)庫修改是不可能的（sql server 就會報錯，阻止修改）?
?
針對于上面兩個問題，所以下面 更高的隔離級別出現(xiàn)了 read committed snapshot：
6、read committed snapshot
?首先開啟數(shù)據(jù)庫的 read committed snapshot 隔離級別：
1
--
設(shè)置 數(shù)據(jù)庫 為 讀取已經(jīng)提交的快照 開啟
2
alter
database
ssdemo
set
read_committed_snapshot
on
;
?
在一個線程中執(zhí)行：
begin
transaction
;
update
[
Sales.Shippers
]
set
companyname
=
'
聯(lián)邦
'
where
shipperid
=
3
;
?
在另外一個線程中：
1
--
不用顯示聲明使用 read committed snapshot 隔離級別，因為設(shè)置完 read_committed_snapshot 隔離級別啟動，默認(rèn)就是 read commited snapshot 隔離級別
2
begin
transaction
;
3
select
*
from
[
Sales.Shippers
]
where
shipperid
=
3
;
--
查詢到是 已經(jīng)提交之后的數(shù)據(jù)
4
5
update
[
Sales.Shippers
]
set
companyname
=
'
xiaoxiao
'
where
shipperid
=
3
;
?
這個時候查詢到的數(shù)據(jù)是還沒有更改之前的，如果將 前面的那個回話提交，那么在查詢 查詢到的數(shù)據(jù)是 提交修改之后的數(shù)據(jù)。所以解決了上面的問題1.
如果在修改的話。也是在第一個 更新線程中的事務(wù)更新之后的數(shù)據(jù)進(jìn)行執(zhí)行修改的操作，不會報錯。
?
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
