首页 > SQL SERVER的锁机制（三）——概述（锁与事务隔离级别）

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

五、锁与事务隔离级别

事务隔离级别简单的说，就是当激活事务时，控制事务内因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

---4查询事务数量(由于没有回滚或提交事务)

SELECT @@TRANCOUNT

事务查询结果如下：

 

--5打开另一条连接，设置事务隔离级别为(未提交读)

set Transaction isolation level read uncommitted

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

select * from tbUnRead where ID=2

如下图：

 

 

（二）已提交读

已提交读是SQL SERVER 默认的事务隔离级别。当事务正在读取数据时，SQL SERVER 会放置共享锁以防止其他事务修改数据，当数据读取完成之后，会自动释放共享锁，其他事务可以进行数据修改。因为共享锁会同时封锁封锁语句执行，所以在事务完成数据修改之前，是无法读取该事务正在修改的数据行。因此此隔离级别可以防止脏读。

 

在SQL SERVER 2005以上版本中，如果设置READ_COMMITTED_SNAPSHOT为ON，则已提交读的事务全使用数据行版本控制的隔离下读取数据。读取操作不会获取正被读取的数据上的共享锁（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

---4查询事务数量(由于没有回滚或提交事务)

SELECT @@TRANCOUNT

--5打开另一条连接，设置事务隔离级别为(已提交读)

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

---4查询事务数量(由于没有回滚或提交事务)

SELECT @@TRANCOUNT

 

--5打开另一条连接，设置事务隔离级别为(已提交读)

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

select * from tbReadLevel where ID=1

 5的查询结果如下图：

 

（三）可重复读

可重复读事务隔离级别在事务过程中，所有的共享锁均保留到事务结束，而不是读取结束就释放，这与已提交读的行为截然不同，虽然在事务过程中，重复查询相同记录时不受其他事务的影响，但可能由于锁定数据过久，而导致其他人无法处理数据，影响并发率，更严重的可能提高发生死锁的机率。

  总之，如果使用可重复读隔离级别读取数据，数据读出之后，其他事务只能对此范围中的数据进行读取或新增，但不可以进行修改，直到读取事务完成。因此，使用此隔离级别需要谨慎小心，根据实际情况进行设置。

 

示例：

 

--1.创建测试表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

--2新增记录

insert tbUnRead 

select 1,'Tom'

union

select 2,'Jack'

 

--3设置事务隔离级别为(可重复读)

set Transaction isolation level REPEATABLE READ

--4开启事务，并进行更新

begin tran

 

--5查询数据

select * from tbUnRead where ID=2

---6查询事务数量(没有回滚或提交事务)

SELECT @@TRANCOUNT

5与6的执行结果如下图

 

---7开启另一条连接，查询数据与修改数据 

---事务虽然没有完成，但可以查询到之前的数据

select * from tbUnRead where ID=2

Go

---8，修改数据，由于事务没有完成，所以无法进行修改

update tbUnRead

set name='Jack_upd'

where ID=2

go

--7、8的执行结果如下，可以查询数据，但无法更新数据，如下图。

 

 

 

（四）快照

快照隔离级别是SQL SERVER 2005之后版本新增的隔离级别，开启之后，允许事务过程中读取操作不受异动影响，事务中任一语句所读取的数据，均予事务激活时，就已经完成提交，符合事务一致性的数据行版本。所以只能查核事务激活之前已经完成提交的数据，也就是说可以查询已经完成提交的数据行快照集，但看不见已激活的事务正在进行修改的数据行。当使用快照隔离级别读取数据时不会要求对数据进行锁定，如果所读取的记录正在被某事务进行修改，它也会读取此记录之前已经提交的数据。故当某记录被事务进行修改时，SQL SERVER的TEMPDB数据库会存储最近提交的数据行，以供快照隔离级别的事务读取数据时使用。将Allow_SNAPSHOT_isolation设为ON，事务就会设置快照隔离级别。

 

use master

go

---创建测试数据库(快照)

create database SNAPSHOT_Test

go

---激活数据行版本控制

alter database SNAPSHOT_Test  set Allow_SNAPSHOT_isolation on

go

 

use SNAPSHOT_Test

go

 

--1.创建测试表

create table tbReadLevel

(ID INT,

name nvarchar(20)

)

 

--2新增记录

insert tbReadLevel

select 1,'测试'

union

select 2,'快照测试'

go

select ID,name as "修改前数据"

from tbReadLevel

go

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

begin tran

update tbReadLevel

set name='Jack_upd_快照'

where ID=1

---4查询事务数量(没有回滚或提交事务)

SELECT @@TRANCOUNT

--2、4的执行结果，如下图。

 

--5打开另一条连接，设置事务隔离级别为(快照)

set Transaction isolation level SNAPSHOT

--6查询数据，查询的数据是上一次提交的数据

select * from tbReadLevel where ID=1

 

 

（五）可序列化

