SQLServer事务管理和锁底层机制浅谈

最新推荐文章于 2023-03-28 10:52:46 发布
最新推荐文章于 2023-03-28 10:52:46 发布
阅读量466 收藏 1
点赞数
分类专栏： sql事务和锁 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43413750/article/details/114950982
版权

SQLServer事务管理和锁的底层机制浅谈

环境准备

  • 创建数据库及表

    create database student;

use student;

create table campus_card
(
	studcardid char(8),
	balance decimal(10,2)
);

create table icbc_card
(
	studcardid char(8),
	icbcid char(10),
	balance decimal(10,2)
);

  • 插入数据

    insert into campus_card
values
	('20150031',30),
	('20150032',50),
	('20150033',70);

insert into icbc_card
values
	('20150031','2015003101',1000),
	('20150032','2015003201',1000),
	('20150033','2015003301',1000);

针对数据库STUDENT，按要求完成以下内容

  • 编写一个事务处理实现如下操作：

    学号为20150032的学生需要从银行卡中转帐200元到自己的校园卡中，若过程中出现错误则回滚

    set transaction isolation level repeatable read
Begin transaction
use STUDENT
go
declare @x decimal(10,2)
select @x=balance
from icbc_card
where studcardid='20150032'
set @x=@x-200
if(@x>=0)
	begin
		update icbc_card set balance=@x where studcardid='20150032'
		update campus_card set balance=balance+200 where studcardid='20150032'
		commit tran
	end
else
	begin
		print '余额不足,不能转账!'
		rollback tran
	end
--隔离级别为REPEATABLE READ

    执行

    select * from campus_card
select * from icbc_card
--默认的隔离级别为READ COMMITTED
--且这两个是隐式的事务定义，每一句都是一个隔离级别为READ COMMITTED的事务
--但是这个和上面的那个事务是顺序执行的，没有啥影响

    执行结果

  • 基于当前数据库STUDENT，设计样例展示四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读（删除和插入）

    • 丢失修改

      --由于上面更改过默认级别，所以先执行
set transaction isolation level READ COMMITTED
--重置所有打开会话的默认隔离级别为READ COMMITTED

      --事务1
begin tran
	declare @balance decimal(10,2)
	select @balance=balance from campus_card where studcardid='20150033'
	waitfor delay '00:00:05'
	set @balance=@balance-10
	update campus_card set balance=@balance where studcardid='20150033'
	commit tran
go
select balance from campus_card where studcardid='20150033'

      --事务2
begin tran
	declare @balance1 decimal(10,2)
	select @balance1=balance from campus_card where studcardid='20150033'
	waitfor delay '00:00:05'
	set @balance1=@balance1-20
	update campus_card set balance=@balance1 where studcardid='20150033'
	commit tran
go
select balance from campus_card where studcardid='20150033'

      在窗口1中执行事务1，很快切换到第二个窗口执行事务2

      执行结果：

      事务1：

      事务2：

    过程分析：

    这两个事务的隔离级别都是READ COMMITTED，都会在读取数据时申请共享锁

    而先执行的那个事务(即事务1)会先申请到共享锁，然后读取完之后释放共享锁，读取到的数据为70，然后延迟5秒，事务2申请到该数据的共享锁，然后读取，然后释放共享锁，读取到的数据为70，两个共享锁可以叠加(但是手速不够)

    然后事务1申请到排他锁，进行数据的更新，将balance更新为60，然后释放，释放后事务2才能申请到排他锁，然后更新，将balance更新为50，然后释放。

    此时发生了更新的覆盖，即50覆盖掉了60

    我第一次验证这个实验的时候，没有修改默认的隔离界别，所以当时两个事务的隔离级别为REPEATABLE READ，发生了死锁

    分析：

    事务1申请到了共享锁，事务2也申请到了共享锁，这两个共享锁都是要持续到事务结束才能释放，所以事务1的更新语句要等事务2的共享锁释放后才能加排他锁，而事务2的更新语句要等事务1的共享锁释放后才能加排他锁，所以造成了死锁

    • 读脏数据(dirty read)：

      --事务1
