MSSQL 事务说明

1.什么是事务:事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时事务是做为最小的控制单元来使用的。他包含的所有数据库操作命令作为一个整体一起向系提交或撤消,这一组数据库操作命令要么都执行,要么都不执行。

2.事务的语句
开始事物:BEGIN TRANSACTION
提交事物:COMMIT TRANSACTION
回滚事务:ROLLBACK TRANSACTION
3.事务的4个属性
①原子性(Atomicity):事务中的所有元素作为一个整体提交或回滚,事务的个元素是不可分的,事务是一个完整操作。
②一致性(Consistemcy):事物完成时,数据必须是一致的,也就是说,和事物开始之前,数据存储中的数据处于一致状态。保证数据的无损。
③隔离性(Isolation):对数据进行修改的多个事务是彼此隔离的。这表明事务必须是独立的,不应该以任何方式以来于或影响其他事务。
④持久性(Durability):事务完成之后,它对于系统的影响是永久的,该修改即使出现系统故障也将一直保留,真实的修改了数据库
4.事务的保存点
SAVE TRANSACTION 保存点名称 --自定义保存点的名称和位置
ROLLBACK TRANSACTION 保存点名称 --回滚到自定义的保存点

-------------------实------------------例----------------------------

BEGIN TRANSACTION--开始事务

DECLARE @errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0

UPDATE a SET id=232 WHERE a=1 --事务操作SQL语句
SET @errorSun=@errorSun+@@ERROR --累计是否有错

UPDATE aa SET id=2 WHERE a=1 --事务操作SQL语句
SET @errorSun=@errorSun+@@ERROR --累计是否有错

IF @errorSun<>0
BEGIN
PRINT '有错误,回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
PRINT '成功,提交'
COMMIT TRANSACTION--事务提交语句
END

示例:创建一个存储过程,向两个表中同时插入数据


代码如下:

Create proc RegisterUser
(@usrName varchar(30), @usrPasswd varchar(30),@age int,@sex varchar(10), @PhoneNum varchar(20), @Address varchar(50) )
as begin
begin tran
insert into userinfo(userName,userPasswd) values(@usrName,@usrPasswd)
if @@error<>0
begin

rollback tran

return -1
end
insert into userdoc(userName,age,sex,PhoneNumber,Address)values(@Usrname,@age,@sex,@PhoneNum,@Address)
if @@error<>0
begin

rollback tran
return -1
end

commit tran
return 0
end

事务的分类
按事务的启动与执行方式,可以将事务分为3类:
显示事务
也称之为用户定义或用户指定的事务,即可以显式地定义启动和结束的事务。分布式事务属于显示事务
自动提交事务
默认事务管理模式。如果一个语句成功地完成,则提交该语句;如果遇到错误,则回滚该语句。
隐性事务
当连接以此模式进行操作时,sql将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始,只需提交或回滚每个事务。它生成连续的事务链。