可序列化是事务隔离级别中最高的级别，为最严谨的隔离级别，因为它会锁定整个范围的索引键，使事务与其他事务完全隔离。在现行事务完成之前，其他事务不能插入新的数据行，其索引键值存在于现行事务所读取的索引键范围之中。此隔离级别与Select 搭配holdlock效果一样。

示例：

--1.创建测试表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

--2新增记录

insert tbUnRead 

select 1,'Tom'

union

select 2,'Jack'

--3设置事务隔离级别为(可序列化)

 

set Transaction isolation level SERIALIZABLE

--5开启事务，并进行更新

begin tran

select * from tbUnRead where ID=2

---6查询事务数量(没有回滚或提交事务)

SELECT @@TRANCOUNT

5、6执行结果如下图。

---7，开启另一条连接，查询数据，可以查询到之前的数据

select * from tbUnRead where ID=2

---8，修改数据，无法修改数据

update tbUnRead

set name='Jack_upd'

where ID=2

--新增数据，无法插入数据

insert tbUnRead 

select 3,'May'

转载于:https://www.cnblogs.com/littlewrong/p/9025759.html

更多相关：

  • 事物的级别_浅谈MySQL并发控制：隔离级别、锁与MVCC

    推荐阅读：恕我直言！收藏这个MySQL文档，你其余的MySQL学习资料都可以扔了前言如果数据库中的事务都是串行执行的，这种方式可以保障事务的执行不会出现异常和错误，但带来的问题是串行执行会带来性能瓶颈；而事务并发执行，如果不加以控制则会引发诸多问题，包括死锁、更新丢失等等。这就需要我们在性能和安全之间做出合理的权衡，使用适当的并发控制...

  • DB-MySQL：MySQL 事务

    ylbtech-DB-MySQL：MySQL 事务  1.返回顶部 1、 MySQL 事务 MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！ 在 MySQL 中只有使用了...

  • mysql事务处理用法与实例详解

    MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关1.MyISAM：不支持事务，用于只读程序提高性能 2.InnoDB：支持ACID事务、行级锁、并发 3.Berkeley DB：支持事务 一个事务是一个连续的一组数据库操作，就好像它是一个单一的工作单元进行。换言之，永远不会是完整的事务，除非该组内的每个...

  • Spring之事务管理配置

    1. 基于注解的事务配置 　　1. 在需要添加事务的方法上加上@Transactional注解 　　2. Spring的配置文件中配置事务管理器 　　　　 1 2

    php读取西门子plc_基于Socket访问西门子PLC系列教程（二）

    本文是西门子开放式TCP通信的第2篇，上一篇我们讲了使用西门子1200PLC作为TCP服务器的程序编写，可以点击下方链接阅读：【公众号dotNet工控上位机：thinger_swj】基于Socket访问西门子PLC系列教程（一）在完成上述步骤后，接下来就是编写上位机软件与PLC之间进行通信。上位机UI界面设计如下图所示：从上图可以看出...

  • python数据框 命名_Python-根据列值将数据框分为多个数据框，并用这些值命名 - python...

    我有一个大型数据集，列出了在全国不同地区销售的竞争对手产品。我希望通过使用这些新数据帧名称中的列值的迭代过程，根据区域将该数据帧分成几个其他区域，以便我可以分别处理每个数据帧-例如根据价格对每个地区的信息进行排序，以了解每个地区的市场情况。我给出了以下数据的简化版本:Competitor Region ProductA Product...

  • 有java基础的人学python_准备自学Python ，会java，有什么建议吗？

    作为一名IT从业者，我来回答一下这个问题。首先，对于具有Java编程基础的人来说，学习Python的初期并不会遇到太大的障碍，但是要结合自己的发展规划来制定学习规划，尤其要重视学习方向的选择。Java与Python都是比较典型的全场景编程语言，相比于Java语言来说，当前Python语言在大数据、人工智能领域的应用更为广泛一些，而且大...

  • v-model无法对返回的数据进行填写_学会数据分析思维，学会透过事物看本质

    这段时间通过学习相关的知识，最大的变化就是看待事物更加喜欢去了解事物后面的本质，碰到问题后解决问题思路也发生了改变。举个具体的例子，我在学习数据分析，将来会考虑从事这方面的工作，需要掌握的相关专业知识这个问题暂且按下不表，那哪些具体的问题是我需要了解的呢，以下简单罗列：1、了解数据分析师这个岗位在各个地区的需求情况？2、数据分析师的薪...

  • c++控制台应用每一列数据如何对齐_Python数据分析第五节 pandas入门

    这一节将开始学习python的一个核心数据分析支持库---pandas，它是python数据分析实践与实战的必备高级工具。对于使用 Python 进行数据分析来说，pandas 几乎是无人不知，无人不晓的。今天，我们就来认识认识数据分析界鼎鼎大名的 pandas。目录一. pandas主要数据结构 SeriesDataFrame二...

©2024 11GX.COM