关于数据库锁的使用

--设tb(A,B,C)
create table #tb(A varchar(2),B varchar(2),C varchar(2))
insert into #tb  select 'a1','b1','c1'  union all select 'a2','b2','c2'  union all select 'a3','b3','c3'

--1）排它锁

--在第一个连接中执行以下语句
begin tran
   update #tb    set A='aa'    where B='b2' 
   waitfor delay '00:00:30'  --等待30秒
commit tran

--在第二个连接中执行以下语句
begin tran
   select * from #tb   where B='b2'   
commit tran
--若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

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

--在第二个连接中执行以下语句
begin tran
   select A,C from #tb    where B='b2' 
   update #tb    set A='aa'    where B='b2'   
commit tran
--若同时执行上述两个语句，则第二个连接中的select查询可以执行
--而update必须等待第一个连接中的共享锁结束后才能执行 即要等待30秒

--3）死锁
--增设tb2(D,E)
create table #tb2(D varchar(2),E varchar(2))
insert into #tb2  select 'd1','e1'  union all select 'd2','e2' 
--在第一个连接中执行以下语句
begin tran
   update #tb    set A='aa'    where B='b2' 
   waitfor  delay '00:00:5'
   update #tb2    set D='d5'    where E='e1' 
commit tran
   
--在第二个连接中执行以下语句
begin tran
   update #tb2     set D='d5'    where E='e1' 
   waitfor  delay '00:00:3'
   update #tb     set A='aa'    where B='b2'  
commit tran

--删除临时表
drop table #tb,#tb2

--同时执行，系统会检测出死锁，并中止进程
/*-------------------------------------------------------------
SET IMPLICIT_TRANSACTIONS  ON --用户每次必须显式提交或回滚。否则当用户断开连接时,
                              --事务及其所包含的所有数据更改将回滚

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

------------------------------------------------------------------------*/

--手工加锁，锁定记录,只允许单用户修改的例子:

--创建测试环境
--创建测试表--部门表
create table 部门(departmentid int,name varchar(10))

--记录锁定表
create table lock(departmentid int,dt datetime)

go
--因为函数中不可以用getdate,所以用个视图,得到当前时间
create view v_getdate as select dt=getdate()
go
--创建自定义函数,判断记录是否锁定
create function f_chk(@departmentid int)
returns bit
as
begin
declare @re bit,@dt datetime
select @dt=dt from v_getdate
if exists(select 1 from lock where departmentid=@departmentid
and datediff(ss,dt,@dt)<5)
set @re=1
else
set @re=0
return(@re)
end
go

--数据处理测试
if dbo.f_chk(3)=1
print '记录被锁定'
else
begin
begin tran
insert into lock values(3,getdate())
update 部门 set name='A' where departmentid=3
delete from lock where departmentid=3
commit tran
end

--删除测试环境
drop table 部门
drop view v_getdate
drop function f_chk

总结：排他锁、共享锁都是存在于事务整个回话中，对表insert，update，delete产生排他锁，对表select产生共享锁。排他锁不允许其他会话对表做任何操作，共享锁时允许对表读但不允许写。在使用时一定要注意将产生锁的类型，避免死锁。手工加锁代价加大，但是相当灵活，可以完成复杂的逻辑处理。另外可以参考http://technet.microsoft.com/zh-cn/library/ms173763.aspx，理解关于事务级别的设置。