2008从入门到精通,Server事务详解

1.3.管理事务

主要使用以下4条语句管理事务:BEGIN TRANSACTION,COMMIT
TRANSACTION,ROLLBACK TRANSACTION和SAVE
TRANSACTION。此外还有2个全局变量可以用在事务处理语句中:@@ERROR和@@TRANCOUNT。
BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION不多说了。

begin tran t1
Insert into demo2(name,age) values(‘ok6’,1)
 —Second Trans no error
 begin transaction t2
insert into demo1 values(‘testName1′,’029303290320’)
 commit transaction t2
 
—-In the first trans .
 Insert into demo2(name,age) values(‘testok’,2)
 commit transaction t1
 
SQL Server 的隔离级别:
 
1: 设置TimeOut 参数
 
Set Lock_TimeOut 5000
 
被锁超时5秒将自动解锁
 
Set Lock_TimeOut 0
 
产立即解锁,返回Error 默认为-1,无限等待
 
2:
 
(SET TRANSACTION ISOLATION LEVEL
 { READ COMMITTED
 | READ UNCOMMITTED
 | REPEATABLE READ | SERIALIZABLE})
 
READ COMMITTED
 
指定在读取数据时控制共享锁以避免脏读,但数据可在事务结束前更改,从而产生不可重复读取或
 
幻像数据。该选项是SQL Server 的默认值。
 
避免脏读,并在其他session 在事务中不能对已有数据进行修改。共享锁。
 
READ UNCOMMITTED
 
执行脏读或 0
级隔离锁定,这表示不发出共享锁,也不接受排它锁。当设置该选项时,可以对数
 
据执行未提交读或脏读;在事务结束前可以更改数据内的数值,行也可以出现在数据集中或从数据
 
集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK
相同。这是四个隔离级别中
 
限制最小的级别。
 
REPEATABLE READ
 
锁定查询中使用的所有数据以防止其他用户更新数据,但是其他用户可以将新的幻像行插入数据
 
集,且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别,所以应只在必要时才使
 
用该选项。
 
SERIALIZABLE
 
在数据集上放置一个范围锁,以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这
 
是四个隔离级别中限制最大的级别。因为并发级别较低,所以应只在必要时才使用该选项。该选项
 
的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同

1.3.1.SAVE TRANSACTION

允许部分地提交一个事务,同时仍能回退这个事务的剩余部分。
示例3:BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK
TRANSACTION和SAVE TRANSACTION的结合使用
执行下列语句

BEGIN TRANSACTION changed
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180014','谭晶','男','533')
SAVE TRANSACTION saveinsert--设置保存事务点saveinsert
UPDATE student
SET stu_sex='错误数据'
WHERE stu_no='20180014'
ROLLBACK TRANSACTION saveinsert--回滚到保存事务点saveinsert
COMMIT TRANSACTION changed

上述代码完成了一个这样的功能:设置一个事务,事务名changed,该事务的作用是向student表中插入一条记录并更新该记录的stu_sex字段。如果更新失败,则回滚到插入操作,即保证不管更新是否成功,插入操作都能成功。

 
 

  • 1.事务
    • 1.1.事务的ACID属性
    • 1.2.事务分类
      • 1.2.1.系统提供的事务
      • 1.2.2.用户自定义的事务
    • 1.3.管理事务
      • 1.3.1.SAVE
        TRANSACTION
      • 1.3.2.@@TRANCOUNT变量和@@ERROR变量
    • 1.4.SQL
      Server本地事务支持

      • 1.4.1.自动提交事务模式
      • 1.4.2.显式事务模式
      • 1.4.3.隐式事务模式
      • 1.4.4.批范围的事务
    • 1.5.隔离级别
      • 1.5.1.四种隔离级别
      • 1.5.2.设置事务隔离级别
    • 1.6.分布式事务
    • 1.7.高级事务主题
    • 1.8.管理长时间运行的事务
      • 1.8.1.查看长时间运行的事务
      • 1.8.2.停止事务

分布式事务中已登记的连接执行一个远程存储过程调用,该调用引用一个远程服务器。
分布式事务中已登记的连接执行一个分布式查询,该查询引用一个远程服务器。

1.4.SQL Server本地事务支持

应用程序主要通过设置事务开始时间和事务结束时间来管理事务。这可以通过函数或者应用程序接口(API)实现。默认情况下,事务按连接级别进行处理,使用API函数或者SQL语句,可以将事务作为显式,隐式和自动提交事务来处理。

 
 

1.5.2.设置事务隔离级别

