sqlserver事务和mysql事务_sqlServer数据库事务处理

最新推荐文章于 2024-02-19 21:48:23 发布

李凜之

最新推荐文章于 2024-02-19 21:48:23 发布

阅读量526

点赞数

文章标签： sqlserver事务和mysql事务

本文链接：https://blog.csdn.net/weixin_42105816/article/details/113285863

版权

事务定义：

事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。

事务三种运行模式：自动提交事务每条单独的语句都是一个事务。显式事务每个事务均以BEGIN

TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。隐性事务在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。

事务操作的语法：

BEGIN TRANSACTION

BEGIN DISTRIBUTED TRANSACTION

COMMIT TRANSACTION

COMMIT WORK

ROLLBACK WORK

SAVE TRANSACTION

BEGIN TRANSACTION

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.

USE pubs

BEGIN DISTRIBUTED TRANSACTION

UPDATE authors

SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'

EXECUTElink_Server_T.pubs.dbo.changeauth_lname

'409-56-7008','McDonald'

COMMIT TRAN

Note:如果需要连接远程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。SETXACT_ABORT的设置是在执行或运行时设置，而不是在分析时设置。

示例下例导致在含有其它Transact-SQL语句的事务中发生违反外键错误。在第一个语句集产生错误，但其它语句均成功执行且事务成功提交。在第二个语句集中，SET

XACT_ABORT设置为ON。这导致语句错误使批处理终止，并使事务回滚。

CREATE TABLE t1 (a int PRIMARY KEY)

CREATE TABLE t2 (a int REFERENCES t1(a))

INSERT INTO t1 VALUES (1)

INSERT INTO t1 VALUES (3)

INSERT INTO t1 VALUES (4)

INSERT INTO t1 VALUES (6)

SET XACT_ABORT OFF

BEGIN TRAN

INSERT INTO t2 VALUES (1)

INSERT INTO t2 VALUES (2)

INSERT INTO t2 VALUES (3)

COMMIT TRAN

SET XACT_ABORT ON

BEGIN TRAN

INSERT INTO t2 VALUES (4)

INSERT INTO t2 VALUES (5)

INSERT INTO t2 VALUES (6)

COMMIT TRAN

SAVE

TRANSACTION在事务内设置保存点。

语法SAVE

TRAN [ SACTION ] { savepoint_name | @savepoint_variable

}参数savepoint_name是指派给保存点的名称。保存点名称必须符合标识符规则，但只使用前32个字符。@savepoint_variable是用户定义的、含有有效保存点名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明该变量。

注释

用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分，事务可以返回的位置。如果将事务回滚到保存点，则必须(如果需要，使用更多的Transact-SQL语句和COMMIT

TRANSACTION语句)继续完成事务，或者必须(通过将事务回滚到其起始点)完全取消事务。若要取消整个事务，请使用ROLLBACK

TRANSACTION

transaction_name格式。这将撤消事务的所有语句和过程。

Note：1：在由BEGIN

DISTRIBUTED

TRANSACTION显式启动或从本地事务升级而来的分布式事务中，不支持SAVE

TRANSACTION。

2：当事务开始时，将一直控制事务中所使用的资源直到事务完成(也就是锁定)。当将事务的一部分回滚到保存点时，将继续控制资源直到事务完成(或者回滚全部事务)。

例子：begin

transaction

save transaction A

insert into demo values('BB','B term')

rollback TRANSACTION Acreate table demo2(name

varchar(10),age int)

insert into demo2(name,age) values('lis',1)

commit transaction

ROLLBACK

TRANSACTION 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法ROLLBACK [ TRAN [ SACTION ]

[ transaction_name | @tran_name_variable | savepoint_name |

@savepoint_variable ] ]

参数transaction_name是给BEGIN

TRANSACTION上的事务指派的名称。transaction_name必须合标识符规则，但只使用事务名称的前32个字符。嵌套事务时，transaction_name必须是来自最远的BEGIN

