SQLServer事务与锁的基础概念总结

sql server设计有两种并发访问的控制机制：锁、行版本控制。

锁：主要用于多用户环境下保证数据库完整性和一致性．每个事务对所依赖的资源会请求不同类型的锁，它可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。当事务不再依赖锁定的资源时，锁将被释放。

行版本控制：当启用了基于行版本控制的隔离级别时，数据库引擎将维护修改的每一行的版本。应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据，而不是使用锁保护所有读取。通过使用行版本控制，读取操作阻止其他事务的可能性将大大降低。

事务：事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的

 

1.2    并发访问的不利影响

并发访问，如果没有并发访问控制机制，可能产生的不利影响有以下几种：

脏读，丢失更新，不可重复读，幻像度

1．脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

2．丢失更新:当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。

3．不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

4．幻象读：指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

 

1.3    锁的分类

按数据库引擎使用的资源锁模式。

锁模式	说明
共享 (S)	用于不更改或不更新数据的读取操作，如 SELECT 语句。
更新 (U)	用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排他 (X)	用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。
意向	用于建立锁的层次结构。意向锁的类型有：意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。
架构	在执行依赖于表架构的操作时使用。架构锁的类型有：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU)	在向表进行大容量数据复制且指定了 TABLOCK 提示时使用
键范围	当使用可序列化事务隔离级别时保护查询读取的行的范围。确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行

 

从程序员的角度，分为悲观锁和乐观锁

乐观锁：完全依靠数据库来管理锁的工作。

悲观锁：程序员自己管理数据或对象上的锁处理。

 

从粒度来分：

表类型：锁定整个表

行类型：锁定某个行

文件类型：锁定某个数据库文件

数据库类型：锁定整个数据库

页类型：锁定8K为单位的数据库页

 

1.4    事务

事务4大特性性：
1   原子性(Atomicity):事务是一个完整的操作。
2   一致性（Consistency）：当事务完成时，数据必须处于一致状态。
3   隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。
4   持久性(Durability):事务完成后，它对于系统的影响是永久性的。

 

T-SQL中管理事务的语句：
1 开始事务: begin transaction
2 提交事务：commit transaction
3 回滚事务: rollback transaction

实例：示例：张三转800元到李四帐户上[来源于网络，感觉比较好]。

 

代码 use stuDB
go
--创建帐户表bank--
if exists(select* from sysobjects where name='bank')
    drop table bank
create table bank
(
    customerName char(10),    --顾客姓名
    currentMoney money        --当前余额
)
go
/**//*--添加约束，帐户不能少于元--*/
alter table bank add constraint CK_currentMoney check(currentMoney>=1)
/**//*--插入测试数据--*/
insert into bank(customerName,currentMoney)
select '张三',1000 union
select '李四',1
select * from bank
go
/**//*--使用事务--*/
use stuDB
go
--恢复原来的数据
--update bank set currentMoney=currentMoney-1000 where customerName='李'
set nocount on    --不显示受影响的行数
print '查看转帐事务前的余额'
select * from bank
go
/**//*--开始事务--*/
begin transaction
declare @errorSum int    --定义变量，用于累计事务执行过程中的错误
/**//*--转帐--*/
update bank set currentMoney=currentMoney-800 where customerName='张三'
set @errorSum=@errorSum+@@error    --累计是否有错误
update bank set currentMoney=currentMoney+800 where customerName='李四'
set @errorSum=@errorSum+@@error --累计是否有错误
print '查看转帐事务过程中的余额'
select * from bank
/**//*--根据是否有错误，确定事务是提交还是回滚--*/
if @errorSum>0
    begin
        print '交易失败，回滚事务.'
        rollback transaction
    end
else
    begin
        print '交易成功，提交事务，写入硬盘，永久保存！'
        commit transaction
    end
go
print '查看转帐后的余额'
select * from bank
go

 

 

事务的隔离级别：

未提交读（READ UNCOMMITTED）：隔离事务的最低级别，只能保证不读取物理上损坏的数据）

已提交读（READ COMMITTED）（数据库引擎的默认级别）

可重复读（REPEATABLE READ）

快照（SNAPSHOT）

可序列化（SERIALIZABLE）：隔离事务的最高级别，事务之间完全隔离）

 

这几种隔离级别，对应上面三种并发访问可能产生的不利影响，分别有不同的效果，见下表：

 

隔离级别	脏读	不可重复读	幻象读	说明
未提交读(read uncommitted)	是	是	是	如果其他事务更新，不管是否提交，立即执行
提交读(read committed默认)	否	是	是	读取提交过的数据。如果其他事务更新没提交，则等待
可重复读(repeatable read)	否	否	是	查询期间，不允许其他事务update
快照（SNAPSHOT）	否	否	否
可串行读(serializable)	否	否	否	查询期间，不允许其他事务insert或delete

 

显示事务与隐性事务

事务分类:
1 显式事务:用begin transaction明确指定事务的开始。
2 隐性事务：打开隐性事务：set implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需要提交或回滚事务。
3 自动提交事务：SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。