关于死锁的疑惑

woshiyiziyu

终于解决了。
环境Oracle 10g（公司的生产库，呵呵）
用户：sys
实验步骤：
1.delete from scott.emp where empno=1111;（之前我插入了一条语句）
2.select a.sid,b.owner,object_name,object_type from v$lock a,all_objects b
  where TYPE = 'TM' and a.id1=b.object_id;
3.select * from v$lock where sid=829;
4.select object_id,object_name from all_objects where object_id in('51149','51151');
在这里我用的是all_objects，原文用的是user_objects,但是我查不出结果，可能跟用户sys有关吧。
操作步骤如下图：

[ 本帖最后由 woshiyiziyu 于 2009-9-14 22:01 编辑 ]

woshiyiziyu

再说一下我原来的思路，我首先想通过v$sql查询到hash值或者address值（不过据说address值有可能不准）再通过v$session查询sid，但是怎么也查不出结果。

woshiyiziyu

关于死锁的实验实在是不敢在生产机上做，等有测试机再做。

Yong Huang

> 我首先想通过v$sql查询到hash值或者address值（不过据说address值有可能不准）再通过v$session
> 查询sid，但是怎么也查不出结果。

If you delete and then in the same session check v$session for this delete SQL, you need to use prev_hash_value. But even that is not new enough. So the reliable way to check is from another session and leave this session that just did delete alone.

> 关于死锁的实验实在是不敢在生产机上做，等有测试机再做。

There's no danger testing deadlocks using a table you create for testing purposes, on whatever database. Just don't test on the table applications are using.

Yong Huang

woshiyiziyu

原帖由 Yong Huang 于 2009-9-15 01:44 发表
> 我首先想通过v$sql查询到hash值或者address值（不过据说address值有可能不准）再通过v$session
> 查询sid，但是怎么也查不出结果。

If you delete and then in the same session check v$session for this delete SQL, you need to use prev_hash_value. But even that is not new enough. So the reliable way to check is from another session and leave this session that just did delete alone.

> 关于死锁的实验实在是不敢在生产机上做，等有测试机再做。

There's no danger testing deadlocks using a table you create for testing purposes, on whatever database. Just don't test on the table applications are using.

Yong Huang

又做了一下实验，结果如下：
在同一窗口中使用prev_hash_value查不出我想要的结果
再开一个窗口，使用prev_hash_value可以查出我想要的结果

[ 本帖最后由 woshiyiziyu 于 2009-9-17 19:49 编辑 ]