默认情况下,SQL Server 2008的事务隔离级别为提交读。可通过SET TRANSACTION
ISOLATION LEVEL来设置事务隔离级别。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

—-In the first trans .
 Insert into demo2(name,age) values(‘ok1’,1)
 
—Second Trans begin transaction t2
insert into demo1 values(‘testName5′,’029303290320’)commit transaction
t2
 
—-In the first trans .
 Insert into demo2(name,age) values(‘ok12’,2)
 rollback transaction t1
 
Note:
 
在一系列嵌套的事务中用一个事务名给多个事务命名对该事务没有什么影响。系统仅登记第一个(最外部的)事务名。回滚
 
到其它任何名字(有效的保存点名除外)都会产生错误。
 
事实上,任何在回滚之前执行的语句都没有在错误发生时回滚。这语句仅当外层的事务回滚时才会进行回滚。
 
例:内部事务回滚SQL server 报错。
 
begin tran t1
Insert into demo2(name,age) values(‘okok’,1)
 —Second Trans

1.8.管理长时间运行的事务

USE pubs
 GO
 BEGIN DISTRIBUTED TRANSACTION
 UPDATE authors
 SET au_lname = ‘McDonald’ WHERE au_id = ‘409-56-7008’
 EXECUTE link_Server_T.pubs.dbo.changeauth_lname
‘409-56-7008′,’McDonald’
 COMMIT TRAN
 GONote:
 如果需要连接远程DB,如果是linkServer
方式连接的话,一定要修该linkServer的 RPC 选项置为 True。
 
SET XACT_ABORT
 指定当 Transact-SQL 语句产生运行时错误时,Microsoft? SQL Server?
是否自动回滚当前事务。
 
( 可以比较简单的理解,如果中间有任何一句SQL
出错,所有SQL全部回滚.特别适用于 Procedure 中间调用Procedure
,如果第一个Procedure Ok,被调用的Procedure 中间有错误,如果SET
XACT_ABORT=false,则出错的部分回滚,其他部分提交,当然外部Procedure
也提交。).
 
—在分布式Trans中一定要注意设置下面参数(XACT_ABORT)
 
语法SET XACT_ABORT { ON | OFF }
 
注释 当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL
语句产生运行时错误,整个事务将终止并回滚。为 OFF
时,只回滚产生错误的Transact-SQL
语句,而事务将继续进行处理。编译错误(如语法错误)不受 SET XACT_ABORT
的影响。
 
对于大多数 OLE DB 提供程序(包括 SQL
Server),隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。
 
SET XACT_ABORT 的设置是在执行或运行时设置,而不是在分析时设置。
 
示例 下例导致在含有其它 Transact-SQL
语句的事务中发生违反外键错误。在第一个语句集中产生错误,但其它语句均成功执行且事务成功
 提交。在第二个语句集中,SET XACT_ABORT 设置为
ON。这导致语句错误使批处理终止,并使事务回滚。

1.2.事务分类

在发出 COMMIT 或 ROLLBACK
语句之前,该事务将一直保持有效。在第一个事务被提交或回滚之后,下次当连接执行这些语句
 
中的任何语句时,SQL Server 都将自动启动一个新事务。SQL Server
将不断地生成一个隐性事务链,
 
直到隐性事务模式关闭为止
 
例子:
 begin transaction
save transaction A
 
insert into demo1 values(‘testName1′,’029303290320’)
 rollback TRANSACTION A
 
create table demo2(name varchar(10),age int)
 insert into demo2(name,age) values(‘lis’,1)
 rollback transaction
 — 在 Create table demo2 时 SQL Server
已经隐式创建一个Trans,知道提交或回滚
 
嵌套事务处理:
 
1: Trans 嵌套,将内部的trans 合并到外部并形成一个Trans.
 
begin tran t1

1.4.4.批范围的事务

该事务只适用于多个活动的结果集。在MARS会话中启动的SQL显式或隐式事务,将变成批范围事务,当批处理完成时,如果批范围事务还没有被提交或回滚,SQL
Server将自动对其进行回滚。

   ROLLBACK TRAN
 END
 GO
 
COMMIT TRANSACTION
 标志一个成功的隐性事务或用户定义事务的结束。如果 @@TRANCOUNT 为
1,COMMIT

1.事务

事务在SQL
Server中相当于一个工作单元,可以确保同时发生的行为与数据的有效性不发生冲突,并且维护数据的完整性。在实际应用中,多个用户在同一时刻对同一部分数据进行操作时,可能会由于一个用户的操作使其他用户的操作和数据失效。事务可以很好地解决这一点。事务总是确保数据库的完整性。

