SQL 锁
// ----------------------------------------------------------------------------
//
// File: 1.txt
// Creation Date: 21/04/13
// Last Modified: 21/04/13
// Purpose:
//
// ----------------------------------------------------------------------------
SQL锁表语句 收藏
2009-08-17 12:18:55| 分类： 默认分类 | 标签： |字号大中小 订阅
SQL锁表语句 收藏
锁定数据库的一个表
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 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。
HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别
NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别
PAGLOCK 在使用一个表锁的地方用多个页锁
READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁
ROWLOCK 强制使用行锁
TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表
UPLOCK 强制在读表时使用更新而不用共享锁
注意: 锁定数据库的一个表的区别
SELECT * FROM table WITH (HOLDLOCK) 其他事务/语句可以读取表，但不能更新删除
SELECT * FROM table WITH (TABLOCKX) 其他事务/语句不能读取表,更新和删除
例子：
begin tran
select * from test_table with (TABLOCKX) //表锁
commit tran
这时，其它语句，比如select * from test_table将只能等待
不同的数据库，多版本的实现机制不同，语句执行情况也就不一样，下面以oracle为例说明：
1.insert/delete语句可以并发执行，不会锁等待
2.并发insert不会锁等待
3.并发update，如果不是操作同一条记录，不会锁等待
SQL锁表语句
锁定数据库的一个表
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 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。
HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别
NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别
PAGLOCK 在使用一个表锁的地方用多个页锁
READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁
ROWLOCK 强制使用行锁
TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表
UPLOCK 强制在读表时使用更新而不用共享锁
注意: 锁定数据库的一个表的区别
SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除
SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除
如何将数据库中被锁表解锁-数据库专栏,SQL Server
发布时间：2007-12-25 21:39:12 来源： 作者： 点击：1041
我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，不知怎么给这些表解锁，在pl/sql developer工具的的菜单“tools”里面的“sessions”可以查询现在存在的会话，但是我们很难找到那个会话被锁定了，想找到所以被锁的会话就更难了，下面这叫查询语句可以查询出所以被锁的会话。如下：
select sn.username, m.sid,sn.serial#, m.type, decode (m.lmode, 0, none, 1, null, 2, row share, 3, row excl., 4, share, 5, s/row excl., 6, exclusive, lmode, ltrim (to_char (lmode, ArrayArray0)) ) lmode, decode (m.request, 0, none, 1, null, 2, row share, 3, row excl., 4, share, 5, s/row excl., 6, exclusive, request, ltrim (to_char (m.request, ArrayArray0)) ) request, m.id1, m.id2 from v$session sn, v$lock m where (sn.sid = m.sid and m.request != 0) --存在锁请求，即被阻塞 or ( sn.sid = m.sid --不存在锁请求，但是锁定的对象被其他会话请求锁定 and m.request = 0 and lmode != 4 and (id1, id2) in ( select s.id1, s.id2 from v$lock s where request != 0 and s.id1 = m.id1 and s.id2 = m.id2) )order by id1, id2, m.request;
通过以上查询知道了sid和 serial#就可以开杀了 alter system kill session sid,serial#;
查看锁表的SQL:
select distinct s.SID,s.SERIAL#,a.owner,a.object_name,q.SQL_TEXT,s.paddr,p.SPID
from v$locked_object l,all_objects a,v$session s,v$sql q,v$process p
where l.OBJECT_ID=a.object_id
and s.SID=l.SESSION_ID
and q.ADDRESS=s.PREV_SQL_ADDR
and s.PADDR=p.ADDR
查看谁锁表SQL
分类： oracle 2008-02-22 16:42 683人阅读 评论(0) 收藏 举报
杀掉查找出的进程必须有sys权限
法一：SELECT "SYS"."V_$LOCKED_OBJECT"."SESSION_ID" ,
"SYS"."V_$LOCKED_OBJECT"."ORACLE_USERNAME" ,
"SYS"."V_$LOCKED_OBJECT"."OS_USER_NAME" ,
"SYS"."OBJ$"."NAME"
FROM "SYS"."V_$LOCKED_OBJECT" ,
"SYS"."OBJ$"
WHERE ( "SYS"."V_$LOCKED_OBJECT"."OBJECT_ID" = "SYS"."OBJ$"."OBJ#" )
ORDER BY "SYS"."V_$LOCKED_OBJECT"."ORACLE_USERNAME" ASC ;
---------------------------------------------------------
法二：SELECT substr(to_char(l.sid),1,4) "SID",
substr(s.type,1,1) "B/U",
P.spid "SRVR PID",
s.process "CLNT PID",
substr(s.machine,1,7) "MACHINE",
l.type,
DECODE(L.TYPE,'MR','File_ID: '||L.ID1,
'TM', LO.NAME,
'TX','USN: '||to_char(TRUNC(L.ID1/65536))||' RWO: '||nvl(RWO.NAME,'None'),
L.ID1)
LOCK_ID1,
decode(l.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
substr(to_char(l.lmode),1,13)) "Locked Mode",
decode(l.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
substr(to_char(l.request),1,13)) "Requested",
l.ctime,
l.block
FROM v$process P,
v$session S,
v$lock l,
sys.obj$ lo,
sys.obj$ rwo
WHERE l.type != 'MR' AND l.sid = S.sid (+)
AND S.paddr = P.addr (+)
AND LO.OBJ#(+) = L.ID1
AND RWO.OBJ#(+) = S.ROW_WAIT_OBJ#
order by l.sid;
oracle表分区总结
分类： oracle 2008-02-22 16:46 188人阅读 评论(0) 收藏 举报
Oracle中提供了对表进行分区的机制，通过表分区，可以将表空间中数据按照某种方式分别存放到特定的分区中。表分区的作用：平衡IO操作，分区均匀，提高效率。
Oracle中表分区方法有：范围分区法、散列分区法、复合分区法、列表分区法。
范围分区:语法 Partition by range(); 适合数值型或日期型
示例：
1 create table Student
2(
3 Studentid integer not null,
4 Studentname varchar2(20),
5 Score integer
6)
7 Partition by range(Score)
8(
9 Partition p1 values less than(60),
10 Partition p2 values less than(75),
11 Partition p3 values less than(85),
12 Partition p4 values less than(maxvalue)
13 );
散列分区法：根据Oracle内部散列算法存储，语法 Partition by hash();
实例：
1 create table department
2 (
3 Deptno int,
4 Deptname varchar2(24)
5 )
6 Partition by hash(deptno)
7 (
8 Partition p1,
9 Partition p2
10 );
复合分区法：由上面两种方法复合而成
示例：
1 create table salgrade
2 (
3 grade number,
4 losal number,
5 hisal number
6 )
7 Partition by range(grade)
8 Subpartition by hash(losal,hisal)
9 (
10 Partition p1 values less than(10)
11 (subpartition sp1,subpartition sp2),
12 Partition p2 values less than(20)
13 (subpartition sp3,subpartition sp4)
14 )
列表分区法：适合字符型 语法Partition by list()
实例：
1 create table customer
2 (
3 custNo int,
4 custname varchar(20),
5 custState varchar(20)
6 )
7 Partition by list(custState)
8 (
9 Partition saia values('中国','韩国','日本'),
10 Partition Europe values('英国','俄国','法国'),
11 Partition ameria values('美国','加拿大','墨西哥'),
12 );
13
表分区维护：
添加分区:alter table student add partition p5 values less than(120);
删除分区:alter table student drop partition p4;
截断分区:alter table student truncate partition p5;
合并分区:alter table student merge partitions p3,p4 into partition p6;
Sql Server 查看锁表
--查看锁信息
create table #t(req_spid int,obj_name sysname)
declare @s nvarchar(4000)
,@rid int,@dbname sysname,@id int,@objname sysname
declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb
select 进程id=a.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
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid
where db_name(rsc_dbid)='DCentreCHQ'
go
drop table #t
ORACLE UPDATE 语句语法与性能分析
分类： oracle 2008-02-22 16:48 129人阅读 评论(0) 收藏 举报
为了方便起见,建立了以下简单模型,和构造了部分测试数据:
在某个业务受理子系统BSS中，
--客户资料表
create table customers
(
customer_id number(8) not null, -- 客户标示
city_name varchar2(10) not null, -- 所在城市
customer_type char(2) not null, -- 客户类型
...
)
create unique index PK_customers on customers (customer_id)
由于某些原因，客户所在城市这个信息并不什么准确，但是在
客户服务部的CRM子系统中，通过主动服务获取了部分客户20%的所在
城市等准确信息，于是你将该部分信息提取至一张临时表中：
create table tmp_cust_city
(
customer_id number(8) not null,
citye_name varchar2(10) not null，
customer_type char(2) not null
)
1) 最简单的形式
--经确认customers表中所有customer_id小于1000均为'北京'
--1000以内的均是公司走向全国之前的本城市的老客户:)
update customers
set city_name='北京'
where customer_id<1000
2) 两表(多表)关联update -- 仅在where字句中的连接
--这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别
update customers a -- 使用别名
set customer_type='01' --01 为vip，00为普通
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
3) 两表(多表)关联update -- 被修改值由另一个表运算而来
update customers a -- 使用别名
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
-- update 超过2个值
update customers a -- 使用别名
set (city_name,customer_type)=(select b.city_name,b.customer_type
from tmp_cust_city b
where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
注意在这个语句中，
=(select b.city_name,b.customer_type
from tmp_cust_city b
where b.customer_id=a.customer_id
)
与
(select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
是两个独立的子查询，查看执行计划可知，对b表/索引扫描了2篇；
如果舍弃where条件，则默认对A表进行全表
更新，但由于(select b.city_name from tmp_cust_city b where where b.customer_id=a.customer_id)
有可能不能提供"足够多"值，因为tmp_cust_city只是一部分客户的信息，
所以报错（如果指定的列--city_name可以为NULL则另当别论）：
01407, 00000, "cannot update (%s) to NULL"
// *Cause:
// *Action:
一个替代的方法可以采用：
update customers a -- 使用别名
set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)
或者
set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),'未知')
-- 当然这不符合业务逻辑了
4) 上述3)在一些情况下，因为B表的纪录只有A表的20-30%的纪录数，
考虑A表使用INDEX的情况，使用cursor也许会比关联update带来更好的性能：
set serveroutput on
declare
cursor city_cur is
select customer_id,city_name
from tmp_cust_city
order by customer_id;
begin
for my_cur in city_cur loop
update customers
set city_name=my_cur.city_name
where customer_id=my_cur.customer_id;
/** 此处也可以单条/分批次提交，避免锁表情况 **/
-- if mod(city_cur%rowcount,10000)=0 then
-- dbms_output.put_line('----');
-- commit;
-- end if;
end loop;
end;
5) 关联update的一个特例以及性能再探讨
在oracle的update语句语法中，除了可以update表之外，也可以是视图，所以有以下1个特例：
update (select a.city_name,b.city_name as new_name
from customers a,
tmp_cust_city b
where b.customer_id=a.customer_id
)
set city_name=new_name
这样能避免对B表或其索引的2次扫描，但前提是 A(customer_id) b(customer_id)必需是unique index
或primary key。否则报错：
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
// map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.
6）oracle另一个常见错误
回到3)情况,由于某些原因，tmp_cust_city customer_id 不是唯一index/primary key
update customers a -- 使用别名
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
当对于一个给定的a.customer_id
(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
返回多余1条的情况，则会报如下错误：
01427, 00000, "single-row subquery returns more than one row"
// *Cause:
// *Action:
一个比较简单近似于不负责任的做法是
update customers a -- 使用别名
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
如何理解 01427 错误，在一个很复杂的多表连接update的语句，经常因考虑不周，出现这个错误，
仍已上述例子来描述，一个比较简便的方法就是将A表代入 值表达式 中,使用group by 和
having 字句查看重复的纪录
(select b.customer_id,b.city_name,count(*)
from tmp_cust_city b,customers a
where b.customer_id=a.customer_id
group by b.customer_id,b.city_name
having count(*)>=2
)
浙公网安备 33010602011771号