SQL Server 的事务和锁（举例）——在其他博客上做些笔误修改

最新推荐文章于 2023-06-06 22:32:15 发布
最新推荐文章于 2023-06-06 22:32:15 发布
阅读量472 收藏
点赞数
分类专栏： 笔记 文章标签： sql server 事务


SQL Server 的事务和锁（举例）

原料：创建Test表

CREATE TABLE [dbo].[Test](

    [id] [int] NULL,

    [name] [nchar](10) COLLATEChinese_PRC_CI_AS NULL,

    [info] [nchar](10) COLLATEChinese_PRC_CI_AS NULL

) ON [PRIMARY]

插入一条记录

INSERT INTO Test VALUES(2,'kk',null)

查询显示有此记录

SELECT * FROM Test  Where name='kk'

id  name   info

1   kk      NULL

 

【测试一：丢失更新】

派生到我的代码片--事务1  

begin tran 

select * from dbo.Test(nolock) where name = 'kk' 

waitfor delay '00:00:05' 

update T set info = 'A更改' from Test T(nolock) where name = 'kk'

commit tran 

--事务2  

begin tran 

    select *from dbo.Test(nolock) where name = 'kk' 

    waitfor delay '00:00:05' 

    update T setinfo = 'B更改' from Test T(nolock) where name = 'kk'  

commit tran  --事务1

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'A更改' from Test T(nolock)where name = 'kk'

committran

 

--事务2

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'B更改' from Test T(nolock)where name = 'kk'

committran

 

事务1和事务2的运行结果：

id  name   info

1   kk      NULL

 

事务结束最后结果：

SELECT * FROM Test  Wherename='kk'

id  name   info

1   kk      B更改

 

说明：【事务1】更改了数据，但是没有被读到。最终【事务2】的更改覆盖了【事务1】的更改值

 

解决：允许对事务操作加锁，即取消(nolock)，nolock级别最低。

 

当前使用：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

（READ UNCOMMITTED：即未提交的数据只能读取，不能更改）

 

 

【测试二：脏读】

派生到我的代码片--事务1  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begin tran 

update T set info = 'A更改' from Test T where name = 'kk'

waitfor delay '00:00:05' 

rollback tran 

--事务2  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begin tran 

    select *from dbo.Test where name = 'kk' 

commit tran  --事务1

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'A更改' from Test T(nolock)where name = 'kk'

committran

 

--事务2

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'B更改' from Test T(nolock)where name = 'kk'

committran

 

 

--事务1

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begintran

  update T set info = 'A更改' from Test T where name= 'kk'

  waitfor delay '00:00:05'

rollbacktran --回滚

 

--事务2

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begintran

  select * from dbo.Test where name = 'kk'

committran

 

事务2的运行结果：

id  name   info

1   kk      A更改

 

事务结束最后结果：

SELECT * FROM Test  Wherename='kk'

id  name   info

1   kk      NULL

 

说明：【事务1】更改了数据，【事务2】读取了表中更改的值，【事务1】回滚。最终的表是没有被更改的。

 

解决一：提高隔离级别

SET TRANSACTION ISOLATIONLEVEL READCOMMITTED

ALTER DATABASEDBNameSETREAD_COMMITTED_SNAPSHOTOFF（默认OFF）

此时【事务1】等待事务【事务2】完成后才读取表中的值。

（READ COMMITTED：更改的数据只能是提交后才能读取）

 

解决二：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

ALTER DATABASEDBNameSETREAD_COMMITTED_SNAPSHOTON（默认OFF）

此时【事务2】读到的是表中之前的快照值（不必等待【事务1】完成）。

此时相当于：

SET TRANSACTION ISOLATIONLEVEL READCOMMITTED

select * from dbo.Test WITH(READPAST) where name='kk'

 

解决三：

事务2用表提示READ COMMITTED LOCK把语句级别改为READ COMMITTED

--事务2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begin tran

    select *from dbo.Test WITH(READCOMMITTEDLOCK)where name='kk'

commit tran

 

如果将 READ_COMMITTED_SNAPSHOT设置为OFF（默认设置），设置时关闭所有对此数据库的连接，

数据库引擎会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。

共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。

 

 

 

【测试三：不可重读】

 

--事务1  

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

    begin tran 

    select *from dbo.Test where name = 'kk' 

    waitfor delay '00:00:05' 

    select *from dbo.Test where name = 'kk' 

commit tran  

--事务2  

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

    begin tran 

    update T setinfo = 'B更改' from Test T where name = 'kk' 

commit tran 

--事务1

SETTRANSACTION ISOLATION LEVEL READ COMMITTED

begintran

  select * from dbo.Test where name = 'kk'

  waitfor delay '00:00:05'

  select * from dbo.Test where name = 'kk'

committran

 

--事务2

SETTRANSACTION ISOLATION LEVEL READ COMMITTED

begintran

  update T set info = 'B更改' from Test T where name= 'kk'

