《数据库系统概论》课程学习(11)——第十一章 并发控制

事务的交叉并发执行

• 单处理机系统中事务并发执行是事务的轮流交叉运行，并没有真正地并行运行，但能够减少处理机的空闲时间，提高系统的效率；
• 多处理机系统中，每个处理机可以运行一个事务，多个处理机可以同时运行多个事务，实现多个事务真正的并行运行；
• 多处理机系统中，当一个处理机被分配执行多个事务时，同样存在着事务的交叉并发运行问题。

问题：事务的交叉并发执行会带来什么问题？

• 1、会产生多个事务同时存取同一数据的情况；
• 2、可能会破坏事务的特性要求和数据库的一致性。

并发控制机制的任务：

• 对并发事务进行正确调度；
• 保证事务的原子性、一致性和隔离性；
• 保证数据库的一致性。

并发事务带来的问题示例

*

*示例

活锁与死锁

解决死锁问题的办法

1、预防性方法

• 一次性*法：要求每个事务必须一次将所有数据全部加锁成功，否则释放锁等待；
• 顺序*法：预先对数据对象规定一个*顺序，所有事务必须按顺序实行*。

2、治疗性方法

• 超时法：认为某事务等待时间超过规定时间就发生了死锁。
• 等待图法：构造事务相互等待的有向图。定期检测，若图中有回路，则发生死锁。此时，选择某个代价较小的事务进行回滚，释放*。

问题：你认为哪种方法好？

并发调度的可串行性

问题：并发事务不同的调度可能会产生不同的结果，那什么样的调度是正确的调度呢？

答：串行调度是正确的。

可串行化调度：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同。

• 1、可串行化调度是并发事务正确调度的准则；
• 2、一个给定的并发调度，当且仅当它是可串行化的才认为是正确的调度。

问题：你有什么办法判断一个调度是可串行化的？

并发调度的可串行性示例

并发调度可串行性的判定

两段锁协议

*粒度

SQL Server 并发控制

• 锁定：每个事务对所依赖的资源（如行、页或表）请求不同类型的锁，当事务不再依赖锁定的资源时，它将释放锁；
• 应用程序可以选择事务隔离级别，以实现并发控制；
• 当事务修改某个数据块时，它将持有保护所做修改的锁直到事务结束。事务持有锁的时间长度，取决于事务隔离级别设置；
• 一个事务持有的所有锁都在事务完成（无论是提交还是回滚）时释放；
• 应用程序一般不直接请求锁。锁由DBMS内部管理；
• 锁粒度：RID(行)、PAGE(页)、EXTENT(区)、HOBT(B树)、TABLE(表)、FILE(文件)、METADATA(元数据)、DATABASE(数据库)。

SQL Server 共享锁与排他锁

SQL Server 更新锁

SQL Server 意向锁

意向锁(I锁)：用于建立锁的层次结构，旨在提高DBMS在较高的粒度级别检测锁冲突的效率。该种锁一般DBMS自行处理。

例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁。意向锁可以提高性能，因为DBMS仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向共享(IS锁)：某事务在某资源上放置IS锁，表明该事务的意向是读取层次结构中的部分（而不是全部）底层资源。

意向排它(IX锁)：某事务在某资源上放置IX锁，表明该事务的意向是修改层次结构中的部分（而不是全部）底层资源。

意向排它共享(SIX锁)：某事务在某资源上放置SIX锁，表明该事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。

SQL Server 意向锁示例

SQL Server 锁相容矩阵

SQL Server 键范围锁定

在使用可序列化事务隔离级别时，对于SQL语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。 键范围锁放置在索引上，指定开始键值和结束键值。 此锁将阻止任何要插入、更新或删除任何带有该范围内的键值的行的尝试，因为这些操作会首先获取索引上的锁。

例如，可序列化事务可能发出了一个SELECT语句，以读取其键值介于'AAA'与'CZZ'之间的所有行。从'AAA'到'CZZ'范围内的键值上的键范围锁可阻止其他事务插入带有该范围内的键值（例如 'ADG'、'BBD' 或 'CAL'）的行。

SQL Server 锁几点说明

• SQL Server使用动态锁定策略确定最经济的锁。执行查询时，数据库引擎会根据查询的特点自动决定最合适的锁；
• 可以查询 sys.dm_tran_locks 动态管理视图获得有关数据库引擎实例中锁当前状态的信息；
• SQL Server实例选择某事务作为死锁牺牲品后，将终止当前批处理，回滚事务并将错误号1205消息返回应用程序。

SQL Server 自定义锁超时

• LOCK_TIMEOUT设置允许应用程序设置语句等待阻塞资源的最长时间；
• 如果某个语句等待的时间超过LOCK_TIMEOUT的设置时间，则被阻塞的语句自动取消，并会有错误消息1222返回给应用程序；
• 但是，SQL Server不会回滚或取消任何包含语句的事务。因此，应用程序必须具有可以捕获错误消息1222的错误处理程序；
• 如果应用程序不能捕获错误，则会在不知道事务中已有个别语句被取消的情况下继续运行，由于事务中后面的语句可能依赖于从未执行过的语句，因此会出现错误；
• 实现捕获错误消息1222的错误处理程序后，应用程序可以处理超时情况，并采取补救措施，例如：自动重新提交被阻塞的语句或回滚整个事务；
• SELECT @@lock_timeout 查看当前超时设置；
• SET LOCK_TIMEOUT timeout_period 超时设置。参数timeout_period为毫秒数。值-1（默认值）表示没有超时期限，即无限期等待。值为0时表示根本不等待，一遇到锁就返回错误。当锁等待超过超时值时，将返回错误。

