sql server 事务处理

事物处理

事务是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 transaction
3 delete student  where sno=100
4 rollback
5 
6 select * from student    --由于回滚该事务,因此student表中没有插入记录。
7 go 

在事务内设置保存点: SAVE TRANSACTION 。保存点是如果有条件的取消事务的一部分,事务可以返回的位置。例如:

1 /*在事务内设置保存点*/
2 begin transaction mytran         --启动事务
3 select * from student
4 save transaction s1      --设置保存点。
5 insert into student values(200,'王洪','男',22)   --插入另一个学生的记录
6 rollback transaction s1   --事务回滚到保存点s1
7 commit transaction
8 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 tran
12 insert table1 values(2)
13 print '当前连接的活动事务数:'+cast(@@trancount as char(5))
14 commit tran
15 
16 print '当前连接的活动事务数:'+cast(@@trancount as char(5))
17 go 
18 
19 print '设置 implicit_transactions为on'
20 set implicit_transactions on
21 go 
22 print '使用隐式事务'
23 insert into table1 values(4)  --这里不需要begin tran语句来定义事务的启动
24 print '当前连接的活动事务数:'+ cast(@@trancount as char(5))
25 commit tran
26 print '当前连接的活动事务数:'+ cast(@@trancount as char(5))
27 go
28 
29 drop table table1
30 set implicit_transactions off
31 
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 1800
5 print @@lock_timeout

2. 定义事务隔离级别(4种) set transaction isolation level ... 。

3. 锁定提示。例如:

1 /*在select,insert,update和delete等语句中使用表级锁定提示*/
2 set transaction isolation level serializable
3 begin tran
4     select * from student with(tablock)
5     exec sp_lock
6 commit tran
7 
8 select object_name(1013578649)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

数据库恢复方案

数据库恢复方案 摘要 这里所谈的内容是对备份数据的恢复,不是对损坏数据表的恢复,或者说灾难恢复。 目录 1. 背景 2. 备份方式分析 3. 恢复方案 3.1....

3645
来自专栏禅林阆苑

Sphinx&coreseek实现中文分词索引

众所周知,mysql等数据库的LIKE模糊搜索不支持索引,因此查询效率极低,需要结合第三方索引引擎程序(索引程序)来提高查询性能。

2553
来自专栏我的博客

ADODB简介以及简单使用

1、adodb数据库类介绍(http://adodb.sourceforge.net/) 虽然 PHP 是建构 Web 系统强有力的工具,但是 PHP 存取数据...

3517
来自专栏智能大石头

XCode v8.11 重量级分表分库(无视海量数据)

XCode天生就有分表分库功能,设计于2005年! 历时9年,这是分表分库功能第一次针对性正式更新。 在XCode里面,分表分库非常简单,在操作数据(查询/更...

23210
来自专栏数据和云

查看SQL执行计划的方法及优劣

作者 | 胡佳伟:云和恩墨技术工程师,有多年数据库优化经验,在一线执行过多个包括通信、保险等行业的优化项目。

1122
来自专栏Spark学习技巧

phoenix二级索引

二级索引 二级索引是从主键访问数据的正交方式。Hbase中有一个按照字典排序的主键Rowkey作为单一的索引。不按照Rowkey去读取记录都要遍历整张表,然后按...

6919
来自专栏编程心路

语言小知识-MySQL数据库引擎

MySQL 作为全世界广受欢迎的数据库,被用于很多中小型的项目中,但是你对 MySQL 数据库的存储引擎了解多少呢?

1304
来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

2597
来自专栏乐沙弥的世界

Oracle 10.2.0.1 升级到 10.2.0.4

数据库升级并不难,只要遵循其步骤,一般问题不大。但是升级失败的情况也是屡见不鲜,尤其是生产数据库的升级,搞不定的时候甚至要创建SR。

1403
来自专栏「3306 Pai」社区

关于MySQL 8.0的几个重点,都在这里

在MySQL8.0中重新设计了redo log,主要改进fsync,使得效率更高,减少锁,优化flush机制,不会频繁flush。同时,支持更高用户并发请求。

1110

扫码关注云+社区

领取腾讯云代金券