committran

 

事务2的运行结果：

id  name   info

1   kk      NULL

 

id  name   info

1   kk      B更改

 

事务结束最后结果：

SELECT * FROM Test  Wherename='kk'

id  name   info

1   kk      B更改

 

说明：【事务1】读取了数据，【事务2】更改数据，【事务1】再读取数据。【事务1】两次读取都不一样！

 

解决一：提高隔离级别

SET TRANSACTION ISOLATIONLEVEL REPEATABLE READ

（REPEATABLE READ：允许重复读取数据，但读取的都是第一次读取的副本）

 

解决二：更改为快照级别

ALTER DATABASE DBName SETALLOW_SNAPSHOT_ISOLATIONON

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

 

此时【事务1】的运行结果2次都一样，读到的都是未更改前的值：

id  name   info

1   kk      NULL

 

 

 

【测试四：幻读】

--事务1   

SET TRANSACTION ISOLATION LEVEL REPEATABLEREAD

begin tran 

    select *from dbo.Test where name = 'kk' 

    waitfor delay '00:00:05'  

    select *from dbo.Test where name = 'kk' 

commit tran 

     

--事务2  

SET TRANSACTION ISOLATION LEVELREPEATABLE READ

begin tran 

    INSERT INTOTest VALUES(2,'kk',null)  

commit tran  --事务1

SETTRANSACTION ISOLATION LEVEL SERIALIZABLE

begintran

  select * from dbo.Test where name = 'kk'

  waitfor delay '00:00:05'

  select * from dbo.Test where name = 'kk'

committran

 

--事务2

SETTRANSACTION ISOLATION LEVEL SERIALIZABLE

begintran

  INSERT INTO Test VALUES(2,'kk',null)

committran

 

事务2的运行结果：

id  name   info

1   kk      NULL

 

id  name   info

1   kk      NULL

2   kk      NULL

 

事务结束最后结果：

SELECT * FROM Test  Wherename='kk'

id  name   info

1   kk      NULL

2   kk      NULL

 

说明：【事务1】读取了数据，【事务2】插入了数据，【事务1】再读取数据。【事务1】两次读取都不一样！幻觉？

 

解决：提高隔离级别

SET TRANSACTION ISOLATIONLEVEL SERIALIZABLE 

此时【事务2】等待【事务1】完成。

事务1的运行结果2次都一样：

id  name   info

1   kk      NULL

 

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

 

 

如果在批处理中设置 REPEATABLE READ，并且该批处理调用一个将隔离级别设置为SERIALIZABLE的存储过程，则当该存储过程将控制返回给该批处理时，隔离级别就会恢复为REPEATABLEREAD。

 

并发事务引起的问题

      问题          

                    描述                      

              结果             

                          解决                     

丢失更新

A读—B读—A改—B改

A更改丢失

READ UNCOMMITTED

脏读

A改—B读—A回滚

B读无效值

READ COMMITTED

不可重读

A读—B改—A读

A读不一致

REPEATABLE READ

不可重读

A读—B改—A读

A读不一致

SNAPSHOT

幻读

A读—B增删—A读

A读或多或少

SERIALIZABLE

 

