【韦德体育】创建数据表,下面倒过来为了演示事务的原子性

  • 栏目:数据 时间:2020-04-30 00:21
<返回列表

以前我们也有讲过sql事务相关文章,下面我们来了解一下关于sql server 2005事务持久性一些东西,有需要的朋友参考一下。

 使用中经常用到@@Error来判断上一个语句是否执行成功,对此小结一下,可能有些不准确,欢迎指出。

创建示例数据库

原子性:事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。一致性:事务完成时,必须使所有的数据都保持一致状态。隔离性:由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。持久性:事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

1.1  介绍

SQL SERVER 中@@表示系统全局变量

(1)   返回执行的上一个 Transact-SQL 语句的错误号,如果执行没有错误,则返回 0 。

(2)   如果错误是 sys.messages 目录视图中的错误之一,则 @@ERROR 将包含 sys.messages.message_id 列中表示该错误的值。  可以在 sys.messages 中查看与 @@ERROR 错误号相关的文本信息。

(3)   由于 @@ERROR 在每一条语句执行后被清除并且重置,因此应在语句验证后立即查看它,或将其保存到一个局部变量中以备以后查看。

 

USE master;
GO
IF DB_ID (N'mytest') IS NOT NULL
DROP DATABASE mytest;
GO
CREATE DATABASE mytest;
GO
USE mytest;
GO
IF OBJECT_ID(N'dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
ProductID INT NOT NULL,
MadeFrom CHAR(20),
Sales MONEY NOT NULL
);

下面用简单的示例(Northwind数据库)来说明这一点。原子性:正常情况下是先删除订单详细表,再删除订单信息,下面倒过来为了演示事务的原子性,生产中这么写就是属于逻辑错误。

1.2  范例及使用

(1)  分析执行SQL出现错误,后续脚本的执行情况

执行语句:

UPDATE tbOrder SET OrderNo = '201605010008' WHERE OrderNo = '201605010001'

--执行上一步出现外键约束错误,并且继续执行下一步(547为约束错误)

PRINT @@ERROR  --输出错误号

 

UPDATE tbOrder SET OrderAmount = 'ABC' WHERE OrderNo = '201605010001'

--在执行上一步时出现类型异常,并且不执行下面的步骤

PRINT @@ERROR  --未输出

 

说明:第1个SQL出错后,仍然执行了后面的代码,输出了@@Error错误号,第2个SQL出错后直接终止后续执行。

说明执行SQL出现错误后,有的错误会直接终止后续执行,有的出现错误后仍然可以继续执行后续脚本

 

(2)  查询成功后,系统变量@@Error变为0

 执行语句:

DELETE FROM tbOrder WHERE OrderNo = '201605010001'

PRINT 'Delete: ' + CAST(@@ERROR AS VARCHAR(20))   --输出

SELECT 1  

PRINT 'Select: ' + + CAST(@@ERROR AS VARCHAR(20))  --输出 Select:0 

 

说明:执行SQL 成功后,@@Error参数被设置为0

 

(3)  通过Try Catch捕获错误,输出@@Error

a)  还是上面的删除操作,产生外键约束问题

执行语句:

BEGIN TRY

    DELETE FROM tbOrder WHERE OrderNo = '201605010001'

    PRINT 'DELETE ' + CAST(@@ERROR AS VARCHAR(20))   --没有输出

END TRY

BEGIN CATCH

    PRINT 'CATCH ' + CAST(@@ERROR AS VARCHAR(20))

    PRINT ERROR_MESSAGE()

    PRINT ERROR_SEVERITY()

    PRINT ERROR_STATE()

END CATCH

GO

 

输出结果:

 韦德体育 1

 

b)  还是上面的更新操作,产生错误,不执行后续代码

执行语句:

BEGIN TRY

    UPDATE tbOrder SET OrderAmount = 'ABC' WHERE OrderNo = '201605010001'

    PRINT 'UPDATE ' + CAST(@@ERROR AS VARCHAR(20))

END TRY

BEGIN CATCH

    PRINT 'CATCH ' + CAST(@@ERROR AS VARCHAR(20))

    PRINT ERROR_MESSAGE()

    PRINT ERROR_SEVERITY()

    PRINT ERROR_STATE()

