MSSQL之十八 事務(wù)

事務(wù)(TRANSACTION)是作為單個邏輯工作單元執(zhí)行的一系列操作,這些操作作為一個整體一起向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行 ,事務(wù)是一個不可分割的工作邏輯單元 .

事務(wù)必須具備以下四個屬性，簡稱ACID 屬性：

原子性（Atomicity）：事務(wù)是一個完整的操作。事務(wù)的各步操作是不可分的（原子的）；要么都執(zhí)行，要么都不執(zhí)行

一致性（Consistency）：當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)

隔離性（Isolation）：對數(shù)據(jù)進行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)

永久性（Durability）：事務(wù)完成后，它對數(shù)據(jù)庫的修改被永久保持，事務(wù)日志能夠保持事務(wù)的永久性

/*--舉例：為什么需要事務(wù)--*/

--同一銀行，如都是農(nóng)行的帳號，可以直接轉(zhuǎn)賬

/*－--------------建表-----------------*/

--創(chuàng)建農(nóng)行帳戶表bank

IF EXISTS(SELECT * FROM sysobjects WHERE name='bank')

DROP TABLE bank

GO

CREATE TABLE bank

(

customerName CHAR(10), --顧客姓名

cardID CHAR(10) NOT NULL , --卡號

currentMoney MONEY --當(dāng)前余額

)

GO

/*---添加約束：根據(jù)銀行規(guī)定，帳戶余額不能少于1元，除非銷戶----*/

ALTER TABLE bank

ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)

GO

/*--插入測試數(shù)據(jù)：張三開戶，開戶金額為800 ；李四開戶，開戶金額1 ---*/

INSERT INTO bank(customerName,currentMoney,cardId) VALUES('張三',1000,'1001 0001')

INSERT INTO bank(customerName,currentMoney,cardId) VALUES('李四',1,'1002 0002')

GO

--查看結(jié)果

delete from bank

SELECT * FROM bank

GO

/*--轉(zhuǎn)帳測試：張三希望通過轉(zhuǎn)賬，直接匯錢給李四1000元--*/

--我們可能會這樣這樣寫代碼

--張三的帳戶少1000元，李四的帳戶多1000元

/***************開始

UPDATE bank SET currentMoney=currentMoney-1000

WHERE customerName='張三'

UPDATE bank SET currentMoney=currentMoney+1000

WHERE customerName='李四'

*********結(jié)束/

GO

--再次查看結(jié)果，結(jié)果發(fā)現(xiàn)了什么嚴(yán)重的錯誤?如何解決呢？

SELECT * FROM bank

GO

--恢復(fù)原來的數(shù)據(jù)

--UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='李四'

SET NOCOUNT ON --不顯示受影響的行數(shù)信息

print '查看轉(zhuǎn)帳事務(wù)前前前前前前的余額'

SELECT * FROM bank

GO

/*--開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的T-SQL語句都是一個整體--*/

BEGIN TRANSACTION

/*--定義變量，用于累計事務(wù)執(zhí)行過程中的錯誤--*/

DECLARE @errorSum INT

SET @errorSum=0 --初始化為0，即無錯誤

/*--轉(zhuǎn)帳：張三的帳戶少1000元，李四的帳戶多1000元*/

UPDATE bank SET currentMoney=currentMoney-200 WHERE customerName='張三'

SET @errorSum=@errorSum+@@error --累計是否有錯誤

UPDATE bank SET currentMoney=currentMoney+200 WHERE customerName='李四'

SET @errorSum=@errorSum+@@error --累計是否有錯誤

print '查看轉(zhuǎn)帳事務(wù)過程中中中中中中的余額'

SELECT * FROM bank

/*--根據(jù)是否有錯誤，確定事務(wù)是提交還是撤銷---*/

IF @errorSum<>0 --如果有錯誤

BEGIN

print '交易失敗，回滾事務(wù)'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

print '交易成功，提交事務(wù)，寫入硬盤，永久的保存'

COMMIT TRANSACTION

END

GO

print '查看轉(zhuǎn)帳事務(wù)后后后后后后后的余額'

SELECT * FROM bank

GO

--*******************************案例一

--@@rowcount 返回受上一語句影響的行數(shù)。

--select @@rowcount

--select @@error

create table tab1

(

stu_id int primary key,

stu_name varchar(5),

stu_age int,

stu_height int

)

create table tab2

(

stu_id int primary key,

stu_name varchar(5),

stu_age int,

stu_height int

)

-----------------------------開始事務(wù)------------------------------------------------------

begin transaction

insert into tab1 (stu_id,stu_name,stu_age,stu_height) values(1,'小明',22,180)

if @@error <> 0 --or @@rowcount <> 1

goto seed

insert into tab1 (stu_id,stu_name,stu_age,stu_height) values(1,'小黃',23,150)

if @@error <> 0 --or @@rowcount <> 1

goto seed

insert into tab1 (stu_id,stu_name,stu_age,stu_height) values(1,'小李',24,190)

if @@error <> 0 --or @@rowcount <> 1

