关于数据库的锁的使用

Question

问题2：
同时执行
     
  A操作: 
        BEGIN TRAN Trans         Insert Into T1 Values(22,2)  
        waitfor DELAY '00:00:10'
 
        COMMIT TRAN Trans
    与  B操作: 
      select * from T1 where Col1=4 或者      select * from T1 则操作B要等10S后才能执行？ 怎么通过LOCK来控制当INSERT 一个表的时候，此表不带WITH（NOLOCK）而可以读取？

Answer 1

/**********  加锁   ***************
设table1(A,B,C)
A    B    C
a1   b1   c1
a2   b2   c2
a3   b3   c31）排它锁
新建两个连接
在第一个连接中执行以下语句
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 --自动提交模式。在自动提交模式下，如果各个语句成功
                               --完成则提交。

Answer 2

问题3：
 对单表的加锁，可以    SELECT * FROM T1 WITH (HOLDLOCK)    其他事务可以读取表，但不能更新删除
    SELECT * FROM T2  WITH (TABLOCKX)   其他事务不能读取表、更新、删除   怎么的加锁方式，可以满足当我执行   select * from T1 where col1=1 操作的时候，
    对此一行记录不再允许读、写操作，但是对此表的其他记录，能照常的进行读、写操作？ 即对单行记录的加锁问题

Answer 3

txlicenhe(不做技术高手) 的动作蛮快的
 能不能针对我的具体问题做回答呢！？ 谢谢了！

Answer 4

TRY:
  A操作: 
        BEGIN TRAN Trans         update    T1  with (XLOCK)  set col1=22 where col1=2 --行锁
 
        waitfor DELAY '00:00:10'
 
        COMMIT TRAN Trans
    与  B操作:       select * from T1 where Col1=4

Answer 5

CrazyFor(蚂蚁) 说的不行！ 正想请教为什么 
  SELECT * FROM t1 WITH (XLOCK)  where Col1=13  后
 再执行 SELECT * FROM t1 ，此是后一条命令就执行不了——一直等锁，怎么释放前一个Xlock  的加锁

Answer 6

执行commit tran或者rollback tran 
自动就释放了xlock

Answer 7

to 楼上：
 
 问题是我没有显式的使用transaction  
  期盼有人为我解答以上问题——特别是UPDAT/INSERTE时候怎么只锁行而不锁表的问题

Answer 8

建议干脆去Foxpro讨论区讨论！

Answer 9

期盼有人为我解答以上问题——特别是UPDAT/INSERTE时候怎么只锁行而不锁表的问题

Answer 10

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中设定事务隔离级别的方法有三种：

Answer 11

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