END CATCH

GO

 

输出结果:

 韦德体育 2

 

说明:在Try中执行SQL产生错误后,直接被Catch捕获,Try中后续代码终止执行,直接执行Catch中的代码

 

(4)  测试有事物的操作是否产生错误后都回滚

a)  直接增加事物

执行脚本:

BEGIN TRAN

    --执行正常

    INSERT INTO dbo.tbOrderDetail( OrderNo, ProductID, Quantity, Price )

       VALUES  ( '201605010001',50,200,10)

 

    --执行报错

    DELETE FROM tbOrder WHERE OrderNo = '201605010001'

    PRINT 'DELETE: ' +  CAST(@@ERROR AS VARCHAR(20))

 

COMMIT TRAN

 

执行结果(同时查询订单明细):

 韦德体育 3

 

说明:执行出错后,前面执行的插入操作数据并没有回滚,说明直接增加事物并不能回滚出错前的数据

 

b)  增加@@Error判断执行是否成功

执行脚本:

BEGIN TRAN

    --执行正常

    INSERT INTO dbo.tbOrderDetail( OrderNo, ProductID, Quantity, Price )

       VALUES  ( '201605010001',50,200,10)

    IF @@ERROR <> 0

    BEGIN

       ROLLBACK TRAN

       RETURN

    END

 

    --执行报错

    DELETE FROM tbOrder WHERE OrderNo = '201605010001'

    --UPDATE tbOrder SET OrderAmount = 'ABC' WHERE OrderNo = '201605010001'   --将Delete操作换成更新操作产生异常后事物也会回滚

    IF @@ERROR <> 0

    BEGIN

       ROLLBACK TRAN

       RETURN

    END

 

COMMIT TRAN

 

执行结果(同时查询订单明细):

 韦德体育 4

 

说明:增加@@Error判断,执行错误时前面的数据都回滚了。同时将语句中的Delete操作换成执行已屏蔽的更新操作产生异常后,事物同样也会回滚

 

(5)  通过设置XACT_ABORT,测试出现错误整个事物是否回滚

备注:当 SET XACT_ABORT 为 ON 时,如果执行 Transact-SQL 语句产生运行时错误,则整个事务将终止并回滚。

当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。如果错误很严重,那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。OFF 是默认设置。

 

a)  XACT_ABORT 为 OFF,只有执行出错的回滚

执行脚本:

SET XACT_ABORT OFF

BEGIN TRAN

    --执行正常

    INSERT INTO dbo.tbOrderDetail( OrderNo, ProductID, Quantity, Price )

       VALUES  ( '201605010001',50,200,10)

 

    --执行报错

    DELETE FROM tbOrder WHERE OrderNo = '201605010001'

    PRINT 'DELETE: ' +  CAST(@@ERROR AS VARCHAR(20))

 

    --执行正常

    INSERT INTO dbo.tbOrderDetail( OrderNo, ProductID, Quantity, Price )

       VALUES  ( '201605010001',51,10,100)

   

COMMIT TRAN

 

执行结果(同时查询明细):

 韦德体育 5

 

b)  XACT_ABORT 为 ON,强制整个事物回滚

执行脚本:

SET XACT_ABORT ON

BEGIN TRAN

    --执行正常

    INSERT INTO dbo.tbOrderDetail( OrderNo, ProductID, Quantity, Price )

       VALUES  ( '201605010001',50,200,10)

 

    --执行报错

    DELETE FROM tbOrder WHERE OrderNo = '201605010001'

    PRINT 'DELETE: ' +  CAST(@@ERROR AS VARCHAR(20))

 

COMMIT TRAN

SET XACT_ABORT OFF

执行结果(同时查询明细):

 韦德体育 6

说明:设置XACT_ABORT为OFF(默认为OFF),只有执行出错的语句回滚了,其他的没有回滚数据;设置XACT_ABORT为ON时,当执行SQL产生错误后,强制整个事物回滚,并且不在执行后续代码

 

(6)  通过Try Catch捕获错误,显示事物执行错误,并回滚

执行脚本:

