请教一个刚遇到的关于lock的问题

net pinaster

请教一个刚遇到的关于lock的问题。
用sql plus 分别开了三个session，然后对同一个表进行下面的操作：
session 1：
SQL> lock table test.test0001 in exclusive mode;

表已锁定。

session 2:
SQL> insert into test.test0001(lsh) values(1);

session 3:
SQL> insert into test.test0001(lsh) values(2);

然后，释放session 1 的锁：
SQL> rollback;

回退已完成。

此时session 2的反应为：
SQL> insert into test.test0001(lsh) values(1);

已创建 1 行。

但是session 3 仍然在等待锁，为什么呢？请高手能解释一下，十分感谢！

NextGPS

综一句话而言：rollback is abnormal.   commit is normal.
if your session 1 useing :commit.  then session2 and session3 will go on together.

NextGPS

insert 是DML语句，操作后不会自动提交，需要人工去干涉的。
session1，给表加锁（Ｘ）了。这时session2、session３顺序来进行ＤＭＬ语句操作，session２要得到一个行锁（ＳＸ）后才会hang住。session３也一样。当session1 rollback后，session２的锁（ＳＸ）〈（Ｘ），session2请求x锁，去初如化锁，因为执行的是ＤＭＬ语句，而且现在还有一个session３完后就又请求了sx锁，所以只有session２commit|rollback，session３的语句才有返回信息，但此时session３也是有一个sx锁。直到commit|rollback.


- session1 "lock table T1 in X mode"
- session2 "insert into T1 values..." -> hang since session2 try to acquire a lock SX on T1
- session3 "insert into T1 values..." -> hang since session3 try to acquire a lock SX on T1
- session1 "rollback" -> since rollback can be considered as "ABNORMAL" operation and held mode is X -> we invalidate the enqueue on T1
- session2 get out from wait . Since session2 requested mode is SX < X mode and enqueue on T1 is invalidated -> session2 tries to acquire a X lock to clear invalidation of the resource . This operation succeds, then session2 releases the X lock and acquires again SX lock and succeeds again .
- session3 get out from wait in same time as session2 and trying the same X lock ( to clear invalidation) . With timing window, session3 tries to get a X lock when session2 has finished to get a SX lock .

shiri512003

对
oracle在好多中资源的abnormal 处理中都是用的这种方法的
怎么高明的方法怎么会之用一次呢
^_^

shiri512003

orcle的惯用手法
一个abnormal的资源
总是由后面的一个来差屁股的

ihekoko

近水楼台先得月啊

zhpsam

到现在还没有个结果？

zhpsam

大家有没有测过表有主键时的情况的，我发现如果表有主键，session1中回滚，则session2和session3可以立即获得锁：
--SESSION1:
SQL> create TABLE test
  2  (
  3    c1 varchar2(1),
  4    c2 varchar2(1)
  5  )
  6  ;

Table created
SQL> alter TABLE test
  2    add constraint PK_TEST primary key (C1);

Table altered

SQL>  lock table test in exclusive mode;

Table(s) locked

SQL> rollback;

Rollback complete

SQL>

--SESSION2：
SQL> insert into test values('1','A');

--SESSION3:
SQL> insert into test values('2','B');

--查询锁：

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST FROM v$lock WHERE sid in(532,
  2  510,
  3  506) ORDER BY sid;

       SID TYPE        ID1        ID2      LMODE    REQUEST
---------- ---- ---------- ---------- ---------- ----------
       506 TM     113966          0          0          3
       510 TM     113966          0          0          3
       532 TM     113966          0          6          0
       532 TX     655380     572628          6          0
      
--在SESSION1中rollback:
SQL> rollback;

Rollback complete

SQL>

--查询锁：

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST FROM v$lock WHERE sid in(532,
  2  510,
  3  506) ORDER BY sid;

       SID TYPE        ID1        ID2      LMODE    REQUEST
---------- ---- ---------- ---------- ---------- ----------
       506 TM     113966          0          3          0
       506 TX     327701     430279          6          0
       510 TX     458772     415416          6          0
       510 TM     113966          0          3          0

SQL>

这个跟表有没有主键或者唯一约束有关系？

[ 本帖最后由 zhpsam 于 2007-12-12 22:28 编辑 ]

logzgh

原帖由 net pinaster 于 2007-12-11 23:48 发表
插一句。为什么在不同的版本，oracle处理的方式是不一样的呢？oracle是基于什么在9.2.0.x之后做了这样的改变？
如果是象ilonng所说的锁提升的话，是否会引起应用程序潜在的问题呢？

针对这个现象不要把注意力放在这点上面。
从目前的测试来看，前面有人说了只有在9207这个版本下面才不会锁住， 其他的版本都有这种现象。所以应该是9207反而是异常。




针对这个现象，好像是rollback和commit两种情况下锁释放的原理不太一样，才会导致这种现象。

ilonng

原帖由 Kamus 于 2007-12-12 01:38 发表


是有这个差异，在我的11g中同样是RX TM锁，我没有9i的数据库环境了，如果谁有帮忙测试一下在9i中的现象。

还有另外一个问题

lock table t1 in ROW SHARE MODE; --此时是RS TM锁
select * from t1 for update;  -- 此时锁转换为RX TM，这是正常的

但是下面
lock table t1 in SHARE MODE; --此时是S TM锁
select * from t1 for update;  -- 此时锁转换为SRX TM，实际上没有必要

看文档中的描述也没有说到后一种转换：

Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE

The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:

    *      The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.

    *      The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.

    *      A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.

    *      In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.

9i的效果也是RX TM锁：
session 1：
SQL> select distinct sid from v$mystat;

       SID
----------
        37

session 2：
SQL> select * from v$lock where sid=37 and type in ('TM','TX');

no rows selected

session 1：
SQL> select * from t where a=1 for update;

         A
----------
         1

session 2：
SQL> /   

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
5D867808 5D867914         37 TX    1048582     113027          6          0          3          0
5D725A28 5D725A3C         37 TM      63338          0          3          0          3          0


根据这个文档上所说的，也不奇怪了。
If the containing transaction already holds a share, share rowexclusive, or exclusive table lock for that table, the row exclusivetable lock is not acquired. If the containing transaction already holdsa row share table lock, Oracle automatically converts this lock to arow exclusive table lock。

主要是锁提升的机制和锁转换的机制，我很不清楚，老大能不能解释一下