交易问题 [英] Transaction Question
问题描述
我必须将当前使用Oracle DB
后端的java Web应用程序转换为使用Postgres后端的一个。
在Oracle的大部分内容中应用程序逻辑使用Oracle数据库中的存储过程从java
中间件层抽象出来。有一些在桌面上看起来没有丢失的Oracle中的某些功能，这些功能似乎让我们有些担忧，我们想知道我们将要做多少工作
必须屠杀后端和db存储过程。
问题 - 嵌套事务
=====
Oracle提供#pragma自主提示，允许您声明一个过程/函数将在其自己的事务中运行，并且可以是
已提交的
无论封闭的
交易的最终提交/回滚状态如何。
这对我们来说是个问题，因为有些程序使用了函数
在表上发出行级锁定（选择...进行更新）按顺序
读取然后更新计数器，然后提交释放
锁定。嵌套函数在返回时返回新的计数器值。我们
不能使用Sequence对象，因为计数器直接绑定到包含它的
记录，并且有任意数量的这些记录类型。
我们同时由多个线程调用该函数，如果
锁定仅在封闭事务结束时释放，那么
第一个之后的后续调用将阻塞，直到第一个完成。
换句话说，虽然线程并行调用，但它们只会因为瓶颈而连续运行
。
我看过一个关于使用单独的连接/线程解决这个问题的说明
。我们的java中间件没有可能使用两个
线程/连接来分离事务，因为这个想法是这样的，即
java对数据库进行一次调用，并且它处理所有并发问题
问题（精美）我们不必在中间件中嵌入数据库特定的代码/逻辑。
是有一个简单/优雅的解决方案吗？还有一个很好的处理并发问题的文件 - 我已经阅读了手册
7.4，虽然它描述了事务隔离级别，但MVCC - 它
并没有真正为这个问题提供任何实用技巧或解决方案。
感谢任何人都可以提供的任何信息。
John Sidney-Woollett
--------------------------- （广播结束）---------------------------
提示2：你可以在以下列出所有列表一次使用取消注册命令
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）
2003年12月3日星期三08:08，John Sidney-Woollett写道：< blockquote class =post_quotes>我必须将当前使用Oracle DB
后端的java Web应用程序转换为使用Postgres后端的Java Web应用程序。
[snip]问题 - 嵌套交易
=====
[snip]这对我们来说是一个问题，因为有些程序使用了一个函数
在表上发出行级锁定（选择...进行更新）以便读取然后更新计数器，然后提交释放
锁定。嵌套函数在返回时返回新的计数器值。我们
不能使用Sequence对象，因为计数器直接绑定到包含它的
记录，并且有许多这些记录类型。
你能详细说明这个柜台是什么/你如何使用它？这听起来好像是b&b。无论插入/更新是否提交
都会增加，这让我想知道它在计算什么。
这个问题有一个简单/优雅的解决方案吗？是否有关于处理并发问题的良好文档 - 我已经阅读了
7.4的手册，虽然它描述了事务隔离级别，但MVCC - 它并没有真正提供这个问题的任何实用技巧或解决方案。
嗯 - 我们似乎没有任何项目处理并发问题
techdocs.postgresql.org，这是一个耻辱，因为他们正好是因为有陷阱的例子而受益。
-
Richard Huxton
Archonet Ltd
--------------------- ------（播出结束）---------------------------
提示2：你可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma * ******@postgresql.org ）
以下是从Oracle的PL / SQL转换而来的两个程序（所以oracle
特定的东西（比如#pragma autonomous）已经消失了。
这第一个函数返回两个值（它曾经使用额外的一对
out参数）。你是正确的，因为函数应该递增
计数器，无论封闭的事务是否提交。
（当然在Postgres中并非如此）。
创建或替换函数GetVolumeFileReference（varchar，integer）
RETURNS整数AS''
- 分配一个卷和唯一文件用于存储资源的ID
- 根据
分配策略确定存储（文件）资源的位置
- pAllocStrategy可以是要么
- null，FIRST（默认）=在使用下一个之前填充第一个可用卷
- DISTRIBUTE =在可用卷上分配文件
DECLARE
pAllocStrategy ALIAS OF
1;
pSpaceReqd ALIAS OF
I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend.
In Oracle much of the application logic is abstracted away from the java
middleware layer using stored procedures within the Oracle database. There
are certain features in Oracle that appear to be missing from Postgres
which are causing us some concern, and we wondered how much we''re going to
have to butcher the backend and db stored procs.
Issue - nested transactions
=====
Oracle provides the #pragma autonomous hint which allows you to declare
that a procedure/function will run in its own transaction and which can be
committed regardless of the final commit/rollback state of the enclosing
transaction.
This is an issue for us because some procedures make use of a function
which issues a row level lock on a table (select ... for update) in order
to read and then update a counter, and which then commits to release the
lock. The nested function returns the new counter value on return. We
cannot use Sequence objects, because the counter is tied directly to the
record which contains it, and there are any number of these record types.
We have the function being called by many threads simultaneously, and if
the lock is only released at the end of the enclosing transaction, then
the subsequent calls after the first will block until the first completes.
In other words, although threads are making calls in parallel, they will
only run serially because of the bottleneck.
I have seen a note about using separate connections/threads to resolve
this issue. There is NO possibility of our java middleware using two
threads/connections to separate out the transaction as the idea is that
the java makes one call to the database, and it handles all concurrency
issues (beautifully) without us having to embed db specific code/logic in
the middleware.
Is there a simple/elegant solution to this problem? And is there a good
document on dealing with concurrency issues - I have read the manual for
7.4 and while it describes the transaction isolation levels, and MVCC - it
doesn''t really offer any practical tips or solutions to this problem.
Thanks for any info anyone can provide.
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
On Wednesday 03 December 2003 08:08, John Sidney-Woollett wrote:I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend. [snip] Issue - nested transactions
=====
[snip] This is an issue for us because some procedures make use of a function
which issues a row level lock on a table (select ... for update) in order
to read and then update a counter, and which then commits to release the
lock. The nested function returns the new counter value on return. We
cannot use Sequence objects, because the counter is tied directly to the
record which contains it, and there are any number of these record types.
Can you elaborate on what this counter is/how you are using it? It sounds like
the "counter" gets incremented regardless of whether an insert/update gets
committed, which makes me wonder what it is counting.
Is there a simple/elegant solution to this problem? And is there a good
document on dealing with concurrency issues - I have read the manual for
7.4 and while it describes the transaction isolation levels, and MVCC - it
doesn''t really offer any practical tips or solutions to this problem.
Hmm - we don''t seem to have any items dealing with concurrency issues on
techdocs.postgresql.org, which is a shame since they are exactly the sort of
thing benefit from having examples of pitfalls.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
Here are two procedures converted from Oracle''s PL/SQL (so the oracle
specific stuff (like #pragma autonomous) has gone.
This first function returns two values (it used to use an extra pair of
out parameters). You are correct in that the function SHOULD increment the
counter regardless of whether the enclosing transaction commits or not.
(Of course in Postgres this is not the case).
CREATE OR REPLACE FUNCTION GetVolumeFileReference (varchar, integer)
RETURNS integer AS ''
-- allocates a volume and unique file ID for storing a resource
-- determines where to store a (file) resource according to the
allocation strategy
-- pAllocStrategy can be either
-- null, FIRST (default) = fill first available volume before using next
-- DISTRIBUTE = distribute files across the available volumes
DECLARE
pAllocStrategy ALIAS OF
1;
pSpaceReqd ALIAS OF
这篇关于交易问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
