事务

最新推荐文章于 2024-06-18 11:52:42 发布

窝法氦镁烷

最新推荐文章于 2024-06-18 11:52:42 发布

阅读量625

点赞数

本文链接：https://blog.csdn.net/AAMahone/article/details/89301249

版权

数据库同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

Microsoft SQL server

13 篇文章 0 订阅

订阅专栏

SQL server

12 篇文章 1 订阅

订阅专栏

文章目录

概述
事务的属性、原理和类型
管理事务
使用锁

概述

事务又称原子操作,这个概念和计算机操作系统的概念是一致的.事务的出现是为了解决多用户并发的问题(计算机操作系统的事务是为了解决多程序并发问题),在数据库中,多用户并发指的是多个用户同时使用数据库.为了解决多用户并发带来的效率和安全性问题,数据库提出了事务的概念.和事务相关的一个概念就是锁–锁的作用就是保护指定的资源不被其他事务所访问,这一点可以类比操作系统的信号量和临界资源的概念.事务和锁具有以下特点:

事务是一个不可分割的操作最小单元,事务要么全部完成,要么全不完成.
事务保证操作的一致性和可恢复性.
每一条T-SQL语句都可以是一个事务.
实际使用的事务是一系列操作的集合,它是根据用户需求定义的.
多服务器环境中,使用用户定义的分布式事务保证一系列操作的一致性.
锁是保证并发控制的有效手段,可以锁定的资源包括行、页、Extent、表、数据库.
锁的基本类型是共享锁和排他锁,特殊类型有意图锁、修改锁、模式锁.
共享锁允许其他事物继续使用锁定的资源,排他锁只允许一个事务访问数据.
系统本身可以处理死锁.

事务的属性、原理和类型

作为一个逻辑单元,事务有以下4个属性:自动性、一致性、独立性、持久性.

自动性指的是必须是一个自动的单元工作,要么修改全部数据,要么不修改任何数据.
一致性指的是当事务完成时,必须使所有数据都具有一致的状态,所有的规则必须应用到事务的修改上,以保证数据完整性.
独立性指的是并行事务的修改与其他并行事务相互独立.一个事务看到来自其他事务的数据,要么是其他事务修改前的,要么是修改后的,无法看到其他事务正在修改的数据,这种特性也称为串行性.
持久性指的是事务完成后,它带来的影响将永久性的产生在系统中,即这种修改写入了系统.

事务的工作原理如下:

事务提供了事务日志和检查点机制.事务日志记录一些列的操作,这些操作一般分为两类: 一类是对数据的操作,另一类是对任务的操作.不管进行了怎样的操作,都会写入事务日志里,一旦事务失败未能提交,系统就会自动执行这些操作的逆操作,以便对事务进行恢复,使其回到事务开始前的状态.至于以上操作是如何发生的,就要依赖于事务的检查点机制,检查点自动记录已经完成的操作,并对每一个完成的操作建立一个检查点.一旦遇到失败的操作,则会向系统发送一个检查点未完成的信号,这是前面所有的检查点都要撤销,系统将自动取消所有的操作,使系统回到事务开始前的状态.

使用事务时,应考虑如下因素:

优先使用短(简单)事务,事务越短,其完成时间就越短,对系统资源的独占时间就会越短,可以提高事务的并发性.
不要使用嵌套事务,事物本身就是一个不可分割的整体,嵌套事务除了让事务复杂,并没有其他好处.

事务的类型: 事务可分为系统事务和用户定义的事务.

系统提供的事务: 一般一条T-SQL语句就是一个系统事务,如建表,创建索引等.
用户定义的事务: 这是我们最常用的事务类型,要注意一个事务必须有开始和结束,提交和回滚定义.

其实还有一种特殊的用户定义的事务–分布式事务.前面说的用户定义事务是指在一台服务器上的操作,分布式事务是指在多台服务器上定义的事务.在分布式事务中,所有的操作都可以涉及对多台服务器的操作.这些操作如果完成,那么就会提交到相应的服务器中,如果有一个操作失败,那么这个分布式事务的全部操作将被取消.

管理事务

管理实务是指定义事务、设置事务标识、提交事务、取消事务等操作.SQL server可以用如下语句管理一个事务:

BEGIN TRANSACTION: 指定(本地)事务的起点.
BEGIN DISTRIBUTED TRANSACTION: 指定(分布式)事务的起点.
COMMIT TRANSACTION: 提交一个事务.
COMMIT WORK: 与COMMIT TRANSACTION效果一致,但不接受具体事务名称.
SAVE TRANSACTION: 设置事务保存点.
ROLLBACK TRANSACTION: 回滚一个事务(可回滚到事务开始/保存点),取消事务开始(或保存点)之后的所有操作.
ROLLBACK WORK: 与ROLLBACK TRANSACTION用法一致,但不接受具体事务名称.
SET IMPLLICIT_TRANSACTION: 将事务设置为隐式事务模式,该模式下事务结束必须明确提交,否则用户断开连接后事务将取消.