set transaction isolation level read uncommitted
begin tran
	declare @balance decimal(10,2)
	select @balance=balance 
	from campus_card
	where studcardid='20150032'
	update campus_card 
	set balance=@balance+100
	where studcardid='20150032'
	waitfor delay '00:00:05'
	rollback tran
go
select balance from campus_card where studcardid='20150032'

      --事务2
set transaction isolation level read uncommitted
begin tran
	declare @balance decimal(10,2)
	select @balance=balance 
	from campus_card 
	where studcardid='20150032'
	update campus_card 
	set balance=@balance+50
	where studcardid='20150032'
	commit tran
go
select balance from campus_card where studcardid='20150032'

      先在一个窗口执行事务1，然后很快在另一个窗口执行事务2

      执行结果

      事务1：在这里插入图片描述

      事务2：

      过程分析：

      事务1和事务2的隔离级别都是READ UNCOMMITTED

      所以事务1和事务2读取数据时都不会申请共享锁

      事务1读取数据后，进行更新数据，此时给数据加上了排他锁，回滚之前将balance更新为350。

      但是事务2此时还是能读取该数据，在此基础上加50成为400，事务2提交了事务，该数据写入数据库。

      而事务1回滚事务，相当于没有操作，但是此时balance已经变成了400

    • 不可重复读(NonRepeatable read)：

      --事务1
set transaction isolation level READ COMMITTED
use STUDENT
go
begin tran
	select balance
	from campus_card
	where studcardid='20150031'
	waitfor delay '00:00:10'
	select balance
	from campus_card
	where studcardid='20150031'
	commit tran

      --事务2
set transaction isolation level READ COMMITTED
use STUDENT
go
begin tran
	update campus_card
	set balance=balance+200
	where studcardid='20150031'
	commit tran

      先在一个窗口执行事务1，然后很快在另一个窗口执行事务2

      执行结果

      事务1：

      过程分析：

      两个事务的隔离级别都是READ COMMITTED

      事务1第一次读取时，加了共享锁，读取完之后释放。在等待期间，数据是没有加锁的，此时事务2加上排他锁进行数据的更新，实行完成提交事务。然后事务1的第二次读取，加上共享锁，读取完成后释放。但是这两次读取到的数据已经不一样了

    • 幻读(Phantom Read)：

      • 插入

        --事务1
set transaction isolation level repeatable read
begin tran
	use STUDENT
	go
	select balance
	from campus_card
	where studcardid='20150031'
	waitfor delay '00:00:05'
	select balance
	from campus_card
	where studcardid='20150031'
	commit tran

        --事务2
set transaction isolation level repeatable read
begin tran
	insert into campus_card
	values('20150031',30)
	commit tran

      先在一个窗口执行事务1，然后很快在另一个窗口执行事务2

      执行结果

      事务1：
      在这里插入图片描述

      过程分析：

      事务1和事务2的隔离级别都是REPEATABLE READ

      则事务1第一次读取时，在数据上面加上了共享锁，知道事务1结束才释放

      但是事务1只是在studcardid='20150031’上面加的共享锁，其它不符合where条件的数据没有加锁，所以事务2能够进行插入操作，所以出现了幻读现象

      • 删除

        --事务1
set transaction isolation level read committed
begin tran
	use STUDENT
	go
	select balance
	from campus_card
	where studcardid='20150031'
	waitfor delay '00:00:10'
	select balance
	from campus_card
	where studcardid='20150031'
	commit tran

        set transaction isolation level repeatable read
