讲解MSSQL数据库中SQL锁机制和事务隔离级别Word格式文档下载.docx
《讲解MSSQL数据库中SQL锁机制和事务隔离级别Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《讲解MSSQL数据库中SQL锁机制和事务隔离级别Word格式文档下载.docx（10页珍藏版）》请在冰豆网上搜索。
3.执行一条插入语句。
INSERTINTOCustomerSELECT'
b'
COMMITTRANt
这个时候，即使步骤1的事务回滚，那么a这条数据将丢失，而b继续插入数据库中。
NOLOCK
1．执行如下语句。
BEGINTRANttt
WAITFORdelay'
00:
20'
COMMITTRANttt
注：
NOLOCK不加任何锁，可以增删查改而不锁定。
–不锁定
DELETECustomerwhereID=1–不锁定
SELECT*FROMCustomer–不锁定
UPDATECustomerSETTitle='
aa'
WHEREID=1–不锁定
ROWLOCK
1.执行一条带行锁的查询语句。
SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD--（必须）
SELECT*FROMCustomerWITH（ROWLOCK）WHEREID=17
在删除和更新正在查询的数据时，会锁定数据。
对其他未查询的行和增加，查询数据无影响。
–不等待
DELETECustomerwhereID=17–等待
DELETECustomerwhereID<
>
17–不等待
SELECT*FROMCustomer–不等待
WHEREID=17–等待
WHEREID<
17–不等待
HOLDLOCK，TABLOCK和TABLOCKX
1.执行HOLDLOCK
SELECT*FROMCustomerWITH（HOLDLOCK）
10'
其他事务可以读取表，但不能更新删除
updateCustomersetTitle='
—要等待10秒中。
SELECT*FROMCustomer—不需要等待
2.执行TABLOCKX
SELECT*FROMCustomerWITH（TABLOCKX）
其他事务不能读取表,更新和删除
SELECT*FROMCustomer—要等待10秒中。
3.执行TABLOCK
SELECT*FROMCustomerWITH（TABLOCK）
UDPLOCK
1.在A连接中执行。
SELECT*FROMCustomerWITH（UPDLOCK）
2.在其他连接中执行。
whereID=1—要等10秒
SELECT*FROMCustomer–不用等
insertintoCustomerselect'
–不用等
对于UDPLOCK锁，只对更新数据锁定。
使用这些选项将使系统忽略原先在SET语句设定的事务隔离级别（SETTransactionIsolationLevel）。
事务隔离级别
脏读：
READUNCOMMITTED
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
INSERTINTOCustomer
SELECT'
123'
2.在B连接中执行。
SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED
SELECT*FROMCustomer
这个时候，未提交的数据会'
会显示出来，当A事务回滚时就导致了脏数据。
相当于（NOLOCK）
提交读：
READCOMMITTED
SETTRANSACTIONISOLATIONLEVELREADCOMMITTED
不会显示出来，当A事务提交以后B中才能读取到数据。
避免了脏读。
不可重复读：
REPEATABLEREAD
不可重复读是指在一个事务内，多次读同一数据。
在这个事务还没有结束时，另外一个事务也访问该同一数据。
那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。
这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
1.在A连接中执行如下语句。
SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD
SELECT*FROMCustomerWHEREID=17
30'
2.在B连接中执行如下语句，而且要在第一个事物的三十秒等待内。
d'
WHEREID=17
这个时候，此连接将锁住不能执行，一直等到A连接结束为止。
而且A连接中两次读取到的数据相同，不受B连接干扰。
注，对于ReadCommitted和ReadUnCommitted情况下，B连接不会锁住，等到A连接执行完以后，两条查询语句结果不同，即第二条查询的Title变成了d。
序列化读：
SERIALIZABLE
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
111'
2.在B连接中执行，并且要在A执行后的20秒内。
BEGINTRANtt
2'
COMMITTRANtt
在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语句还将请求在联接表中读取的行上的共享锁。
下面的示例显示SQLServer中当前持有的所有锁的信息。
USEmaster
EXECsp_lock
其中"
类型"
列显示当前锁定的资源类型。
资源类型描述
RID用于锁定表中的一行的行标识符。
KEY索引中的行锁。
用于保护可串行事务中的键范围。
PAG数据或索引页。
EXT相邻的八个数据页或索引页构成的一组。
TAB包括所有数据和索引在内的整个表。
DB数据库。
如何查看当前锁（企业管理器）
展开服务器组，然后展开服务器。
展开"
管理"
，然后展开"
当前活动"
执行下列操作中的一种：
锁/进程ID"
以查看每个连接的当前锁。
锁/对象"
以查看每个对象的当前锁。
在控制台树中单击要查看的连接（SPID）或对象。
该连接或对象的当前锁显示在详细信息窗格中。
SQLServer使用以下资源锁模式。
锁模式描述
共享（S）用于不更改或不更新数据的操作（只读操作），如SELECT语句。
更新（U）用于可更新的资源中。
防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它（X）用于数据修改操作，例如INSERT、UPDATE或DELETE。
确保不会同时对同一资源进行多重更新。
意向用于建立锁的层次结构。
意向锁的类型为：
意向共享（IS）、意向排它（IX）以及与意向排它共享（SIX）。
架构在执行依赖于表架构的操作时使用。
架构锁的类型为：
架构修改（Sch-M）和架构稳定性（Sch-S）。
大容量更新（BU）向表中大容量复制数据并指定了TABLOCK提示时使用。
虽然Microsoft&
reg;
SQLServer&
#8482;
2000自动执行锁定，但它仍可以通过以下方法自定义应用程序中的锁定：
处理死锁和设置死锁优先级。
处理超时和设置锁超时持续时间。
设置事务隔离级别。
对SELECT、INSERT、UPDATE和DELETE语句使用表级锁定提示。
配置索引的锁定粒度。
可以使用SELECT、INSERT、UPDATE和DELETE语句指定表级锁定提示的范围，以引导Microsoft&
2000使用所需的锁类型。
当需
要对对象所获得锁类型进行更精细控制时，可以使用表级锁定提示。
这些锁定提示取代了会话的当前事务隔离级别。
说明SQLServer查询优化器自动作出正确的决定。
建议仅在必要时才使用表级锁定提示更改默认的锁定行为。
禁止锁定级别反过来会影响并发
。
锁定提示描述
HOLDLOCK将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要时就立即释放锁。
HOLDLOCK等同于SERIALIZABLE。
NOLOCK不要发出共享锁，并且不要提供排它锁。
当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。
有可能发生脏读。
仅
应用于SELECT语句。
PAGLOCK在通常使用单个表锁的地方采用页锁。
READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。
默认情况下，SQLServer2000在此隔离级别上操作。
READPAST跳过锁定行。
此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放
在这些行上的锁。
READPAST锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。
仅适用于SELECT语句。
READUNCOMMITTED等同于NOLOCK。
REPEATABLEREAD用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。
ROWLOCK使用行级锁，而不使用粒度更粗的页级锁和表级锁。
SERIALIZABLE用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。
等同于HOLDLOCK。
TABLOCK使用表锁代替粒度更细的行级锁或页级锁。
在语句结束前，SQLServer一直持有该锁。
但是，如果同时指定HOLDLOCK，那么在事务结束
之前，锁将被一直持有。
TABLOCKX使用表的排它锁。
该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持有。
UPDLOCK读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。
UPDLOCK的优点是允许您读取数据（不阻塞其它事务）并
在以后更新数据，同时确保自从上次读取数据后数据没有被更改。
XLOCK使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。
可以使用PAGLOCK或TABLOCK指定该锁，这种情况下排它锁适用于适当
级别的粒度。
例如，如果将事务隔离级别设置为SERIALIZABLE，并且在SELECT语句中使用表级锁定提示NOLOCK，则键范围锁通常用于维护不采用可串行事务
USEpubs
GO
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
BEGINTRANSACTION
SELECTau_lnameFROMauthorsWITH（NOLOCK）
本文来自CSDN博客，转载请标明出处：
升级会员 