BEGIN TRAN

    BEGIN TRY

    --执行正常

    INSERT INTO dbo.tbOrderDetail( OrderNo, ProductID, Quantity, Price )

       VALUES  ( '201605010001',50,200,10)

   

    --执行报错

    DELETE FROM tbOrder WHERE OrderNo = '201605010001'

    --UPDATE tbOrder SET OrderAmount = 'ABC' WHERE OrderNo = '201605010001'

   

    COMMIT TRAN

   

END  TRY

BEGIN CATCH

    PRINT '执行错误' + CAST(@@ERROR AS VARCHAR(20))

    PRINT ERROR_MESSAGE()

    PRINT ERROR_SEVERITY()

    PRINT ERROR_STATE()

    ROLLBACK TRAN --必须增加回滚,否则事物会一直挂死在哪里

END CATCH

 

执行结果(同时查询订单明细):

 韦德体育 7

 删除数据库

代码如下复制代码 USE Northwind;GOBEGIN TRAN; DELETE FROM dbo.Orders WHERE OrderID = 10249; DELETE FROM dbo.Order Details WHERE OrderID = 10249;COMMIT TRAN;GO

1.3  总结说明

参照上面的使用演示及结果,可以由如下3种处理方式确保数据完整性

(1) 通过@@Error判断来语句是否执行成功,是否事物需要回滚(参见1.2 (4) )

(2) 设置XACT_ABORT为ON,强制整个事物执行出错时都回滚 (参见1.2 (5) )

(3) 通过Try Catch捕获执行异常,并回滚事物 (参见1.2 (6) )

 

use master -- 设置当前数据库为master,以便访问sysdatabases表
go
if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
go
或者

IF DB_ID (N'mytest') IS NOT NULL
DROP DATABASE mytest;

执行结果:表Orders因为违反引用完整性,删除操作失败, 表Order Details成功删除2行.从这个例子来看第一条T-SQL语句执行失败而第二条T-SQL却执行成功,这不明显证明事务不具有原子性。别急接

1.4  参考资料

 微软官网解释

try catch 捕获不到的一些错误及解决方法

事务执行情况跟踪分析

 

创建数据表

着往下看,因为BEGIN TRAN与END TRAN之间的语句为一个事务,要么操作全部成功,要么操作全部回滚,其实导致这个事务没有回滚的原因是会话的XACT_ABORT选项默认为OFF,产生运行时错误

1.5  相关附件

 相关SQL脚本

 

标准语法
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

示例
CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

时只回滚产生错误的T-SQL语句,事务会继续进行处理,如果错误严重也可能回滚整个事务。所以对于大多数时候需要显示将XACT_ABORT选项设置为ON,以便产生运行时错误时回滚事务。运行以下T-SQL

删除表包含结构

代码如下复制代码 SET XACT_ABORT ON;BEGIN TRAN; DELETE FROM dbo.Orders WHERE OrderID = 10250; DELETE FROM dbo.Order Details WHERE OrderID = 10250;COMMIT TRAN;GO

Drop table tablename

执行结果:Orders,Order Details表删除失败,因为删除Orders表数据出现错误,所以没有继续执行第二条T-SQL就进行事务回滚了,由此可以证明事务具备原子性。

 视图

一致性:指相关的所有规则都必须应用于事务的修改,以保证事务的完整性,如用户自定义完整性(业务逻辑)以及数据库引擎提供的数据完整性(如实体完整性,域完整性,引用完整性,NULL,DEFAULT)。也可以理解为事务的操作必须遵守数据库现有的规则。

create view  视图名字
as
查询代码

demo:
create view  view_user
as
select id from user
go
/*以上为创建一个视图,视图里面存储的是user表的ID列*/

/*删除视图*/
if exusts(select * from sysobjects where name='视图名字')/*如果数据库中存在这个视图,就删除该视图,查不查询随便你*/
drop view 视图名字  /*执行删除视图语法*/ 

隔离性:隔离性主要说明事务之间不能相互影响,(主要是依靠锁,行版本控制实现)。新打开2个查询窗口:在第1个窗口输入下列T-SQL并且执行

视图注意事项:注意事项:
1.一个视图可以嵌套另一个视图,但最后不要超过3层
2.视图定义中的select语句不能包括下列内容
*order by排序子句,除非在select语句中带有一个top子句
*into关键字
*引用临时表或表变量