其中,这些语句的基本用法如下:

--Applies to SQL Server and Azure SQL Database
 
BEGIN { TRAN | TRANSACTION }   
    [ { transaction_name | @tran_name_variable }  
      [ WITH MARK [ 'description' ] ]  
    ]  
[ ; ]

/*
 * 事务名是可选的,一般不指定;
 *	如果使用了WITH MARK,则必须指定事务名.WITH MARK允许将事务日志还原到命名标记;
 */
 
BEGIN DISTRIBUTED { TRAN | TRANSACTION }   
     [ transaction_name | @tran_name_variable ]   
[ ; ]

/*
 * 分布式事务的事务名可选,一般不指定;
 */
 
 -- Applies to SQL Server (starting with 2008) and Azure SQL Database
  
COMMIT [ { TRAN | TRANSACTION }  [ transaction_name | @tran_name_variable ] ] 
	[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]  
[ ; ]

/*
 * DELAYED_DURABILITY是请求应将此事务与延迟持续性一起提交的选项;
 * 详细可参见文档,一般不指定;
 */
 
COMMIT [ WORK ]  
[ ; ]

/*
 * 功能与COMMIT TRANSACTION相同,但COMMIT WORK不接受用户定义的事务名称;
 */
 
SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }  
[ ; ]

/*
 * 在事务内设置保存点,保存点可影响回滚操作.
 */
 
ROLLBACK { TRAN | TRANSACTION }   
     [ transaction_name | @tran_name_variable  
     | savepoint_name | @savepoint_variable ]   
[ ; ]

/*
 * 指定transaction_name一类参数,可回滚到事务开始前的状态,自事务开始所有操作全部取消;
 * 指定savepoint_name一类参数,可回滚到保存点的状态,自保存点之后所有操作全部取消;
 */

COMMIT [ WORK ]  
[ ; ]

/*
 * 同ROLLBACK { TRAN | TRANSACTION } [transaction_name | @tran_name_variable];
 * 取消整个事务,不接受用户定义的事务名称;
 */
 
SET IMPLICIT_TRANSACTIONS { ON | OFF }
/*
 * 为ON时,系统处于“隐式”事务模式,事务若不主动提交,则在用户断开连接后会自动取消;
 */

下面是一个事务的使用实例:

USE ElecTravelCom;
GO

CREATE TABLE Employee (
	EmployeeID INT,
	EmployeeName VARCHAR(20)
)
GO

BEGIN TRANSACTION
INSERT INTO dbo.Employee VALUES (1,'Henry')
SAVE TRANSACTION checkpoint1
INSERT INTO dbo.Employee VALUES (1,'Aveline')
SAVE TRANSACTION checkpoint2
INSERT INTO dbo.Employee VALUES (1,'Aaron')
SAVE TRANSACTION checkpoint3
INSERT INTO dbo.Employee VALUES (1,'Connor')
SAVE TRANSACTION checkpoint4
ROLLBACK TRANSACTION checkpoint2
COMMIT TRANSACTION
GO

SELECT * FROM Employee;
GO

运行结果如下:

使用锁

锁的概念和作用

锁是防止其他事务访问指定资源的手段,是实现并发控制的主要方法.一般来说,锁可以防止脏读、不可重复读、幻觉读等问题.

脏读: 指一个事物访问并修改数据,但是该数据并未提交到系统,另一个事务就访问并修改了这个数据.由于这个数据还没有提交,因此后来的事务读取到的这个数据就是脏数据,基于脏数据做出的修改得到的结果很可能是不正确的.
不可重复读: 指在一个事务内,多次读同一个数据却得不到相同的结果.造成不可重复读的原因是另一个事务读取并修改了这个数据,因此在第一个事务内多次读取同一个数据得到的结果并不相同.
幻觉读: 指当前事务不是独立执行时发生的一种"错误"现象.如有一个事务修改表中所有数据,此时另一个事务往表中插入一条数据,对于第一个事务的用户来说就会有一种"表中有的数据并未被修改"的"幻觉".

可锁定的资源

为了控制锁定的资源,首先应该了解SQL server系统的空间特点:在SQL server中,最小的空间管理单位是页,一个页8KB,所有的数据都放在页上.另外,页有一个限制: 表中同一行的数据放在同一页上,不允许跨页存储.页上面的空间管理单位是Extent,一个Extent是8个连续的页.表和索引占用的最小单位是Extent.数据库是由一个或多个表组成的,即一个或多个Extent组成数据库.