TRANSACTION语句的名称。@tran_name_variable是用户定义的、含有有效事务名称的变量的名称。必须用char,varchar、nchar或nvarchar数据类型声明该变量。savepoint_name是来自SAVE

TRANSACTION语句的savepoint_name。savepoint_name必须符合标识符规则。当条件回滚只影响事务的一部分时使用savepoint_name。@savepoint_variable是用户定义的、含有有效保存点名称的变量的名称。必须用char,varchar、nchar或nvarchar数据类型声明该变量。

注释ROLLBACK

TRANSACTION清除自事务的起点或到某个保存点所做的所有数据修改。ROLLBACK还释放由事务控制的资源。不带savepoint_name和transaction_name的ROLLBACK

TRANSACTION回滚到事务的起点。嵌套事务时，该语句将所有内层事务回滚到最远的BEGIN

TRANSACTION语句。在这两种情况下，ROLLBACK

TRANSACTION均将@@TRANCOUNT系统函数减为0。ROLLBACK

TRANSACTION

savepoint_name不减少@@TRANCOUNT。

Note: ROLLBACK

TRANSACTION语句若指定savepoint_name则不释放任何锁。在由BEGIN

DISTRIBUTED

TRANSACTION显式启动或从本地事务升级而来的分布式事务中，ROLLBACK

TRANSACTION不能引用savepoint_name。在执行COMMIT

TRANSACTION语句后不能回滚事务。

在事务内允许有重复的保存点名称，但ROLLBACK

TRANSACTION若使用重复的保存点名称，则只回滚到最近的使用该保存点名称的SAVE

TRANSACTION。

在存储过程中，不带savepoint_name和transaction_name的ROLLBACK

TRANSACTION语句将所有语句回滚到最远的BEGINTRANSACTION。在存储过程中，ROLLBACK

TRANSACTION语句使@@TRANCOUNT在触发器完成时的值不同于调用该存储过程时的@@TRANCOUNT值，并且生成一个信息。该信息不影响后面的处理。

如果在触发器中发出ROLLBACK

TRANSACTION：将回滚对当前事务中的那一点所做的所有数据修改，包括触发器所做的修改。触发器继续执行ROLLBACK语句之后的所有其余语句。如果这些语句中的任意语句修改数据，则不回滚这些修改。执行其余的语句不会激发嵌套触发器。在批处理中，不执行所有位于激发触发器的语句之后的语句。每次进入触发器，@@TRANCOUNT就增加1，即使在自动提交模式下也是如此。(系统将触发器视作隐性嵌套事务。) 在存储过程中，ROLLBACK

TRANSACTION语句不影响调用该过程的批处理中的后续语句；将执行批处理中的后续语句。在触发器中，ROLLBACK

TRANSACTION语句终止含有激发触发器的语句的批处理；不执行批处理中的后续语句。

ROLLBACK TRANSACTION

语句不生成显示给用户的信息。如果在存储过程或触发器中需要警告，请使用 RAISERROR 或 PRINT 语句。RAISERROR

是用于指出错误的首选语句。

ROLLBACK

对游标的影响由下面三个规则定义：

当 CURSOR_CLOSE_ON_COMMIT 设置为 ON 时，ROLLBACK

关闭但不释放所有打开的游标。

当 CURSOR_CLOSE_ON_COMMIT 设置为 OFF 时，ROLLBACK 不影响任何打开的同步 STATIC 或

INSENSITIVE 游标不影响已完全填充的异步 STATIC 游标。将关闭但不释放任何其它类型的开的游标。

对于导致终止批处理并生成内部回滚的错误，将释放在含有该错误语句的批处理内声明的所有游标.不论游标的类型或

CURSOR_CLOSE_ON_COMMIT

的设置，所有游标均将被释放，其中包括在该错误批处理所调用的存储过程内声明的游标。在该错误批处理之前的批处理内声明的游标以规则 1 和

2 为准。死锁错误就属于这类错误。在触发器中发出的 ROLLBACK 语句也自动生成这类错误。

