ORACLE锁管理大全.docx
- 文档编号:10951731
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:18
- 大小:23.15KB
ORACLE锁管理大全.docx
《ORACLE锁管理大全.docx》由会员分享,可在线阅读,更多相关《ORACLE锁管理大全.docx(18页珍藏版)》请在冰豆网上搜索。
ORACLE锁管理大全
ORACLE锁的管理
锁概念基础
数据库是一个多用户使用的共享资源。
当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。
若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。
加锁是实现数据库并发控制的一个非常重要的技术。
当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。
加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。
在数据库中有两种基本的锁类型:
排它锁(ExclusiveLocks,即X锁)和共享锁(ShareLocks,即S锁)。
当数据对象被加上排它锁时,其他的事务不能对它读取和修改。
加了共享锁的数据对象可以被其他事务读取,但不能修改。
数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
Oracle数据库的锁类型
根据保护的对象不同,Oracle数据库锁可以分为以下几大类:
DML锁(datalocks,数据锁),用于保护数据的完整性;DDL锁(dictionarylocks,字典锁),用于保护数据库对象的结构,如表、索引等的结构定义;内部锁和闩(internallocksandlatches),保护数据库的内部结构。
DML锁的目的在于保证并发情况下的数据完整性,。
在Oracle数据库中,DML锁主要包括TM锁和TX锁,其中TM锁称为表级锁,TX锁称为事务锁或行级锁。
当Oracle执行DML语句时,系统自动在所要操作的表上申请TM类型的锁。
当TM锁获得后,系统再自动申请TX类型的锁,并将实际锁定的数据行的锁标志位进行置位。
这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志,而只需检查TM锁模式的相容性即可,大大提高了系统的效率。
TM锁包括了SS、SX、S、X等多种模式,在数据库中用0-6来表示。
不同的SQL操作产生不同类型的TM锁。
在数据行上只有X锁(排他锁)。
在Oracle数据库中,当一个事务首次发起一个DML语句时就获得一个TX锁,该锁保持到事务被提交或回滚。
当两个或多个会话在表的同一条记录上执行DML语句时,第一个会话在该条记录上加锁,其他的会话处于等待状态。
当第一个会话提交后,TX锁被释放,其他会话才可以加锁。
当Oracle数据库发生TX锁等待时,如果不及时处理常常会引起Oracle数据库挂起,或导致死锁的发生,产生ORA-60的错误。
这些现象都会对实际应用产生极大的危害,如长时间未响应,大量事务失败等。
悲观封锁和乐观封锁
一、悲观封锁
锁在用户修改之前就发挥作用:
Select..forupdate(nowait)
Select*fromtab1forupdate
用户发出这条命令之后,oracle将会对返回集中的数据建立行级封锁,以防止其他用户的修改。
如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。
1:
对返回结果集进行update或delete操作会发生阻塞。
2:
对该表进行ddl操作将会报:
Ora-00054:
resourcebusyandacquirewithnowaitspecified.
原因分析
此时Oracle已经对返回的结果集上加了排它的行级锁,所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放,产生的外在现象就是其他的操作将发生阻塞,这个这个操作commit或rollback.
同样这个查询的事务将会对该表加表级锁,不允许对该表的任何ddl操作,否则将会报出ora-00054错误:
:
resourcebusyandacquirewithnowaitspecified.
二、乐观封锁
乐观的认为数据在select出来到update进取并提交的这段时间数据不会被更改。
这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定,是存在一种可能被其他用户更改的可能。
因此Oracle仍然建议是用悲观封锁,因为这样会更安全。
阻塞
定义:
当一个会话保持另一个会话正在请求的资源上的锁定时,就会发生阻塞。
被阻塞的会话将一直挂起,直到持有锁的会话放弃锁定的资源为止。
4个常见的dml语句会产生阻塞
INSERT
UPDATE
DELETE
SELECT…FORUPDATE
INSERT
Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。
当2个的会话同时试图向表中插入相同的数据时,其中的一个会话将被阻塞,直到另外一个会话提交或会滚。
一个会话提交时,另一个会话将收到主键重复的错误。
回滚时,被阻塞的会话将继续执行。
UPDATE和DELETE当执行Update和delete操作的数据行已经被另外的会话锁定时,将会发生阻塞,直到另一个会话提交或会滚。
Select…forupdate
当一个用户发出select..forupdate的错作准备对返回的结果集进行修改时,如果结果集已经被另一个会话锁定,就是发生阻塞。
需要等另一个会话结束之后才可继续执行。
可以通过发出select…forupdatenowait的语句来避免发生阻塞,如果资源已经被另一个会话锁定,则会返回以下错误:
Ora-00054:
resourcebusyandacquirewithnowaitspecified.
死锁-deadlock
定义:
当两个用户希望持有对方的资源时就会发生死锁.
即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚.
例子:
1:
用户1对A表进行Update,没有提交。
2:
用户2对B表进行Update,没有提交。
此时双反不存在资源共享的问题。
3:
如果用户2此时对A表作update,则会发生阻塞,需要等到用户一的事物结束。
4:
如果此时用户1又对B表作update,则产生死锁。
此时Oracle会选择其中一个用户进行会滚,使另一个用户继续执行操作。
起因:
Oracle的死锁问题实际上很少见,如果发生,基本上都是不正确的程序设计造成的,经过调整后,基本上都会避免死锁的发生。
DML锁分类表
表1Oracle的TM锁类型
锁模式
锁描述
解释
SQL操作
0
none
1
NULL
空
Select
2
SS(Row-S)
行级共享锁,其他对象只能查询这些数据行
Selectforupdate、Lockforupdate、Lockrowshare
3
SX(Row-X)
行级排它锁,在提交前不允许做DML操作
Insert、Update、Delete、Lockrowshare
4
S(Share)
共享锁
Createindex、Lockshare
5
SSX(S/Row-X)
共享行级排它锁
Locksharerowexclusive
6
X(Exclusive)
排它锁
Altertable、Dropable、Dropindex、Truncatetable、Lockexclusive
1.关于V$lock表和相关视图的说明
Column
Datatype
Description
ADDR
RAW(4|8)
Addressoflockstateobject
KADDR
RAW(4|8)
Addressoflock
SID
NUMBER
Identifierforsessionholdingoracquiringthelock
TYPE
VARCHAR2
(2)
Typeofuserorsystemlock
Thelocksontheusertypesareobtainedbyuserapplications.Anyprocessthatisblockingothersislikelytobeholdingoneoftheselocks.Theusertypelocksare:
TM-DMLenqueue
TX-Transactionenqueue
UL-Usersupplied
--我们主要关注TX和TM两种类型的锁
--UL锁用户自己定义的,一般很少会定义,基本不用关注
--其它均为系统锁,会很快自动释放,不用关注
ID1
NUMBER
Lockidentifier#1(dependsontype)
ID2
NUMBER
Lockidentifier#2(dependsontype)
---当locktype为TM时,id1为DML-lockedobject的object_id
---当locktype为TX时,id1为usn+slot,而id2为seq。
--当locktype为其它时,不用关注
LMODE
NUMBER
Lockmodeinwhichthesessionholdsthelock:
∙0-none
∙1-null(NULL)
∙2-row-S(SS)
∙3-row-X(SX)
∙4-share(S)
∙5-S/Row-X(SSX)
∙6-exclusive(X)
--大于0时表示当前会话以某种模式占有该锁,等于0时表示当前会话正在等待该锁资源,即表示该会话被阻塞。
--往往在发生TX锁时,伴随着TM锁,比如一个sid=9会话拥有一个TM锁,一般会拥有一个或几个TX锁,但他们的id1和id2是不同的,请注意
REQUEST
NUMBER
Lockmodeinwhichtheprocessrequeststhelock:
∙0-none
∙1-null(NULL)
∙2-row-S(SS)
∙3-row-X(SX)
∙4-share(S)
∙5-S/Row-X(SSX)
∙6-exclusive(X)
--大于0时,表示当前会话被阻塞,其它会话占有改锁的模式
CTIME
NUMBER
Timesincecurrentmodewasgranted
BLOCK
NUMBER
Thelockisblockinganotherlock
0,'NotBlocking',/*Notblockinganyotherprocesses*/
1,'Blocking',/*Thislockblocksotherprocesses*/
2,'Global',/*Thislockisglobal,sowecan'ttell*/
--该锁是否阻塞了另外一个锁
2.其它相关视图说明
视图名
描述
主要字段说明
v$session
查询会话的信息和锁的信息。
sid,serial#:
表示会话信息。
program:
表示会话的应用程序信息。
row_wait_obj#:
表示等待的对象,和dba_objects中的object_id相对应。
lockwait:
该会话等待的锁的地址,与v$lock的kaddr对应.
v$session_wait
查询等待的会话信息。
sid:
表示持有锁的会话信息。
Seconds_in_wait:
表示等待持续的时间信息
Event:
表示会话等待的事件,锁等于enqueue
dba_locks
对v$lock的格式化视图。
Session_id:
和v$lock中的Sid对应。
Lock_type:
和v$lock中的type对应。
Lock_ID1:
和v$lock中的ID1对应。
Mode_held,mode_requested:
和v$lock中
的lmode,request相对应。
v$locked_object
只包含DML的锁信息,包括回滚段和会话信息。
Xidusn,xidslot,xidsqn:
表示回滚段信息。
和
v$transaction相关联。
Object_id:
表示被锁对象标识。
Session_id:
表示持有锁的会话信息。
Locked_mode:
表示会话等待的锁模式的信
息,和v$lock中的lmode一致。
1.查询数据库中的锁
select*fromv$lock;
select*fromv$lockwhereblock=1;
2.查询被锁的对象
select*fromv$locked_object;
3.查询阻塞
查被阻塞的会话
select*fromv$lockwherelmode=0and typein('TM','TX');
查阻塞别的会话锁
select*fromv$lockwherelmode>0and typein('TM','TX');
4.查询数据库正在等待锁的进程
select*fromv$sessionwherelockwaitisnotnull;
5.查询会话之间锁等待的关系
selecta.sidholdsid,b.sidwaitsid,a.type,a.id1,a.id2,a.ctimefromv$locka,v$lockb
wherea.id1=b.id1anda.id2=b.id2anda.block=1andb.block=0;
6.查询锁等待事件
select*fromv$session_waitwhereevent='enqueue';
ORACLE里锁有以下几种模式:
0:
none
1:
null空
2:
Row-S行共享(RS):
共享表锁
3:
Row-X行专用(RX):
用于行的修改
4:
Share共享锁(S):
阻止其他DML操作
5:
S/Row-X共享行专用(SRX):
阻止其他事务操作
6:
exclusive专用(X):
独立访问使用
数字越大锁级别越高,影响的操作越多。
一般的查询语句如select...from...;是小于2的锁,有时会在v$locked_object出现。
select...from...forupdate;是2的锁。
当对话使用forupdate子串打开一个游标时,
所有返回集中的数据行都将处于行级(Row-X)独占式锁定,
其他对象只能查询这些数据行,不能进行update、delete或select...forupdate操作。
insert/update/delete...;是3的锁。
没有commit之前插入同样的一条记录会没有反应,
因为后一个3的锁会一直等待上一个3的锁,我们必须释放掉上一个才能继续工作。
创建索引的时候也会产生3,4级别的锁。
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作,
但DDL(alter,drop等)操作会提示ora-00054错误。
有主外键约束时update/delete...;可能会产生4,5的锁。
DDL语句时是6的锁。
以DBA角色,查看当前数据库里锁的情况可以用如下SQL语句:
查看锁表进程SQL语句1:
selectsess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
fromv$locked_objectlo,
dba_objectsao,
v$sessionsess
whereao.object_id=lo.object_idandlo.session_id=sess.sid;
查看锁表进程SQL语句2:
select*fromv$sessiont1,v$locked_objectt2wheret1.sid=t2.SESSION_ID;
杀掉锁表进程:
如有記錄則表示有lock,記錄下SID和serial#,將記錄的ID替換下面的738,1429,即可解除LOCK
altersystemkillsession'738,1429';
用这个可以查:
selects.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode,'ALTERSYSTEMKILLSESSION'''||s.sid||','||s.serial#||''';'Commandfromv$locked_objectl,v$sessions,all_objectsowherel.session_id=s.sidandl.object_id=o.object_id可以查看哪台机器哪个用户锁了记录,其中command是用来杀掉锁住记录的session
******************************************************************************************************************
SELECTA.OBJECT_ID,B.OBJECT_NAME,A.SESSION_ID,A.ORACLE_USERNAME,A.OS_USER_NAME,A.PROCESS,A.LOCKED_MODEFROMV$LOCKED_OBJECTA,DBA_OBJECTSBWHEREA.OBJECT_ID=B.OBJECT_ID;
SELECTT2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROMV$LOCKED_OBJECTT1,V$SESSIONT2WHERET1.SESSION_ID=T2.SIDORDERBYT2.LOGON_TIME;
ALTERSYSTEMKILLSESSION'sid,serial#';
********************************************************************************************************************
session1:
C:
\>sqlplushxg/hxg
SQL>select*fromscott.t;
ABC--------------------------------------------------111aabb222helloworld
SQL>updatescott.tsetb='good'wherea=222;
已更新1行。
session2:
C:
\>sqlplusscott/tiger
SQL>select*fromscott.t;
ABC--------------------------------------------------111aabb222helloworld
SQL>updatetsetb='asdfds'wherea=222;
挂起。
。
。
。
session3:
C:
\>sqlplus"system/***assysdba"
SQL>selectsid,serial#,username,statusfromv$session;
SIDSERIAL#USERNAMESTATUS----------------------------------------------------14625SYSACTIVE14711SYSINACTIVE1482HXGINACTIVE1505SCOTTACTIVE1511ACTIVE1541ACTIVE1596ACTIVE1601ACTIVE1611ACTIVE1621ACTIVE1631ACTIVE
SIDSERIAL#USERNAMESTATUS----------------------------------------------------1641ACTIVE1651ACTIVE1661ACTIVE1671ACTIVE1681ACTIVE1691ACTIVE1701ACTIVE
已选择18行。
SQL>altersystemkillsession'148,2';
系统已更改。
SQL>selectsid,serial#,username,statusfromv$session;
SIDSERIAL#USERNAMESTATUS----------------------------------------------------14625SYSACTIVE14711SYSINACTIVE1482HXGKILLED1505SCOTTINACTIVE1511ACTIVE1541ACTIVE1596ACTIVE1601ACTIVE1611ACTIVE1621ACTIVE1631ACTIVE
SIDSERIAL#USERNAMESTATUS----------------------------------------------------1641ACTIVE1651ACTIVE1661ACTIVE1671ACTIVE1681ACTIVE1691ACTIVE1701ACTIVE
已选择18行。
SQL>
selectV$SESSION.sid,v$session.SERIAL#,v$process.spid,
rtrim(object_type)object_type,rtrim(owner)||'.'||object_nameobject_name,
decode(lmode,0,'None',
1,'Null',
2,'Row-S',
3,'Row-X',
4,'Share',
5,'S/Row-X',
6,'Exclusive','Unknown')LockMode,
decode(request,0,'None',
1,'Null',
2,'Row-S',
3,'Row-X',
4,'Share',
5,'S/
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 管理 大全