goto seed

insert into tab1 (stu_id,stu_name,stu_age,stu_height) values(4,'小張',25,176)

seed:

--print @@error

if @@error <> 0 or @@rowcount <> 1

begin

--select @@error

--select @@rowcount as ssss

rollback transaction

print '發(fā)生錯誤提交無法完成！！！！'

end

else

begin

commit transaction

print '無錯誤發(fā)生提交正常！！！'

end

-------------------------------結(jié)束事務(wù)--------------------------------------------------

delete from tab1

select * from tab1

truncate table tab1

drop table tab1

drop table tab2

---------------------------------------------------------------------------------------------

Select a,b,c into tab1 from tab2 where a=2

If @@rowcount=0 Print "no rows were copied"

SELECT CONVERT(char(5), 3.147) AS 'CHAR(1)',

CONVERT(char(5), 3.147) AS 'CHAR(3)',

CONVERT(char(120), 3.147) AS 'CHAR(5)'

GO

--**************************************************************案例2

use master

go

create table 物品管理數(shù)據(jù)表

(

部門 varchar(10),

物品 varchar(10),

數(shù)量 int,

CONSTRAINT CK_物品管理數(shù)據(jù)表 CHECK (數(shù)量 > 0)

)

insert 物品管理數(shù)據(jù)表 (部門,物品,數(shù)量) values('財務(wù)部','辦公桌',2)

insert 物品管理數(shù)據(jù)表 (部門,物品,數(shù)量) values('業(yè)務(wù)部','辦公桌',10)

insert 物品管理數(shù)據(jù)表 (部門,物品,數(shù)量) values('管理部','辦公桌',5)

insert 物品管理數(shù)據(jù)表 (部門,物品,數(shù)量) values('業(yè)務(wù)部','會議桌',5)

insert 物品管理數(shù)據(jù)表 (部門,物品,數(shù)量) values('研發(fā)部','會議桌',7)

insert 物品管理數(shù)據(jù)表 (部門,物品,數(shù)量) values('生產(chǎn)部','會議桌',8)

go

select * from 物品管理數(shù)據(jù)表

truncate table 物品管理數(shù)據(jù)表

drop table 物品管理數(shù)據(jù)表

--顯式事務(wù)

-------------------------事務(wù)開始----------------------------------------

Begin Transaction --開始事務(wù)

update 物品管理數(shù)據(jù)表

set 數(shù)量 = 數(shù)量 + 1

where 部門='業(yè)務(wù)部' and 物品='辦公桌'

if @@error>0 --or @@rowcount<>1

begin

goto error1

end

update 物品管理數(shù)據(jù)表

set 數(shù)量 = 數(shù)量 - 1

where 部門='財務(wù)部' and 物品='辦公桌'

error1:

if @@error>0 --or @@rowcount<>1

begin

print '毛病!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'

rollback transaction --取消并回滾事務(wù)

end

else

--print '毛病！！！'

commit tran --提交事務(wù)

select @@rowcount

select @@error

-------------------------事物結(jié)束----------------------------------------

-------------------------查詢結(jié)果----------------------------------------

select * from 物品管理數(shù)據(jù)表

--**********************************隱形事務(wù)

set implicit_transactions on --

--set implicit_transactions off --關(guān)閉隱含事務(wù)模式

--隱性事務(wù)一般只使用在測試或查錯上，由于會占用大量資源，

--因此并不建議在數(shù)據(jù)庫實際運作時使用。

--**********************************

create table 物品管理

(

物品id int not null primary key,

物品名稱 char(10),

物品數(shù)量 int,

部門 char(10)

)

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(1,'桌子',12,'行政部')

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(2,'板凳',23,'學(xué)術(shù)部')

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(3,'書架',33,'市場部')

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(4,'電腦',22,'人事部')

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(5,'杯子',6,'財務(wù)部')

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(6,'鼠標(biāo)',45,'組織部')

select * from 物品管理

---------------------------------------事務(wù)開始---------------------------------------------

begin transaction object

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(7,'C語言',2,'開發(fā)部')

save transaction jet

insert into 物品管理 (物品id,物品名稱,物品數(shù)量,部門) values(8,'Java',9,'開發(fā)部')

if @@error <> 0

begin

rollback tran jet

print '輸入記錄出現(xiàn)問題，請重新檢查！！！'

end

commit tran object

---------------------------------------事務(wù)結(jié)束---------------------------------------------

truncate table 物品管理

drop table 物品管理

select * from 物品管理

---*************************************************************事務(wù)保存點2

begin tran affair

....... -- 操作語句

save tran temptran

...... -- 操作語句

if (@@error <> 0)

rollback tran temptran -- 回滾到事務(wù)保存點

else

commit tran affair

----------------------示 例----------------------------------

create table stu_info

(

stu_id int primary key not null,

stu_name varchar(5),

stu_age int,

stu_height int

)

insert into stu_info (stu_id,stu_name,stu_age,stu_height) values(1,'小明',22,180)

