MSSQL之十八 事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作,这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 ,事务是一个不可分割的工作逻辑单元 .

事务必须具备以下四个属性，简称ACID 属性：

原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行

一致性（Consistency）：当事务完成时，数据必须处于一致状态

隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

/*--举例：为什么需要事务--*/

--同一银行，如都是农行的帐号，可以直接转账

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

--创建农行帐户表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 --当前余额

)

GO

/*---添加约束：根据银行规定，帐户余额不能少于1元，除非销户----*/

ALTER TABLE bank

ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)

GO

/*--插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 ---*/

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

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

GO

--查看结果

delete from bank

SELECT * FROM bank

GO

/*--转帐测试：张三希望通过转账，直接汇钱给李四1000元--*/

--我们可能会这样这样写代码

--张三的帐户少1000元，李四的帐户多1000元

/***************开始

UPDATE bank SET currentMoney=currentMoney-1000

WHERE customerName='张三'

UPDATE bank SET currentMoney=currentMoney+1000

WHERE customerName='李四'

*********结束/

GO

--再次查看结果，结果发现了什么严重的错误?如何解决呢？

SELECT * FROM bank

GO

--恢复原来的数据

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

SET NOCOUNT ON --不显示受影响的行数信息

print '查看转帐事务前前前前前前的余额'

SELECT * FROM bank

GO

/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/

BEGIN TRANSACTION

/*--定义变量，用于累计事务执行过程中的错误--*/

DECLARE @errorSum INT

SET @errorSum=0 --初始化为0，即无错误

/*--转帐：张三的帐户少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 '查看转帐事务过程中中中中中中的余额'

SELECT * FROM bank

/*--根据是否有错误，确定事务是提交还是撤销---*/

IF @errorSum<>0 --如果有错误

BEGIN

print '交易失败，回滚事务'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

print '交易成功，提交事务，写入硬盘，永久的保存'

COMMIT TRANSACTION

END

GO

print '查看转帐事务后后后后后后后的余额'

SELECT * FROM bank

GO

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

--@@rowcount 返回受上一语句影响的行数。

--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

)

-----------------------------开始事务------------------------------------------------------

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 '发生错误提交无法完成！！！！'

end

else

begin

commit transaction

print '无错误发生提交正常！！！'

end

-------------------------------结束事务--------------------------------------------------

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 物品管理数据表

(

部门 varchar(10),

物品 varchar(10),

数量 int,

CONSTRAINT CK_物品管理数据表 CHECK (数量 > 0)

)

insert 物品管理数据表 (部门,物品,数量) values('财务部','办公桌',2)

insert 物品管理数据表 (部门,物品,数量) values('业务部','办公桌',10)

insert 物品管理数据表 (部门,物品,数量) values('管理部','办公桌',5)

insert 物品管理数据表 (部门,物品,数量) values('业务部','会议桌',5)

insert 物品管理数据表 (部门,物品,数量) values('研发部','会议桌',7)

insert 物品管理数据表 (部门,物品,数量) values('生产部','会议桌',8)

go

select * from 物品管理数据表

truncate table 物品管理数据表

drop table 物品管理数据表

--显式事务

-------------------------事务开始----------------------------------------

Begin Transaction --开始事务

update 物品管理数据表

set 数量 = 数量 + 1

where 部门='业务部' and 物品='办公桌'

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

begin

goto error1

end

update 物品管理数据表

set 数量 = 数量 - 1

where 部门='财务部' and 物品='办公桌'

error1:

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

begin

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

rollback transaction --取消并回滚事务

end

else

--print '毛病！！！'

commit tran --提交事务

select @@rowcount

select @@error

-------------------------事物结束----------------------------------------

-------------------------查询结果----------------------------------------

select * from 物品管理数据表

--**********************************隐形事务

set implicit_transactions on --

--set implicit_transactions off --关闭隐含事务模式

--隐性事务一般只使用在测试或查错上，由于会占用大量资源，

--因此并不建议在数据库实际运作时使用。

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

create table 物品管理

(

物品id int not null primary key,

物品名称 char(10),

物品数量 int,

部门 char(10)

)

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(1,'桌子',12,'行政部')

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(2,'板凳',23,'学术部')

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(3,'书架',33,'市场部')

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(4,'电脑',22,'人事部')

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(5,'杯子',6,'财务部')

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(6,'鼠标',45,'组织部')

select * from 物品管理

---------------------------------------事务开始---------------------------------------------

begin transaction object

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(7,'C语言',2,'开发部')

save transaction jet

insert into 物品管理 (物品id,物品名称,物品数量,部门) values(8,'Java',9,'开发部')

if @@error <> 0

begin

rollback tran jet

print '输入记录出现问题，请重新检查！！！'

end

commit tran object

---------------------------------------事务结束---------------------------------------------

truncate table 物品管理

drop table 物品管理

select * from 物品管理

---*************************************************************事务保存点2

begin tran affair

....... -- 操作语句

save tran temptran

...... -- 操作语句

if (@@error <> 0)

rollback tran temptran -- 回滚到事务保存点

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)

------------------------------开始事务-------------------------------------

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

----------------结束事务--------------------------------------------------

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) --这个语句就提供出了所有的数据，包括正在被其它处理器使用的数据，所以，得出的数据可能是脏数据，但是对于任务而言并没有很大的影响。

UPDATE

Employees WITH (tablock)

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

/*

FASTFIRSTROW —选取结果集中的第一行，并将其优化

HOLDLOCK —持有一个共享锁直至事务完成

NOLOCK —不允许使用共享锁或独享锁。这可能会造成数据重写或者没有被确认就返回的情况；因此，就有可能使用到脏数据。这个提示只能在SELECT中使用。

PAGLOCK —锁表格

READCOMMITTED —只读取被事务确认的数据。这就是SQL Server的默认行为。

READPAST —跳过被其它进程锁住的行，所以返回的数据可能会忽略行的内容。这也只能在SELECT中使用。

READUNCOMMITTED —等价于NOLOCK.

REPEATABLEREAD —在查询语句中，对所有数据使用锁。这可以防止其它的用户更新数据，但是新的行可能被其它的用户插入到数据中，并且被最新访问该数据的用户读取。

ROWLOCK —按照行的级别来对数据上锁。SQL Server通常锁到页或者表级别来修改行，所以当开发者使用单行的时候，通常要重设这个设置。

SERIALIZABLE —等价于HOLDLOCK.

TABLOCK —按照表级别上锁。在运行多个有关表级别数据操作的时候，你可能需要使用到这个提示。

UPDLOCK —当读取一个表的时候，使用更新锁来代替共享锁，并且保持一直拥有这个锁直至事务结束。它的好处是，可以允许你在阅读数据的时候可以不需要锁，并且以最快的速度更新数据。

XLOCK —给所有的资源都上独享锁，直至事务结束。

*/

--*****************************************************************脏读

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

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

--========================事务缺陷======================================

--脏读

begin transaction

update student

set grade=100

where stud_id=1

if @@error >0

rollback transaction

commit transaction

--=======================不可重复读==============================

--事务查询

set transaction isolation level

repeatable read

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

set transaction isolation level

repeatable read

begin transaction

select * from student

--where stud_id=1

commit transaction

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

--事务一

set transaction isolation level

repeatable read

begin transaction

update student

set grade=220

where stud_id=1

commit transaction

--事务二

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。