yclilian
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sqlserver中的事务和锁详细解析
12-15
前几天“拜读”《sqlserver2005高级程序设计》和《SQL Server 2008编程入门经典(第3版)》这两本翻译后的中文版书籍。竟然发现目录结构大致一样，其讲解的内容几乎差不多。有抄袭的嫌疑。看到“事务和锁”那一张中，...
SqlServer事务详解（事务隔离性和隔离级别详解）
qq_18932003的博客
08-08 1033
目录 概述 什么是事务（定义） 事务的4个特性（特性） 原子性（atomicity） 一致性（consistency） 隔离性（isolation） 持久性（durability） 事务的分类 （分类） 简单事务应用示例 事务不隔离导致的问题 1、更新丢失（Lost update） 2、脏读（Dirty Reads） 3、不可重复读（Non-repeatable Reads） 事务的隔离级别 1、未提交读取（相当于with(nolock)）：第一级别 2、
SqlServer 并发事务（一）：事务隔离级别
热门推荐
11-26 1万+
--查了当前数据库是事务隔离级别 DBCC USEROPTIONS 【测试一：丢失更新】 --事务1 begin tran select * from dbo.Test(nolock) where name = 'kk' waitfor delay '00:00:05' update T set info = 'A更改' from Test T(nolock) where name
事务隔离级别READ_UNCOMMITTED测试
Blitz_Man
10-14 9416
有个方法想使用read_uncommitted，但是一直不起作用，排查了半天终于发现了原因。结论情景一： 这种情况下是可以读取到未commit的数据的。public class AService{ @Transactional(isolation=Isolation.READ_UNCOMMITTED) public void fun(){ //这里可以读到脏数据
数据事务四种隔离机制和七种传播行为
wsb8233696的博客
04-25 523
一、隔离级别： 数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。 １.ISOLATION_READ_UNCOMMITTED：这是事务最低的隔离级别，它充许令外一个事务可以看到这个事务未提交的数据。 这种隔离...
SqlServer中四种隔离级别分别是什么？
最新发布
weixin_69620017的博客
06-06 455
简单来说，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务中又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。可重复读解决了脏读的问题。在未提交读级别，事务中的修改，即使没有提交，对其他事务也都是可见的。
SQLServer中的事务与锁
03-03
在一个事务中，你写啦2条sql语句，一条是修改订单表状态,一条是修改库存表库存-1。如果在修改订单表状态的时候出错，事务能够回滚，数据将恢复到没修改之前的数据状态，下面的修改库存也就不执行，这样确保
详解SQL Server中的事务与锁问题
09-09
在数据库方面，对于非DBA的程序员来说，事务与锁是一大难点，针对该难点，本篇文章试图采用图文的方式来与大家一起探讨sqlserver事务与锁问题
spring的事务中，Isolation4种事务隔离级别的理解
ynyeel的博客
04-24 2267
spring事务中的4种隔离事务的理解
数据库事务概述
zzxinwow123的博客
06-27 2260
事务管理欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编
@Transactional的四种隔离级别测试
C18298182575的博客
11-14 406
@Transactional
SQLServer事务隔离级别
coco的专栏
09-12 5292
数据库中的事物是具有原子性(Atomicity)，一致性(Consistemcy)，隔离性(Isolation)，持久性(Durability)四个特征。 1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么全部不执行。 2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序 串行执行的结果相一致。 3、夺隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。 4、夺持久性(Dura
SQLServer事务的隔离级别
小白将
09-12 2738
传统隔离级别隔离级别确定了并发用户读取或者写入的行为。读取者可以是任何选择数据的语句，默认情况下使用共享锁。写入者是任何对表进行修改的语句，并且需要一个排它锁。 SQL Server支持4个基于悲观并发控制（锁定）的传统隔离级别：READ UNCOMMITTED，READ COMMITTED， REPEATABLE READ与SERIALIZABLE。对于这4个隔离级别，隔离级别越高，读取者请求的
spring 事务-使用@Transactional 注解（事务隔离级别）
红袖添香的博客
08-26 7210
先看下@Transactional可以配制那些参数及以其所代表的意义。 isolation事务隔离级别,使用时一般如下。 @Transactional(isolation=Isolation.DEFAULT) public void method(){} isolation的参数有以下五种： 1_1、Isolation.DEFAULT:为数据源的默认隔离级别 1_2、isolation=Isolation.READ_UNCOMMITTED:未授权读取级别 以操作同一行数据为前提，读.
事务失效的几个场景
weixin_37252542的博客
03-30 199
Propagation.NESTED：支持当前事务，新增Savepoint点，也就是在进入子事务之前，父事务建立一个回滚点，与当前事务同步提交或者回滚。子事务是父事务的一部分，在父事务还未提交时，子事务一定没有提交。比如A方法使用默认的事务传播属性，B方法使用REQUIRES_NEW，此时A方法在内部B方法，一旦A方法出现异常，A方法中的事务回滚了，但是B方法并没有回滚，因为A方法和B方法使用的不是同一个事务，B方法新建了一个事务。比如A方法内部调用了B方法，此时B方法将会使用A方法的事务。
spring：事务的5大隔离级别，7大传播行为
03-03 200
一、五大隔离级别 　　ISOLATION_READ_UNCOMMITTED:读未提交 　　ISOLATION_READ_COMMITTED:读已提交 　　ISOLATION_REPEATABLE_READ:可重复读 　　ISOLATION_SERIALIZABLE:串行化 　　ISOLATION_DEFAULT:默认的隔离级别（不解释） 脏读:A事务读取B事务尚未提交的更...
Spring——事务控制
Paranoidse的博客
05-01 396
Spring事务控制： 1、JavaEE 体系进行分层开发,事务处理位于业务层,Spring提供了分层设计业务层的事务处理解决方 案 2、Spring 框架为我们提供了一组事务控制的接口,这组接口在spring-tx-5.0.2.RELEASE.jar中 3、Spring 的事务控制都是基于AOP的,它既可以使用配置的方式实现,也可以使用编程的方式实现.推荐使用配置方式实现. 数据库事务的...
sqlserver中的锁/事务/隔离级别
thy822的专栏
08-13 754
sqlserver中的锁 共享锁(s)/更新锁（U）/排它锁(独占锁X)共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。 更新 (U) 锁可以防止通常形式的死锁。若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。 如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。 排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会
SQLServer+.net 事务锁表问题
05-23
在 SQL Server 中，事务锁表问题通常出现在多个事务同时处理同一张表时。如果一个事务占用了表中的某些行或页的锁，而另一个事务又试图请求这些锁，则会发生阻塞，导致第二个事务无法继续执行。 为了解决这个问题，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值