讲解有关"SELECT FOR UPDATE"的一些概念

 在网上找了些文章,总结在了一起....

statement: 一个SQL语句。

 

session: 一个由ORACLE用户产生的连接，一个用户可以产生多个SESSION ，但相互之间是独立的。

transaction:所有的改变都可以划分到transaction里，一个transaction包含一个或多个SQL。当一个SESSION建立的时候就是一个TRANSACTION开始的时刻，此后transaction的开始和结束由DCL控制，也就是每个COMMIT/ROLLBACK都标示着一个transaction的结束。

consistency：是对于statement级别而不是transaction级别来说的。sql statement 得到的数据都是以sql statement开始的IMAGE。

LOCK的基本情况：

update, insert ,delete, select ... for update会LOCK相应的ROW 。

只有一个TRANSACTION可以LOCK相应的行，也就是说如果一个ROW已经LOCKED了，那就不能被其他TRANSACTION所LOCK了。

LOCK由statement产生但却由TRANSACTION结尾（commit，rollback），也就是说一个SQL完成后LOCK还会存在，只有在COMMIT/ROLLBACK后才会RELEASE。

 

SELECT.... FOR UPDATE [OF cols] [NOWAIT];OF colsSELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT];

前面的FOR UPDATE省略，下面我们来讲一下OF。

 

transaction A运行select a.object_name,a.object_id from wwm2 a,wwm3 b2 where b.status='VALID' and a.object_id=b.object_id3* for update of a.status

则transaction B可以对b表wwm3的相应行进行DML操作,但不能对a表wwm2相应行进行DML操作.

反一下看看。

 

transaction A运行select a.object_name,a.object_id from wwm2 a,wwm3 b2 where b.status='VALID' and a.object_id=b.object_id3* for update of b.status

则transaction B可以对a表wwm2的相应行进行DML操作,但不能对b表wwm3相应行进行DML操作.

也就是说LOCK的还是行,只是如果不加OF的话会对所有涉及的表LOCK的,加了OF后只会LOCK OF 字句所在的TABLE.

NOWAIT（如果一定要用FOR UPDATE，我更建议加上NOWAIT）

当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待.返回错误是"ORA-00054: resource busy and acquire with NOWAIT specified"

另外如下用法也值得推荐，应该酌情考虑使用。

 

FOR UPDATE WAIT 5

5秒后会出现提示：

 

ORA-30006: resource busy; acquire with WAIT timeout expiredFOR UPDATE NOWAIT SKIP LOCKED;

出现提示：

 

no rows selectedTABLE LOCKSLOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];

同样也是在transaction结束时才会释放lock。

DEADLOCK:

 

transaction a lock rowA , then transaction b lock rowBthen transaction a tries to lock rowB, and transaction b tries to lock rowA

也就是说两个transaction都相互试图去lock对方已经lock的ROW，都在等待对方释放自己的lock，这样就使死锁。另外，deadlock也会有600提示。（

question:
-----------------------------------------------------------
在oracle 中用了:

select *  from tb1  for  update

然后删了几条记录，确定后。就不能再对 表进行增、删、改了,

这是为什么？要怎么释放for  update的锁？？

-----------------------------------------------------------
answer1:

结束事务.

-----------------------------------------------------------
answer2:

commit

-----------------------------------------------------------
answer3:

我是在PLSQL Developer中用按钮删的,怎么commit

？？

-----------------------------------------------------------
answer4:

在这个开发工具中,工具条上有个绿色的向下箭头的图标,就是commit 了,旁边的是 rollback

-----------------------------------------------------------
answer5:

对了,执行commit前,要先在显示数据的上面有个对勾,按一下,然后关闭锁头,然后按 commit就可以了

-----------------------------------------------------------
answer6:

完整回答:先按F8,再绿色箭头,出现对话框点确定.

-----------------------------------------------------------
answer7:

慎用for update

-----------------------------------------------------------
answer8:

删除以后,先要post changes,再点commit button

Update语句与锁     

          Select…For Update语句的语法与select语句相同，只是在select语句的后面加FOR UPDATE [NOWAIT]子句。

该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。

如图20.51所示，左上角的会话用Select…For Update语句锁定了Department表中DeptNo='01'的行，右上角的会话说明其他会话不可以继续更改该行上的数据。从OEM中的锁的信息可以看出，Select…For Update语句所加的锁与update语句所加的锁相同：一个行级别的EXCLUSIVE锁（说明多个事务不能同时操作同一行）、一个表级别的ROW EXCLUSIVE锁。

图20.51   Select…For Update语句锁定了符合where条件的行

如图20.52所示，左上角的会话用Update语句锁定了Department表中DeptNo='01'的行，右上角的会话说明其他会话不可以用Select…For Update语句继续锁定该行。

图20.52   Select…For Update语句被其他会话阻塞了

