SQLSERVER的锁机制（三）概述（锁与事务隔离级别）

E、锁与事务隔离级别

事务隔离级别简单的说，香港服务器，就是当激活事务时，控制事务内因SQL语句产生的锁定需要保留多入，影响范围多大，以防止多人访问时，在事务内发生数据查询的错误。设置事务隔离级别将影响整条连接。

SQL Server 数据库引擎支持所有这些隔离级别：

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

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

· 可重复读

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

SQL Server 还支持使用行版本控制的两个事务隔离级别。一个是已提交读隔离的新实现，另一个是新事务隔离级别（快照）。

设置语句如下：

SET TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE

}

[ ; ]

（一）未提交读

未提交读是最低的事务隔离级别，允许读取其他事务已经修改但未提交的数据行。SQL SERVER 当此事务等级进行尝试读取数据时，不会放置共享锁，直接读取数据，所以忽略已存在的互斥锁。换句话说，即使该资源已经受到了独占锁的保护，当使用未提交读隔离级别时，此数据还是可以被读取，加快查询速度，但是会读取到别人未修改的数据，所以此种读取被称为脏读。此种隔离级别适合不在乎数据变更的查询场景。此隔离级别与SELECT 语句搭配 NOLOCK 所起到的效果相同

未提交读示例：

–1.–1.创建测试表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

–2新增记录

insert tbUnRead

select 1,’Tom’

union

select 2,’Jack’

–3开启事务，并进行更新

begin tran

update tbUnRead

set name=’Jack_upd’

where ID=2

SELECT @@TRANCOUNT

事务查询结果如下：

set Transaction isolation level read uncommitted

–6查询数据，查询到的数据是修改之后的数据。

select * from tbUnRead where ID=2

如下图：

（二）已提交读

控制的隔离下读取数据。读取操作不会获取正被读取的数据上的共享锁（S 锁），因此不会阻塞正在修改数据的事务。同时，香港服务器，由于减少了所获取的锁的数量，因此最大程度地降低了锁定资源的开销。使用行版本控制的已提交读隔离和快照隔离旨在提供副本数据的语句级或事务级读取一致性。

示例一：设置READ_COMMITTED_SNAPSHOT为OFF

–1.创建测试表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

–2新增记录

insert tbUnRead

select 1,’Tom’

union

select 2,’Jack’

–3开启事务，并进行更新

begin tran

update tbUnRead

set name=’Jack_upd’

where ID=2

SELECT @@TRANCOUNT

set Transaction isolation level read committed

–6查询数据，由于当前事务没有提交，所以无法查询数据

select * from tbUnRead where ID=2

6查询数据的结果 如下图：

示例二：设置READ_COMMITTED_SNAPSHOT为ON

use master

go

—创建测试数据库

create database read_committed_SNAPSHOT_Test

go

—激活数据行版本控制

alter database read_committed_SNAPSHOT_Test set read_committed_SNAPSHOT on

go

use read_committed_SNAPSHOT_Test

go

–1.创建测试表

create table tbReadLevel

(ID INT,

name nvarchar(20)

)

–2新增记录

insert tbReadLevel

select 1,’测试’

go

select ID,name as “修改前数据” from tbReadLevel

如下图：

go

–3开启事务，并进行更新

begin tran

update tbReadLevel

set name=’Jack_upd’

where ID=1

SELECT @@TRANCOUNT

–查询数据，查询到的数据是上一次提交的数据

select * from tbReadLevel where ID=1

5的查询结果如下图：

（三）可重复读