数据库事务与锁

数据库事务与锁第1页，共31页，2023年，2月20日，星期六学习目标掌握事务掌握锁第2页，共31页，2023年，2月20日，星期六一、事务事务是并发控制的基本单位。所谓事务，就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。第3页，共31页，2023年，2月20日，星期六1.1、事务的特性原子性一致性隔离性持久性第4页，共31页，2023年，2月20日，星期六1.2、事务的分类（1）自动提交事务每条单独的语句都是一个事务。（2）显式事务每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。（3）隐式事务在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。通过SetImplicit_TransactionsOn命令。第5页，共31页，2023年，2月20日，星期六1.2.1、自动提交事务SQLServer2005将一切都作为事务来处理，如果用户没有定义事务，它会自动定义用户或应用程序各种数据库操作的事务，这称之为自动提交事务，也是SQLServer2005默认的事务处理模式。如：createtableaaa(intnotnull)Insertintoaavalues(1)Insertintoaavalues(null)Insertintoaavalues(2)其结果是第一和第三条语句插入，第二条失败SQLServer2005在自动提交事务工作模式下，每条语句本身就是一个事务第6页，共31页，2023年，2月20日，星期六1.2.2、显式事务显示事务是执行的SQL语句之前增加begintransaction，事务提交采用committransaction，事务回滚采用rollbacktransaction，保留事务点采用savetransaction比如：begintransactioninsertintoaavalues(1)insertintoaavalues(null)IF@@ERROR<>0BEGINrollbacktransactionreturnENDinsertintoaavalues(2)committransaction因为第二条语句的出错，导致三条语句都不成功。

第7页，共31页，2023年，2月20日，星期六插入的知识点：TRY...CATCH对Transact-SQL实现类似于C#和C++语言中的异常处理的错误处理。Transact-SQL语句组可以包含在TRY块中。如果TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。改造如下：begintransactionBEGINTRYinsertintoaavalues(1) insertintoaavalues(null) insertintoaavalues(2) committransactionENDTRYBEGINCATCHprint'错误号为:'+cast(@@errorasvarchar(10))print'错误内容为:'+ERROR_MESSAGE()rollbacktransactionENDCATCH第8页，共31页，2023年，2月20日，星期六保留点的使用保留点是事务内部的一个标识，可以做事务的局部回滚。改造如下：begintransactioninsertintoaavalues(1)Savetransactionaainsertintoaavalues(3)insertintoaavalues(2)rollbacktransactionaaCommittransaction其结果是后两条得到了回滚，而第一条数据进行了提交。第9页，共31页，2023年，2月20日，星期六1.2.3、隐式事务要改变SQLServer2005的显式事务缺省设置为隐式，需要采用如下的语句：

setimplicit_transactionson这样就相当于每个delete、update、insert语句都开始一个begintransaction,那么也需要committransaction或者rollbacktransaction来进行结束事务。可以尝试deletefromaa后，再打开一个会话，去select*fromaa，发现了什么？结论是阻塞锁的发生，所以对于一个事务的结束是网络编程对数据库操作的必须注意的地方，否则会造成网络的瘫痪。采用setimplicit_transactionsoff关闭隐式事务。第10页，共31页，2023年，2月20日，星期六1.3设置事务隔离级别在SQL92标准中，事务隔离级别分为四种，分别为：ReadUncommitted、ReadCommitted、RepeatableRead、Serializable，其中ReadUncommitted与ReadCommitted为语句级别的，而RepeatableRead与Serializable是针对事务级别的。Sqlserver的隔离级别默认都是：ReadCommitted，可以使用语句SetTransactionIsolationLevelReadCommitted来设置第11页，共31页，2023年，2月20日，星期六ReadUncommitted一个会话可以读取其他事务未提交的更新结果。 如果这个事务最后以回滚结束，这时的读取结果就可能是错误的，所以多数的数据库应用都不会使用这种隔离级别。 注意:另外一个会话也要设置为ReadUncommitted.第12页，共31页，2023年，2月20日，星期六ReadCommitted只能读取其他事务已经提交的更新结果，否则，发生等待。 但是其他会话可以修改这个事务中被读取的记录，而不必等待事务结束，显然，在这种隔离级别下，一个事务中的两个相同的读取操作，其结果可能不同。比如：begintransactionselect*fromaawaitfordelay'00:00:05'select*fromaarollbacktransaction第13页，共31页，2023年，2月20日，星期六RepeatableRead在一个事务中，如果在两次相同条件的读取操作之间没有添加记录的操作，也没有其他更新操作导致在这个查询条件下记录数增多，则两次读取结果相同。换句话说，就是在一个事务中第一次读取的记录保证不会在这个事务期间发生改变。SQLServer是通过在整个事务期间给读取的记录加锁实现这种隔离级别的，这样，在这个事务结束前，其他会话不能修改事务中读取的记录，而只能等待事务结束，但是SQLServer不会阻碍其他会话向表中添加记录，也不阻碍其他会话修改其他记录。 如：

