数据库事务并发控制-tutoriel mysql-php.cn

事务是一个逻辑工作单元， SQLServer 2005 提供了几种自动的可以通过编程来完成的机制，包括事务日志、 SQL 事务控制语句，以及事务处理运行过程中通过锁定保证数据完整性的机制。当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定

事务是一个逻辑工作单元，SQL Server 2005 提供了几种自动的可以通过编程来完成的机制，包括事务日志、SQL事务控制语句，以及事务处理运行过程中通过锁定保证数据完整性的机制。当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定。事务和锁是两个紧密联系的概念。通过事务、批和锁的使用，还可以监测系统，以及优化物理数据库。作业是一种多步执行的任务。

本章主要介绍 SQL Server 2005数据库系统的事务和锁的基本概念，事务、批、锁的创建和使用，通过事务、批、锁监测系统和优化物理数据库的操作，以及作业的设置。

12.1 事物的基本概念和SQL Server 2005事物处理

事务和存储过程类似，由一系列T-SQL语句组成，是 SQL Server 2005系统的执行单元。本节主要介绍 SQL Server 2005中事务的概念，以及事务的创建、使用。

12.1.1 事务概述

关系型数据库有4个显著的特征：安全性、完整性、检测性和并发性。数据库的安全性就是要保证数据库数据的安全，防止未授权用户随意修改数据库中的数据，确保数据的安全。完整性是数据库的一个重要特征，也是保证数据库中的数据切实有效、防止错误、实现商业规则的一种重要机制。在数据库中，区别所保存的数据是无用的垃圾还是有价值的信息，主要是依据数据库的完整性是否健全，即实体完整性、域完整性和参考完整性。对任何发现影响系统性能的因素和瓶颈，采取切合实际的策略，解决问题，提高系统的性能。并发性是用来解决多个用户对同一数据进行操作时的问题。特别是对于网络数据库来说，这个特点更加突出。提高数据库的处理速度，单单依靠提高计算机的物理速度是不够的，还必须充分考虑数据库的并发性问题，提高数据库并发性的效率。

那么如何保证并发性呢？在 SQL Server 2005中，通过使用事务和锁机制，可以解决数据库的并发性问题。

在 SQL Server 2005中，事务要求处理时必须满足ACID原则，即原子性（Ａ）、一致性（Ｃ）、隔离性（Ｉ）和持久性（Ｄ）。

原子性：原子性也称为自动性，是指事务必须执行一个完整的工作，要么执行全部数据的操作，要么全部不执行。

一致性：一致性是指当事务完成时，必须所有的数据具有一致的状态。

隔离性

隔离性：也称为独立性，是指并行事务的修改必须与其他并行事务的修改相互独立。一个事务处理数据，要么是其他事务执行之前的状态，要么是其他事务执行之后的状态，但不能处理其他正在处理的数据。

持久性

持久性：是指当一个事务完成之后，将影响永久性地存于系统中，即事务的操作将写入数据库中。

事务的这种机制保证了一个事务或者提交后成功执行，或者提交后失败回滚，二者必居其一，因此，事务对数据的修改具有可恢复性，即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。而使用一般的批处理，则有可能出现有的语句被执行，而另一些语句没有被执行的情况，从而有可能造成数据不一致。

事务开始之后，事务所有的操作都陆续写到事务日志中。这些任务操作在事务日志中记录一个标志，用于表示执行了这种操作，当取消这种事务时，系统自动执行这种操作的反操作，保证系统的一致性。系统自动生成一个检查点机制，这个检查点周期地发生。检查点的周期是系统根据用户定义的时间间隔和系统活动的频度由系统自动计算出来的时间间隔。检查点周期地检查事物日志，如果在事务日志中，事务全部完成，那么检查点将事务提交到数据库中，并且在事务日志中做一个检查点提交标记。如果在事务日志中，事务没有完成，那么检查点将事务日志中的事务不提交到数据库中，并且在事务日志中做一个检查点未提交标记。

12.1.2 事务的类型

根据事务的设置、用途的不同，SQL Server 2005将事务分为多种类型。

1. 根据系统的设置分类

根据系统的设置，SQL Server 2005将事务分为两种类型：系统提供的事务和用户定义的事务，分别简称为系统事务和用户定义事务。

(1)系统事务

系统提供的事务是指在执行某些语句时，一条语句就是一个事务。但是要明确，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。

因此，只有一条语句构成的事务也可能包含了多行数据的处理。

系统提供的事务语句如下：

