oracle锁表问题处理.docx
- 文档编号:5246747
- 上传时间:2022-12-14
- 格式:DOCX
- 页数:14
- 大小:25.25KB
oracle锁表问题处理.docx
《oracle锁表问题处理.docx》由会员分享,可在线阅读,更多相关《oracle锁表问题处理.docx(14页珍藏版)》请在冰豆网上搜索。
oracle锁表问题处理
oracle锁表问题处理
查询表的状况的对象:
V$LOCK,V$LOCKED_OBJECT,V$SESSION,V$SQLAREA,V$PROCESS
select*fromv$locked_object
select*fromdba_objects
方法:
首先查看那些表被锁住了
selectb.owner,b.object_name,a.session_id,a.locked_mode
fromv$locked_objecta,dba_objectsb
whereb.object_id=a.object_id;
selectb.username,b.sid,b.serial#,logon_time
fromv$locked_objecta,v$sessionb
wherea.session_id=b.sidorderbyb.logon_time;
杀进程中的会话
altersystemkillsession'sid,serial#';
例如:
altersystemkillsession'29,5497';
查询锁表的方法:
SELECTS.SIDSESSION_ID,S.USERNAME,s.SERIAL#,
DECODE(LMODE,0,'None',1,'Null',2,'Row-S(SS)',3,'Row-X(SX)',4,'Share',5,'S/Row-X(SSX)',6,'Exclusive',
TO_CHAR(LMODE))MODE_HELD,DECODE(REQUEST,0,'None',1,'Null',2,'Row-S(SS)',3,'Row-X(SX)',4,'Share',5,'S/Row-X(SSX)',6,'Exclusive',
TO_CHAR(REQUEST))MODE_REQUESTED,O.OWNER||'.'||O.OBJECT_NAME||'('||O.OBJECT_TYPE||')',
S.TYPELOCK_TYPE,L.ID1LOCK_ID1,L.ID2LOCK_ID2
FROMV$LOCKL,SYS.DBA_OBJECTSO,V$SESSIONS
WHEREL.SID=S.SIDANDL.ID1=O.OBJECT_ID;
解锁方法:
ALTERSYSTEMKILLSESSION'SID,SERIR#'
0----------0--------
SQL>altersystemkillsession'1679,2456';
altersystemkillsession'1679,2456'
*
ERRORatline1:
ORA-01031:
insufficientprivileges
grantexecuteonp_kill_user_sessiontortgs_liush();
操作的时候用命令:
EXECSYS.P_KILL_USER_SESSION(1679);
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
数据库锁表的分析与解决
数据库锁表的分析与解决
数据库锁表的分析与解决
上面介绍了内存溢出的原因和处理方法,下面再介绍一下数据库锁表及阻塞的原因和处理办法。
数据库和操作系统一样,是一个多用户使用的共享资源。
当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。
若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。
加锁是实现数据库并发控制的一个非常重要的技术。
在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严重影响应用的正常执行。
在数据库中有两种基本的锁类型:
排它锁(ExclusiveLocks,即X锁)和共享锁(ShareLocks,即S锁)。
当数据对象被加上排它锁时,其他的事务不能对它读取和修改。
加了共享锁的数据对象可以被其他事务读取,但不能修改。
数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
死锁的第一种情况
一个用户A访问表A(锁住了表A),然后又访问表B;另一个用户B访问表B(锁住了表B),然后企图访问表A;这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B才能继续,同样用户B要等用户A释放表A才能继续,这就死锁就产生了。
解决方法:
这种死锁比较常见,是由于程序的BUG产生的,除了调整的程序的逻辑没有其它的办法。
仔细分析程序的逻辑,对于数据库的多表操作时,尽量按照相同的顺序进行处理,尽量避免同时锁定两个资源,如操作A和B两张表时,总是按先A后B的顺序处理,必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源。
死锁的第二种情况
用户A查询一条纪录,然后修改该条纪录;这时用户B修改该条纪录,这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁,而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁,而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现了死锁。
这种死锁比较隐蔽,但在稍大点的项目中经常发生。
如在某项目中,页面上的按钮点击后,没有使按钮立刻失效,使得用户会多次快速点击同一按钮,这样同一段代码对数据库同一条记录进行多次操作,很容易就出现这种死锁的情况。
解决方法:
1、对于按钮等控件,点击后使其立刻失效,不让用户重复点击,避免对同时对同一条记录操作。
2、使用乐观锁进行控制。
乐观锁大多是基于数据版本(Version)记录机制实现。
即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个“version”字段来实现。
读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。
此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。
乐观锁机制避免了长事务中的数据库加锁开销(用户A和用户B操作过程中,都没有对数据库数据加锁),大大提升了大并发量下的系统整体性能表现。
Hibernate在其数据访问引擎中内置了乐观锁实现。
需要注意的是,由于乐观锁机制是在我们的系统中实现,来自外部系统的用户更新操作不受我们系统的控制,因此可能会造成脏数据被更新到数据库中。
3、使用悲观锁进行控制。
悲观锁大多数情况下依靠数据库的锁机制实现,如Oracle的Select…forupdate语句,以保证操作最大程度的独占性。
但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。
如一个金融系统,当某个操作员读取用户的数据,并在读出的用户数据的基础上进行修改时(如更改用户账户余额),如果采用悲观锁机制,也就意味着整个操作过程中(从操作员读出数据、开始修改直至提交修改结果的全过程,甚至还包括操作员中途去煮咖啡的时间),数据库记录始终处于加锁状态,可以想见,如果面对成百上千个并发,这样的情况将导致灾难性的后果。
所以,采用悲观锁进行控制时一定要考虑清楚。
死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句,则执行全表扫描,把行级锁上升为表级锁,多个这样的事务执行后,就很容易产生死锁和阻塞。
类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候,使得经常发生全表扫描,最终应用系统会越来越慢,最终发生阻塞或死锁。
解决方法:
SQL语句中不要使用太复杂的关联多表的查询;使用“执行计划”对SQL语句进行分析,对于有全表扫描的SQL语句,建立相应的索引进行优化。
5.小结
总体上来说,产生内存溢出与锁表都是由于代码写的不好造成的,因此提高代码的质量是最根本的解决办法。
有的人认为先把功能实现,有BUG时再在测试阶段进行修正,这种想法是错误的。
正如一件产品的质量是在生产制造的过程中决定的,而不是质量检测时决定的,软件的质量在设计与编码阶段就已经决定了,测试只是对软件质量的一个验证,因为测试不可能找出软件中所有的BUG。
-------------------------------------------------------------------------------------------------------------
相关文章:
ORACLE里几种锁模式
遇到一个多事务并发的问题
mysql数据库锁
推荐圈子:
Pipboy
更多相关推荐对锁机制的研究要具备两个条件:
1.数据量大
2.多个用户同时并发
如果缺少这两个条件,数据库不容易产生死锁问题。
研究起来可能会事倍功半。
如果这两个条件都有,但你还是按数据库缺省设置来处理数据,则会带来很多的问题,比如:
1)丢失更新
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果
2)脏读
A用户修改了数据时,B用户也在读该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致
3)不可重复读
B用户读出该数据并修改,同时,A用户也在读取数据,此时A用户再读取数据时发现前后两次的值不一致
SQLSERVER作为多用户数据库系统,以事务为单位,使用锁来实现并发控制。
SQLSERVER使用“锁”确保事务完整性和数据一致性。
一、锁的概念
锁(LOCKING)是最常用的并发控制机构。
是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。
锁是事务对某个数据库中的资源(如表和记录)存取前,先向系统提出请求,封锁该资源,事务获得锁后,即取得对数据的控制权,在事务释放它的锁之前,其他事务不能更新此数据。
当事务撤消后,释放被锁定的资源。
当一个用户锁住数据库中的某个对象时,其他用户就不能再访问该对象
二、锁的粒度
SQLServer2000具有多粒度锁定,允许一个事务锁定不同类型的的资源。
为了使锁定的成本减至最少,SQLServer自动将资源锁定在适合任务的级别。
锁定在较小的粒度(例如行)可以增加并发但需要较大的开销,因为如果锁定了许多行,则需要控制更多的锁。
锁定在较大的粒度(例如表)就并发而言是相当昂贵的,因为锁定整个表限制了其它事务对表中任意部分进行访问,但要求的开销较低,因为需要维护的锁较少。
SQLServer可以锁定行、页、扩展盘区、表、库等资源。
资源级别描述
RID行锁表中的单个行
Key行级锁索引中的行
Page页级锁一个数据页或者索引页
Extent页级锁一组数据页或者索引页
Table表级锁整个表
Database数据库级锁整个数据库
选择多大的粒度,根据对数据的操作而定。
如果是更新表中所有的行,则用表级锁;如果是更新表中的某一行,则用行级锁。
行级锁是一种最优锁,因为行级锁不可能出现数据既被占用又没有使用的浪费现象。
但是,如果用户事务中频繁对某个表中的多条记录操作,将导致对该表的许多记录行都加上了行级锁,数据库系统中锁的数目会急剧增加,这样就加重了系统负荷,影响系统性能。
因此,在SQLServer中,还支持锁升级(lockescalation)。
所谓锁升级是指调整锁的粒度,将多个低粒度的锁替换成少数的更高粒度的锁,以此来降低系统负荷。
在SQLServer中当一个事务中的锁较多,达到锁升级门限时,系统自动将行级锁和页面锁升级为表级锁。
特别值得注意的是,在SQLServer中,锁的升级门限以及锁升级是由系统自动来确定的,不需要用户设置。
三、锁的模式
锁模式以及描述表
锁模式描述
共享(S)用于不更改或不更新数据(只读操作),如SELECT语句
更新(U)用于可更新的资源中。
防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它(X)用于数据修改操作,例如INSERT、UPDATE或DELETE。
确保不会同时对同一资源进行多重更新
意向当MicrosoftSQLServer数据库引擎获取低级别的锁时,它还将在包含更低级别对象的对象上放置意向锁.例如:
当锁定行或索引键范围时,数据库引擎将在包含行或键的页上放置意向锁。
当锁定页时,数据库引擎将在包含页的更高级别的对象上放置意向锁。
意向锁的类型为:
意向共享(IS)、意向排它(IX)以及意向排它共享(SIX)
架构在执行依赖于表架构的操作时使用。
架构锁的类型为:
架构修改(Sch-M)和架构稳定(Sch-S)
大容量更新(BU)向表中大容量复制数据并指定了TABLOCK提示时使用
四SQLServer中锁的设置
1处理死锁和设置死锁优先级
死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待
可以使用SETDEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。
Syntax:
SETDEADLOCK_PRIORITY{LOW|NORMAL}
其中LOW说明该进程会话的优先级较低,在出现死锁时,可以首先中断该进程的事务。
2处理超时和设置锁超时持续时间。
@@LOCK_TIMEOUT返回当前会话的当前锁超时设置,单位为毫秒
SETLOCK_TIMEOUT设置允许应用程序设置语句等待阻塞资源的最长时间。
当语句等待的时间大于LOCK_TIMEOUT设置时,系统将自动取消阻塞的语句,并给应用程序返回"已超过了锁请求超时时段"的1222号错误信息
示例
1)将锁超时期限设置为1,800毫秒。
SETLOCK_TIMEOUT1800
2)配置索引的锁定粒度
可以使用sp_indexoption系统存储过程来设置用于索引的锁定粒度
3)设置事务隔离级别
SETTRANSACTIONISOLATIONLEVEL
五查看锁的信息
1执行EXECSP_LOCK报告有关锁的信息
2查询分析器中按Ctrl+2可以看到锁的信息
六、奇怪的sql语句
Java代码
begintran
updatetitlessettitle_idid=title_idwhere1=2
if(selectavg(price)fromtitles)>$15
begin
updatetitlessetprice=price*1.10
whereprice<(selectavg(price)fromtitles)
end
committran
begintran
updatetitlessettitle_idid=title_idwhere1=2
if(selectavg(price)fromtitles)>$15
begin
updatetitlessetprice=price*1.10
whereprice<(selectavg(price)fromtitles)
end
committran
updatetitlessettitle_idid=title_idwhere1=2,这个条件是永远也不会成立的,如此写的含义是什么呢?
这里的where子句看起来很奇怪,尽管计算出的结果总是false。
当优化器处理此查询时,因为它找不到任何有效的SARG,它的查询规划就会强制使用一个独占锁定来进行表扫描。
此事务执行时,where子句立即得到一个false值,于是不会执行实际上的扫描,但此进程仍得到了一个独占的表锁定。
因为此进程现在已有一个独占的表锁,所以可以保证没有其他事务会修改任何数据行,能进行重复读,且避免了由于holdlock所引起的潜在性死锁。
但是,在使用表锁定来尽可能地减少死锁的同时,也增加了对表锁定的争用。
因此,在实现这种方法之前,你需要权衡一下:
避免死锁是否比允许并发地对表进行访问更重要。
所以,在这个事务中,没有其他进程修改表中任何行的price。
七如何避免死锁
1使用事务时,尽量缩短事务的逻辑处理过程,及早提交或回滚事务;
2设置死锁超时参数为合理范围,如:
3分钟-10分种;超过时间,自动放弃本次操作,避免进程悬挂;
3所有的SP都要有错误处理(通过@error)
4一般不要修改SQLSERVER事务的默认级别。
不推荐强行加锁
5优化程序,检查并避免死锁现象出现;
1)合理安排表访问顺序
2)在事务中尽量避免用户干预,尽量使一个事务处理的任务少些。
3)采用脏读技术。
脏读由于不对被访问的表加锁,而避免了锁冲突。
在客户机/服务器应用环境中,有些事务往往不允许读脏数据,但在特定的条件下,我们可以用脏读。
4)数据访问时域离散法。
数据访问时域离散法是指在客户机/服务器结构中,采取各种控制手段控制对数据库或数据库中的对象访问时间段。
主要通过以下方式实现:
合理安排后台事务的执行时间,采用工作流对后台事务进行统一管理。
工作流在管理任务时,一方面限制同一类任务的线程数(往往限制为1个),防止资源过多占用;另一方面合理安排不同任务执行时序、时间,尽量避免多个后台任务同时执行,另外,避免在前台交易高峰时间运行后台任务
5)数据存储空间离散法。
数据存储空间离散法是指采取各种手段,将逻辑上在一个表中的数据分散到若干离散的空间上去,以便改善对表的访问性能。
主要通过以下方法实现:
第一,将大表按行或列分解为若干小表;第二,按不同的用户群分解。
6)使用尽可能低的隔离性级别。
隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度,SQL92定义了4种隔离性级别:
未提交读、提交读、可重复读和可串行。
如果选择过高的隔离性级别,如可串行,虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性,但各事务间冲突而死锁的机会大大增加,大大影响了系统性能。
7)使用BoundConnections。
Boundconnections允许两个或多个事务连接共享事务和锁,而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样,因此可以允许这些事务共享数据而不会有加锁的冲突。
8)考虑使用乐观锁定或使事务首先获得一个独占锁定。
八如何对行、表、数据库加锁
1如何锁一个表的某一行
Java代码
SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED
SELECT*FROMtable1ROWLOCKWHEREA='a1'
SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED
SELECT*FROMtable1ROWLOCKWHEREA='a1'
2锁定数据库的一个表
selectcol1from表(tablockx)where1=1;
加锁后其它人不可操作,直到加锁用户解锁,用commit或rollback解锁
3.实例
建表
Java代码
createtabletable1(Avarchar(50)notnull,Bvarchar(50),Cvarchar(50));
createtabletable2(Dvarchar(50),Evarchar(50))
inserttable1(A,B,C)values(‘a1’,’b1’,’c1’);
inserttable1(A,B,C)values(‘a2’,’b2’,’c2’);
inserttable1(A,B,C)values(‘a3’,’b3’,’c3’);
inserttable2(D,E)values(‘d1’,’e1’);
inserttable2(D,E)values(‘d2’,’e2’);
createtabletable1(Avarchar(50)notnull,Bvarchar(50),Cvarchar(50));
createtabletable2(Dvarchar(50),Evarchar(50))
inserttable1(A,B,C)values(‘a1’,’b1’,’c1’);
inserttable1(A,B,C)values(‘a2’,’b2’,’c2’);
inserttable1(A,B,C)values(‘a3’,’b3’,’c3’);
inserttable2(D,E)values(‘d1’,’e1’);
inserttable2(D,E)values(‘d2’,’e2’);
1)排它锁
Java代码
--A事务先更新table1表,在更新时,对其他事务进行排他
begintran
updatetable1setA='aa'whereB='b2';
waitfordelay'00:
00:
30';--等待30秒
committran
--A事务先更新table2表
begintran
select*fromtable1whereB='b2';
committran
--A事务先更新table1表,在更新时,对其他事务进行排他
begintran
updatetable1setA='aa'whereB='b2';
waitfordelay'00:
00:
30';--等待30秒
committran
--A事务先更新table2表
begintran
select*fromtable1whereB='b2';
committran若同时执行上述两个事务,则select查询必须等待update执行完毕才能执行即要等待30秒
2)共享锁
Java代码
--A事务先查询table1表,在查询时,加共享锁,防止其他事务对该表进行修改操作
begintran
select*fromtable1holdlockwhereB='b2';
-holdlock人为加锁
waitfordelay'00:
00:
30';--等待30秒
committran
--A事务先查询table1表,后更改table1表
begintran
selectA,Cfromtable1whereB='
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 问题 处理