SQL Server事務(wù)全攻略(一)
一 事務(wù)的屬性
事務(wù)具有ACID屬性，即 Atomic原子性，Consistent一致性， Isolated隔離性， Durable永久性
?
原子性
就是事務(wù)應(yīng)作為一個工作單元，事務(wù)處理完成，所有的工作要么都在數(shù)據(jù)庫中保存下來，要么完全?
回滾，全部不保留
一致性
事務(wù)完成或者撤銷后，都應(yīng)該處于一致的狀態(tài)
隔離性
多個事務(wù)同時進行，它們之間應(yīng)該互不干擾.應(yīng)該防止一個事務(wù)處理其他事務(wù)也要修改的數(shù)據(jù)時，?
不合理的存取和不完整的讀取數(shù)據(jù)
永久性
事務(wù)提交以后，所做的工作就被永久的保存下來
二 事務(wù)并發(fā)處理會產(chǎn)生的問題
丟失更新
當兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，會發(fā)生丟失更新問題、?
每個事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。
臟讀
當?shù)诙€事務(wù)選擇其它事務(wù)正在更新的行時，會發(fā)生未確認的相關(guān)性問題。第二個事務(wù)正在讀取的數(shù)據(jù)還沒有確認并且可能由更新此行的事務(wù)所更改。
不可重復(fù)讀
當?shù)诙€事務(wù)多次訪問同一行而且每次讀取不同的數(shù)據(jù)時，會發(fā)生不一致的分析問題。不一致的分析與未確認的相關(guān)性類似，因為其它事務(wù)也是正在更改第二個事務(wù)正在讀取的數(shù)據(jù)。然而，在不一致的分析中，第二個事務(wù)讀取的數(shù)據(jù)是由已進行了更改的事務(wù)提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次信息都由其它事務(wù)更改；因而該行被非重復(fù)讀取。
幻像讀
當對某行執(zhí)行插入或刪除操作，而該行屬于某個事務(wù)正在讀取的行的范圍時，會發(fā)生幻像讀問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因為該行已被其它事務(wù)刪除。同樣，由于其它事務(wù)的插入操作，事務(wù)的第二次或后續(xù)讀顯示有一行已不存在于原始讀中。
三 事務(wù)處理類型
自動處理事務(wù)
系統(tǒng)默認每個T－SQL命令都是事務(wù)處理 由系統(tǒng)自動開始并提交
隱式事務(wù)
當有大量的DDL 和DML命令執(zhí)行時會自動開始，并一直保持到用戶明確提交為止，切換隱式事務(wù)可以用SET? IMPLICIT_TRANSACTIONS? 為連接設(shè)置隱性事務(wù)模式.當設(shè)置為 ON 時，SET IMPLICIT_TRANSACTIONS 將連接設(shè)置為隱性事務(wù)模式。當設(shè)置為 OFF 時，則使連接返回到自動提交事務(wù)模式
用戶定義事務(wù)
由用戶來控制事務(wù)的開始和結(jié)束 命令有: begin tran commit tran rollback tran 命令
分布式事務(wù)
跨越多個服務(wù)器的事務(wù)稱為分布式事務(wù)，sql server 可以由DTc microsoft distributed transaction coordinator?
來支持處理分布式事務(wù)，可以使用 BEgin distributed transaction 命令啟動一個分布式事務(wù)處理
?
四 事務(wù)處理的隔離級別
使用SET TRANSACTION ISOLATION LEVEL來控制由連接發(fā)出的所有語句的默認事務(wù)鎖定行為
從低到高依次是 READ UNCOMMITTED
執(zhí)行臟讀或 0 級隔離鎖定，這表示不發(fā)出共享鎖，也不接受排它鎖。當設(shè)置該選項時，可以對數(shù)據(jù)執(zhí)行未提交讀或臟讀；在事務(wù)結(jié)束前可以更改數(shù)據(jù)內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集消失。該選項的作用與在事務(wù)內(nèi)所有語句中的所有表上設(shè)置 NOLOCK 相同。這是四個隔離級別中限制最小的級別。
舉例，設(shè)table1(A，B，C)
| A | ?B | C |
| a1 | ?b1 | ?c1 |
| a2 | ?b2 | c2 |
| a3 | ?b3 | ?c3 |
新建兩個連接
在第一個連接中執(zhí)行以下語句?
select
*
from table1 begin tran update table1
set
c
=
'
c
'
select
*
from table1 waitfor delay
'
00:00:10
'
--
等待10秒 rollback tran select
*
from table1
? 在第二個連接中執(zhí)行以下語句
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED print
'
臟讀
'
select
*
from table1
if
@@rowcount
>
0
begin waitfor delay
'
00:00:10
'
print
'
不重復(fù)讀
'
select
*
from table1 end
第二個連接的結(jié)果
臟讀?
| A | ?B | C |
| a1 | ?b1 | ?c1 |
| a2 | ?b2 | ?c |
| a3 | ?b3 | c |
'不重復(fù)讀'
| A | ?B | C |
| a1 | ?b1 | ?c1 |
| a2 | ?b2 | ?c2 |
| a3 | ?b3 | c3 |
READ COMMITTED
指定在讀取數(shù)據(jù)時控制共享鎖以避免臟讀，但數(shù)據(jù)可在事務(wù)結(jié)束前更改，從而產(chǎn)生不可重復(fù)讀取或幻像數(shù)據(jù)。該選項是 SQL Server 的默認值。
在第一個連接中執(zhí)行以下語句
SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran print
'
初始
'
select
*
from table1 waitfor delay
'
00:00:10
'
--
等待10秒 print
'
不重復(fù)讀
'
select
*
from table1 rollback tran
在第二個連接中執(zhí)行以下語句
SET TRANSACTION ISOLATION LEVEL READ COMMITTED update table1
set
c
=
'
c
'
第一個連接的結(jié)果
初始
| A | B | C |
| a1 | ?b1 | ?c1 |
| a2 | ?b2 | ?c2 |
| a3 | ?b3 | ?c3 |
不重復(fù)讀
| A | B | C |
| a1 | b1 | c |
| a2 | ?b2 | c |
| a3 | ?b3 | ?c |
REPEATABLE READ
鎖定查詢中使用的所有數(shù)據(jù)以防止其他用戶更新數(shù)據(jù)，但是其他用戶可以將新的幻像行插入數(shù)據(jù)集，且幻像行包括在當前事務(wù)的后續(xù)讀取中。因為并發(fā)低于默認隔離級別，所以應(yīng)只在必要時才使用該選項。
在第一個連接中執(zhí)行以下語句?
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ begin tran print
'
初始
'
select
*
from table1 waitfor delay
'
00:00:10
'
--
等待10秒 print
'
幻像讀
'
select
*
from table1 rollback tran
? 在第二個連接中執(zhí)行以下語句
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ insert table1 select
'
a4
'
，
'
b4
'
，
'
c4
'
第一個連接的結(jié)果
初始
| A | B | C |
| a1 | b1 | ?c1 |
| a2 | ?b2 | c2 |
| a3 | ?b3 | ?c3 |
幻像讀
| A |
B |
C |
| a1 | ?b1 | ?c1 |
| a2 | b2 | ?c2 |
| a3 | ?b3 | ?c3 |
| a4 | b4 | ?c4 |
???? SERIALIZABLE
在數(shù)據(jù)集上放置一個范圍鎖，以防止其他用戶在事務(wù)完成之前更新數(shù)據(jù)集或?qū)⑿胁迦霐?shù)據(jù)集內(nèi)。這是四個隔離級別中限制最大的級別。因為并發(fā)級別較低，所以應(yīng)只在必要時才使用該選項。該選項的作用與在事務(wù)內(nèi)所有 SELECT 語句中的所有表上設(shè)置 HOLDLOCK 相同。
在第一個連接中執(zhí)行以下語句
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE begin tran print
'
初始
'
select
*
from table1 waitfor delay
'
00:00:10
'
--
等待10秒 print
'
沒有變化
'
select
*
from table1 rollback tran
在第二個連接中執(zhí)行以下語句
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE insert table1 select
'
a4
'
，
'
b4
'
，
'
c4
'
第一個連接的結(jié)果
初始
| A | B | C |
| a1 | ?b1 | ?c1 |
| a2 | ?b2 | c2 |
| a3 | ?b3 | ?c3 |
| A | B | C |
| a1 | ?b1 | ?c1 |
| a2 | ?b2 | c2 |
| a3 | ?b3 | c3 |
五 事務(wù)處理嵌套的語法和對@@TRANCOUNT的影響
BEGIN TRAN @@TRANCOUNT＋
1
COMMIT TRAN @@TRANCOUNT－
1
ROLLBACK TRAN
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