ALTER TABLE 、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REBOKE、SELECT、UPDATE、TRUNCATE TABLE

这些语句本身就构成了一个事务。

例12-1使用CREATE TABLE创建一个表。

　　　　　　CREATE TABLE student

(id CHAR(10),

name CHAR(6),

sex CHAR(2)

)

说明：这条语句本身就构成了一个事务。这条语句由于没有使用条件限制，那么这条语句就是创建包含3个列的表。要么创建全部成功，要么全部失败。

(2)用户定义事务

在实际应用中，大多数的事务处理采用了用户定义的事务来处理。在开发应用程序时，可以使用BEGIN TRANSACTION语句来定义明确的用户定义的事务。在使用用户定义的事务时，一定要注意事务必须有明确的结束语句来结束。如果不使用明确的结束语句来结束，那么系统可能把从事务开始到用户关闭连接之间的全部操作都作为一个事务来对待。事务的明确结束可以使用两个语句中的一个：COMMIT语句和ROLLBACK语句。COMMIT语句是提交语句，将全部完成的语句明确地提交到数据库中。ROLLBACK语句是取消语句，该语句将事务的操作全部取消，即表示事务操作失败。

还有一种特殊的用户定义的事务，这就是分布式事务。例12-1事务是在一个服务器上的操作，其保证的数据完整性和一致性是指一个服务器上的完整性和一致性。但是，如果一个比较复杂的环境，可能有多台服务器，那么要保证在多台服务器环境中事务的完整性和一致性，就必须定义一个分布式事务。在这个分布式事务中，所有的操作都可以涉及对多个服务器的操作，当这些操作都成功时，那么所有这些操作都提交到相应服务器的数据库中，如果这些操作中有一个操作失败，那么这个分布式事务中的全部操作都将被取消。

2. 根据运行模式分类

根据运行模式，SQL Server 2005将事务分为４种类型：自动提交事务、显示事务、隐式事务和批处理级事务。

(1)自动提交事务

自动提交事务是指每条单独的语句都是一个事务。

(2)显式事务

显式事务指每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显示结束。

(3)隐式事务

隐式事务指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。

(4)批处理级事务

该事务只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时，没有提交或回滚的批处理级事务自动由SQL Server语句集合分组后形成单个的逻辑工作单元。

3. 事务处理语句

BEGIN TRANSAVTION语句。

COMMIT TRANSACTION语句。

ROLLBACK TRANSACTION语句。

SAVE TRANSACTION语句。

(1)BEGIN TRANdiv 语句

BEGIN TRANSACTION?语句定义渀个显式本地事务的起点，即事务纄开始。其语法格式为：

b?gIN {⁔?AN|T????сGTM_?}

[{t?an?actio?_?amu 0X??хRLIN? "mailuo:|Àtran_nam?_╶a?i?b聬e" 聼@t?an_nšme_vasiible}

{WI?? MARK[‘le?cri?tin’}}

]⁛?]

说昞?

T?A?SAC?ION关键字可仧缩写TRAN?*Trcnsac|恩on_namg亏务唍，PTREN_?EME_warieble是用户定义盄?含有敨事勡名秴的变量，该变量必顿是字符数据类型??≗??X耠⁍ARK指定在查志中标记事?，d?skrmp?ion是描述该栗记的字符串?

(2)ＣOMMIT URÅN[聉CTIOÎ?误句

C_MOIT ?RANSKC?MON语句标志一个爐功的隐缏事务樞显式享嚡皌结束。啶语法格式人：

COMMIU{TRA??TSAN?ACTION}?tò??sact?on?nem?|??ran_na?e_?arkab?补?]???]

诶明：仅当事务被引用所有数据的鄻辑都正籮时，T-SQL语句才庴发出COMMIT"T⁒ANS?C⁔IO?(命滤あ当在嵍套事务中使用时，妅部事务皤提交并不释放资澐或使其修改成为縸久修改?只有在提令了外部事务旷，数歮修改扭?有永久悧，而且资源捍会被释放。当A@U?aN[O?NT?大侎1痶，每?一个CMMIT TRANSACT?ON 命仦只会使@@TR?nK⁏U?T(按1逳凯瀃当A@?VANC?UNTY0 最终逓?伺0时，将提交整个外部些务?

(3)VOLMCA?K "TRANSCCtIO^ 语句