REVOKE
 

1.4.1.自动提交事务模式

自动提交事务模式是SQL
Server默认的事务管理模式,每个SQL语句都是一个事务,在完成时都会被提交或回滚。在自动提交事务模式下,当遇到的错误是编译时错误,会回滚整个批处理,当遇到的错误是运行时错误,不会回滚整个批处理,而是执行部分语句并提交。
示例6:遇到编译时错误和运行时错误时,事务处理方式是不同的
执行下列语句

--编译时错误代码
USE test
GO
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
GO
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUE
('1003','卢哲','27')--语法错误,回滚整个批处理
GO
SELECT * FROM T1

结果可以看到,T1表虽然被创建了,但是三条数据都没有插入成功。可见编译时错误会回滚整个批处理。
删除T1表后执行下列语句

--运行时错误代码
USE test
GO
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
GO
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUES
('1001','卢哲','27')--主键重复错误,仅该语句不执行
GO
SELECT * FROM T1

结果如图所示
图片 1
仅错误的INSERT语句不执行,而整个批处理并没有回滚。可见运行时错误不会导致整个批处理被回滚,仅仅只是中断执行。

INSERT
 

1.5.1.四种隔离级别

  • 未提交读(READ
    UNCOMMITTED):事务隔离的最低级别,可执行未提交读和脏读,任何情况都无法保证
  • 提交读(READ
    COMMITTED):在读取数据时控制共享锁,避免脏读,但无法避免不可重复读和幻读。它是SQL
    Server 2008的默认值。
  • 可重复读(REPEATABLE
    READ):锁定查询过程中所有数据,防止用户更新数据,避免了脏读和不可重复读的发生,无法避免幻读。
  • 可串行读(SERIALZABLE):在数据集上放置一个范围锁,防止其他用户在事务完成之前更新数据或插入行,是事务隔离的最大限制级别,避免了脏读,不可重复读和幻读的发生。

事务隔离级别越高,越能保证数据的一致性和完整性。

 
 

1.4.3.隐式事务模式

隐式事务模式是一种连接选项,在该选项下每个连接执行的SQL语句都被视为单独的事务。当连接以隐式事务模式进行操作时,SQL
Server将在事务提交或事务回滚后自动开始新事务。隐式事务模式无需BEGIN
TRANSACTION这种语句来进行定义。

 
 

1.2.1.系统提供的事务

系统提供的事务是指执行某些T-SQL语句时,一条语句段构成了一个事务,如ALTER
TABLE,CREATE,DELETE,DROP,FETCH等。

示例
 本例在本地和远程数据库上更新作者的姓。本地和远程数据库将同时提交或同时回滚本事务。

1.8.2.停止事务

停止事务可能必须运行KILL语句,使用该语句时要小心,特别是在运行重要的进程时。

BEGIN TRANSACTION
 标记一个显式本地事务的起始点。
 
BEGIN TRANSACTION将 @@TRANCOUNT 加 1。
 
BEGIN TRANSACTION
代表一点,由连接引用的数据在该点是逻辑和物理上都一致的。如果遇上错误,在
BEGIN TRANSACTION
之后的所有数据改动都能进行回滚,以将数据返回到已知的一致状态
。每个事务继续执行直到它无误地完成并且用 COMMIT TRANSACTION
对数据库作永久的改动,或者遇上错误并且用 ROLLBACK TRANSACTION
语句擦除所有改动
 
语法
 BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable
[ WITH MARK [ ‘description’ ] ] ]
 
例子:
 BEGIN TRAN T1
 UPDATE table1 …
 –nest transaction M2
 BEGIN TRAN M2 WITH MARK
 UPDATE table2 …
 SELECT * from table1
 COMMIT TRAN M2
 UPDATE table3 …
 COMMIT TRAN T1
 
BEGIN DISTRIBUTED TRANSACTION
 指定一个由 Microsoft 分布式事务处理协调器 (MS DTC) 管理的 Transact-SQL
分布式事务的起始。
 
语法
 BEGIN DISTRIBUTED TRAN [ SACTION ]
 [ transaction_name | @tran_name_variable ]
 
参数
 transaction_name
 是用户定义的事务名,用于跟踪 MS DTC 实用工具中的分布式事务。
transaction_name 必须符合标识符规则,但是仅使用头 32 个字符
 
@tran_name_variable
 是用户定义的一个变量名,它含有一个事务名,该事务名用于跟踪 MS DTC
