MSSQL之十八 事务
【摘要】
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作,这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 ,事务是一个不可分割的工作逻辑单元 . 事务必须具备以下四个属性，简称ACID 属性：原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行一...
-
事务(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
-
-
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/52389360
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）