SETTRANSACTIONISOLATIONLEVELREPEATABLEREADGO

begintransactionselect*fromaawaitfordelay'00:00:15'select*fromaarollbacktransaction第14页，共31页，2023年，2月20日，星期六SerializableSqlserver:比RepeatableRead实现更加严格的控制，在整个事务中不允许插入数据。比如：SETTRANSACTIONISOLATIONLEVELSerializableGO

begintransactionselect*fromaawaitfordelay'00:00:15'select*fromaarollbacktransaction第15页，共31页，2023年，2月20日，星期六二、锁锁（LOCKING）是最常用的并发控制机构。是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。锁是事务对某个数据库中的资源（如表和记录）存取前，先向系统提出请求，封锁该资源，事务获得锁后，即取得对数据的控制权，在事务释放它的锁之前，其他事务不能更新此数据。当事务撤消后，释放被锁定的资源。

第16页，共31页，2023年，2月20日，星期六2.1、为什么要引入锁当多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:◆丢失更新A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果◆脏读A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致◆不可重复读A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致第17页，共31页，2023年，2月20日，星期六2.2、锁的粒度SQLServer具有多粒度锁定，允许一个事务锁定不同类型的的资源。为了使锁定的成本减至最少，SQLServer自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其它事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。SQLServer可以锁定行、页、扩展盘区、表、库等资源。第18页，共31页，2023年，2月20日，星期六插入的话题：关于存储结构SQLServer的存储结构为：文件组－文件－区(8*8=64k)－页(8K)，最小单位为页，页的类型有：第19页，共31页，2023年，2月20日，星期六按照粒度级别，SQLServer提供了如下类型的锁:整个数据库数据库级锁Database整个表表级锁Table一组数据页或者索引页页级锁Extent一个数据页或者索引页页级锁Page索引中的行行级锁Key表中的单个行行级锁RID描述级别资源第20页，共31页，2023年，2月20日，星期六选择多大的粒度，根据对数据的操作而定。如果是更新表中所有的行，则用表级锁;如果是更新表中的某一行，则用行级锁。

行级锁是一种最优锁，因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在SQLServer中，还支持锁升级(lockescalation)。

所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQLServer中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。

特别值得注意的是，在SQLServer中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。第21页，共31页，2023年，2月20日，星期六2.3、锁的模式锁模式描述共享（S）用于不更改或不更新数据（只读操作），如SELECT语句更新（U）用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。排它（X）用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新意向当MicrosoftSQLServer数据库引擎获取低级别的锁时，它还将在包含更低级别对象的对象上放置意向锁.例如：当锁定行或索引键范围时，数据库引擎将在包含行或键的页上放置意向锁。当锁定页时，数据库引擎将在包含页的更高级别的对象上放置意向锁。

