事物处理
事务是
SQL Server中的单个逻辑单元,一个事务内的所有SQL语句作为一个整体执行,要么全部执行,要么都不执行。
事务有4个属性,称为ACID(原子性、一致性、隔离性和持久性)
原子性 事务必须是原子工作单元。对于其数据修改,要么全都执行,要么全都不执行。
一致性 事务在完成时,必须使所有的数据都保持一致状态。
隔离性 由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
持久性 事务完成之后,它对于系统的影响是永久性的。
事务分类
按事务的启动和执行方式,可将事务分为3类:
1.显示事务:显式地定义启动和结束的事务。
2.自动提交事务:自动提交模式是
SQL Server的默认事务管理模式。每个Transact-SQL语句在完成时,都被提交或回滚。如果一个语句成功地完成,则提交该语句;如果遇到错误,则回滚该语句。
3.隐性事务:当连接以隐性事务模式进行操作时,SQL Server将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始,只须提交或回滚每个事务。隐性事务模式形成连续的事务链。
1.显示事务
显示事务需要显示地定义事务的启动和结束。
它是通过 BEGIN TRANSACTION 、 COMMIT TRANSACTION 、ROLLBACK TRANSACTION 、 SAVE TRANSACTION 等Transact-SQL语句来完成的。
启动事务: BEGIN TRANSACTION 。
结束事务: COMMIT TRANSACTION 。例如:
1 use test 2 go 3 4 /*启动一个事务向student表中插入一个记录*/ 5 begin transaction 6 insert into student values(100,'陈浩','男',19) 7 commit tran 8 9 select * from student 10 go
回滚事务: ROLLBACK TRANSACTION 。例如:
1 /*启动一个事务向student表中删除一个记录,然后回滚该事务*/2 begin transaction3 delete student where sno=1004 rollback5 6 select * from student --由于回滚该事务,因此student表中没有插入记录。7 go
在事务内设置保存点: SAVE TRANSACTION 。保存点是如果有条件的取消事务的一部分,事务可以返回的位置。例如:
1 /*在事务内设置保存点*/2 begin transaction mytran --启动事务3 select * from student4 save transaction s1 --设置保存点。5 insert into student values(200,'王洪','男',22) --插入另一个学生的记录6 rollback transaction s1 --事务回滚到保存点s17 commit transaction8 go 9 select * from student --陈浩插入到表中而王洪没有插入到表中
不能用于事务的操作:
操作 | 相应的SQL语句 |
创建数据库 | CREATE DATABASE |
修改数据库 | ALTER DATABASE |
删除数据库 | DROP DATABASE |
恢复数据库 | RESTORE DATABASE |
加载数据库 | LOAD DATABASE |
备份日志文件 | BACKUP LOG |
恢复日志文件 | RESTORE LOG |
更新统计数据 | UPDATE STATISTICS |
授权操作 | GRANT |
复制事务日志 | DUMP TRANSACTION |
磁盘初始化 | DISK INIT |
更新使用sp_configure系统存储过程更改的配置选项的当前配置值 | RECONFIGURE |
2.自动提交事务
SQL Server没有使用BEGIN TRANSACTION语句启动显式事务,或隐性事务模式未打开,将以自动提交模式进行操作。
当提交或回滚显式事务或者关闭隐性事务模式时,SQL Server将返回到自动提交模式。
3.隐式事务
隐性事务模式设置为打开之后,当SQL Server首次执行某些Transact-SQL语句时,都会自动启动一个事务,而不需要使用 BEGIN TRANSACTION 语句。
启动新事务的Transact-SQL语句包括:
在发出COMMIT或ROLLBACK语句之前,该事务一直保持有效。在第一个事务被提交或回滚之后,下次当连接执行这些语句的任何语句时,SQL Server都将自动启动一个新事务。
隐性事务模式可以通过使用SET语句来打开或者关闭,其语法格式为: SET IMPLICIT_TRANSACTIONS { ON | OFF }
隐性事务模式打开时,用户必须在该事务结束时显式提交或回滚。
隐性事务模式将保持有效,直到执行 SET IMPLICIT_TRANSACTIONS OFF 语句使连接返回到自动提交模式。
例如:
1 /*演示在将IMPLICIT_TRANSACTIONS设置为ON时显式或隐式启动事务。 2 使用@@trancount函数返回当前连接的活动事务数。 */ 3 set nocount on 4 print cast(@@trancount as char(5)) 5 create table table1(a int) 6 insert table1 values(1) 7 go 8 print cast(@@trancount as char(5)) 9 10 print '使用显式事务'11 begin tran12 insert table1 values(2)13 print '当前连接的活动事务数:'+cast(@@trancount as char(5))14 commit tran15 16 print '当前连接的活动事务数:'+cast(@@trancount as char(5))17 go 18 19 print '设置 implicit_transactions为on'20 set implicit_transactions on21 go 22 print '使用隐式事务'23 insert into table1 values(4) --这里不需要begin tran语句来定义事务的启动24 print '当前连接的活动事务数:'+ cast(@@trancount as char(5))25 commit tran26 print '当前连接的活动事务数:'+ cast(@@trancount as char(5))27 go28 29 drop table table130 set implicit_transactions off31 32 /*BEGIN TRANSACTION 语句使 @@TRANCOUNT 递增 1。33 ROLLBACK TRANSACTION 将 @@TRANCOUNT 递减为 0,34 但 ROLLBACK TRANSACTION savepoint_name 语句并不影响 35 @@TRANCOUNT 值。COMMIT TRANSACTION 将 @@TRANCOUNT 递减 1。*/
分布式事务
跨越两个或多个服务器上的数据库的事务就是分布式事务。
与本地事务的不同在于事务的提交(2pc)
控制分布式事务的T-SQL语句包括: begin distributed transaction 、 commit transaction\commit work 、 rollback transaction\rollback work
数据的锁定
并发问题包括:修改丢失;脏读;不可重复读;幻读
事务的隔离级别:未提交读;提交读;可重复读;可串行读
SQL SERVER 2005中的锁: 共享锁; 排它锁;更新锁;意向锁;
架构锁
封锁技术需要解决的问题:死锁
锁的若干自定义操作:
1.通过Set lock_timeout 设置事务被阻塞的最长时间;通过@@lock_timeout查看。例如:
1 /*查看@@lock_timeout*/2 print @@lock_timeout --LOCKTIMEOUT 的缺省值是 -1,这意味着将没有锁超时3 4 set lock_timeout 18005 print @@lock_timeout
2. 定义事务隔离级别(4种) set transaction isolation level ... 。
3. 锁定提示。例如:
1 /*在select,insert,update和delete等语句中使用表级锁定提示*/2 set transaction isolation level serializable3 begin tran4 select * from student with(tablock)5 exec sp_lock6 commit tran7 8 select object_name(1013578649)
原标题:sql server 事务处理
关键词:sql