如图20.53所示，左上角的会话用Update语句锁定了Department表中DeptNo='01'的行，右上角的会话说明其他会话不可以用Select…For Update NOWAIT语句继续锁定该行，且会立即返回一个错误提示“ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源”，而不需要等待加锁成功。

图20.53    如果加锁不成功，则Select…For Update NOWAIT语句就会立即返回错误提示

可以看出，如果仅仅用update语句来更改数据时，可能会因为加不上锁而没有响应地、莫名其妙地等待，但如果在此之前，先用Select…For Update NOWAIT语句将要更改的数据试探性地加锁，就可以通过立即返回的错误提示而明白其中的道理，或许这就是For Update和NOWAIT的意义之所在。

SELECT FOR UPDATE 游标 2006-9-13 10:40:00 By:xling

通常我们从游标中提取出数据都要进行一定的处理，在多数情况下都会修改由游标检索出来的行．为了方便对这样的处理进行操作，Oracle提供了一种方便的语法，包含两个部分：
1,在游标的声明部分加上FOR UPDATE 子句．
2,在UPDATE 或 DELETE语句中加上WHERE CURRENT OF 子句．

在SELECT语句中用了FOR UPDATE子句，就相当于给由SELECT语句提取出来的结果集加上互斥锁（EXCLUSIVE LOCK）实行行级锁定．当我们执行OPEN操作后，别的用户或会话就不能对该结果集中的行进行修改，直到整个事务被提交为止．

SELECT .. FROM .. FOR UPDATE [OF column_refreence] [NOWAIT]

下面的代码执行后，只有GUID大于3600的记录才被修改．

WHERE CURRENT OF 用来得到当前游标所检索出来的行，其所指定的游标一定要打开．

DECLARE
    CURSOR CC IS
        SELECT * FROM XLING_USER WHERE GUID >3600 FOR UPDATE;
BEGIN
  FOR C1 IN CC LOOP
    UPDATE XLING_USER SET DESP = DESP || '1' WHERE CURRENT OF CC;
  END LOOP;
  COMMIT;
END;

还有一个地方必须用到FOR UPDATE:对大型对象进修改性质的操作

SET SERVEROUT ON
DECLARE
    S_LOB CLOB;
    D_LOB CLOB;
    V_AMOUNT NUMBER;
BEGIN
  SELECT CLOB_LOCATOR INTO S_LOB FROM MYLOBS WHERE LOB_INDEX = 1;
  SELECT CLOB_LOCATOR INTO D_LOB FROM MYLOBS WHERE LOB_INDEX = 3 FOR UPDATE;--这里必须加上FOR UPDATE,否则RA-22920: 未锁定含有 LOB 值的行?

  V_AMOUNT := DBMS_LOB.GETLENGTH(S_LOB);
  DBMS_LOB.COPY(D_LOB,S_LOB,V_AMOUNT);
  COMMIT;
  --DBMS_OUTPUT.PUT_LINE(V_AMOUNT);
 
  SELECT CLOB_LOCATOR INTO S_LOB FROM MYLOBS WHERE LOB_INDEX = 2;
  SELECT CLOB_LOCATOR INTO D_LOB FROM MYLOBS WHERE LOB_INDEX = 4 FOR UPDATE;
  V_AMOUNT := DBMS_LOB.GETLENGTH(S_LOB);
  DBMS_LOB.APPEND(D_LOB,S_LOB);
  COMMIT;
END;

而非修改性质的操作，不必加：

SET SERVEROUT ON
DECLARE
    S_LOB CLOB;
    V_AMT INTEGER := 10; -- 要读取的长度
    V_OFFSET INTEGER :=10;--偏移量（从位置开始）
    V_BUFFER VARCHAR2(10);--读取的结果
BEGIN
  SELECT CLOB_LOCATOR INTO S_LOB FROM MYLOBS WHERE LOB_INDEX = 4;
  V_BUFFER := DBMS_LOB.SUBSTR(S_LOB,V_AMT,V_OFFSET);
  DBMS_OUTPUT.PUT_LINE(V_BUFFER);
END;

 for update 是为当前的查询加锁。利用这种方式可以大大的提高效率。下面的一个例子中利用有 for update of 的　游标更新数据。当然具体效率的提升情况需要用大数据量的处理来测试才能得出来。

 

 

declare

cursor gData(var1 varchar2) is select item_name,item_name_en,code_value from y0411 where item_name=var1 for update of code_value;
rs gData%rowtype;
begin
  open gData('钢管');
  loop
     fetch gData into rs;
     exit when gData%notfound;
         if rs.item_name='铝型材' then
            update y0411 set code_value='northsnow' Where Current Of gData;
         else
            update y0411 set code_value='塞北的雪' Where Current Of gData;
         end if;
  
  end loop;
  close gData;
end