代码如下复制代码 BEGIN TRAN;UPDATE dbo.Products SET ProductName = N'IPHONE4' WHERE ProductID = 1; GO

存储过程

在第2个窗口输入下列T-SQL并且执行

存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用他就行了。

韦德体育 ,代码如下复制代码 SELECT * FROM dbo.Products;GO

存储过程的好处:
1.由于数据库执行动作时,是先编译后执行的。然而存储过程是一个编译过的代码块,所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句,所以也能降低网络的通信量,提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库,从而确保数据的安全。

第二个窗口执行的状态是正在执行查询...,这是因为第一个事务并没有完成(COMMIT OR ROLLBACK),而第二个事务需要读取第一个事务所依赖的资源,事务不会识别中间状态(我们也可以理

示例:

解为未知状态,因为在未完成之前,我们无法知道知道第一个事务的最终状态是提交,还是回滚),正式因为这个原因,而已第二个事务才举棋不定,无法读取。这个示例很直接的说明了事务的

--查询存储过程

IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
    SELECT COUNT(ID) FROM Students
GO

--执行

EXEC PROC_SELECT_STUDENTS_COUNT

隔离性。

带参数

持久性:持久性主要是依靠记录设备,SQL Server 2005中指的是事务日志。即使服务器硬件、操作系统或数据库引擎 实例自身出现故障,该实例也可以在重新启动时使用事务日志,将所有未完成的事务自动地回滚到系统出现故障的点,具体的实现在这就不详细说明了,大家可以参考相关资料

--查询存储过程,根据城市查询总数

IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))
AS
    SELECT COUNT(ID) FROM Students WHERE City=@city
GO

-- 执行

EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

带通配符

--3、查询姓氏为李的学生信息,含通配符

IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME
    @surnName nvarchar(20)='李%' --默认值
AS 
    SELECT ID,Name,Age FROM Students WHERE Name like @surnName
GO

--执行

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'

带输出参数

--根据姓名查询的学生信息,返回学生的城市及年龄

IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_NAME
    @name nvarchar(50),     --输入参数
    @city nvarchar(20) out, --输出参数
    @age  int output        --输入输出参数
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

--执行
declare @name nvarchar(50),
        @city nvarchar(20),
        @age int;
set @name = N'李明';
set @age = 20;
exec PROC_SELECT_STUDENTS_BY_NAME @name,@city out, @age output;
select @city, @age;

存储过程实现增删改

1、新增demo

 

--1、存储过程:新增学生信息

IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_INSERT_STUDENT;
GO
CREATE procedure PROC_INSERT_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    INSERT INTO Students(ID,Name,Age,City) VALUES(@id,@name,@age,@city)
GO

--执行

EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'

2、修改

--修改学生信息

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_UPDATE_STUDENT;
GO
CREATE procedure PROC_UPDATE_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    UPDATE Students SET Name=@name,Age=@age,City=@city WHERE ID=@id
GO

--执行

EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'

3、删除

--根据id删除记录

IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL
    DROP procedure PROC_DELETE_STUDENT_BY_ID;
GO
CREATE procedure PROC_DELETE_STUDENT_BY_ID
    @id int
AS 
    DELETE FROM  Students WHERE ID=@id
GO

--执行

EXEC PROC_DELETE_STUDENT_BY_ID 1001

存储过程实现分页

-- ROW_NUMBER分页查询

IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE;
GO
CREATE procedure PROC_SELECT_BY_PAGE
    @startIndex int,
    @endIndex int
AS 
    SELECT  * FROM (SELECT ID,Name,Age,City,ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber FROM Students) AS Temp 
    WHERE Temp.RowNumber BETWEEN @startIndex AND @endIndex
GO

--执行

EXEC PROC_SELECT_BY_PAGE 1,10

--使用TOP分页

IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;
GO
CREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP
    @pageIndex int,
    @pageSize int
AS 
    SELECT TOP(@pageSize) * FROM Students 
    WHERE ID >=(SELECT MAX(ID) FROM (SELECT TOP(@pageSize*(@pageIndex-1) + 1) ID FROM Students ORDER BY ID) AS Temp)    
GO

--执行

EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2

事务