insert into stu_info (stu_id,stu_name,stu_age,stu_height) values(2,'小黃',23,150)

insert into stu_info (stu_id,stu_name,stu_age,stu_height) values(3,'小張',25,176)

insert into stu_info (stu_id,stu_name,stu_age,stu_height) values(4,'小王',26,164)

insert into stu_info (stu_id,stu_name,stu_age,stu_height) values(5,'小兵',24,170)

------------------------------開始事務(wù)-------------------------------------

begin transaction stu

delete from stu_info

where stu_id = 1

save transaction protec

update stu_info

set stu_name = '紅旗'

where stu_id in (3,5)

if @@error > 0 or @@rowcount <> 1

rollback tran protec

else

commit transaction stu

----------------結(jié)束事務(wù)--------------------------------------------------

select * from stu_info

truncate table stu_info

--**************************************************************鎖

select suser_sid('Arwen')

select suser_sname(0x2EBCE6E90123D24AA542D8F538F278AD)

select user_name(3)

select user_id('guest')

-----------------------------------------------------------------------------------------------------------------------------

use northwind

SELECT *

FROM Employees WITH (nolock) --這個語句就提供出了所有的數(shù)據(jù)，包括正在被其它處理器使用的數(shù)據(jù)，所以，得出的數(shù)據(jù)可能是臟數(shù)據(jù)，但是對于任務(wù)而言并沒有很大的影響。

UPDATE

Employees WITH (tablock)

SET Title='Test' -- 這個例子就是更新表中所有的行，所以使用了一個表鎖。

/*

FASTFIRSTROW —選取結(jié)果集中的第一行，并將其優(yōu)化

HOLDLOCK —持有一個共享鎖直至事務(wù)完成

NOLOCK —不允許使用共享鎖或獨享鎖。這可能會造成數(shù)據(jù)重寫或者沒有被確認(rèn)就返回的情況；因此，就有可能使用到臟數(shù)據(jù)。這個提示只能在SELECT中使用。

PAGLOCK —鎖表格

READCOMMITTED —只讀取被事務(wù)確認(rèn)的數(shù)據(jù)。這就是SQL Server的默認(rèn)行為。

READPAST —跳過被其它進程鎖住的行，所以返回的數(shù)據(jù)可能會忽略行的內(nèi)容。這也只能在SELECT中使用。

READUNCOMMITTED —等價于NOLOCK.

REPEATABLEREAD —在查詢語句中，對所有數(shù)據(jù)使用鎖。這可以防止其它的用戶更新數(shù)據(jù)，但是新的行可能被其它的用戶插入到數(shù)據(jù)中，并且被最新訪問該數(shù)據(jù)的用戶讀取。

ROWLOCK —按照行的級別來對數(shù)據(jù)上鎖。SQL Server通常鎖到頁或者表級別來修改行，所以當(dāng)開發(fā)者使用單行的時候，通常要重設(shè)這個設(shè)置。

SERIALIZABLE —等價于HOLDLOCK.

TABLOCK —按照表級別上鎖。在運行多個有關(guān)表級別數(shù)據(jù)操作的時候，你可能需要使用到這個提示。

UPDLOCK —當(dāng)讀取一個表的時候，使用更新鎖來代替共享鎖，并且保持一直擁有這個鎖直至事務(wù)結(jié)束。它的好處是，可以允許你在閱讀數(shù)據(jù)的時候可以不需要鎖，并且以最快的速度更新數(shù)據(jù)。

XLOCK —給所有的資源都上獨享鎖，直至事務(wù)結(jié)束。

*/

--*****************************************************************臟讀

create database bank

go

use bank

go

create table student

(

stud_id int,

stud_name char(10),

grade int

)

drop table student

insert into student(stud_id,stud_name,grade)

values(1,'小貴子',79)

insert into student(stud_id,stud_name,grade)

values(2,'小春子',98)

insert into student(stud_id,stud_name,grade)

values(3,'小溜子',68)

insert into student(stud_id,stud_name,grade)

values(4,'小毛子',86)

delete from student

select * from student

===================================================

--set implicit_transactions on

--set implicit_transactions off

------------------------------------------------------------------------

--========================事務(wù)缺陷======================================

--臟讀

begin transaction

update student

set grade=100

where stud_id=1

if @@error >0

rollback transaction

commit transaction

--=======================不可重復(fù)讀==============================

--事務(wù)查詢

set transaction isolation level

repeatable read

---============================================

set transaction isolation level

repeatable read

begin transaction

select * from student

--where stud_id=1

commit transaction

--=============================================

--事務(wù)一

set transaction isolation level

repeatable read

begin transaction

update student

set grade=220

where stud_id=1

commit transaction

--事務(wù)二

set transaction isolation level

repeatable read

begin transaction

select * from student

where stud_id=1

commit transaction

--************************隔離級別

--隔離級別

級別一 read uncommitted

級別二 read committed

級別三 repeatable read

級別四 serializable

set transaction isolation level

repeatable read

begin transaction

select * from student

where stud_id=1

commit transaction

SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。