SQL的SELECT FOR UPDATE游标

remington_young

　　游标SELECT操作将不会对正处理的行执行任何锁定设置，这使得连接到该数据库的其他会话可以改变正在选择的数据，使用FOR UPDATE子句，在OPEN返回以前的活动集的相应行上会加上互斥锁，这些锁会避免其他的会话对活动集中的行进行更改。直到整个事务被提交为止。
　　示例：
　　DECLARE cur CURSOR FOR SELECT * FROM 　　FOR UPDATE OF [Table.col]
　　OPEN cur
　　WHILE @@FETCH_STATUS=0
　　BEGIN
　　UPDATE [Table] SET [Table.col] WHILE CURRENT OF cur
　　END
　　CLOSE cur
　　DEALLOCATE cur
　　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。
[table]　　SELECT.... FOR UPDATE [OF cols] [NOWAIT];
OF cols
SELECT 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 b 2 where b.status='VALID' and a.object_id=b.object_id 3* 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 b 2 where b.status='VALID' and a.object_id=b.object_id 3* 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 NOWAITspecified"
　　另外如下用法也值得推荐，应该酌情考虑使用。

FOR UPDATE WAIT 5

　　5秒后会出现提示：

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

no rows selected TABLE LOCKS LOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];

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

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

　　也就是说两个transaction都相互试图去lock对方已经lock的ROW，都在等待对方释放自己的lock，这样就使死锁。另外，deadlock也会有600提示。
　　版权声明：本文为博主原创文章，未经博主允许不得转载。
　　一：认识游标
　　游标(Cursor)它使用户可逐行访问由SQLServer返回的结果集。
　　使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。
　　用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。
　　游标机制允许用户在SQLserver内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。
　　二：游标的基本形式
　　声明游标：形式1
　　DECLARE cursor_name[INSENSITIVE] [SCROLL] CURSOR
　　FOR select_statement
　　[FOR {READ ONLY | UPDATE ][OFcolumn_list]}]
　　形式2
　　DECLARE cursor_name CURSOR
　　[LOCAL | GLOBAL]
　　[FORWARD_ONLY | SCROLL]
　　[STATIC | KEYSET | DYNAMIC]
　　[READ_ONLY | SCROLL_LOCKS |OPTIMISTIC]
　　FOR select_statement
　　[FOR {READ ONLY | UPDATE ][OFcolumn_list]}]
　　INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。
　　如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
　　SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。
　　如果忽略该选项，则游标只能向前滚动（next）。
　　Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
　　READ ONLY指明在游标结果集中不允许进行数据修改。
　　UPDATE关键字指明游标的结果集可以修改。
　　OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
　　LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
　　GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
　　FORWARD_ONLY指明游标只能向前滚动。
　　STATIC的游标与INSENSITIVE的游标是相同的。
　　KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。
　　因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
　　DYNAMIC指明游标将反映所有对结果集的修改。
　　SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
　　OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
　　注意：
　　· 如果在SELECT语句中使用了DISTINCT、UNION、GROUPBY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
　　· 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
　　· 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。
　　如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
　　打开游标
　　打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
　　OPEN { { [GLOBAL] cursor_name }| cursor_variable_name}
　　GLOBAL指明一个全局游标。
　　Cursor_name是被打开的游标的名称。
　　Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
　　在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。
　　@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。
　　异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
　　从游标中取值
　　在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。
　　如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。
　　对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
　　FETCH [NEXT | PRIOR| FIRST |LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
　　FROM [GLOBAL] cursor_name} |cursor_variable_name}
　　[INTO @variable_name ][,……n]]
　　NEXT指明从当前行的下一行取值。
　　PRIOR指明从当前行的前一行取值。
　　FIRST是结果集的第一行。
　　LAST是结果集的最后一行。
　　ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
　　RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
　　INTO @cursor_variable_name表示游标列值存储的地方的变量列表。
　　该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。
　　变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
　　每一次FETCH的执行都存储在系统变量@@fetch_status中。
　　如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。
　　@@fetch_status可以用来构造游标处理的循环。
　　关闭游标
　　CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
　　CLOSE [GLOBAL] cursor_name |cursor_variable_name
　　释放游标
　　游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
　　DEALLOCATE [GLOBAL] cursor_name| cursor_variable_name
　　三：游标的基本使用模板
　　declare ：
　　declare  游标名[scroll] cursor  for select语句[forupdate [of列表名]]
　　定义一个游标，使之对应一个select语句
　　forupdate任选项，表示该游标可用于对当前行的修改与删除
　　open
　　打开一个游标，执行游标对应的查询，结果集合为该游标的活动集
　　open  游标名
　　fetch
　　在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中
　　fetch[next | prior | first | last | current | relative n | absolute m] 游标名into [变量表]
　　close
　　关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句
　　close  游标名
　　deallocate
　　删除游标，以后不能再对该游标执行open语句
　　deallocate 游标名
　　@@FETCH_STATUS
　　返回被FETCH 语句执行的最后游标的状态.
　　0 fetch语句成功
　　-1fetch语句失败
　　-2被提取的行不存在
　　例：DECLARE Employee_Cursor CURSOR FORSELECTEmployeeID, Title
　　FROM AdventureWorks.HumanResources.Employee;
　　OPEN Employee_Cursor;FETCH NEXT FROM Employee_Cursor;
　　WHILE @@FETCH_STATUS = 0   
　　BEGIN      
　　--//TO DO...
　　FETCH NEXT FROM Employee_Cursor;   
　　END;
　　CLOSE Employee_Cursor;DEALLOCATE Employee_Cursor;
　　GO
　　四：游标性能问题
　　最好的改进游标性能的技术就是：能避免时就避免使用游标，尽可能用对应的语句完成相同的功能（一般情况下，考虑得当效率能大大提升）。
　　SQL Server是关系数据库，其处理数据集比处理单行好得多，单独行的访问根本不适合关系DBMS。
　　若有时无法避免使用游标，则可以用如下技巧来优化游标的性能。
　　(1). 除非必要否则不要使用static/insensitive游标。打开static游标会造成所有的行都被拷贝到临时表。
　　这正是为什么它对变化不敏感的原因——它实际上是指向临时数据库表中的一个备份。
　　很自然，结果集越大，声明其上的static游标就会引起越多的临时数据库的资源争夺问题。
　　(2). 除非必要否则不要使用keyset游标。和static游标一样，打开keyset游标会创建临时表。
　　虽然这个表只包括基本表的一个关键字列(除非不存在唯一关键字)，但是当处理大结果集时还是会相当大的。
　　(3). 当处理单向的只读结果集时，使用fast_forward代替forward_only。使
　　用fast_forward定义一个forward_only，则read_only游标具有一定的内部性能优化。
　　(4). 使用read_only关键字定义只读游标。这样可以防止意外的修改，并且让服务器了解游标移动时不会修改行。
　　(5). 小心事务处理中通过游标进行的大量行修改。根据事务隔离级别，这些行在事务完成或回滚前会保持锁定，这可能造成服务器上的资源争夺。
　　(6). 小心动态光标的修改，尤其是建在非唯一聚集索引键的表上的游标，因为他们会造成“Halloween”问题——对同一行或同一行的重复的错误的修改。
　　因为SQL Server在内部会把某行的关键字修改成一个已经存在的值，并强迫服务器追加下标，使它以后可以再结果集中移动。
　　当从结果集的剩余项中存取时，又会遇到那一行，然后程序会重复，结果造成死循环.
　　(7). 对于大结果集要考虑使用异步游标，尽可能地把控制权交给调用者。当返回相当大的结果集到可移动的表格时，异步游标特别有用.