关于数据库加锁的知识

LockType 属性

指示编辑过程中对记录使用的锁定类型。

设置和返回值

设置或返回以下某个 LockTypeEnum 的值。

常量 说明
adLockReadOnly 默认值，只读。无法更改数据。
adLockPessimistic 保守式记录锁定（逐条）。提供者执行必要的操作确保成功编辑记录，通常采用编辑时立即锁定数据源的记录的方式。
adLockOptimistic 开放式记录锁定（逐条）。提供者使用开放式锁定，只在调用 Update 方法时锁定记录。
adLockBatchOptimistic 开放式批更新。用于与立即更新模式相反的批更新模式。

说明
打开 Recordset 前设置 LockType 属性可指定打开时提供者应该使用的锁定类型。读取该属性可返回在打开的 Recordset 对象上正在使用的锁定类型。Recordset 关闭时 LockType 属性为读/写，打开时该属性为只读。

提供者可能不支持所有的锁定类型。如果某提供者不支持所需的 LockType 设置，则将替换为其他类型的锁定。要确定 Recordset 对象可用的实际锁定功能，请通过 adUpdate 和 adUpdateBatch 使用 Supports 方法。

如果 CursorLocation 属性被设置为 adUseClient，将不支持 adLockPessimistic 设置。设置不支持的值不会产生错误，因为此时将使用支持的最接近的 LockType 的值。

远程数据服务用法 当在客户端 (ADOR) 的 Recordset 对象上使用时，LockType 属性只能设置为 adLockOptimisticBatch。