事务是什么?事务关键在与其原子性。原子性概念是指可以把一些事情当作一个执行单元来看待。从数据库角度看待。他是指应该全部执行或者全部不执行一条或多条语句的最小组合。当处理数据时候经常确保一件事发生另一件事也随之发生。或者二件事都不发生。实际上可能达到程度是有几十件事情或者更多的事情都必须一起发生或者都不发生。来看一个一个经典事例。这个事例各大书籍讲事务都有。就是你去银行转帐给朋友。转了100元sql如下:你的帐号扣了100元,你朋友帐号加了100元。看起来很完美。其实有个BUG存在,你的帐号被扣100元后在执行你朋友帐号加100元时候,数据出现错误,没有添加进去。 

 

ACID事务

如果你的系统被设计为使用ACID事务,则不会出现上面那种情况。ACID事务

1.原子性:事务会全部执行,要么全部不执行。

2.一致性:需要遵循所有约束以及其他数据库完整性规则,并且完全的更新所有相关的对象(数据和索引页)

3.隔离性:每一个事务与其他任何事务完全的隔离。一个事务动作不会受其它事务动作干扰。

4.持久性:完成事务后,它的作用结果永久保存在系统内部。数据是“安全的”。这是指不会在停电或者系统故障或设备故障不会导致数据写入一半的情况发生。

事务简单操作简介

你需要一种办法确保第一条SQL语句执行,第二条也跟着执行。实际上不存在这样一种可完全控制可能。从硬件故障到违反数据完整性规则简单事情。都有可能发生错误。然而幸运的是;有一种可能达到相同的目地方法-基本忘记从前发生什么事。至少可以强制这个概念:如果某件事没有发生,那什么不会发生,至少事务的作用范围内部就是这样。

如何标志标志是开始和结束,回滚

BEGIN TRAN : 设置为起点

COMMIT TRAN :让事务成为数据中心永久的,不可逆的一部分

ROLLBACK TRAN:不考虑所有更改,本质上想说忘记以前发生的一却

SAVE TRAN:创建一个特定标识符,只允许部分回滚

BEGIN TRAN 事务开始很好理解,它唯一目的就是表示一个执行单元开始。高级部分不讨论了,因为面向DBA的高级特性。

COMMIT TRAN  提交事务一个终点,当发出COMMIT TRAN命令时候,可以认为该事务是持久性

ROLLBACK TRAN 每当考虑ROLLBACK TRAN 时候表示。表示该执行单元有错误。或者回到起点重新开始。(忘记过去,重新开始)

SAVE TRAN 保存事务创建书签。创建书签在数据回滚时候,回滚到每个书签上。记住ROLLBACK在回滚会清除所有书签。如果保存了5个书签,一旦执行ROLLBACK,5个书签全部清空。

demo

 Create Procedure  MyProcedure
    AS
       Begin
           Set    NOCOUNT    ON; 
           Set XACT_ABORT on; --这句话非常重要

           Begin  Tran   --开始事务

           insert into userinfo(username,userpwd,RegisterTime) values('admin','admin',getdate())
           insert into userinfo(username,userpwd,RegisterTime) values('jack','jack',getdate())

           Commit Tran       --提交事务
       End

注意:

SET XACT_ABORT ON是设置事务回滚的!
当为ON时,如果你存储中的某个地方出了问题,整个事务中的语句都会回滚
为OFF时,只回滚错误的地方

欢迎关注我的微信公众号:互联网碎片搜集

上一篇:我们需要在Oracle里面建立两个视图 下一篇:11在UltraLite数据库中提供了一些新特性,联系方式如您还需要了解SQL Anywhere

更多阅读

【韦德体育】创建数据表,下面倒过来为

数据 2020-04-30
以前我们也有讲过sql事务相关文章,下面我们来了解一下关于sql server2005事务持久性一些东西,...
查看全文

我们需要在Oracle里面建立两个视图

数据 2020-04-30
1.根据Q193893,我们需要在Oracle里面建立两个视图。 现象:         浏览ASP页面提示   500内...
查看全文

11在UltraLite数据库中提供了一些新特性,

数据 2020-04-30
总结SQL Anywhere11新增加的功能旨在满足前端数据库的性能、可靠性和可扩展性的需求。它同样也...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有