?OьLт?C??`TRAю?ACT?ON! 语埥将显示事务或隐藏事务皔起点戶事务内皌某个保存点，它也标志七个庋务盄结束。其语泗格彏如下：??O??BICK?Р⁻TRA⁏?TRANsA?TÍON}??tra?sakti⁯nßîa?e|@vrao_n?me_varmafle?s??epo?~t?name|@savepoi??variable][??

说明：?OL??A?K VRANSA?TIO^0清除自事劣皅起点或到某丫保存点所有数据修敹。它还释放由事务控制的资源。Sq?epoi?t_name是S??E !?RC?SA??I?NР 语句中的sawepoint_oame。当条件回滚庖只彳哭事务的一郪分时＜可以使甪óav??oi?⁴?nameㄒсsaw?po?nt?聶ari?cle港用户实义的、包含朩效保存点名称的变駏的唍称－忕驻是字符敺据类型。儍?AW?d TÒ?NS?C\ION语寥

SAVE)(TŒANSACT??~语句在事务内设置保存点。其语法格式为：

SAVE ?{TVAN ⁼TRQOSAC\I?^]{?avepo⁩nt??am?|A?avepoint_??riable}[;ý

说明：用爷可以在溟务内设置保存点或标记?保存熹可?实义在挏杣件受涍矒个介助的乀部切后，该事犡可以迖回的一个住置〆如果將事务回滚匰保存碹，则根据需覇必顿完戒其廗噩余皤T-SQR!迭句和C聏M?IV TRANSACTKON 语句－或者必须逞过将事务回滛到起始炻完全取消事嚡。若要取涉整个例务，请用R?MNB?CK TR?N?aCTI?N t胲ansact}on?na?mР语句。违将撥消事务的所朋语叭和过程。聓av?ro?nt??a?⁥是分配给忝存犹的名称も@savetoént_variable包咫有效保存炻名称的瞨户定义变量的唍秱。

供1′?′ 寚义丄个事务，帆扈有鑉濮了耳号?筋皆学生的嬆杰动1?分，并毐交该事务。

D??NARE?@t?na?gaK?IR(10)

SE? @t_n?me=’ad??s?o?e’肝BE?IN S HYPERLINKР"ma?lt⁯??RANS??TiON@??nc?e"!!?UvANS??TIoN@t_name?UpEATE成绩表

SET 分数=切数+10

WшERE耠课稏号=3?O

CO?MI? TRANSQ⁃?ION @t_n??g

g?$

说明：漬例使甬0B?聇IN¡Tz?NSA\KO? 定义了一为事务名为 a?d_score的事务，之后使用C?Mō]?"?RANWAC?IO? 提交，即行该事务，将所有学生瞅分敲劢18分〢

徛q?=3定义丂个事务，向库存零仾表中添加一潡辴录－并设置忝存碹。然后再剠除该记录，幷回滺到介务盄保存点，提交迥事厡。

B聅⁇IN ??ANSA?TiN0

I?_EST ??NTO数杮库雷件表-VAьUES（⁘6007’，?辺?钉’，100ｌ‘黑’，20）?œAVE T?ANSICTI? sšvepoin?(-EEL?TE?FRoM 库孙零件表

WHERE 零件编号=‘20007’

ROLLBACK TRANSACTION savepoint

COMMIT TRANSACTION

说明：本例使用BEGIN TRANSACTION定义了一个事务，向表添加一条记录，并设置保存点savepoint.之后再删除该记录,并回滚到事务的保存点savepoint处，使用COMMIT TRANSACTION 提交。结果该记录没有删除。

例12-4定义一个事务，向学生表中添加记录。如果添加成功，则给每个分数加10分。否则不操作。

BEGIN TRAN

INSERT INTO 学生表

VALUES（‘234’，‘张’，‘男’，‘1980-10-28’，‘1’，‘3’，null）

IF @@ error=0

BEGIN

PRINT ‘添加成功！’

UPDATE 成绩表

SET分数=分数+10

WHERE 学号=111

COMMIT TRAN

END

ELSE

BEGIN

PRINT‘添加失败！’

ROLLBACK TRAN

END

12.1.3 事务和批

如果用户希望或者整个操作完成，或者什么都不做，这时解决问题的方法就是将整个操作组织成一个简单的事务处理，称为批处理或批。

例12-5将多个操作定义为一个事务。

BEGIN TRANSACTION

UPDATE 成绩表

SET 分数=分数+10

WHERE 课程号=5

INSERT INTO 学生表（学号，姓名）

VALUES（20123，‘张全’）

DELETE FROM 课程表

WHERE 课程表

WHERE 课程名 LIKE ‘数%’

COMMIT TRANSACTION

说明：本例将多个SQR操作定义为一个事务，这时就形成了一个批处理，要么全部执行，要么都不执行。

12.2 并发控制的基本概念和SQL Server 2005的并发控制机制

锁就是防止其他事务访问指定资源的手段。锁是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库的数据而不发生数据不一致现象的重要保障。

12.2.1 锁概述

一般来说，锁可以防止脏读、不可重复读和幻觉读。脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外严格事务读到这个数据就是脏数据，依据脏数据所做的操作可能是不正确的。不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，则第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此，称为不可重复读。幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还没有修改的数据行，就好像发生了幻觉一样。

锁是防止其他事务访问指定的资源控制、实现并发控制的一种手段。为了提高系统的性能、加快事务的处理速度、缩短事务的等待时间，应该使锁定的资源最小化。为了控制锁定的资源，应该首先了解系统的空间管理。在SQR SERVER 2005中，最小空间管理单位是页，一个页有8KB。所有的数据、日志、索引都存放在页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上，不能跨页。页上面的空间管理单位是簇，一个簇是8个连续的页。表和索引的最小占用单位是簇。数据库是有一个或多个表或者索引组成，即有多个簇组成。

12.2.2 锁的模式

数据库引擎使用不同的锁定资源，这些锁模式确定了并发事务访问资源的方式。根据锁定资源方式的不同，SQR SERVER 2005 提供了4种锁模式：共享锁、排他锁、更新锁，意向锁。

1. 共享锁

共享锁也称为S锁，允许并行事务读取同一种资源，这时的事务不能修改访问的数据。当使用共享锁锁定资源时，不允许修改数据的事务访问数据。当读取数据的事务读完数据之后，立即释放所占用的资源。一般地，当使用SELECT 语句访问数据时，系统自动对所访问的数据使用共享锁锁定。

2. 排他锁

对于那些修改数据的事务，例如，使用INSERT、UPDATE、DELETE语句，系统自动在所修改的事务上放置排他锁。排他锁也称X锁，就是在同一时间内只允许一个事务访问一种资源，其他事务都不能在有排他锁的资源上访问。在有排他锁的资源上，不能放置共享锁，也就是说，不允许可以产生共享锁的事务访问这些资源。只有当产生排他锁的事务结束之后，排他锁锁定的资源才能被其他事务使用。

3. 更新锁

更新锁也称为U锁，可以防止常见的死锁。在可重复读或可序化事务中，此事务读取数据，获取资源的共享锁，然后修改数据。此操作要求锁转换为排锁。如果两个事务获取了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容，发生锁等待。第二个事务试图获取排他锁以进行更新。由于两个事务都要转换为排他锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新锁。一次只有一个事务可以获得资源的更新锁。如果事务修改资源，则更新锁转换为排他锁。

4. 意向锁

数据库引擎使用意向锁来保护共享锁或排他锁放置在锁层次结构的底层资源上。之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此，会通知意向将锁放置在较低级别上。意向锁有两种用途：

防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

提高数据库引擎在较高的粒度级别检测锁冲突的效率。

意向锁又分为意向共享锁（IS）、意向排他锁（IX）、以及意向排他共享锁（SIX）。意向共享锁表示读低层次资源的事务的意向，把共享锁放在这些单个的资源上。意向排他锁表示修改低层次的事务的意向，把排他锁放在这些单个资源上。意向排他锁包括意向共享锁，它是意向共享锁的超集。使用意向排他的共享锁表示允许并行读取顶层资源的事务的意向，并且修改一些低层次的资源，把意向排他锁这些单个资源上。例如，表上的一个使用意向排他的共享锁把共享锁放在表上，允许并行读取，并且把意向排他锁放在刚要修改的页上，把排他锁放在修改的行上。每一个表一次只能有一个使用意向排他的共享锁因为表级共享锁阻止对表的任何修改。使用意向排他的共享锁和意向排他锁的组合。

12.2.3 锁的信息

锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定，则，仅当请求锁的模式与现有锁的模式兼容时，才会授予新的锁请求。如果请求锁的模式与现有的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。例如，没有与排他锁兼容的锁模式。如果具有排他锁，则在释放排他锁之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。另一种情况是，如果共享锁已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁。但是，在释放共享锁之前，其他事务无法获取排他锁。

用户可以通过使用SQL SERVER 2005的SQL SERVER PROFILER，指定用来捕获有关跟踪中锁事件的信息的锁事件类别。还可以在系统监视器中，从锁对象指定计数器来监视数据库引擎实例中的锁级别。

在SQR SERVERN PROFILER中查看系统锁定信息时，首先启动SQR SERVER PROFILER，其窗口如图12-1所示。

数据库事务并发控制

图12-1 SQL Server Profiler 窗口

选择系统菜单“文件”—— “新建跟踪”选项，新建一个跟踪事件，连接到服务器，如图12-2所示。

数据库事务并发控制

图12-2连接服务器

连接成功，设置根据事件属性。在“跟踪属性”对话框的“常规”选项卡中，用户可以设置跟踪名称、使用模板，以及启用跟踪停止时间和将跟踪存储到指定文件，如图12-3所示。

数据库事务并发控制

图12-3　跟踪属性　对话框的常规选项卡

还可以将跟踪保存到指定表。在“跟踪属性”对话框的“事件选择”选项卡中，用户可以设置跟踪的事件以及事件的列，如图12-4所示。

数据库事务并发控制

图12-4跟踪属性对话框的事件选择选项卡

设置完毕，在SQL Server Profiler中显示跟踪事件，如图12-5所示。

数据库事务并发控制

图　12-5 跟踪事件的显示

同时，在SSMS的对象资源管理器中，系统将新建一个新表，即设置的保存到表。打开该表，显示用户设置跟踪的事件以及事件的列。

为了和SQL Server兼容，还可以使用sys.dm_tran_locks动态管理视图来替代sp_lock系统存储过程。

12.2.4 死锁及处理

在事务锁的使用过程中，死锁是一个不可避免的现象。在下列两种情况下，可以发生死锁。

第一种情况是，当两个事务分别锁定了两个单独的对象，这时每一个事务都有要求在另外一个事务锁定的对象上获得一个锁，因此第一个事务都有必须等待另一个释放占有的锁，这时就发生了死锁，这种死锁是最典型的死锁形式。

死锁的第二种情况是，当在一个数据库中。有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么；由于不能控制处理的顺序，有可能发生死锁现象。

当发生了死锁现象时，除非某个外部进程断开死锁，否则死锁中的两个事务都将无期等待下去。SQL Server 2005的SQL Server Database Engine自动检测SQL Server中的死锁循环。数据库引擎选择一个会话作为死锁牺牲，然后终止当前事务（出现错误）来打断死锁。如果监视器检测到循环依赖关系，通过自动取消其中一个事务来结束死锁。处理时间长的事务具有较高的优先级，处理时间较短的事务具有较低的优先级。在发生冲突时，保留优先级高的事务，取消优先级的事务。

用户可以使用SQL Server Profiler确定死锁的原因。当SQLServer中某组资源的两个或多个线程或进程之间存在的依赖关系时，将会发生死锁。使用SQL Server Profiler，可以创建记录、重播和显示死锁事件的跟踪以进行分析。

若要跟踪死锁事件，请将Deadlock graph事件类添加到跟踪。可以通过下列任一方法进行提取：

在配置跟踪时，使用“事件提取设置”选项卡。请注意，只有在“事件选择“选项卡上选择了Deadlock graph事件，才会出现此选项卡。

也可以使用“文件“菜单中的”提取SQL Server事件“选项，或者通过鼠标右键击特定事件并选择”提取事件数据“，来提取并保存各个事件。

12.3 数据库优化

一个数据库系统的性能依赖于组成这些系统的数据库中物理设计结构的有效配置。这些物理设计结构包括索引、聚集索引、索引视图和分区等，其目的在于提高数据库的性能和可管理性。SQL Server 2005提供了一套综合的工具，用于优化物理数据库的设计，其中数据库引擎优化顾问，是分析一个或多个数据库上工作负荷（对要做出化的数据库招待的一组T-SQL语名句）的性能效果的工具。

本节主要介绍数据库引擎优化顾问的使用。

12.3.1 数据库引擎优化顾问概述

数据库引擎优化顾问是一种工具，用于分析在一个或多个数据库中运行的工作负荷的性能效果。工作负荷是对在优化的数据库招待的一组T-SQL语句。分析数据库的工作负荷效果后，数据库引擎优化顾问会提供在SQL Server 2005 数据库中添加、删除或修改物理设计结构的建议。这些物理性能结构包括聚集索引、非聚集索引、索引视图和分区。实现这些结构之后，数据库引擎优化顾问使查询处理器能够用最短的时间性执行工作负荷任务。