意向锁的类型为：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX）架构在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改（Sch-M）和架构稳定（Sch-S）大容量更新（BU）向表中大容量复制数据第22页，共31页，2023年，2月20日，星期六2.4、查看锁1查询分析器上执行EXECSP_LOCK报告有关锁的信息2企业管理器上查看锁，见下图：第23页，共31页，2023年，2月20日，星期六2.5、如何避免死锁1使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；2设置锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；(SETLOCK_TIMEOUT3000)3所有的SP都要有错误处理（通过@error）4一般不要修改SQLSERVER事务的默认级别。(SET

TRANSACTION

ISOLATION

LEVEL)5在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。6尽量不要手工加锁第24页，共31页，2023年，2月20日，星期六2.6、排它锁的例子--

A事务先更新table1表，在更新时，对其他事务进行排他

begin

tran

update

table1

set

A='aa'

where

B='b2'；

waitfor

delay

'00:00:30'；

--等待30秒

commit

tran

--

A事务先更新table2表

begin

tran

select

*

from

table1

where

B='b2';

commit

tran

若同时执行上述两个事务，则select查询必须等待update执行完毕才能执行即要等待30秒

第25页，共31页，2023年，2月20日，星期六2.7、共享锁的例子--A事务先查询aa表，在查询时，加共享锁，防止其他事务对该表进行修改操作begintransactionselect*fromaawith(holdlock)whereb='2'waitfordelay'00:00:30';--等待秒committransaction--

B事务先查询table1表，后更改table1表begintransactionselect*fromaawhereb='2'updateaasetb='22'whereb='2'committransaction

注意：这个是发生在默认的隔离级别ReadCommitted下，如果发生在RepeatableRead或者Serializable下将会自动加共享锁。若并发执行上述两个事务，则B事务中的select查询可以执行,而update必须等待第一个事务释放共享锁后才能执行即要等待30秒第26页，共31页，2023年，2月20日，星期六2.8、死锁的例子--

A事务先更新table1表，然后延时30秒，再更新table2表；

begin

tran

update

table1

set

A='aa'

where

B='b2';

--这将在

Table1

中生成排他行锁，直到事务完成后才会释放该锁。

waitfor

delay

'00:00:30';

--进入延时

update

table2

set

D='d5'

where

E='e1'

;

commit

tran

--

B事务先更新table2表，然后延时10秒，再更新table1表；

begin

tran

update

table2

set

D='d5'

where

E='e1';

--这将在

Table2

中生成排他行锁，直到事务完成后才会释放该锁

waitfor

delay

'00:00:10'

--进入延时

update

table1

set

A='aa'

where

B='b2'

;

commit

tran

若并发执行上述两个事务，A,B两事务都要等待对方释放排他锁，这样便形成了死锁。第27页，共31页，2023年，2月20日，星期六2.9、手工加锁的命令语法：select*fromtablewhit(命令)1．HOLDLOCK:在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

2．NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

3．PAGLOCK：指定添加页锁（否则通常可能添加表锁）4．READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQLServer2000在此隔离级别上操作。5．READPAST:跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READCOMMITTED隔离性级别下事务操作中的SELECT语句操作6．READUNCOMMITTED：等同于NOLOCK。

7．REPEATABLEREAD：设置事务为可重复读隔离性级别。

8．ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。9．SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于HOLDLOCK。10．TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQLServer在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。11．TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。12．UPDLOCK：指定在读表中数据时设置更新锁（updatelock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改SELECT*FROMtableWITH(HOLDLOCK)其他事务可以读取表，但不能更新删除SELECT*FROMtableWITH(TABLOCKX)其他事务不能读取表,更新和删除第28页，共31页，2023年，2月20日，星期六课程小结本课程是非常重要并且有难度，事务是数据库运行的最小单位，可以说没有事务，则数据库根本无法实现数据的完整和一致性。我们在使用事务的同时，不知不觉用到了锁，锁是系统自动定义的一个对象，用以保证并发的顺利完成。对于锁我们不提倡手工控制，但是我们要知道锁的原理以及并发操作是如何通过使用锁来实现的。第29页，共31页，2023年，2