数据库加锁操作.doc

例： begin tran insert 表TABLOCKX -加锁 (字段列表) values(值列表) commit tran其他锁的示例 -设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 -等待3秒 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:3 -等待3秒 commit tran -在第二个连接中执行以下语句 begin tran select A,C from tb where B=b2 update tb set A=aa where B=b2 commit tran -若同时执行上述两个语句，则第二个连接中的select查询可以执行 -而update必须等待第一个连接中的共享锁结束后才能执行 即要等待3秒 -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 -同时执行，系统会检测出死锁，并中止进程 /*- SET IMPLICIT_TRANSACTIONS ON -用户每次必须显式提交或回滚。否则当用户断开连接时, -事务及其所包含的所有数据更改将回滚 SET IMPLICIT_TRANSACTIONS OFF -自动提交模式。在自动提交模式下，如果各个语句成功 -完成则提交。 1.问：有什么样的办法让几个程序同时调用同一个或者不同存贮过程同时更新数据表的同一行的不同字段时互不干扰各完成各的操作? A: sqlserver2000默认的lock的粒度是行级，所以如果一个线程在update一条记录时，就在该行加了排他锁，所以其它的线程是无法读取该记录（除非可以脏读），这是因为在sqlserver中是不可以同时给一条记录加不同的锁。另外sqlserver没有锁某一列的锁！ 所以让几个程序同时调用同一个或者不同存贮过程同时更新数据表的同一行的不同字段时如果一个在更新，其他的就只能WAIT. 2. 如何锁一个表的某一行 A 连接中执行 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ begin tran select * from tablename with (rowlock) where id=3 waitfor delay 00:00:05 commit tran B连接中如果执行 update tablename set colname=10 where id=3 -则要等待5秒 update tablename set colname=10 where id3 -可立即执行 3. 锁定数据库的一个表 SELECT * FROM table WITH (HOLDLOCK) 注意: 锁定数据库的一个表的区别 SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除 SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除 SELECT 语句中“加锁选项”的功能说明 SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果。SELECT语句中的各项“加锁选项”以及相应的功能说明。 功能说明： NOLOCK（不加锁） 此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。 HOLDLOCK（保持锁） 此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。 UPDLOCK（修改锁） 此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。 TABLOCK（表锁） 此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。 PAGLOCK（页锁） 此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。 TABLOCKX（排它表锁） 此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。4.程序沒有碩操作的但是今天發現某行無法select,別的行都可以。 在管理-目前活動-鎖定/處理序識別碼看見有被封鎖和封鎖。 不知道怎么開鎖。 use master go create proc killspid (dbname varchar(20) as begin declare sql nvarchar(500) declare spid int set sql=declare getspid cursor for select spid from sysprocesses where dbid=db_id(+dbname+) exec (sql) open getspid fetch next from getspid into spid while fetch_status -1 begin exec(kill +rtrim(spid) fetch next from getspid into spid end close getspid deallocate getspid end -用法 use master exec killspid 数据库名 5.-查看锁信息 select 进程id=req_spid ,数据库=db_name(rsc_dbid) ,类型=case rsc_type when 1 then NULL 资源（未使用） when 2 then 数据库 when 3 then 文件 when 4 then 索引 when 5 then 表 when 6 then 页 when 7 then 键 when 8 then 扩展盘区 when 9 then RID（行 ID) when 10 then 应用程序 end ,rsc_objid,rsc_indid from master.syslockinfo 6.实现一个特定的操作，大概是这样的： 在SQL SERVER里实现对某个特定的表进行操作锁定，应用程序要对这个表的数据进行删除操作，必须经过审核（注：只想在SQL SERVER里控制，不想用应用程序实现）。 用触发器 create trigger . for delete as if exists(select 1 from deleted where 未审核) rollback 5.在SQL SERVER里二张一样的表，做了个TRIGGERE,可以将同样的数据复制的那一表，在此将源表叫做A，被TRIGGER的表叫做B，A被一套程序使用,B被另一个程序使用，当B被程序访问时（读取），A表正好有问题要INSERT或UPDATE，此时TRIGGER起作用但写不进B表，造成访问A表的程序不正常，请问有什么方法可以解决吗！？ 用手工锁定就行了.类似下面的例子: -锁定记录,只允许单用户修改的例子: -创建测试环境 -创建测试表-部门表 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 即创建一个锁表,在B表被访问时,添加一个记录到锁表中,如果A表发生更新,首先判断锁表的内容,如果被锁定,则触发器失败. a.创建锁表 create table 锁表(lock bit,dt datetime) b.B表被访问时: insert into 锁表 values(1,getdate() 访问B表. 访问B表结束 truncate talbe 锁表 c.A表的触发器 create trigger t_process on A表 for insert,delete,update as if exists(select 1 from 锁表 where dat