现象:
系统突然报连接数过高,基本的现象就是有什么东西被锁了,导致后续的连接都在等待,那么到底是那个会话导致了阻塞那?
可以查看视图v$session ,关注以下几个字段
sid-------------------------被阻塞的进程id
status--------------------被阻塞的进程状态
COMMAND--------------被阻塞的进程执行的命令
ROW_WAIT_FILE#----被阻塞的进程对应的rowid所在的数据文件id
row_wait_block#-----row_wait_row#对应的rowid所在的表的object id
row_wait_obj#-------row_wait_row#对应的rowid所在的表的object id
row_wait_row#-----Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ#
is not -1
.但是准确的说是对应的于rowid的rownum,并非是单纯的rownum
blocking_session -----阻塞进程id
STATE-------------------被阻塞进程的状态
EVENT#----------------被阻塞进程等待的事件号
EVENT------------------被阻塞进程等待的事件
-----注意create_time是程序自动添加的
select t.sid,t.status,t.COMMAND,t.ROW_WAIT_FILE#,t.row_wait_block#,t.row_wait_obj#,t.row_wait_row#,t.blocking_session ,t.STATE,t.EVENT#,t.EVENT from temp_session t where t.BLOCKING_SESSION_STATUS='VALID' and t.create_time=to_date('2012/3/20 18:47:16','yyyy/mm/dd hh24:mi:ss');
SID STATUS COMMAND ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_OBJ# ROW_WAIT_ROW# BLOCKING_SESSION STATE EVENT# EVENT
175 620 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
174 616 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
173 615 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
333 1050 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
179 632 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
178 629 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
332 1049 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
171 610 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
166 592 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
165 591 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
164 589 ACTIVE 3 8 571147 77418 17 885 WAITING 239 enq: TX - row lock contention
现在可以看到885进程阻塞了好多进程,那么要获得到底是那条记录被锁定了那?
想要知道某一个表具体被锁定的是哪一行,可以利用上面这几个值,查找出被锁定行的rowid。
使用dbms_rowidb包的一个子过程(Subprograms)rowid_create
DBMS_ROWID.ROWID_CREATE (
rowid_type IN NUMBER,
object_number IN NUMBER,
relative_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER)
RETURN ROWID;
其中rowid_type的取值为 0 表示生成一个restricted ROWID(pre-oracle8 format); 取值为1 表示生成一个extended ROWID.
object_number取值是dba_objects视图中的data_object_id,并不是object_id,也就是说不能用row_wait_obj#.
relative_fno取值是Relative文件号,在dba_data_files里可以查到
block_number取值是数据文件的块号,也就是v$session中的row_wait_block#的值
row_number通过rowid对应出来的rownum,也就是row_wait_row#的值。
接下来找到这些数据
rowid_type=1(11g)
object_number
selectdata_object_id from dba_objects where object_id='77418'OBJECT_ID
TT_BIGINT NOT NULL
Dictionary object number of the object.DATA_OBJECT_ID
TT_BIGINT
Is ignored.----这是11g的文档说明,可以看出,使用object_id就可以
relative_fno=8
block_number=row_wait_block#
row_number =row_wait_row#
select * from table_name t where rowid=(
select DBMS_ROWID.ROWID_CREATE(1,77418,8,571147,17) from dual);---得到被锁定的记录;
此时查看885进程在做神马?
kill之就可以
v$session 中查找locking信息
最新推荐文章于 2022-05-03 21:35:08 发布