------------------------------------------------------------------------------------
/********** 加锁 ***************
设table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它锁
新建两个连接
在第一个连接中执行以下语句
begin tran
update table1
set A=’aa’
where B=’b2’
waitfor delay ’00:00:30’ --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select * from table1
where B=’b2’
commit tran

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B=’b2’
waitfor delay ’00:00:30’ --等待30秒
commit tran

在第二个连接中执行以下语句
begin tran
select A,C from table1
where B=’b2’
update table1
set A=’aa’
where B=’b2’
commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个连接中的共享锁结束后才能执行 即要等待30秒

3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A=’aa’
where B=’b2’
waitfor delay ’00:00:30’
update table2
set D=’d5’
where E=’e1’
commit tran

在第二个连接中执行以下语句
begin tran
update table2
set D=’d5’
where E=’e1’
waitfor delay ’00:00:10’
update table1
set A=’aa’
where B=’b2’
commit tran

同时执行，系统会检测出死锁，并中止进程

SET IMPLICIT_TRANSACTIONS ON --用户每次必须显式提交或回滚。否则当用户断开连接时,
--事务及其所包含的所有数据更改将回滚

SET IMPLICIT_TRANSACTIONS OFF --自动提交模式。在自动提交模式下，如果各个语句成功
--完成则提交。
--------------------------------------------------------------------------------------

修改前
RS.lock
完成后
RS.unlock
--------------------------------------------------------------------------------------


Sql中游标和加锁的问题

日期：2000-11-27 15:42:00
出处：ALL ASP
作者：feny

%

%
%
%
%

游标使用时是比较灵活的，它有时用来描述一个记录集，有时又是用来描述当前记录集中某一条记录的指针。游标主要是用来建立一个关系数据库中行/列关系的一种SQL可利用的访问格式。与游标有关系的技术术语还有一个叫Bookmark的。如果你选择的游标方式支持Bookmarks。数据库将提供有关记录数目的强大功能。
在上面写出的那么多游标方式中，adOpenDynamic是没有太的用处的，虽然它提供实时显示数据库中的记录的所有更新操作的功能，但是因为并不是所有的数据库都支持该游标方式，没有移植性的游标方式对当前错综复杂的数据库来说真是用处不大。
在实际的编程中，我相信大家使用得最频繁的是adOpenStatic方式，当然这种方式的缺点是不能够就、实时反应出数据库中内容改变时的状况。如果要想看到数据库被其它用户改变的状况，可使用adOpenKeyse方式（但是它只能够反应出被编辑的改变情况，也就是说不能够反映出新增和删除记录的改变情况。）
其实上面的内容大家一般都可以在微软的技术参考资料中找到，下面来说说在使用这些游标方式和加锁方式时要注意到的问题。
首先要注意到的是这两种方式在混合使用时的问题，就是说你同时设置游标方式和加锁方式。除非你是在使用Access数据库，一般而言当你混合使用时是并不能够得到你预期想要的游标方式和加锁方式的。例如，如果你同时将游标设置为adOpenStatic方式，而将加锁设置为adLockOptimistic，你将得不到adOpenStatic方式的游标，你这时使用的游标方式将是adOpenKeyset，也就是说你使用ADO的话，它将返回adOpenKeyset的游标。
其次，游标和加锁的混合使用还会导致ADO返回的不是你想要的加锁方式，ADO会改变你的加锁方式。例如，在默认状态下游标方式是adOpenForwardOnly，在使用这种游标方式的同时如果你使用的加锁方式为-1（就是让数据源来判断加锁方式）或则adLockReadOnly，那么这种混合方式基本上不支持RecordSet的任何方法，也就是说RecordSet的任何方法将返回False(你的recordcount,absoultpage,addnew,delete,update等都会返回-1，-1就是表示不支持该属性)，但是这时如果你使用的是adOpenForwardOnly游标方式和其它的加锁方式混合，它反而会支持填加，删除和更新。

-----------------------------------------------------------------------------------------------

就启明星提出的在SQL Server中使用加锁的问题，我就以前的经验和收集的一些资料简单的提出我自己的一些看法，不知道对启明星是否有所帮助：
一般而言，下面是个典型的打开数据库的过程。
%

%
%
%
%
游标使用时是比较灵活的，它有时用来描述一个记录集，有时又是用来描述当前记录集中某一条记录的指针。游标主要是用来建立一个关系数据库中行/列关系的一种SQL可利用的访问格。与游标有关系的技术术语还有一个叫Bookmark的。如果你选择的游标方式支持Bookmarks。数据库将提供有关记录数目的强大功能。在上面写出的那么多游标方式中，adOpenDynamic是没有太的用处的，虽然它提供实时显示数据库中的记录的所有更新操作的功能，但是因为并不是所有的数据库都支持该游标方式，没有移植性的游标方式对当前错综复杂的数据库来说真是用处不大。在实际的编程中，我相信大家使用得最频繁的是adOpenStatic方式，当然这种方式的缺点是不能够就、实时反应出数据库中内容改变时的状况。如果要想看到数据库被其它用户改变的状况，可使用adOpenKeyse方式（但是它只能够反应出被编辑的改变情况，也就是说不能够反映出新增和删除记录的改变情况。）
其实上面的内容大家一般都可以在微软的技术参考资料中找到，下面来说说在使用这些游标
方式和加锁方式时要注意到的问题。
1。首先要注意到的是这两种方式在混合使用时的问题，就是说你同时设置游标方式和加锁方式。
除非你是在使用Access数据库，一般而言当你混合使用时是并不能够得到你预期想要的游标方式和加锁方式的。例如，如果你同时将游标设置为adOpenStatic方式，而将加锁设置为adLockOptimistic，你将得不到adOpenStatic方式的游标，你这时使用的游标方式将是
adOpenKeyset，也就是说你使用ADO的话，它将返回adOpenKeyset的游标。
2。其次，游标和加锁的混合使用还会导致ADO返回的不是你想要的加锁方式，ADO会改变你的加锁
方式。例如，在默认状态下游标方式是adOpenForwardOnly，在使用这种游标方式的同时如果
你使用的加锁方式为-1（就是让数据源来判断加锁方式）或则adLockReadOnly，那么这种混合方式基本上不支持RecordSet的任何方法，也就是说RecordSet的任何方法将返回False
(你的recordcount,absoultpage,addnew,delete,update等都会返回-1，-1就是表示不支持该属性)，但是这时如果你使用的是adOpenForwardOnly游标方式和其它的加锁方式混合，它反而
会支持填加，删除和更新。

--------------------------------------------------------------------------------------------------------------

SELECT 语句中“加锁选项”的功能说明

SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果。 本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。
功能说明：　
NOLOCK（不加锁）
此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）
此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。

UPDLOCK（修改锁）
此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）
此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）
此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。

TABLOCKX（排它表锁）
此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。

使用这些选项将使系统忽略原先在SET语句设定的事务隔离级别（Transaction Isolation Level)。 请查阅SQL Server 联机手册获取更多信息。
-------------------------------------------------------------------------------------------------------------

1 如何锁一个表的某一行


A 连接中执行

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

begin tran

select * from tablename with (rowlock) where id=3

waitfor delay ’00:00:05’

commit tran

B连接中如果执行

update tablename set colname=’10’ where id=3 --则要等待5秒

update tablename set colname=’10’ where id 3 --可立即执行

2 锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK)


注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK)
其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX)
其他事务不能读取表,更新和删除

select * from table with (..)


SELECT 语句中“加锁选项”的功能说明
SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果。 本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。
功能说明：　
NOLOCK（不加锁）
此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）
此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。

UPDLOCK（修改锁）
此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）
此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）
此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。

TABLOCKX（排它表锁）
此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。

使用这些选项将使系统忽略原先在SET语句设定的事务隔离级别（Transaction Isolation Level)。 请查阅SQL Server 联机手册获取更多信息。

------------------------------------------------------------------------------------------------------------------------

什幺是事务
事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要幺都执行，要幺都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要幺都执行，要幺都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

数据一致性问题
多用户并发存取同一数据将会导致以下的数据不一致性问题。
• 丢失修改（ Lost Update）
在下表中，T1、T2、T3和T4表示顺序的时间。
用户 T 1 T 2 T 3 T 4
A x = 40 X = x-30
B X = 40 X = x-20

假设用户A和B都读取x ( x = 40 ) ,然后分别把x减少30和20。用户A在t3把改后的x ( x = 10 )写入数据库。随后，用户B在t4把改后的x ( x = 20 )写入数据库。于是，对用户A而言，他的修改在t4
处丢失了。
• 脏读数据（ Dirty Read）
请看下表，
用户 T1 T2 T3 T4
A x = 40 X = x 30 X = x - 30 rollback
B X = 70 X = x-20
用户A在t2把x增加30（尚没写入数据库），用户B在t3由数据缓存读出x = 70。但用户A在t4时撤消（Undo）了对x的修改，数据库中仍维持x = 40。但用户B已把改变的数据（ x = 70）取走。
• 不能重复读（Non-Repeatable Read）
用户 T1 T2 T3 T4 T5 T6
A X=40 Y=30 X Y=70 Z=30 X Y Z=100
B x=40 X=X 20 Commit X=x-20
用户A、用户B分别读取x = 40后，在t 3用户A取出y = 30并计算x y = 70。在t4时用户B把x增加20，并于t 5把x ( x = 60 )写入数据库。在t6时，用户A取出z ( z = 30 )并继续计算x y z = 100。但如果用户A为进行核算而把x、y、x重读一次再进行计算，却出现x y z = 120！（x已增加20）。

如何标识一个事务
在SQL Server中，通常事务是指以BEGIN TRAN开始，到ROLLBACK或一个相匹配的COMMIT之间的所有语句序列。ROLLBACK表示要撤消（ U n d o）该事务已做的一切操作，回退到事务开始的状态。COMMIT表示提交事务中的一切操作，使得对数据库的改变生效。
在SQL Server中，对事务的管理包含三个方面：
• 事务控制语句：它使程序员能指明把一系列操作（ Transact - SQL命令）作为一个工作单
位来处理。
• 锁机制（ Locking）：封锁正被一个事务修改的数据，防止其它用户访问到“不一致”的数据。
• 事务日志( Transaction Log)：使事务具有可恢复性。

SQL Server的锁机制
所谓封锁,就是一个事务可向系统提出请求，对被操作的数据加锁( Lock )。其它事务必须等到此事务解锁（ Unlock）之后才能访问该数据。从而，在多个用户并发访问数据库时,确保不互相干扰。可锁定的单位是：行、页、表、盘区和数据库。
1. 锁的类型
SQL Server支持三种基本的封锁类型：共享（ S）锁，排它（X）锁和更新（U）锁。封锁的基本粒度为行。
1) 共享（S）锁：用于读操作。
• 多个事务可封锁一个共享单位的数据。
• 任何事务都不能修改加Ｓ锁的数据。
• 通常是加Ｓ锁的数据被读取完毕，Ｓ锁立即被释放。
2) 独占（Ｘ）锁：用于写操作。
• 仅允许一个事务封锁此共享数据。
• 其它任何事务必须等到Ｘ锁被释放才能对该数据进行访问。
• Ｘ锁一直到事务结束才能被释放。
3) 更新（Ｕ）锁。
• 用来预定要对此页施加Ｘ锁，它允许其它事务读，但不允许再施加Ｕ

锁或Ｘ锁。
• 当被读取数据页将要被更新时，则升级为Ｘ锁。
• Ｕ锁一直到事务结束时才能被释放。
2. 三种锁的兼容性
如下表简单描述了三种锁的兼容性：
通常，读操作（SELECT）获得共享锁，写操作（ INSERT、DELETE）获得独占锁；而更新操作可分解为一个有更新意图的读和一个写操作，故先获得更新锁，然后再升级为独占锁。
执行的命令 获得锁 其它进程可以查询？ 其它进程可以修改？
Select title_id from titles S Yes No
delete titles where price>25 X No No
insert titles values( ...) X No No
update titles set type=“general” U Yes No
where type=“business” 然后Ｘ NO No

使用索引降低锁并发性
我们为什幺要讨论锁机制？如果用户操作数据时尽可能锁定最少的数据，这样处理过程,就不会等待被锁住的数据解锁，从而可以潜在地提高SQL Server的性能。如果有200个用户打算修改不同顾客的数据，仅对存储单个顾客信息的单一行进行加锁要比锁住整个表好得多。那幺，用户如何只锁定行而不是表呢？当然是使用索引了。正如前面所提到的，对存有要修改数据的字段使用索引可以提高性能，因为索引能直接找到数据所在的页面，而不是搜索所有的数据页面去找到所需的行。如果用户直接找到表中对应的行并进行更新操作，只需锁定该行即可，而不是锁定多个页面或者整个表。性能的提高不仅仅是因为在修改时读取的页面较少，而且锁定较少的页面潜在地避免了一个用户在修改数据完成之前其它用户一直等待解锁的情况。

事务的隔离级别
ANSI标准为SQL事务定义了4个隔离级别(isolation level)，隔离级别越高,出现数据不一致性的可能性就越小(并发度也就越低)。较高的级别中包含了较低级别中所规定了的限制。
• 隔离级别0：防止“丢失修改”，允许脏读。
• 隔离级别1：防止脏读。允许读已提交的数据。
• 隔离级别2：防止“不可重复读”。
• 隔离级别3：“可串行化”（serializable）。其含义为，某组并行事务的一种交叉调度产生的结果和这些事务的某一串行调度的结果相同（可避免破坏数据一致性）。SQL Server支持四种隔离级别，级别1为缺省隔离级别，表中没有隔离级别2， 请参考表：
SQL Server支持的隔离级别 封锁方式 数据一致性保证
X锁施加于被修改的页 S锁施加于被读取的页 防止丢失修改 防止读脏数据 可以重复读取
级别0 封锁到事务结束 是
级别1（缺省） 封锁到事务结束 读后立即释放 是 是
级别3 封锁到事务结束 封锁到事务结束 是 是 是
在SQL Server也指定级别2，但级别3已包含级别2。ANSI-92 SQL中要求把级别3作为所有事务的缺省隔离级别。
SQL Server用holdlock选项加强S锁的限制，实现隔离级别3。SQL Server的缺省隔离级别为级别1，共享读锁（S锁）是在该页被读完后立即释放。在select语句中加holdlock选项，则可使S锁一直保持到事务结束才释放。她符合了ANSI隔离级别3的标准─“可串行化”。

下面这个例子中，在同一事务中对avg ( advance )要读取两次，且要求他们取值不变─“可重复读”，为此要使用选项holdlock。
BEGIN tran
DECLARE @avg-adv money
SELECT @avg-adv = avg(advance)
FROM titles holdlock
WHERE type = “business“
if @avg-adv > 5000
SELECT title from titles
WHERE type=“business“ and advance >@avg_adv
COMMIT tran
在SQL Server中设定事务隔离级别的方法有三种：

• 会话层设定
语法如下：
SET TRANSACTION ISOLATION LEVEL
{
READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
系统提供的系统存储过程将在级别1下执行，它不受会话层设定的影响。
• 语法层设定
在SELECT、DECLARE cursor及read text语句中增加选项。比如：
SELECT...at isolation{0|read uncommitted}
注意：语法层的设定将替代会话层的设定。
• 利用关键词设定
─在SELECT语句中，加选项holdlock则设定级别3
─在SELECT语句中，加noholdlock则设定级别0

如下程序清单中所列的脚本实例在authors表上持有一个共享锁，它将用户检查服务器当前活动的时间推迟两分钟。
程序清单测试事务隔离等级
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
SELECT *
FROM authors
WHERE au_lname = ’Green’
WAITFOR DELAY ’00:02:00’
ROLLBACK TRAN
GO
Activity Legend(活动图标)表明：当SQL Server检索数据时会去掉页面表意向锁。Current Activity窗口(见图3 - 3 )显示共享锁一直被保持直到事务完成为止(也就是说，直到WAITFOR和ROLLBACK TRAN语句完成)。
使用锁定优化程序提示
让我们再深入考察程序清单的实例。通过改变优化程序提示，用户可以令SQL Server在authors表上设置一个独占表锁(如程序所示)。
BEGIN TRAN
SELECT *
FROM authors (tablockx)
WHERE au_lname = ’Green’
WAITFOR DELAY ’00:02:00’
ROLLBACK TRAN
GO

SELECT语句使用优化程序提示tablockx来保持独占表锁直到事务结束为止。下表显示了可用的锁定优化程序提示。
锁定优化程序提示及其描述
优化程序提示 优化程序提示描述
holdlock 保持锁定直到事务结束
nolock 检索数据时不使用锁
paglock 使用页面锁
tablock 使用表锁
tablockx 使用独占表锁
updlock 使用更新锁
holdlock优化程序提示能够在整个事务期间保持共享锁，读者在可串行化和可重复读事务隔离等级中对此已很熟悉了。如果用户偶尔想使用共享锁，最好使用系统默认的读交付事务隔离等级并需要使用holdlock优化程序提示。holock优化程序提示与读不交付事务隔离等级有相同的功能，它通过在读数据时不要任何锁定而实现非交付数据的读操作(从而避免了任何独占锁定引起的阻隔)。使用索引和锁定优化程序提示需要注意的是：用户可以将这两种类型的提示结合起来使
用，但必须将索引提示最后列出，这一点很重要。如下程序清单中的代码给出了合法优化程序提示的正确方法。如一个混合优化程序提示
SELECT *
FROM authors (paglock holdlock index=aunmind)
--------------------2003. 4, draf by Wally--------------------------------
-------------------------------------------------------------------------------------------------------------------------
记录集游标和锁定类型

可以用四种类型的游标打开一个记录集。游标决定了可以对一个记录集进行什么操作，游标还决定了其他用户可以对一个记录集进行什么样的改变。下面列出了游标的不同类型和限制：

一、AdOpenFowardOnly：使用前向游标，只能在记录集中向前移动。
二、AdOpenKeyset：使用Keyset游标，可以在记录集中向前或向后移动。如果另一个用户删除或改变了一条记录，记录集中将反映这个变化。但是，如果另一个用户添加了一条新记录，新记录不会出现在记录集中。
三、AdOpenDynamic：使用动态游标，可以在记录集中向前或向后移动。其他用户造成的记录的任何变化都将在记录集中有所反映。
四、AdOpenStatic：使用静态游标，可以在记录集中向前或向后移动。但是，静态游标不会对其他用户造成的记录变化有所反映。

下面是记录集的四种锁定方法：
一、AdLockReadOnly：指定不能修改记录集中的记录。
二、AdLockPessimistic：指定在编辑一个记录时，立即锁定它。
三、AdLockOptimstic：指定只有调用记录集的Update方法时，才锁定记录。
四、AdLockBatchOptimstic：指定记录只能成批地更新。
在缺省情况下，记录集使用只读锁定。

-----------------------------------------------------------------------------------------------

为什么 SQL Server的编程语言叫做 T-SQL(transact-SQL)？

如果不使用begin tran、commit tran等语句，那么每一条SQL语句也会自动工作在一个事务中。
----------------------------------------------------------------------------------------------