SQL Server 事务隔离级别

SQL Server 事务隔离级别选项

1、READ UNCOMMITTED    

指定语句可以读取已由其他事务修改但尚未提交的行。

该级别运行的事务：

• 不会发出共享锁来防止其他事务修改当前事务读取的数据；
• 也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行；
• 设置此选项之后，可以读取未提交的修改，这种读取称为脏读；
• 该选项的作用与在事务内所有SELECT语句中的所有表上设置NOLOCK相同；
• 这是隔离级别中限制最少的级别。

2、READ COMMITTED    

指定语句不能读取已由其他事务修改但尚未提交的数据。

该级别运行的事务：

• 可以避免脏读；
• 其他事务可在当前事务读语句之间更改数据，从而产生不可重复读取；
• 该选项是SQL Server的默认设置。

3、REPEATABLE READ      

指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。

该级别运行的事务：

• 对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止；
• 其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索到新行，从而产生幻读；
• 并发级别低于默认的READ COMMITTED隔离级别；
• 此选项只在必要时使用。

4、SNAPSHOT

指定事务中任何语句读取的数据都将是在事务开始时所存在的数据的事务上一致的版本。

该级别运行的事务：

• 事务只能识别在其开始之前提交的数据修改；
• 在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照；
• 除非正在恢复数据库，否则SNAPSHOT事务不会在读取数据时请求锁；
• 读取数据的SNAPSHOT事务不会阻止其他事务写入数据。写入数据的事务也不会阻止SNAPSHOT事务读取数据；
• 在数据库恢复的回滚阶段，如果尝试读取由其他正在回滚的事务锁定的数据，则SNAPSHOT事务将请求一个锁。在事务完成回滚之前，SNAPSHOT事务会一直被阻塞；
• 必须将ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON，才能开始一个使用SNAPSHOT隔离级别的事务；
• 在该隔离级别下运行的事务可查看由该事务所做的更改。例如，若事务对表执行UPDATE，然后对该表发出SELECT，则可检索到修改后的数据。

5、SERIALIZABLE

指定下列内容：

• 语句不能读取已由其他事务修改但尚未提交的数据。
• 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。
• 在当前事务完成之前，其他事务不能使用当前事务中任何语句读取的键值插入新行。

该级别运行的事务：

• 范围锁处于与事务中执行的每个语句搜索条件相匹配的键值范围之内；
• 可以阻止其他事务更新或插入任何行，从而限定当前事务所执行的任何语句。这意味着如果再次执行事务中的任何语句，则这些语句便会读取同一组行；
• 在事务完成之前将一直保持范围锁；
• 这是限制最多的隔离级别，所以应只在必要时才使用该选项；
• 该选项的作用与在事务内所有SELECT语句在表上设置HOLDLOCK相同。

SQL Server 事务隔离级别的两点说明

1、当事务进行时，可以随时将事务从一个隔离级别更改为另一个隔离级别；更改隔离级别后，便会根据新级别的规则对更改后读取的资源执行保护；而更改前读取的资源将继续根据先前级别的规则进行保护。

例如，一个事务由REPEATABLE READ更改为SERIALIZABLE。由更改前发出的SELECT语句读取的行将继续受到行级、页级或表级共享锁的保护。这些锁会继续保持，直至事务结束。由SELECT语句在更改后读取的行将受到范围锁的保护。

2、如果在存储过程、触发器、用户定义函数中设置了新的事务隔离级别，则当对象返回控制时，隔离级别会重设为在调用对象时有效的级别。

SQL Server 表级锁提示

锁提示：当需要对对象所获得锁类型进行更精细控制时，可以使用表级锁提示。这些锁提示覆盖会话的当前事务隔离级别。

• DBMS几乎总是选择正确的锁级别。建议仅在必要时才使用表级锁提示更改默认的锁行为。
• 只有在万般无奈的情况下才由经验丰富的开发人员和数据库管理员使用。
• 适用范围：select、insert、update、delete语句。

例如：

select * from student with(rowlock) where sage>18
select * from student with(rowlock, holdlock) where sage>18

SQL Server 常用锁提示选项

• ROWLOCK：指定在锁定页或表时，采用行锁。
• SERIALIZABLE等同于HOLDLOCK：保持共享锁直到事务完成。执行扫描时所用的语义与SERIALIZABLE隔离级别运行的事务语义相同。
• TABLOCK：指定表采用的共享锁一直保持到语句结束。
• TABLOCKX：指定表采用排他锁一直保持到语句结束。
• UPDLOCK：指定采用更新锁并保持到事务完成。
• XLOCK：指定采用排他锁并保持到事务完成。如果同时指定了ROWLOCK, PAGLOCK或TABLOCK，则排他锁将应用于相应的粒度级别。
• READUNCOMMITTED：指定允许脏读。不发布共享锁来阻止其他事务修改当前事务在读的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作。
• READPAST：指定不读取由其他事务锁定的行和页。数据库引擎跳过这些行或页，而不是在释放锁之前阻塞当前事务。仅可在运行于READ COMMITTED或 REPEATABLE READ隔离级别的事务中指定READPAST。
• NOLOCK：等同于READUNCOMMITTED隔离级别。
• NOWAIT：指示SQL Server在遇到表的锁时，立即返回一个消息。NOWAIT等同于将特定表的SET LOCK_TIMEOUT值指定为0。
• PAGLOCK：指定采用页锁。
• READCOMMITTED：指定遵循有关READ COMMITTED隔离级别的规则。