一、显示事务
通过begin transacton、commit transaction、commit work、rollback transaction或rollback work等语句完成。
1、启动事务
格式:begin tran 事务名或变量 with mark 描述
2、结束事务
格式:commit tran 事务名或变量 (事务名与begin tran中的事务名一致
或commit work 但此没有参数
3、回滚事务
rollback tran 事务名或变量 | savepoint_name | savepoint_variable
或rollback work
说明:清除自事务的起点或到某个保存点所做的所有数据修改
4、在事务内设置保存点
格式:save tran savepoint_name | savepoint_variable
示例:


代码如下:

use bookdb
go
begin tran mytran
insert into book
values(9,"windows2000',1,22,'出版社')
save tran mysave
delete book where book_id=9
rollback tran mysave
commit tran
go
select * from book
go

可以知道,上面的语句执行后,在book中插入了一笔记录,而并没有删除。因为使用rollback tran mysave 语句将操作回滚到了删除前的保存点处。
5、标记事务
格式:with mark
例:使用数据库标记将日志恢复到预定义时间点的语句
在事务日志中置入一个标记。请注意,被标记的事务至少须提交一个更新,以标记该日志。

BEGIN TRAN MyMark WITH MARK
UPDATE pubs.dbo.LastLogMark SET MarkTime = GETDATE()
COMMIT TRAN MyMark

按照您常用的方法备份事务日志。

BACKUP LOG pubs TO DISK='C:\Backups\Fullbackup.bak' WITH INIT

现在您可以将数据库恢复至日志标记点。首先恢复数据库,并使其为接受日志恢复做好准备。

RESTORE DATABASE pubs FROM DISK=N'C:\Backups\Fullbackup.bak' WITH NORECOVERY

现在将日志恢复至包含该标记的时间点,并使其可供使用。请注意,STOPAT在数据库正在执行大容量日志时禁止执行。

RESTORE LOG pubs FROM DISK=N'C:\Backups\Logbackup.bak' WITH RECOVERY,
STOPAT='02/11/2002 17:35:00'

5、不能用于事务的操作
创建数据库 create database
修改数据库 alter database
删除数据库 drop database
恢复数据库 restore database
加载数据库 load database
备份日志文件 backup log
恢复日志文件 restore log
更新统计数据 update statitics
授权操作 grant
复制事务日志 dump tran
磁盘初始化 disk init
更新使用sp_configure后的系统配置 reconfigure

二、自动提交事务
sql连接在begin tran 语句启动显式事务,或隐性事务模式设置为打开之前,将以自动提交模式进行操作。当提交或回滚显式事务,或者关闭隐性事务模式时,将返回到自动提交模式。
示例:
由于编译错误,使得三个insert都没执行


代码如下:

use test
go
create table testback(cola int primary key ,colb char(3))
go
insert into testback values(1,'aaa')
insert into testback values(2,'bbb')
insert into testback value(3,'ccc')
go
select * from testback
go

没有任何结果返回

三、隐式事务
通过 API 函数或 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 语句,将隐性事务模式设置为打开。下一个语句自动启动一个新事务。当该事务完成时,再下一个 Transact-SQL 语句又将启动一个新事务。
当有大量的DDL 和DML命令执行时会自动开始,并一直保持到用户明确提交为止,切换隐式事务可以用SET IMPLICIT_TRANSACTIONS
为连接设置隐性事务模式.当设置为 ON 时,SET IMPLICIT_TRANSACTIONS 将连接设置为隐性事务模式。当设置为 OFF 时,则使连接返回到自动提交事务模式
语句包括:
alter table insert open create delete revoke drop
select fetch truncate table grant update
示例:
下面使用显式与隐式事务。它使用@@tracount函数演示打开的事务与关闭的事务:


代码如下:

use test
go
set nocount on
create table t1(a int)
go
insert into t1 values(1)
go

print '使用显式事务'
begin tran
insert into t1 values(2)
print '事务外的事务数目:'+cast(@@trancount as char(5))
commint tran
print '事务外的事务数目:'+cast(@@trancount as char(5))
go

print
go
set implicit_transactions on
go

print '使用隐式事务'
go
insert into t1 values*4)
print'事务内的事务数目:'+cast(@@trancount as char(5))
commint tran
print'事务外的事务数目:'+cast(@@trancount as char(5))
go

执行结果:
使用显示事务
事务内的事务数目:2
事务外的事务数目:1
使用隐式事务
事务内的事务数目:1
事务外的事务数目:0

四、分布式事务
跨越两个或多个数据库的单个sql server中的事务就是分布式事务。
与本地事务区别:必须由事务管理器管理,以尽量避免出现因网络故障而导致一个事务由某些资源管理器成功提交,但由另一些资源管理器回滚的情况。

sql server 可以由DTc microsoft distributed transaction coordinator 来支持处理分布式事务,可以使用 BEgin distributed transaction 命令启动一个分布式事务处理

分二阶段:
A 准备阶段
B 提交阶段

执行教程:
1、sql 脚本或应用程序连接执行启动分布式事务的sql语句
2、执行该语句的sql在为事务中的主控服务器
3、脚本或应用程序对链接的服务器执行分布式查询,或对远程服务器执行远程存储过程。
4、当执行了分布式查询或远程过程调用后,主控服务器将自动调用msdtc以便登记分布式事务中链接的服务器和远程服务器
5、当脚本或应用程序发出commit或rollback语句时,主控sql将调用msdtc管理两阶段提交过程,或者通知链接的服务器和远程服务器回滚其事务。

mssql 事务的一个例子

代码如下:

begin tran
                            declare @rownum1 int --未添加订单的空房数
                            declare @rownum2 int --添加订单的空房数目
                            declare @BookID1 int
                                set @BookID1=0
                            insert into T_BookRoomInfo(RoomID,CustomerName,CustomerCardID,Discount,
                                  EnterTime,DepositMoney,Memo,UserID,UpdTime)
                            values (@RoomID,@CustomerName,@CustomerCardID,@Discount,
                                   getdate(),@DepositMoney,@Memo,@UserID,getdate())
                            select @BookID1=@@IDENTITY
                            if(@BookID1<>0)
                                begin
                                       select @rownum1=count(1)
                                         from T_Room
                                        where IsEmploy=0
                                update T_Room
                                set IsEmploy=1
                                where RoomID=@RoomID
                                       select @rownum1=count(1)
                                         from T_Room
                                        where IsEmploy=0

if(@rownum1<=@rownum2)
                                    begin
                                        rollback tran
                                    end
                                else
                                    begin
                                        commit tran       
                                    end
                                end
                            else
                                    begin
                                         rollback tran       
                                    end

(0)

相关推荐

  • SQLServer分布式事务问题

    一. 问题现象 在执行 SQL Server分布式事务时,在SQL Server 2005下收到如下错误: 消息 7391,级别 16,状态 2,过程 xxxxx,第 16 行 无法执行该操作,因为链接服务器 "xxxxx" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务. 在SQL Server 2000下收到如下错误: 该操作未能执行,因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务. [OLE/DB provider

  • Sqlserver 存储过程中结合事务的代码

    复制代码 代码如下: --方式一 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[USP_ProcedureWithTransaction_Demo] GO -- ===================

  • 使用Sqlserver事务发布实现数据同步(sql2008)

    事务的功能在sqlserver中由来已久,因为最近在做一个数据同步方案,所以有机会再次研究一下它以及快照等,发现还是有很多不错的功能和改进的.这里以sqlserver2008的事务发布功能为例,对发布订阅的方式简要介绍一下操作流程,一方面做个总结备份,一方面与大家进行一下分享和交流.费话就不多说了,进入一下今天的正题:) 这里要说明一下环境:首先我在本地局域网内有两台安装有sqlserver2008的机器(注意:已发布的快照版本无法向老版本数据库兼容,意味着2008下创建的事务或快照发布,无法被

  • MSSQL事务的存储过程

    在酒店管理系统开发中,我们会创建房间表和房间类型表(房型表)这两个表,如下图所示: 房型表:RoomType             房间表:Room 首先这两个表的关系:Room是从表,RoomType是主表,两表有主外键关系,RoomType.rTypeId=Room.rTypeId 分析这两个表,我们会发现这样一个问题:在房间表和房型表管理中,当我们修改或者删除一个RoomType表,对应的Room表我们也要做修改和删除,那么我们怎么解决这个问题呢?首先可以在数据库中设置级联删除,或者利用

  • c#实现sqlserver事务处理示例

    复制代码 代码如下: private static void ExecuteSqlTransaction(string connectionString)    {        using (SqlConnection connection = new SqlConnection(connectionString))        {            connection.Open();            SqlCommand command = connection.CreateC

  • Transactional replication(事务复制)详解之如何跳过一个事务

    在transactional replication, 经常会遇到数据同步延迟的情况.有时候这些延迟是由于在publication中执行了一个更新,例如update ta set col=? Where ?,这个更新包含巨大的数据量.在subscription端,这个更新会分解成多条命令(默认情况下每个数据行一个命令),应用到subscription上. 不得已的情况下,我们需要跳过这个大的事务,让replication继续运行下去. 现在介绍一下transactional replicatio

  • MSSQL 事务说明

    1.什么是事务:事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时事务是做为最小的控制单元来使用的.他包含的所有数据库操作命令作为一个整体一起向系提交或撤消,这一组数据库操作命令要么都执行,要么都不执行. 2.事务的语句 开始事物:BEGIN TRANSACTION 提交事物:COMMIT TRANSACTION 回滚事务:ROLLBACK TRANSACTION 3.事务的4个属性 ①原子性(Atomicity):事务中的所有元素作为一个整体提交或回滚,事务的个元素是不可分的,事务

  • ASP操作数据库的类

    今天俺在这裡给大家提供一种数据库操作的思路,这些代码是俺在长期的ASP应用中不断完成和修正的,也已经用它完成了很多项目了,应该说直接拿去用是没有问题的.当然本人能力也有限,希望大家一起来讨论. 说明:此帖代码均是VBScript版本.另外最好你对手写ASP已经有一定的基础. 先简单介绍一下俺这个类的一些特点: 可同时操作多个不同类型的数据库. 完全不用考虑数据类型的差别,再也不用想字符型字段加不加单引号. 调用非常简单,对数据库的主要操作一般只需要一行代码. 支持mssql事务回滚. 可自动生成

  • MSSQL与Oracle数据库事务隔离级别与锁机制对比

    一,事务的4个基本特征 Atomic(原子性): 事务中包含的操作被看做一个逻辑单元,这个逻辑单元中的操作要 么全部成功,要么全部失败. Consistency(一致性): 只有合法的数据可以被写入数据库,否则事务应该将其回滚到最初 状态. Isolation(隔离性): 事务允许多个用户对同一个数据进行并发访问,而不破坏数据的正 确性和完整性.同时,并行事务的修改必须与其他并行事务的修改 相互独立. Durability(持久性): 事务结束后,事务处理的结果必须能够得到固化. 以上属于废话

  • 深入分析MSSQL数据库中事务隔离级别和锁机制

    锁机制 NOLOCK和READPAST的区别. 1.       开启一个事务执行插入数据的操作. BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2.       执行一条查询语句. SELECT * FROM Customer WITH (NOLOCK) 结果中显示"a"和"a".当1中事务回滚后,那么a将成为脏数据.(注:1中的事务未提交) .NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修

  • Mssql,Access的sql经典SQL语句大全

    下列语句部分是Mssql语句,不可以在access中使用. SQL分类: DDL-数据定义语言(CREATE,ALTER,DROP,DECLARE) DML-数据操纵语言(SELECT,DELETE,UPDATE,INSERT) DCL-数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK) 首先,简要介绍基础语句: 1.说明:创建 数据库 CREATE DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份

  • MSSQL自动同步设置方法

    MSSQL自动同步 这里以117和75为例说明:117发布&分发服务器.75订阅服务器 一.准备工作: 1.117&75上建立sqlagt用户,隶属于administrators组,用于启动Sqlserveragent服务(开机自启动),本地系统帐户local,将不具备网络功能,会产生错误 2.117/75上企业管理器中的SQL注册必须为本机名称,不能为local或ip 3.在117上建立75的SQL注册 4.117/75上如果安装了MSSQL后更改过机器名,需要修改MSSQL注册主机名:

  • MSSQL 数据库同步教程

    制的概念 复制是将一组数据从一个数据源拷贝到多个数据源的技术,是将一份数据发布到多个存储站点上的有效方式.使用复制技术,用户可以将一份数据发布到多台服务器上,从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据.复制技术可以确保分布在不同地点的数据自动同步更新,从而保证数据的一致性. SQL复制的基本元素包括 出版服务器.订阅服务器.分发服务器.出版物.文章 SQL复制的工作原理 SQLSERVER主要采用出版物.订阅的方式来处理复制.源数据所在的服务器是出版服务器,负责发表数据.出版服

  • MSSQL优化之探索MSSQL执行计划(转)

    网上的SQL优化的文章实在是很多,说实在的,我也曾经到处找这样的文章,什么不要使用IN了,什么OR了,什么AND了,很多很多,还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么(有点可笑),让许多人不知道其是对还是错.而SQL优化又是每个要与数据库打交道的程序员的必修课,所以写了此文,与朋友们共勉. 谈到优化就必然要涉及索引,就像要讲锁必然要说事务一样,所以你需要了解一下索引,仅仅是索引,就能讲半天了,所以索引我就不说了(打很多字是很累的,况且我也知之甚少),可以去参考相关的文章,这个网上

  • SQL MSSQL 常用代码大全

    /*********************************************************/ function:SQL MSSQL TECHNOLOGY ARTICLE file :SQL-MSSQL.TXT author :chinayaosir QQ:44633197 Tools :MSSQL QUERY ANALYSIS date :4/01/2010 blog :http://blog.csdn.net/chinayaosir note :禁止其它网站转载此文章

随机推荐