数据库加锁问题解协

数据库加锁问题解协

2009年01月07日 星期三 下午 12:02

设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 --自动提交模式。在自动提交模式下，如果各个语句成功                                --完成则提交。    --自己做编号,防止冲突的处理 --处理示例1(用主键/唯一键) --得到最新编号的函数 create function f_newid() returns char(7) --编号位数固定,用char的检索效率高于varchar as begin declare @re char(7) select @re=max(BHID) from 表 return(    case when @re is null then 'BH00001'     else 'BH'+right('0000'+cast(cast(right(@re,5) as int)+1 as varchar),5)    end) end go --测试的表,表名与函数中的表名对应 create table 表( BHID char(7) default dbo.f_newid() --设置默认值,自动生成编号 primary key, --设置成主键,防止编号冲突 -- constraint UNIQUE_BHID_表 unique, --如果用唯一约束,则删除上面的主键约束语句,改用此句 txt1 varchar(10), txt2 varchar(10), num float) go /*--插入数据时,就可以不理会编号字段,直接用这样的语句 多用户同时插入时,如果编号重复,就会有错误发生 此时,前台程序拦截错误,如果是违反约束的错误 只需要重新执行插入的语句即可,此时的编号会自动再重新生成 而重新执行插入语句也很方便,因为根本就不需要改语句 --*/ insert 表(txt1,txt2,num) values('aa','bb',1) go --删除测试 drop table 表 drop function f_newid   --处理示例2(用表级锁) --得到最新编号的函数 create function f_newid() returns char(7) --编号位数固定,用char的检索效率高于varchar as begin declare @re char(7) select @re=max(BHID) from 表(tablockx) --加表级锁 return(    case when @re is null then 'BH00001'     else 'BH'+right('0000'+cast(cast(right(@re,5) as int)+1 as varchar),5)    end) end go --测试的表,表名与函数中的表名对应 create table 表( BHID char(7) default dbo.f_newid() --设置默认值,自动生成编号 txt1 varchar(10), txt2 varchar(10), num float) go --用事务/配合函数中的锁,就可以实现在插入时锁定表 begin tran insert 表(txt1,txt2,num) values('aa','bb',1) commit tran go --删除测试 drop table 表 drop function f_newid   /*--关于两种方法: 设置主键或唯一键的处理效率是最高的,因为在处理数据的同时,其他用户还可以浏览数据, 如果不用主键或唯一键,就要用表级锁,而用表级锁,在锁定时,其他用户是不能做任何操作的,这样在多用户环境下,进程肯定容易堵塞,造成千军万马过独木桥的情况. --*/