169it科技资讯
169it -->


当前位置:  数据库>sqlserver

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

    来源: 互联网  发布时间:2014-10-14

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


    
相关技术文章:
    ▪SQL优化技巧指南

     对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描 select id from t where num is null 最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库。 备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。 不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如......


    ▪人工智能自动sql优化工具--SQLTuning for SQL Server

     针对这种情况,人工智能自动SQL优化工具应运而生。现在我就向大家介绍这样一款工具:SQLTuning for SQL Server。 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具。 QuestCentral(图1)是一款集成化、图形化、跨平台的数据库管理解决方案,可以同时管理Oracle、DB2 和 SQL server 数据库。它包含了如下的多个工具: 数据库管理(DBA)  数据库监控(Monitoring Pack)  数据库诊断 (Spotlight Diagnostics)  数据库分析 (Database Analysis)  SQL优化 (SQL Tuning)  空间管理 (Space Management)  压力测......


    ▪使用 TOP 子句限制UPDATE 语句更新的数据

     可以使用 TOP 子句来限制 UPDATE 语句中修改的行数。当 TOP (n) 子句与 UPDATE 一起使用时,将针对随机选择的 n 行执行删除操作。例如,假设您要为一位高级销售人员减轻销售负担,而将一些客户分配给了一位初级销售人员。下列示例将随机抽样的 10 个客户从一位销售人员分配给了另一位。 USE AdventureWorks2008R2; UPDATE TOP (10) Sales.Store SET SalesPersonID = 276 WHERE SalesPersonID = 275; GO 如果需要使用 TOP 来应用按有意义的时间顺序排列的更新,您必须同时使用 TOP 和 ORDER BY 子句。下列示例更新了雇佣最早的 10 名雇员的假......


 
最新技术文章:
    ▪Sql Server里删除数据表中重复记录的例子

     [项目] 数据库中users表,包含u_name,u_pwd两个字段,其中u_name存在重复项,现在要实现把重复的项删除! [分析] 1、生成一张临时表new_users,表结构与users表一样; 2、对users表按id做一个循环,每从users表中读出一个条记录,判断new_users中是否存在有相同的u_name,如果没有,则把它插入新表;如果已经有了相同的项,则忽略此条记录; 3、把users表改为其它的名称,把new_users表改名为users,实现我们的需要。 [程序] 代码如下: declare ......


    ▪如何查看SQLSERVER中某个查询用了多少TempDB空间

         在SQL Server中,TempDB主要负责供下述三类情况使用: 内部使用(排序、hash join、work table等) 外部使用(临时表,表变量等) 行版本控制(乐观并发控制)       而对于内部使用,一些比较复杂的查询中由于涉及到了大量的并行、排序等操作时就需要大量的内存空间,每一个查询在开始时都会由SQL Server预估需要多少内存,在具体的执行过程中,如果授予的内存不足,则需要将多出来的部分由TempDB处理,这也就是所谓的Spill to TempDB。     通过下述语句可以观察到某个查询对TempDB造成了多少读写: ......


    ▪在SQL Server中使用ISNULL执行空值判断查询

     有如下查询: 代码如下: select isnull(lastchanged,'') as lastchanged from vhhostlist where s_comment='test202' 本来,ISNULL函数只有一个参数,它表示的含义就是判断这个参数的值是否为NULL,是NULL则返回TRUE,否则返回FALSE; 但是,在SQLServer的查询语句中,isnull需要两个参数,它的含义是如果参数1为NULL,则以参数2为isnull函数的返回值; 即,上面查询的的含义是,查询vhhostlist表中s_comment='test202'那一行lastchanged字段的值,如果它为NULL,则返回一个空字符串; 记得以前不懂这个的时候,遇到这样一......


 


站内导航:


特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

©2012-2017,169IT.COM,E-mail:www_169it_com#163.com(请将#改为@)

浙ICP备11055608号