深入分析MSSQL数据库中事务隔离级别和锁机制

锁机制
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 语句还将请求在联接表中读取的行上的共享锁。

(0)

相关推荐

  • Android开发中的数据库事务用法分析

    本文实例讲述了Android开发中的数据库事务用法.分享给大家供大家参考,具体如下: 在android应用程序开发中,在使用到数据库的时候,事务处理是非常重要的. 首先Android数据库操作(特别是写操作)是非常慢的,将所有操作打包成一个事务能大大提高处理速度. 其次是保证数据的一致性,让一个事务中的所有操作都成功执行,或者失败,或者所有操作回滚. 如果您喜欢使用其他平台(如PHP + MySQL),代码通常在一个功能强大的服务器上运行,一般不会被意外中止,但在android平台上,您将会因为

  • PHP入门教程之使用Mysqli操作数据库的方法(连接,查询,事务回滚等)

    本文实例讲述了PHP入门教程之使用Mysqli操作数据库的方法.分享给大家供大家参考,具体如下: Demo1.php <?php //使用 mysqli 对象操作数据库 //创建 mysqli 对象(资源句柄) $_mysqli = new mysqli(); //连接数据库 1.主机名(ip) 2.账户 3.密码 4.数据库 //mysqli_connect 函数 == $_mysqli -> connect(); $_mysqli -> connect('localhost','ro

  • 为mysql数据库添加添加事务处理的方法

    语句如下:alter table tableName engine=InnoDB; 用到的表 复制代码 代码如下: CREATE TABLE IF NOT EXISTS `test` ( `id` int(10) NOT NULL auto_increment, `websitename` varchar(200) character set utf8 NOT NULL, `websiteurl` varchar(200) character set utf8 NOT NULL, PRIMARY

  • SQLServer数据库中开启CDC导致事务日志空间被占满的原因

    SQLServer中开启CDC之后,在某些情况下会导致事务日志空间被占满的现象为: 在执行增删改语句(产生事务日志)的过程中提示,The transaction log for database '***' is full due to 'REPLICATION'(数据库"***"的事务日志已满,原因为"REPLICATION"). CDC以及复制的基本原理粗略地讲,对于日志的使用步骤如下: 1,每当基础表(开启了CDC或者replication的表)产生事务性操作

  • ORACLE数据库事务隔离级别介绍

    两个并发事务同时访问数据库表相同的行时,可能存在以下三个问题: 1.幻想读:事务T1读取一条指定where条件的语句,返回结果集.此时事务T2插入一行新记录,恰好满足T1的where条件.然后T1使用相同的条件再次查询,结果集中可以看到T2插入的记录,这条新纪录就是幻想. 2.不可重复读取:事务T1读取一行记录,紧接着事务T2修改了T1刚刚读取的记录,然后T1再次查询,发现与第一次读取的记录不同,这称为不可重复读. 3.脏读:事务T1更新了一行记录,还未提交所做的修改,这个T2读取了更新后的数据

  • MySQL数据库事务隔离级别介绍(Transaction Isolation Level)

    数据库隔离级别有四种,应用<高性能mysql>一书中的说明: 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 复制代码 代码如下: #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. [mysqld] transaction-isolation = REPEATABLE-READ 这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别

  • MSSQL与Oracle数据库事务隔离级别与锁机制对比

    一,事务的4个基本特征 Atomic(原子性): 事务中包含的操作被看做一个逻辑单元,这个逻辑单元中的操作要 么全部成功,要么全部失败. Consistency(一致性): 只有合法的数据可以被写入数据库,否则事务应该将其回滚到最初 状态. Isolation(隔离性): 事务允许多个用户对同一个数据进行并发访问,而不破坏数据的正 确性和完整性.同时,并行事务的修改必须与其他并行事务的修改 相互独立. Durability(持久性): 事务结束后,事务处理的结果必须能够得到固化. 以上属于废话

  • 深入分析MSSQL数据库中事务隔离级别和锁机制

    锁机制 NOLOCK和READPAST的区别. 1.       开启一个事务执行插入数据的操作. BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2.       执行一条查询语句. SELECT * FROM Customer WITH (NOLOCK) 结果中显示"a"和"a".当1中事务回滚后,那么a将成为脏数据.(注:1中的事务未提交) .NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修

  • 深入理解Mysql事务隔离级别与锁机制问题

    概述 数据库一般都会并发执行多个事务,多个事务可能会并发的对相同的一批数据进行增删改查操作,可能导致脏读.脏写.不可重复度和幻读.这些问题的本质都是数据库的多事务并发问题,为了解决事务并发问题,数据库设计了事务隔离机制.锁机制.MVCC多版本并发控制隔离机制,用一整套机制来解决多事务并发问题. 事务及其ACID属性 原子性:操作的不可分割: 一致性:数据的一致性: 隔离性:事务之间互不干扰: 持久性:数据的修改时永久的: 并发事务处理带来的问题 脏写:丢失更新,最后的更新覆盖了由其他事务所做的更

  • Spring框架事务属性中事务隔离级别与传播行为全面讲解

    目录 一.事务隔离级别 ①介绍 ②使用方式 二.事务传播行为 ①介绍 ②测试 一.事务隔离级别 ①介绍 数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题.一个事 务与其他事务隔离的程度称为隔离级别.SQL标准中规定了多种事务隔离级别,不同隔离级别对应不同 的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱. 隔离级别一共有四种: 读未提交:READ UNCOMMITTED 允许Transaction01读取Transaction02未提交的修改. 读已提交

  • MySQL隔离级别和锁机制的深入讲解

    目录 简述: 1. 事务的四大特性 2.多事务并发带来的问题 3.事务的隔离级别 4.演示不同隔离级别出现的问题 读未提交 读已提交 可重复读 串行化 5.锁机制 间隙锁 临建锁 排他锁 总结 简述: 我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读.不可重复读.幻读这些问题. 这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制.MVCC多版本并发控制隔离机制.以及事务隔离

  • MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式.同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力.所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在.这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么. 一次封锁or两段锁? 因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会

  • spring事务隔离级别、传播机制以及简单配置方式

    一.spring支持的事务声明方式 1. 编程式事务 当系统需要明确的,细粒度的控制各个事务的边界,应选择编程式事务. 2. 声明式事务 当系统对于事务的控制粒度较粗时,应该选择申明式事务,通过<tx>标签和<aop>切面形式在xml中进行配置. 3. 无论你选择上述何种事务方式去实现事务控制,spring都提供基于门面设计模式的事务管理器供选择,如下是spring事务中支持的事务管理器 事务管理器实现(org.springframework.*) 使用时机 jdbc.dataso

  • MySQL四种事务隔离级别详解

    本文实验的测试环境:Windows 10+cmd+MySQL5.6.36+InnoDB 一.事务的基本要素(ACID) 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节.事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样.也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,

随机推荐