针对不同资源使用不同的锁,以达到最佳的事务和数据保护功能.锁既可锁定大资源,也可锁定小资源,对应资源的锁有: 行级锁、页级锁、Extent级锁、表级锁、数据库级锁.可锁定的资源如下:

行: 使用行级锁锁定一行或多行数据,其他事物不能访问这些数据.这样的做法大大提高了系统的并发性,加快系统效率.行级锁是最优锁,因为行级锁不存在锁定资源又无人使用的情况,不会造成资源的浪费.
页: 使用页级锁每次只能锁定一页,不管操纵了多少数据,这一页的所有数据都无法被其他事务所使用,这样可能会出现资源浪费的现象,因为页中可能包括多个表的数据,事务并不能总是使用这些表,这种情况下资源浪费最大限度不超过一页数据.
Extent: 这是一种特殊的锁,一次锁定一个Extent,这种锁仅在系统分配资源时使用.如系统创建数据库和表时,是按照Extent来分配资源的,一次锁定若干Extent,在创建这些对象完成后,便不再使用Extent级锁.
表: 表级锁也是一种非常重要的锁,在系统对表进行数据操纵时,往往会使用表级锁锁定表,使其他事务无法使用表.这种锁的好处是占用很少的系统资源(如内存),但是缺点是占用较多的数据资源,致使其他事务可能需要等待,降低系统的并发性.
数据库: 数据库级锁锁定整个数据库,防止任何用户或事务访问锁定的数据库.这是一种非常特殊的锁,是最高等级的锁.仅在系统恢复数据库时自动使用,此时数据库进入单用户模式,防止其他用户对数据库进行访问.实际上这种锁就是单用户机制.

锁的类型和兼容性

锁的基本类型有两种: 共享锁和排他锁.除此之外还有一些其它的锁,如意图锁、修改锁、模式锁等.

共享锁: 指允许并行的事务对同一个资源同时读取,此时的所有事务都无法修改该资源.如使用SELECT访问的资源,系统会自动地在其上面加共享锁.
排他锁: 排他锁是指同一时间内只允许一个事务修改资源,其他事务只能等到当前事务提交之后才能修改资源.如使用INSERT、UPDATE、DELETE操作的资源,系统会自动地在其上面加排他锁.

意图锁被用来最小化锁之间的冲突.意图锁建立一个锁机制的分层的结构,这种结构依据锁定的资源范围从低到高依次是行级锁层、页级锁层、表级锁层.意图锁表示系统希望在低层次的资源上获得共享锁或排他锁.例如,放置在表级上的意图共享锁表示一个事务可以在表中的页或行上放置共享锁.在表级上设置共享锁防止以后另一个修改该表中页的事务在包含了该页的表上放置排他锁.意图锁可以提高性能,这是因为系统只需要在表级上检查意图锁,确定一个事务能否在那个表上安全地获取一个锁,而不需要检查表上的每一个行锁或者页锁,来确定一个事务是否可以锁定整个表.

意图锁有三种类型,即意图共享锁、意图排他锁、使用意图排他的共享锁.意图共享锁表示读取低层次资源的事务的意图,把排他锁放在这些单个的资源上.意图排他锁包括意图共享锁,它是意图共享锁的超集.使用意图排他的共享锁表示允许并行读取顶层资源的事务意图,并且修改一些低层次的资源,把意图排他锁放在这些单个资源上.例如,表上的一个使用意图排他的共享锁把共享锁放在表上,允许并行读取,并且把意图排他锁放在将要修改的页上,把排他锁放在将要修改的行上.每一个表一次只能有一个使用意图排他的共享锁,这是因为表级共享锁阻止对表的任何修改.使用意图排他的共享锁是共享锁和意图排他锁的组合.

修改锁用于修改一个页,修改该页前,系统自动的把这个修改页锁上升到排他锁,防止冲突.第一次读取页时,在修改操作的开始阶段获得修改锁.修改锁和共享锁是兼容的,如果该页被修改了,那么修改锁上升到排他锁.

模式锁保证当前表或索引被另外一个会话参考时,不能被删除或修改其结构模式.一共由两种模式锁: 模式稳定锁和模式修改锁.前者用于保证模式结构不能被删除,后者保证模式结构不能被修改.

关于SQL server中的锁之间的兼容性,如下表所示:

锁名称	IS	S	U	IX	SIX
意图共享锁(IS)	$\checkmark$	$\checkmark$	$\checkmark$	$\checkmark$	$\checkmark$
共享锁(S)	$\checkmark$	$\checkmark$	$\checkmark$
修改锁(U)	$\checkmark$	$\checkmark$
意图排他锁(IX)	$\checkmark$			$\checkmark$
意图排他的共享锁(SIX)	$\checkmark$
排他锁(X)

