关于数据库锁的使用

Love The Way You Lie 2023-10-04 13:14 17阅读 0赞

\--设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，理解关于事务级别的设置。