begin tran
	delete from campus_card
	where studcardid='20150031'
	commit tran

        先在一个窗口执行事务1，然后很快在另一个窗口执行事务2

        执行结果

        事务1：

      过程分析：

      这里将事务1的隔离级别换成了READ COMMITTED

      如果还是REPEATABLE READ则不能成功验证，因为事务2的delete操作涉及到studcardid='20150031’的数据，事务1结束之前该数据上是有事务1加的共享锁，所以得等到事务1执行完。

      事务1的隔离级别是READ COMMITTED

      则事务1的读取加的共享锁会在读取操作完成之后就释放，那么等待期间，这些数据上没有锁，则事务2就可以删除这些数据，所以事务1第二次读取的时候，数据已经被删除了

  • 利用锁机、数据库的隔离级别等知识，设计方案分别解决上述丢失修改、读脏数据和不可重复读、幻读的数据不一致问题，可以用sp_lock过程查看当前锁的状态。

    修改隔离级别以确保数据的正确性:

    • 丢失修改，在SQL语句前加已提交读``

      分析：

    如果隔离级别是REPEATABLE READ和SERIALIZABLE

    则会发生死锁，原因前面已经分析

    本实验中丢失修改不发生的关键是让事务1和事务2最好能够顺序执行，我的一个想法是可以在事务1最前面加上一句修改语句，那么事务1开始执行时就会给该数据加上排他锁直到事务1结束。事务2就只能等到事务1结束后才能获取到共享锁和排他锁

    执行结果：

    事务1：

    事务2：

    • 读脏数据，将事务2的隔离级别设置成READ COMMITTED，事务1的隔离级别随意

      则此时，事务1读取在修改数据期间，在数据上面加上了排他锁，则事务2在事务1确认提交之前不能得到该数据的共享锁，所以不能读取该数据，等待事务1回滚后，该数据上的排他锁就释放了，事务2就可以读取数据进行更新

    我觉得这个事务2修改为READ COMMITED隔离级别之后，还是存在着读脏数据的风险，因为事务1有个数据的读取阶段，也就是说事务1在执行时是先将该数据加上共享锁(或者不加锁)，但是共享锁允许其它事务在该数据上再加共享锁，所以如果事务1和事务2执行的间隔够短，则事务2就能够再事务1加上排他锁之前读取到数据，还是会发生读取脏数据

    执行结果：

    • 不可重复读，将事务1的隔离级别设置为REPEATABLE READ，事务2的隔离级别至少设置为READ COMMITTED(要申请共享锁)

      此时事务1执行，第一次读取数据时，在数据上加上了共享锁，且共享锁一直持续到事务结束才释放，所以在事务1结束之前，事务2不能够获得事务的排他锁进行更新。

      所以事务1两次读取到的数据是一致的

      执行结果：

      事务1：

    • 幻影读

      • 插入：设置事务1的隔离级别为serializable，事务2的隔离级别随意

      此时事务1执行期间，是不允许其它事务插入数据的，所以不会发生幻影读

      执行结果：

      事务1：

      • 删除：设置事务1的隔离级别为REPEATABLE READ，事务2的隔离级别随意

      因为事务2是删除操作，这个排他锁是要加的数据是符合事务1的where条件的数据

      所以事务1只需在事务1结束之前，给这些数据上加上共享锁即可

      执行结果：

      事务1：

  • 构造一个出现死锁的情况

    set transaction isolation level read committed

    --事务1
begin tran
declare @read int
select @read=grade
from sc
where sno='95003'
waitfor delay '00:00:10'
update sc
set grade=@read-1
where sno='95003'

    --事务2
begin tran
declare @read int
select @read=grade
from sc
where sno='95003'
update sc
set grade=@read-1
where sno='95003'

    将两个事务的隔离级别都设置成为REPEATABLE READ即可发生死锁

    这个和我前面验证那个修改丢失很类似

    即事务1先获取到共享锁，知道事务1结束后才释放

    事务2执行时同样也获得了该数据的共享锁，直到事务2结束后才释放

    则此时事务1的修改语句要加排他锁，但是得先等事务2释放共享锁

    同理，事务2想加排他锁得先等事务1释放共享锁，就发生了死锁

