Optimistic lock
来源:互联网 发布:手机跑字典软件 编辑:程序博客网 时间:2024/05/19 22:02
We are optimistic that the data will not bechanged by some other user; hence ,we wait until the very last moment to findout if we are right.
There are many methods of implmentingoptimistic concurrency control. We’ve discussed one whereby the applicationwill store all of the before images of the row in the application itself.
1. Using a special column that ismaintained by a database trigger or application code to tell us the ‘version ’of the record.
2. Using the new oracle 10g feather ORA_ROWSCN
3 . Usinh a checksum or hash thatwas computed using the original data
Optimistic locking using a version column
SQL> create table dept 2 ( deptno number(2), 3 dname varchar2(14), 4 loc varchar2(13), 5 last_mod timestamp with time zone 6 default systimestamp 7 not null, 8 constraint dept_pk primary key(deptno) 9 ) 10 /表已创建。SQL> insert into dept(deptno,dname,loc) 2 select deptno,dname,loc 3 from scott.dept;已创建4行。SQL> commit;提交完成。SQL> variable deptno numberSQL> viriable dname varchar2(14)SP2-0734: 未知的命令开头 "viriable d..." - 忽略了剩余的行。SQL> variable dname varchar2(14)SQL> variable loc varchar2(13)SQL> variable last_mod varchar2(50)SQL>SQL> begin 2 :deptno := 10; 3 select dname,loc, last_mod 4 into :dname,:loc,:last_mod 5 from dept 6 where deptno = :deptno; 7 end; 8 /PL/SQL 过程已成功完成。SQL> select :deptno dno, :dname dname, :loc loc, :last_mod lm 2 from dual; DNO DNAME LOC---------- -------------------------------- --------------------------------LM------------------------------------------------------------------------------- 10 ACCOUNTING NEW YORK09-4月 -11 08.54.13.125000 下午 +08:00SQL> update dept 2 set dname = initcap(:dname), 3 last_mod = systimestamp 4 where deptno = :deptno 5 and last_mod = to_timestamp_tz(:last_mod);已更新 1 行。再执行一次SQL> update dept 2 set dname = initcap(:dname), 3 last_mod = systimestamp 4 where deptno = :deptno 5 and last_mod = to_timestamp_tz(:last_mod);已更新0行。So, in this case, I suggest encapsulating the update logic in the procedure and not allowing the application to update table directly at all. If it cannot bee trusted to maintain the value in this field, then it cannot be trusted to check it properly either. So the stored procedure would take as inputs the bind variables we used in the previous updates and do exactly the same update. Upon detecting that zero rows were updated. The stored procedure could raise an exception back to the client know the update had, in effect,faild.
另附itpub上的帖子,讲解的很不错
http://www.itpub.net/viewthread.php?tid=466512&highlight=Optimistic%2Blocking
Optimistic locking using a checksum
Starting with oracle 10g release 1, you have the option to use thebuild-in ORA_ROWSCN function.
Optimistic locking using ORA_ROWSCN
ORA_SCN是基于oracle的SYSTEM CLOCK的,即SCN。
This is, by default many rows on singleblock will share the same ORA_ROWSCN value. So you must create the table tosupport the maintenance of ORA_ROWSCN at the row level,the default is at theblock level.
实验SQL> create table dept 2 (deptno,dname,loc,data, 3 constraint dept_pk primary key(deptno) 4 ) 5 as 6 select deptno,dname,loc,rpad('*',3500,'*') 7 from scott.dept;SQL> select deptno,dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 ACCOUNTING 308 4880552 20 RESEARCH 308 4880552 30 SALES 309 4880552 40 OPERATIONS 309 4880552
对DEPT表进行更新
What we will observe next shows theconsequence of ORA_ROWSCN being tracked at the block level. We modified andcommited the changes to single row, but the ORA_ROWSCN values of both of therows on block 308 have been advanceed.
SQL> R 1 select deptno,dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4* from dept DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 accounting 308 4881400 20 RESEARCH 308 4881400 30 SALES 309 4880552 40 OPERATIONS 309 4880552
在更新DEPTNO=10的时候,DEPTNO=20的也被更新了。
So the question becomes howto modify this default behavior. Well,unfortunately, we have to re-create thesegment with ROWDEPENDENCIES enabled.
SQL> drop table dept;Table dropped.SQL> create table dept 2 (deptno,dname,loc,data, 3 constraint dept_pk primary key(deptno) 4 ) 5 ROWDEPENDENCIES 6 as 7 select deptno,dname,loc,rpad('*',3500,'*') 8 from scott.dept; Table created.SQL>SQL> select deptno,dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 ACCOUNTING 324 4888258 20 RESEARCH 324 4888258 30 SALES 325 4888258 40 OPERATIONS 325 4888258SQL> update dept 2 set dname = lower(dname) 3 where deptno = 10;1 row updated.SQL> commit;Commit complete.SQL> select deptno,dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 accounting 324 4890100 20 RESEARCH 324 4888258 30 SALES 325 4888258 40 OPERATIONS 325 4888258
The only modified ORA_ROWSCN at this pointbelongs to DEPTNO = 10, exactly what we wanted. We can now rely on ORA_ROWSCNto detect row-level changes for us.
- Optimistic lock
- hibernate中的optimistic-lock(...
- hibernate中的optimistic-lock(...
- Lock Mode Type 之 Optimistic 使用场景
- Lock Mode Type 之 Optimistic 使用场景
- Hibernate中的悲观锁(pessimistic lock)和乐观锁(optimistic lock)
- Hibernate 中的悲观锁( pessimistic lock )和乐观锁( optimistic lock )
- 乐观锁(Optimistic Lock)之version(版本号)与timestamp(时间戳)配置解读
- just be optimistic!
- Conditional Update (Optimistic Concurrency)
- Optimistic locking---PoEAA
- Pessimistic and Optimistic locking
- Hibernate Gossip: 樂觀鎖定(Optimistic locking)
- 英语学习路(一) optimistic
- LOCK
- LOCK
- lock
- lock
- 网站SEO查询相关指令
- Android AES加密算法及其实现
- 异常的探索-Thinking in java
- 一些特殊的JS扩展的实例
- 学习设计模式的一些常见问题
- Optimistic lock
- Java程序的汉化
- WCF 自定义异常
- LINUX命令行下如何附带参数运行PHP脚本实例详解
- tomcat的work目录
- Android程序完全退出的三种方法
- Android应用完全退出终极篇
- git log 用法大全
- 读书笔记-----类和动态内存分配