第七章SQL数据库开发--TSQL—事务和锁
7.1 事务与日志体系简单介绍
每个 SQL Server 数据库都具有事务日志，用于记录所有事务以及每个事务对数据库所做的修改。 事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。
7.1.1 事务日志逻辑体系结构
SQL Server 事务日志按逻辑运行，就好像事务日志是一串日志记录一样。 每条日志记录由一个日志序列号 (LSN) 标识。 每条新日志记录均写入日志的逻辑结尾处，并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建时的串行序列存储。 每条日志记录都包含其所属事务的 ID。 对于每个事务，与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。
数据修改的日志记录或者记录所执行的逻辑操作，或者记录已修改数据的前像和后像。 前像是执行操作前的数据副本；后像是执行操作后的数据副本。
操作的恢复步骤取决于日志记录的类型：
记录逻辑操作
若要前滚逻辑操作，请再次执行该操作。
若要回滚逻辑操作，请执行相反的逻辑操作。
记录前像和后像
若要前滚操作，请应用后像。
若要回滚操作，请应用前像。
许多类型的操作都记录在事务日志中。 这些操作包括：
每个事务的开始和结束。
每次数据修改（插入、更新或删除）。 这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。
每次分配或释放区和页。
创建或删除表或索引。
回滚操作也记录在日志中。 每个事务都在事务日志中保留空间，以确保存在足够的日志空间来支持由显式回滚语句或遇到错误引起的回滚。 保留的空间量取决于在事务中执行的操作，但通常等于用于记录每个操作的空间量。 事务完成后将释放此保留空间。
日志文件中从必须存在以确保数据库范围内成功回滚的第一条日志记录到最后写入的日志记录之间的部分称为日志的活动部分，即“活动日志”。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。 此第一条日志记录的日志序列号 (LSN)，称为最小恢复 LSN (MinLSN)。
7.1.2 事务日志物理体系结构
数据库中的事务日志映射在一个或多个物理文件上。 从概念上讲，日志文件是一系列日志记录。 从物理上讲，日志记录序列被有效地存储在实现事务日志的物理文件集中。 每个数据库必须至少有一个日志文件。
7.1.3 事务通俗理解
事务是由一系列语句构成的逻辑工作单元，事务和存储过程和批处理，有一定程度上有相似之度，都是为了完成一定业务逻辑将多条语句封装起来。使他们与其他语句之前出现一条逻辑上的边界，并形成相对独立的工作单元。
当事务修改多个数据表时，如果在处理的过程中出现了某种错误，例如死机或者突然断电等情况，返回的结果全部没有被保存。对所有数据修改全部回滚，全部撤销。事务只有两种情况一 全部回滚，全部撤销。二事务全部提交。
7.1.4 事务的ACID（原子性，一致性，隔离性，持久性）
1 原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
2 一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
3 隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，
这样每个事务都感觉不到有其他事务在并发地执行。
4 持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
7.2 事务操作
显性事务是用户自定义的事务，需要通过BEGIN TRAN，COMMIT TRAN，ROLLBACK TRAN完成事务。在显性事务中如果开始BEGIN TRAN，没有结束，服务器断电，或者应用死，就直接回滚了。
自动提交模式是 SQL Server 数据库引擎的默认事务管理模式。 每个 Transact-SQL 语句在完成时，都被提交或回滚。 如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。
7.2.1 事务操作简介
BEGIN TRAN :设置事务启点
COMMIT TRAN:事务提交，使事务成为数据库中永存的，不可逆转部分。
ROLLBACK TRAN:回滚，不考虑所有更改，回滚操作。
SAVE TRAN:创建一个标识符，只允许做部分回滚。
7.2.2 开始事务 BEGIN TRAN 开始
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
transaction_name 事务名称
@tran_name_variable 事务名称变量
WITH MARK [ 'description' ] 指定在日志中标记事务。 description 是描述该标记的字符串。
BEGIN TRAN d2000;
select * FROM A70_USERS_ZH where user_num<15005000
delete FROM A70_USERS_ZH where user_num<15005000
select * FROM A70_USERS_ZH where user_num<15005000
rollback TRAN d2000;
DECLARE @TranName VARCHAR(20);
SELECT @TranName = 'MyTransaction';
BEGIN TRANSACTION @TranName;
USE AdventureWorks2012;
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT TRANSACTION @TranName;
GO
BEGIN TRANSACTION CandidateDelete
WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks2012;
GO
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION CandidateDelete;
GO
7.2.3 SAVE TRAN 保存事务
主要作用创建事务保存点 事务是顺序执行。
SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]
BEGIN TRAN
SELECT * FROM A70 where user_num<16001500;
DELETE from A70 where user_num<16001500;
SAVE TRAN syj15
SELECT * FROM A70 where user_num>16001500 and user_num<16002000;
delete FROM A70 where user_num>16001500 and user_num<16002000;
SAVE TRAN syj20
SELECT * FROM A70 where user_num>16002000 and user_num<16005000;
delete FROM A70 where user_num>16002000 and user_num<16005000;
ROLLBACK TRAN syj15 顺序执行，回滚到SYJ15，连SYJ20也回滚了。
7.3 事务的并发与隔离级别
7.3.1 事务的并发产生影响
用户可以并发访问数据。 如果数据存储系统没有并发控制，则用户可能会看到以下负面影响：
丢失更新
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。 每个事务都不知道其他事务的存在。最后的更新将覆盖由其他事务所做的更新，这将导致数据丢失。
例如，两个编辑人员制作了同一文档的电子副本。 每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。 最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。 如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
未提交的依赖关系（脏读）
当第二个事务选择其他事务正在更新的行时，会发生未提交的依赖关系问题。 第二个事务正在读取的数据还没有提交并且可能由更新此行的事务所更改。
例如，一个编辑人员正在更改电子文档。 在更改过程中，另一个编辑人员复制了该文档（该副本包含到目前为止所做的全部更改）并将其分发给预期的用户。 此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应视为从未存在过。 如果在第一个编辑人员保存最终更改并提交事务之前，任何人都不能读取更改的文档，则可以避免此问题。
不一致的分析（不可重复读）
当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。 不一致的分析与未提交的依赖关系类似，因为其他事务也是正在更改第二个事务正在读取的数据。 但是，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。 此外，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都被其他事务更改，因此我们称之为“不可重复读”。
例如，编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。 当编辑人员第二次读取文档时，文档已更改。 原始读取不可重复。 如果在编辑人员完成最后一次读取文档之前，作者不能更改文档，则可以避免此问题。
- 虚拟读取(幻读)
执行两个相同的查询但第二个查询返回的行集合是不同的，此时就会发生虚拟读取。 下面的示例显示了何时会出现幻读。 假定下面两个事务同时执行。 由于第二个事务中的SELECT 语句更改了两个事务所用的数据，所以第一个事务中的两个INSERT 语句可能返回不同的结果。
7.3.2 不可重复读与幻读区别
从总的结果来看, 似乎两者都表现为两次读取的结果不一致.
但如果你从控制的角度来看, 两者的区别就比较大
对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录
-----------------------------------------------------------
我这么理解是否可以？
避免不可重复读需要锁行就行
避免幻影读则需要锁表
####不可重复读和幻读的区别####
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。
如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复 读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会 发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。
7.3.3 事务的隔离级别
| SQL数据隔离级别 | 解释 | 更新丢失 | 脏读 | 不可重复读 | 幻读 | |
| 未提交读（Read Uncommitted） | 如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现 | 避免 |
|
|
| |
| 已提交度（Read Committed）SQL数据库默认 | 允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。（说白了就是提交数据可以读） | 避免 | 避免 |
|
| |
| 可重复读（Repeatable Read） | 禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。 | 避免 | 避免 | 避免 |
| |
| 可序列化（Serializable） | 提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。 | 避免 | 避免 | 避免 | 避免 | |
常见数据库隔离级别
| 数据库 | 默认级别 |
| MySQL | 可重复读（Repeatable Read） |
| Oracle | 读提交（Read Committed） |
| SQLServer | 读提交（Read Committed） |
| DB2 | 读提交（Read Committed） |
| PostgreSQL | 读提交（Read Committed）
|
7.4 锁和并发
并发是任何数据库系统都会遇到的一个主要问题，对于每个用户来说，交互操作的本质可能是不同的（更新，删除，读取，插入）。
锁是一种防止在某对象上执行动作的一个进程与已在该对象上执行的其他进程相冲突的机制。
锁主要解决4类主要问题：丢失更新，脏读（dirty read）、不可重复读（No-Repeatable Read）、幻影（phantom）。
7.4.1 锁的模式
| 锁 | 史英杰理解 | 描述 | 作用 |
| 共享锁（S ） | 最基本的锁，能和其他锁兼容， | 用于不更改或不更新数据的读取操作 | 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁） |
| 更新锁(U) | 为了能UDPDATE,第一个阶段满足where的行，查询有一个更新锁。第二个阶段执行UPDATE升级成排他锁。 | 用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 | 如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。 共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务试图获取排他锁（X 锁）以进行更新。 由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。 |
| 排他锁（X 锁） | 排他锁和其他锁不兼容， | 可以防止并发事务对资源进行访问。 使用排他锁（X 锁）时，任何其他事务都无法访问与修改数据； | 数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此，数据修改语句通常请求共享锁和排他锁。 例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。 |
| 意向锁（Intention Lock） | 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。 | SQL Server 数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。 | 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。 |
7.4.2 锁的兼容性
| 请求模式 | IS | S | U | IX | SIX | X |
| 意向共享 (IS) | 是 | 是 | 是 | 是 | 是 | 否 |
| 共享 (S) | 是 | 是 | 是 | 否 | 否 | 否 |
| 更新 (U) | 是 | 是 | 否 | 否 | 否 | 否 |
| 意向排他 (IX) | 是 | 否 | 否 | 是 | 否 | 否 |
| 意向排他共享 (SIX) | 是 | 否 | 否 | 否 | 否 | 否 |
| 排他 (X) | 否 | 否 | 否 | 否 | 否 | 否 |
7.4.3 锁的粒度
| SQL Server 数据库引擎可以锁定的资源 | |
| 资源 | 描述 |
| RID 行锁 | 用于锁定堆中的单个行的行标识符。用于单独锁定表中的一行 |
| KEY 键锁 | 索引中用于保护可序列化事务中的键范围的行锁。 |
| PAGE页锁 | 数据库中的 8 KB 页，例如数据页或索引页。 |
| EXTENT扩展盘锁 | 一组连续的八页，例如数据页或索引页。锁定相邻的8个数据页或者索引页 |
| TABLE表锁 | 包括所有数据和索引的整个表。 |
| DATABASE数据库锁 | 整个数据库。 |
7.5 设置隔离级别
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED 未提交读
| READ COMMITTED 已提交度默认
| REPEATABLE READ 可重复读
| SNAPSHOT 快照
| SERIALIZABLE 可序列化
}
查看数据库隔离级别
dbcc useroptions
设置数据库隔离级别（只是会话级别）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
当前会话
dbcc useroptions
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
dbcc useroptions
begin tran
update A70_USERS_ZH set user_name='加油' where user_num<'15002000'
另一个会话
SELECT * FROM A70_USERS_ZH where user_num<'15002000'
扫一扫
1211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