lzukomorebi
关注
专栏目录
数仓实践：浅谈 Kimball 维度建模
云祁QI
05-21 1681
我们不管是基于 Hadoop 的数据仓库（如 Hive ），还是基于传统 MPP 架构的数据仓库（如 Teradata ），抑或是基于传统 Oracle 、MySQL 、SQL Server 关系型数据库的数据仓库，其实都面临如下问题：怎么组织数据仓库中的数据？怎么组织才能使得数据的使用最为方便和便捷？怎么组织才能使得数据仓库具有良好的可扩展性和可维护性？Kimball ...
浅谈Mysql
m0_58600477的博客
09-25 374
MySQL简单的SQL语句limit限定字段order bywherebetween and空值检查and/or/notin模糊查询 通配符%/_正则表达式 简单的SQL语句 limit 取出表order的前三行数据 SELECT * FROM orders LIMIT 3 取order表中的第二行到第三行 SELECT * FROM orders LIMIT 2,3 从order表中从第二行开始往下取三行 SELECT * FROM orders LIMIT 2 OFFSET
事务管理（二）——SQL SERVER的事务管理
03-19
NULL 博文链接：https://t5crambing.iteye.com/blog/735340
SQLSERVER-底层基础-体系结构-文件和文件组结构
weixin_33937913的博客
04-06 618
SQLServer 将数据库映射为一组操作系统文件。数据和日志信息绝不会混合存放在同一个文件中，而且一个文件只能由一个数据库使用。文件组是命名的文件集合，用于帮主数据布局和管理任务，譬如备份和还原操作。数据库文件SQLServer数据库具有三种类型的文件：.mdf,.ldf,ndf（主数据文件、日志文件、次要数据文件），注：文件的后缀名是可自主命名的。主数据文件：每个数据库都...
共勉之~SQLServer底层介绍
qq_41191427的博客
06-03 974
一、首先先了解SQLserver存储类型在底层是怎么分布的： 时间和日期： date，time，datetime2，smalldatetime，datetime，datetimeoffset Date:只存储一个日期值，它支持的日期范围从0001-01-01到9999-12-31，存储为3个字节的无符号数 time：底层存储是3到5个字节，取决与精度，无符号整数 Datetime数据类型存储日期时...
sqlserver锁机制
zgqtxwd的专栏
04-28 402
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
一个好的Sqlserver底层数据库类
aiqingyu的专栏
07-09 874
using System;using System.Data;using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebCon
浅谈SQL Server数据库访问.pdf
最新发布
01-04
"浅谈SQL Server数据库访问" 本文主要介绍了Microsoft SQL Server数据库的基本概念和访问方法，讨论了三种常用的数据库访问接口DAO、ODBC和ADO，并提供了一些示例程序来访问SQL Server数据库。此外，本文还介绍了...
浅谈Delphi与SQL Server 2000数据库连接方法及其实现过程.pdf
09-19
4. 数据缓存和事务处理：对于大量数据操作，可能需要考虑数据缓存和事务管理，确保数据的一致性和完整性。 5. 连接关闭：在完成数据库操作后，必须正确关闭连接，以释放资源并避免潜在的问题。 以学生信息管理系统...
mysql事务与锁（一）精谈Mysql事务
02-18 201
浅谈Mysql事务事务介绍事务的四个特性（ACID）mysql的事务锁 事务介绍 事务全称叫数据库事务，是数据库并发控制时的基本单位，它是一个操作集合，这些操作要么不执行，要么都执行，不可分割。例如我们的转账这个业务，就需要进行数据库事务的处理。 事务的四个特性（ACID） 一致性 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 原子性 指事务是一个不可分割的工作单位，事务中的操作...
sqlserver中的事务和锁详细解析
09-10
下面小编就为大家介绍一下事务和锁，这大概是数据库中比较难理解的东西了
SQL Server底层架构技术对比
SQL专家云的博客
03-28 453
从根本上说是通用技术和SQL Server专用技术的比较，如果要实现数据安全和可用性等基本需求，通用技术就能可以做到，如果要实现读写分离、负载均衡等高级别的需求，就需要充分利用SQL Server特性而开发的专用技术。并不是说通用技术不好，只是满足的场景不同而已，我们要根据实际的场景选择合适的方案。
如何访问SQL Server底层的机制
xiaoluoblog的专栏
09-27 867
各位朋友大家好,我有这样一个问题,向大家请教,先谢谢了.请教这样一个问题：如何访问SQL Server底层的机制,得到当前SELECT 的表 ?可能不好描述，详细的问题过程和背景都写在了下面，非常不好意思来打扰你，也非常感谢你能够抽出时间来读在这篇文章，谢谢了CREATE TABLE TEST (NAME CHAR(10),COURSE CHAR(10),GOALS INT)INSERT  T
理解Sql Server 事务隔离层级(Transaction Isolation Level)
weixin_30783629的博客
08-01 355
关于Sql Server 事务隔离级别，百度百科是这样描述的 隔离级别：一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。隔离级别从允许的并发副作用（例如，脏读或虚拟读取）的角度进行描述。 隔离级别共5种： read uncommitted | 0 未提交读read committed | 1 已提交读repeatable read | 2 可重复读serializable | 3 ...
SqlServer事务详解（事务隔离性和隔离级别详解）
熊泽-学习中的苦与乐
05-31 1936
SqlServer事务详解（事务隔离性和隔离级别详解）概述什么是事务（定义）事务的4个特性（特性）原子性（atomicity）一致性（consistency）隔离性（isolation）持久性（durability）事务的分类 （分类）简单事务应用示例事务不隔离导致的问题1、更新丢失（Lost update）2、脏读（Dirty Reads）3、不可重复读（Non-repeatable Reads）事务的隔离级别1、未提交读取（相当于with(nolock)）：第一级别2、提交读取（Oracle和SQLSe
【7. 事务的底层原理和 MVCC】
努力学习
12-15 1300
事务的底层原理和 MVCC ​ 在事务的实现机制上，MySQL 采用的是 WAL（Write-ahead logging，预写式日志）机制来实现的。 ​ 在使用 WAL 的系统中，所有的修改都先被写入到日志中，然后再被应用到 系统中。通常包含 redo 和 undo 两部分信息。 ​ 为什么需要使用 WAL，然后包含 redo 和 undo 信息呢？举个例子，如果一 个系统直接将变更应用到系统状态中，那么在机器掉电重启之后系统需要知道操 作是成功了，还是只有部分成功或者是失败了（为了恢复状态）。如果使
SQLSERVER 体系结构-页与区
xiaoye1202的博客
03-05 2474
页是SQL Server 中数据存储的基本单位，为数据库中的数据文件（mdf、ndf）分配的磁盘空间可以逻辑上划分为页（0-n连续编号），日志文件不包含页，而是包含一系列日志记录。磁盘I/O操作在页级执行。也就是说SQLServer读取或写入所有的数据页；页的大小为8KB区是由8个物理上连续的页构成的集合，区有助于有效管理页。在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每...
SQL查询的底层运行原理分析
yang灬仔
10-08 1015
SQL查询的底层运行原理分析 SQL 语言无处不在。SQL 已经不仅仅是技术人员的专属技能了，似乎人人都会写SQL，就如同人人都是产品经理一样。如果你是做后台开发的，那么CRUD就是家常便饭。如果你是做数仓开发的，那么写SQL可能占据了你的大部分工作时间。我们在理解 SELECT 语法的时候，还需要了解 SELECT 执行时的底层原理。只有这样，才能让我们对 SQL 有更深刻的认识。本文分享将逐步分解SQL的执行过程，剖析其底层运行原理，希望对你有所帮助。 数据准备 本文旨在说明SQL查询的执行过程，
SQLServer事务与锁详解：保持数据一致性与安全
"本文主要介绍了SQLServer中的事务与锁机制，包括事务的特性、分类以及锁的作用，并提及了死锁的问题。事务是确保数据一致性与可恢复性的关键，而锁则用于管理多用户访问数据库资源的顺序。文章还提到了死锁对数据库...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lzukomorebi

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值