--参考,自己设计锁,锁定记录,只允许单用户修改的例子:--创建测试环境 --创建测试表--部门表 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(minute,dt,@dt)<5) --锁的超时时间为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
--手工锁+SQL的锁机制处理示例--处理示例 http://expert.csdn.net/Expert/topic/2921/2921334.xml?temp=.2235376--在表中增加几个字段: read_time --读取记录的时间 host_name --读取记录的电脑名称 read_user --读取记录的操作员 --在 CSDNM 的基础上修改(结合两种方法)后的示例create proc 提取记录 @read_user char(20) ---审核者 as declare @id intBEGIN TRANSACTION --从本机,本人读取的记录中取 select top 1 @id=id from tablename with (UPDLOCK) where 某字段=2 and [host_name]=host_name() and read_user=@read_user order by time if @@rowcount=0 --如果没有,从没有被读取的记录中读取 begin select top 1 @id=id from tablename with (UPDLOCK) where 某字段=2 and(host_name is null or read_user is null) order by time if @@rowcount=0 --如果没有,从超时的记录中读取 begin select top 1 @id=id from tablename with (UPDLOCK) where 某字段=2 and read_time<datediff(hout,-1,getdate()) --假设超时时间为1小时 order by time if @@rowcount=0 --无记录退出 begin commit tran return end end end update tablename set read_user=@read_user, [host_name]=host_name(), read_time=@read_time where id=@id commit transaction select * from tablename where id=@id go
--创建测试表--部门表
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(minute,dt,@dt)<5) --锁的超时时间为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
http://expert.csdn.net/Expert/topic/2921/2921334.xml?temp=.2235376--在表中增加几个字段:
read_time --读取记录的时间
host_name --读取记录的电脑名称
read_user --读取记录的操作员
--在 CSDNM 的基础上修改(结合两种方法)后的示例create proc 提取记录
@read_user char(20) ---审核者
as
declare @id intBEGIN TRANSACTION
--从本机,本人读取的记录中取
select top 1 @id=id from tablename with (UPDLOCK)
where 某字段=2
and [host_name]=host_name()
and read_user=@read_user
order by time if @@rowcount=0 --如果没有,从没有被读取的记录中读取
begin
select top 1 @id=id from tablename with (UPDLOCK)
where 某字段=2
and(host_name is null
or read_user is null)
order by time if @@rowcount=0 --如果没有,从超时的记录中读取
begin
select top 1 @id=id from tablename with (UPDLOCK)
where 某字段=2
and read_time<datediff(hout,-1,getdate()) --假设超时时间为1小时
order by time if @@rowcount=0 --无记录退出
begin
commit tran
return
end
end
end update tablename set
read_user=@read_user,
[host_name]=host_name(),
read_time=@read_time
where id=@id
commit transaction
select * from tablename where id=@id
go