I have a database with some tables and also data in them.I need to implement for all tables optimistic concurrency.
我有一个包含一些表的数据库,还有数据。我需要为所有表实现乐观并发。
I was wondering what would be the best way.
我想知道什么是最好的方式。
Query with a predicate will be created on the application side.
将在应用程序端创建带谓词的查询。
My concern is how-to store the rowversion(timestamp) value.
我关心的是如何存储rowversion(timestamp)值。
First I was thinking of using ora_rowscn for rowversion value,but then I realised I have to recreate all tables to set up ora_rowscn. Maybe just adding a some kind of timestamp column would be good,but then I would be forced to create and save a new timestamp value for every update in the application.
首先我考虑使用ora_rowscn进行rowversion值,但后来我意识到我必须重新创建所有表来设置ora_rowscn。也许只是添加某种时间戳列会很好,但是我会*为应用程序中的每次更新创建并保存一个新的时间戳值。
Any ideas ?
有任何想法吗 ?
2 个解决方案
#1
7
Oracle has a built-in package for optimistic locking called OWA_OPT_LOCK. This can be used to generate a checksum for any row like this:
Oracle有一个用于乐观锁定的内置包,名为OWA_OPT_LOCK。这可以用于为任何行生成校验和,如下所示:
select owa_opt_lock.checksum('SCOTT','EMP',ROWID)
from emp
where empno = 123;
This can be called when originally getting the record and again before saving the changes; if the 2 values are different, someone else has changed the record since you got it.
这可以在最初获取记录时调用,并在保存更改之前再次调用;如果2个值不同,则其他人在您获得记录后更改了记录。
#2
4
A very simple but effective pattern is to first fetch the whole row about to be edited without keeping a lock. When you are finally ready for the update, augment the where clause with clauses like COLUMNA='OLDVALUEA'. The number of changed records indicates if some other modification interfered with your optimistic update or not.
一个非常简单但有效的模式是首先获取要编辑的整行而不保持锁定。当您最终准备好进行更新时,请使用COLUMNA ='OLDVALUEA'之类的子句扩充where子句。更改的记录数表示是否有其他修改干扰了您的乐观更新。
This method will notice all modifications that are still in effect when you try update. Any method relying on checksums can falsely indicate that no modifications have taken place. The reliability you need depends on your application. I would not bet human lifes on checksums. However, I would try not to rely on optimistic updates in this case either.
此方法将注意到您尝试更新时仍然有效的所有修改。任何依赖校验和的方法都可能错误地表明没有进行任何修改。您需要的可靠性取决于您的应用。我不会在校验和上打赌人类生命。但是,在这种情况下,我会尽量不依赖乐观更新。
#1
7
Oracle has a built-in package for optimistic locking called OWA_OPT_LOCK. This can be used to generate a checksum for any row like this:
Oracle有一个用于乐观锁定的内置包,名为OWA_OPT_LOCK。这可以用于为任何行生成校验和,如下所示:
select owa_opt_lock.checksum('SCOTT','EMP',ROWID)
from emp
where empno = 123;
This can be called when originally getting the record and again before saving the changes; if the 2 values are different, someone else has changed the record since you got it.
这可以在最初获取记录时调用,并在保存更改之前再次调用;如果2个值不同,则其他人在您获得记录后更改了记录。
#2
4
A very simple but effective pattern is to first fetch the whole row about to be edited without keeping a lock. When you are finally ready for the update, augment the where clause with clauses like COLUMNA='OLDVALUEA'. The number of changed records indicates if some other modification interfered with your optimistic update or not.
一个非常简单但有效的模式是首先获取要编辑的整行而不保持锁定。当您最终准备好进行更新时,请使用COLUMNA ='OLDVALUEA'之类的子句扩充where子句。更改的记录数表示是否有其他修改干扰了您的乐观更新。
This method will notice all modifications that are still in effect when you try update. Any method relying on checksums can falsely indicate that no modifications have taken place. The reliability you need depends on your application. I would not bet human lifes on checksums. However, I would try not to rely on optimistic updates in this case either.
此方法将注意到您尝试更新时仍然有效的所有修改。任何依赖校验和的方法都可能错误地表明没有进行任何修改。您需要的可靠性取决于您的应用。我不会在校验和上打赌人类生命。但是,在这种情况下,我会尽量不依赖乐观更新。