权限ROLLBACK TRANSACTION

权限默认授予任何有效用户。例子：

begin transaction

save transaction A

insert into demo values('BB','B term')

rollback TRANSACTION A

-- select * into demo2 from demo1

create table demo2(name varchar(10),age

int)

insert into demo2(name,age) values('lis',1)

rollback transaction

COMMIT

TRANSACTION标志一个成功的隐性事务或用户定义事务的结束。

如果@@TRANCOUNT为1，COMMITTRANSACTION使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放连接占用的资源，并将@@TRANCOUNT减少到0。如果@@TRANCOUNT大于1，则COMMITTRANSACTION使@@TRANCOUNT按1递减。只有当事务所引用的所有数据的逻辑都正确时，发出COMMIT

TRANSACTION命令。COMMIT

WORK标志事务的结束。语法COMMIT [ WORK ]

注释此语句的功能与COMMIT

TRANSACTION相同，但COMMIT

TRANSACTION接受用户定义的事务名称。这个指定或没有指定可选关键字WORK的COMMIT语法与SQL-92兼容

例子：begin transaction a

insert into demo values('BB','B term')

commit TRANSACTION A

隐性事务当连接以隐性事务模式进行操作时，SQL

Server将在提交或回滚当前事务后自动启动新事务无须描述事务的开始，只需提交或回滚每个事务。隐性事务模式生成连续的事务链。在为连接将隐性事务模式设置为打开之后，当SQL

Server首次执行下列任何语句时，都会自动启动一个事务：

ALTER TABLE

INSERT

CREATE

OPEN

DELETE

REVOKE

DROP

SELECT

FETCH

TRUNCATE TABLE

GRANT

UPDATE

在发出COMMIT或ROLLBACK语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行这些语句

中的任何语句时，SQL

Server都将自动启动一个新事务。SQL

Server将不断地生成一个隐性事务链，

直到隐性事务模式关闭为止

例子：begin transaction

save transaction A

insert into demo values('BB','B term')

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

----In the first trans .Insert into demo2(name,age) values('lis',1)

---Second

Transbegin transaction

insert into demo values('BB','B term')

commit transaction t2

----In the first trans .Insert into demo2(name,age) values('lis',2)

rollback transaction t1

Note:

在一系列嵌套的事务中用一个事务名给多个事务命名对该事务没有什么影响。系统仅登记第一个(最外部的)事务名。回滚到其它任何名字(有效的保存点名除外)都会产生错误。事实上，任何在回滚之前执行的语句都没有在错误发生时回滚。这语句仅当外层的事务回滚时才会进行回滚。

例：内部事务回滚SQL

server报错。

begin tran

Insert into demo2(name,age) values('lis',1)

---Second Trans

--Server: Msg 6401, Level 16, State 1, Line

---Cannot roll back t2. No transaction or savepoint of that name

was found.

begin transaction t2

insert into demo values('BB','B

term')rollbacktransaction

t2----In the first trans .

Insert into demo2(name,age) values('lis',2)

commit transaction t1

例：内部事务提交SQL

server不会报错。

begin tran

Insert into demo2(name,age) values('lis',1)

---Second Trans no error

begin transaction t2

insert into demo values('BB','B

term')committransaction

t2----In the first trans .

Insert into demo2(name,age) values('lis',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相同。

锁机制

锁是软件使用的对象，用于表明用户和资源有某种相关性，而其他用户不能在锁定的资源上执行，可能对拥有锁的用户的相关性产生负面影响的操作。在SQL

Server中，锁是防止事务访问指定资源、实现并发控制的主要手段。锁可以防止脏读、不可重复读和幻像读。

李凜之

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sqlserver事务和mysql事务_sqlServer数据库事务处理

事务定义：事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。事务三种运行模式：自动提交事务每条单独的语句都是一个事务。显式事务每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。隐性事务在前一个事务完成时新事务隐式启动，但每个事务仍...
复制链接

扫一扫