死锁问题

死锁是一个重要的问题,一般来说,死锁是系统资源调度不当造成的事务阻塞.在下面两个情况下,会发生死锁:

当两个事务分别锁定了两个单独的对象,这时每一个事务要求在另一个事务锁定的对象上获得一个锁,因此每一个事务都必须等待另一个事务释放占有的锁,这时就发生了死锁.
在一个数据库中,正在进行若干个长时间运行的事务,当查询分析器处理一种非常复杂的查询,例如连接查询时,由于不能控制处理的顺序,有可能发生死锁.

当发生死锁时,系统可以自动检测到,然后通过自动取消其中一个事务来解除死锁.在发生死锁的两个事务中,根据事务处理时间长短作为规则来确定它们的优先级,长时间的处理任务优先级高,发生冲突时,会优先取消优先级低的事务.

会话级锁和表级锁

从以上关于锁的介绍来看,锁都是由系统自动指定的,无需用户干预.但是,用户也可以定制锁,定制范围限于会话级锁和表级锁.

会话级锁的定制包括两个方面: 事务隔离等级和锁超时限制.

事务隔离等级保护指定的事务,该事务隔离允许对一个会话中的全部事务设置隔离等级,一共有如下几个隔离等级:

READ UNCOMMITTED: 指导系统既不使用共享锁,也不使用排他锁.此种等级下,会看到正在修改的数据,会发生脏读或不可重复读,这是一种隔离等级最低的设置.
READ COMMITTED: 指导系统在读取数据时使用共享锁.此种等级下,不会发生脏读,但是在事务结束之前,原有的数据可能会发生改变,所以可能产生不可重复读或幻觉读.
REPEATABLE READ: 指导系统在查询使用的全部数据上放置锁,防止其他用户修改数据,但是不能防止另外的事务插入数据,因此可能会发生幻觉读.
SNAPSHOT: 指定事务中的任何语句都只能读取事务开始之前的语句,仿佛读取的是一个系统快照一样.当前事务执行的语句看不到由其他事务对数据做出的修改.除非在恢复数据库,否则SNAPSHOT事务不会在读取数据时请求锁.读取数据的SNAPSHOT事务不阻止其他事务写入数据,写入数据的事务也不阻止其他SNAPSHOT事务读取数据.在数据库恢复的回滚阶段,如果尝试读取由其他正在回滚的事务锁定的数据,则SNAPSHOT事务将请求一个锁.在事务完成回滚前,SNAPSHOT事务将一直被阻塞.当事务获得授权后,便立即释放锁.
SERIALIZABLE: 指导系统在数据上放置锁,直到该事务完成后,用户才可访问或插入数据.这是隔离等级最高的设置.另外也可以定制事务的等待访问时间,需要设置锁超时限制.当设置了锁超时以后,若事务等待时间超过了锁超时时间,那么事务自动取消.

用户可以使用SET TRANSACTION ISOLATION LEVEL来设置会话的事务隔离等级,如下示例:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

以上方法设置的会话事务隔离等级作用范围是从设置点开始到一个完整的事务结束(GO之前的所有语句).

定制表级锁是指通过为表指定一个或多个选项来设置表级锁的行为.实际上,定制表级锁就是使用一种优化隐藏的方式.优化隐藏是指在FROM子句后面,附加上有关行为的选项,提高系统识辩操作的能力.定制表级锁有如下选项:

~~ROWLOCK: 使用行级锁而不是页级锁或表级锁,这是系统默认的选项~~,现已在SQL server中删除.
PAGLOCK: 使用页级锁.
TABLOCK: 使用表级锁,且使用共享锁,这样允许其他事务读取表中数据,但不能修改.
TABLOCKX: 使用表级锁,且使用排他锁,防止除当前事务的其他事务读取或修改表中数据.
NOLOCK: 既不使用共享锁,也不使用排他锁,这样会发生脏读.
HOLDLOCK: 系统占有共享锁直到事务结束,而不是其他锁请求时立即释放.
UPDLOCK: 事务在读取表中数据时,使用修改的页级锁而不是共享锁.该锁一直到语句/事务结束为止.

定制表级锁的行为在SQL server中被称为表提示(table_hint),常常使用这样的语法定义:

FROM t (TABLOCK);

--如果指定的提示含其他选项,则指定的提示必须含WITH关键字:
FROM t WITH (TABLOCK, INDEX(myindex))

一般建议让系统自动确定使用的锁,用户不要多加干预(也没有太多干预的余地),只需知道锁的概念即可.

上一篇: 备份和还原

窝法氦镁烷

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
事务

关于SQL server事务的内容
复制链接

扫一扫

专栏目录