深入分析MSSQL数据库中事务隔离级别和锁机制
作者:hebedich 发布时间:2024-01-22 02:53:35
锁机制
NOLOCK和READPAST的区别。
1. 开启一个事务执行插入数据的操作。
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. 执行一条查询语句。
SELECT * FROM Customer WITH (NOLOCK)
结果中显示”a”和”a”。当1中事务回滚后,那么a将成为脏数据。(注:1中的事务未提交) 。NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修改。
SELECT * FROM Customer
这条语句将一直死锁,直到排他锁解除或者锁超时为止。(注:设置锁超时SET LOCK_TIMEOUT 1800)
SELECT * FROM Customer WITH (READPAST)
这条语句将显示a未提交前的状态,但不锁定整个表。这个提示指明数据库引擎返回结果时忽略加锁的行或数据页。
3. 执行一条插入语句。
BEGIN TRAN t
INSERT INTO Customer
SELECT 'b','b'
COMMIT TRAN t
这个时候,即使步骤1的事务回滚,那么a这条数据将丢失,而b继续插入数据库中。
NOLOCK
1. 执行如下语句。
BEGIN TRAN ttt
SELECT * FROM Customer WITH (NOLOCK)
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
注:NOLOCK不加任何锁,可以增删查改而不锁定。
INSERT INTO Customer SELECT 'a','b' –不锁定
DELETE Customer where ID=1 –不锁定
SELECT * FROM Customer –不锁定
UPDATE Customer SET Title='aa' WHERE ID=1 –不锁定
ROWLOCK
1. 执行一条带行锁的查询语句。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (必须)
BEGIN TRAN ttt
SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
注:在删除和更新正在查询的数据时,会锁定数据。对其他未查询的行和增加,查询数据无影响。
INSERT INTO Customer SELECT 'a','b' –不等待
DELETE Customer where ID=17 –等待
DELETE Customer where ID<>17 –不等待
SELECT * FROM Customer –不等待
UPDATE Customer SET Title='aa' WHERE ID=17–等待
UPDATE Customer SET Title='aa' WHERE ID<>17–不等待
HOLDLOCK,TABLOCK和TABLOCKX
1. 执行HOLDLOCK
BEGIN TRAN ttt
SELECT * FROM Customer WITH (HOLDLOCK)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
注:其他事务可以读取表,但不能更新删除
update Customer set Title='aa' —要等待10秒中。
SELECT * FROM Customer —不需要等待
2. 执行TABLOCKX
BEGIN TRAN ttt
SELECT * FROM Customer WITH (TABLOCKX)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
注:其他事务不能读取表,更新和删除
update Customer set Title='aa' —要等待10秒中。
SELECT * FROM Customer —要等待10秒中。
3. 执行TABLOCK
BEGIN TRAN ttt
SELECT * FROM Customer WITH (TABLOCK)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
注:其他事务可以读取表,但不能更新删除
update Customer set Title='aa' —要等待10秒中。
SELECT * FROM Customer —不需要等待
UDPLOCK
1. 在A连接中执行。
BEGIN TRAN ttt
SELECT * FROM Customer WITH (UPDLOCK)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
2. 在其他连接中执行。
update Customer set Title='aa' where ID=1—要等10秒
SELECT * FROM Customer –不用等
insert into Customer select 'a','b'–不用等
注:对于UDPLOCK锁,只对更新数据锁定。
注:使用这些选项将使系统忽略原先在SET语句设定的事务隔离级别(SET Transaction Isolation Level)。
事务隔离级别
脏读:READ UNCOMMITTED
脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是脏数据,依据脏数据所做的操作可能是不正确的。
1. 在A连接中执行。
BEGIN TRAN t
INSERT INTO Customer
SELECT '123','123'
WAITFOR delay '00:00:20'
COMMIT TRAN t
2. 在B连接中执行。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Customer
这个时候,未提交的数据会'123'会显示出来,当A事务回滚时就导致了脏数据。相当于(NOLOCK)
提交读:READ COMMITTED
1. 在A连接中执行。
BEGIN TRAN t
INSERT INTO Customer
SELECT '123','123'
WAITFOR delay '00:00:20'
COMMIT TRAN t
2. 在B连接中执行。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM Customer
这个时候,未提交的数据会'123'不会显示出来,当A事务提交以后B中才能读取到数据。避免了脏读。
不可重复读:REPEATABLE READ
不可重复读是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。
例如:
1. 在A连接中执行如下语句。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN ttt
SELECT * FROM Customer WHERE ID=17
WAITFOR delay '00:00:30'
SELECT * FROM Customer WHERE ID=17
COMMIT TRAN ttt
2. 在B连接中执行如下语句,而且要在第一个事物的三十秒等待内。
UPDATE Customer SET Title='d' WHERE ID=17
这个时候,此连接将锁住不能执行,一直等到A连接结束为止。而且A连接中两次读取到的数据相同,不受B连接干扰。
注,对于Read Committed和Read UnCommitted情况下,B连接不会锁住,等到A连接执行完以后,两条查询语句结果不同,即第二条查询的Title变成了d。
序列化读:SERIALIZABLE
1. 在A连接中执行。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN t
UPDATE Customer SET Title='111'
WAITFOR delay '00:00:20'
COMMIT TRAN t
2. 在B连接中执行,并且要在A执行后的20秒内。
BEGIN TRAN tt
INSERT INTO Customer
SELECT '2','2'
COMMIT TRAN tt
在A连接的事务提交之前,B连接无法插入数据到表中,这就避免了幻觉读。
注:幻觉读是指当事务不是独立执行时发生的一种现象,例如 第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好像发生了幻觉一样。
共享锁
共享锁(S 锁)允许并发事务在封闭式并发控制(请参阅并发控制的类型)下读取 (SELECT) 资源。资源上存在共享锁(S 锁)时,任何其他事务都不能修改数据。读取操作一完成,就立即释放资源上的共享锁(S 锁),除非将事务隔离级别设置为可重复读或更高级别,或者在事务持续时间内用锁定提示保留共享锁(S 锁)。
更新锁
更新锁(U 锁)可以防止常见的死锁。在可重复读或可序列化事务中,此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为排他锁(X 锁)]。如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排他锁(X 锁)。共享模式到排他锁的转换必须等待一段时间,因为一个事务的排他锁与其他事务的共享模式锁不兼容;发生锁等待。第二个事务试图获取排他锁(X 锁)以进行更新。由于两个事务都要转换为排他锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。
若要避免这种潜在的死锁问题,请使用更新锁(U 锁)。一次只有一个事务可以获得资源的更新锁(U 锁)。如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)。
排他锁
排他锁(X 锁)可以防止并发事务对资源进行访问。使用排他锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句(如 INSERT、UPDATE 和 DELETE)合并了修改和读取操作。语句在执行所需的修改操作之前首先执行读取操作以获取数据。因此,数据修改语句通常请求共享锁和排他锁。例如,UPDATE 语句可能根据与一个表的联接修改另一个表中的行。在此情况下,除了请求更新行上的排他锁之外,UPDATE 语句还将请求在联接表中读取的行上的共享锁。
猜你喜欢
- PHP mysqli_thread_id() 函数返回当前连接的线程 ID,然后杀死连接:<?php// 假定数据库用户名:root,
- 数据库对于服务端开发人员来说,几乎就是左膀右臂。几乎每一个服务端开发人员都要和数据库打交道。而MySQL又以其开源免费,小巧玲珑,简单易用,
- 使用PyQt5应用程序制作PDF转换成图片的小工具,可以导入PDF文档后一键生成对应的PNG图片。PDF图片转换小工具使用的中间件:pyth
- 报错代码粉丝群里面的一个小伙伴想用requests爬虫,然后遍历Xpath定位的数据列表的时候,发生了报错(当时他心里瞬间凉了一大截,跑来找
- 最近要搭建一个阿里云的LMAP环境,选了CentOS7来做搭建。1.ApacheCentos7默认已经安装httpd服务,只是没有启动。如果
- 系列目录:1. 服务器XMLHTTP(Server XMLHTTP in ASP)基础2. 
- 纪念我的第一个爬虫程序,一共写了三个白天,其中有两个上午没有看,中途遇到了各种奇怪的问题,伴随着他们的解决,对于一些基本的操作也弄清楚了。果
- 可能许多同学对SQL Server的备份和还原有一些了解,也可能经常使用备份和还原功能,我相信除DBA之外我们大部分开发员队伍对备份和还原只
- 我们的规范到底做到哪一步算是发挥良好的价值?其实一件事物我们理解错根本目的会导致出大不一样的结果,直接反应在设计师到底要体现什么的价值。想想
- 这篇文章主要介绍了Python字符串格式化输出代码实例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋
- 突然想到写这个话题,是基于最近在设计产品界面时,年糕一直在给我们灌输设计的品牌概念以及文化内涵要求而产生的,因之前在界面设计中也遇到一些困惑
- 使用了这么就pip命令,但是一直是简单使用,很多命令都是用了查,查了用,今天把常用的命令汇总一下,方便使用。命令:pip由上图可以看到 pi
- 正文开始:服务器端代码:# 再来简单的测试,这个是服务器端import socketimport sysBUF_SIZE = 1024ip_
- 前言cookie:在网站中,http请求是无状态的。也就是说即使第一次和服务器连接后并且登录成功后,第二次请求服务器依然不能知道当前请求是哪
- 本文实例讲述了Python从序列中移除重复项且保持元素间顺序不变的方法。分享给大家供大家参考,具体如下:问题:从序列中移除重复的元素,但仍然
- 本文实例讲述了python计算书页码的统计数字问题,是Python程序设计中一个比较典型的应用实例。分享给大家供大家参考。具体如下:问题描述
- 前言本文主要给大家介绍了关于使用Python通过subprocess调用adb命令,subprocess包主要功能是执行外部命令(相对Pyt
- 通常情况下,我们想构建一张表单时会在模板文件login.html中写入<form action="/your-name/&q
- 应用目录下apps.pyclass OperationConfig(AppConfig): name = 'operat
- 上次帮朋友写过的一个简单切换效果,超级简单,但也比较适用.因为用到了CSS Sprite技术,DEMO中附带了IE6兼容png的JS.核心J