实用工具中的分布式事务。必须用 char、varchar、nchar 或 nvarchar
数据类型声明该变量。
 
注释
 执行BEGIN DISTRIBUTED TRANSACTION
语句的服务器是事务创建人,并且控制事务的完成
 
当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时,
 主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。
 有两个方法可将远程 SQL 服务器登记在一个分布式事务中:

目录

说明
当前的SQL Server 上必须安装 MS DTC.

1.2.2.用户自定义的事务

实际应用中,经常使用用户自定义的事务。自定义的方法是,以BEGIN
TRANSACTION开始,以COMMIT TRANSACTION或ROLLBACK
TRANSACTION结束。这两个语句之间所有语句都被视为一体。
示例2:自定义事务的应用

BEGIN TRANSACTION
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180013','贾乃亮','1993-01-20','498')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180014','周星星','1993-07-20','532')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180015','雨化田','错误格式数据','570')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180016','周琪','1993-01-20','653')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180017','陈璐','1998-01-20','599')
COMMIT TRANSACTION

在上面的事务中,第三条插入数据是错误数据,无法成功插入,执行上面的语句,发现所有插入语句都没有被执行成功。
还有一种用户自定义事务——分布式事务。如果在比较复杂的环境中,有多台服务器,为了保证服务器中数据的完整性和一致性,就必须定义一个分布式事务。举个例子,有2台服务器,一台存放库存数据,另一台存放订单数据,用户下单的逻辑是,下单前先扣除库存数据,再下单。如果没有分布式事务,容易出现扣除库存数量,单下单却没成功,造成两个数据库数据不一致的情况。

GRANT
 

1.1.事务的ACID属性

  • 原子性(Atomicity):事务是工作单元。事务内的所有工作要不全部完成,要不全部没完成,不存在完成一部分的说法。
  • 一致性(Consistency):事务完成时,所有的数据都必须是一致的。事务结束时,所有内部数据结构都必须是正确的。
  • 隔离性(Isolation):由并发事务所做的修改必须与其他并发事务所做的修改隔离。事务识别数据时数据所处的状态,要不是另一并发事务修改前的状态,要不是另一并发事务修改后的状态,不存在中间状态。
  • 持久性(Durability):事务提交后,事务所完成的工作结果会得到永久保存。

示例1:情况如下2个代码

--语句1:
UPDATE student
SET stu_birthday='1993-02-01',
stu_native_place='山西',
stu_phone='15729810290'
WHERE stu_no='20180101'
--语句2:
UPDATE student
SET stu_birthday='1993-02-01'
WHERE stu_no='20180101'
UPDATE student
SET stu_native_place='山西'
WHERE stu_no='20180101'
UPDATE student
SET stu_phone='15729810290'
WHERE stu_no='20180101'

在语句1中,只有一个事务,对列的更新要不全部成功更新,要不全部更新失败。而语句2中,有三个事务,就算其中有某个列更新失败,也不会影响其他列的更新。

TRANSACTION 使得自从事务开始以来所执行的
所有数据修改成为数据库的永久部分,释放连接
 
占用的资源,并将 @@TRANCOUNT 减少到 0。如果@@TRANCOUNT 大于
1,则COMMIT

1.5.隔离级别

当多个线程都开启事务来操作数据库中的数据时,数据库要能进行隔离操作,以确保各个线程获取数据的准确性。如果没有隔离操作,会出现以下几种情况:

  • 脏读:一个事务处理过程里读取了另一个未提交的事务中的数据。

例如:A转100块钱给B,SQL语句如下

UPDATE acount
SET cash=cash+100
WHERE name='B'--此时A通知B
UPDATE acount
SET cash=cash-100
WHERE name='A'

执行完第一条语句时,A通知B,让B确认是否到账,B确认钱到账(此时发生了脏读),而后无论第二条SQL语句是否执行,只要事务没有提交,所有操作都将回滚,B第二次查看时发现钱没有到账。

  • 不可重复读:一个事务范围内多次查询某个数据,返回不同的值,这是因为该数据被另一个事务修改并提交了。脏读和不可重复读的区别在于,脏读是读取了另一个事务还未提交的数据,不可重复都是读取了反复读取了前一个事务提交了的数据
  • 幻读:比如事务T1将表中某一列数据从1修改成2,同时T2事务插入一条数据,该列值仍然是1,那么用户查询时就会发现该表还有1列数据为1,未被T1事务修改。

UPDATE
 

网站地图xml地图