oracle连接无法支持并行事务 [英] oracle connection cannot support parallel transactions

问题描述

大家好，

实际上，我的Windows服务执行一些数据库事务，例如插入，选择和更新.
根据传入的请求，将执行脚本.我正在为整个应用程序维护一个Oracle连接.当我的服务收到两个请求，试图使用相同的oracle连接和begintransaction()方法同时执行插入和更新时，我遇到了这个问题.

我的代码是这样的:

Hi All,

Actually my windows service does some database transactions like insert, select and update.
Based on the incoming requests scripts will be executed. I am maintaining one Oracle connection for whole the application. I am facing this problem when my service receives two requests in which it is trying to perform simultaneous inserts and updates using the same oracle connection and begintransaction() method.

My code is some thing like this:

public static oracleconnection  dbconn = new oracleconnection("...");
OracleTransaction dbtrans=null;
public void insert()
{
    dbconn.open();
    dbconn.begintx();
    // more code here ...
}

public void update()
{
    dbconn.open();
    dbconn.begintx();
    // more code here ...
}
public transaction begintx()
{
    return dbconn.BeginTransaction();
}

当同时调用这两种方法时，我得到了错误
"oracle connection cannot support parallel transactions"因为这里使用了相同的Oracle连接，并且每次Google搜索都只允许进行一次事务.


谁能提出解决方案.


谢谢！

When both the methods are called simultaneously I am getting the error
"oracle connection cannot support parallel transactions" since the same Oracle connection is used here and only one transaction is allowed as I found out per google search.


Can any one suggest a solution how to handle this.


Thank you!

推荐答案

我使用Oracle已有很长时间了，但是请尝试使用此方法

Been a long time since I used Oracle, but try this

string yourconnectionstring = "user id=username;" +
                              "password=yourpassword;" +
                              "data source=yourdatasource;" +
                              "enlist=false" +
                              "pooling=false";

1.抱歉，如果需要进行两个并行事务，唯一的解决方法是对每个并发事务使用两个单独的连接，或者更好的方法是对每个并发事务使用单独的连接.
如果您使用连接池，并且使用适当数量的连接来初始化连接池，那么建立新(免费)连接的时间也应该可以容忍.

2.您也可以选择序列化连接上的所有操作.您可以在请求进入时对工作项进行排队，并在一个单独的线程中通过使它们出队并进行数据库处理来处理它们. 注意！对于服务而言，这不是一个明智的选择，因为按顺序执行数据库请求会降低所有速度，并且不是可扩展的解决方案.
如果不允许建立多个连接，这可能是唯一的方法.

侧面注意
不过，您需要注意的一件事是Oracle DB管理员.上班时间，我用50个并行连接和大量查询淹没了其中一台服务器，而他们几乎为此而烦恼，因为该服务器也被其他人使用，并且他们不再能够使用数据库.因此，可能会很努力地询问您允许使用多少个并行连接. :)


问候，


曼弗雷德(Manfred)

1. Sorry, the only way to go here if you need to have two parallel transactions is to use two separate connections, or better yet a separate connection for each concurrent transaction.
If you use connection pooling the time to get a new (free) connection should also be tolerable if you initalize the connection pool with an appropriate amount of connections.

2. You can also go for the option to serialize all operations on the connection. You queue up work items as the requests come in and in a separate thread you handle them by dequeueing them and doing the DB processing stuff. Caveat!: This is not an advisable scenario for a service as doing the DB requests sequentially will slow everything down and it''s not a scalable solution.
It might be the only way to go though if you''re not allowed to make more than one connection.

Note on the side:
One thing you have to look out for is the Oracle DB administrators though. One time at work I swamped one of their servers with 50 parallel connections and a heavy load of queries and they almost fried me for that, since the server was also used by others and they were no longer able to use the database. So it might be diligent to inquire how many parallel connections you''re allowed to use. :)


Regards,


Manfred

这篇关于oracle连接无法支持并行事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！