java “存储过程‘xxx’只能在非链式事务模式下运行。” 在 EJB 上下文中从数据源调用过程时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3873058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Stored procedure 'xxx' may be run only in unchained transaction mode." error when calling a procedure from a DataSource, in an EJB context
提问by Gnoupi
We have a Database in Sybase, which we access from a Java server.
我们在 Sybase 中有一个数据库，我们可以从 Java 服务器访问它。
Access to the DB was made directly through the Sybase driver, using DriverManager. It was working correctly, we were able to call our stored procedures.
直接通过 Sybase 驱动程序访问数据库，使用DriverManager. 它工作正常，我们能够调用我们的存储过程。
Recently, we are migrating to an application server (on JBoss 5), and the calls to the database are now made through a JNDI connector, using a DataSource:
最近，我们正在迁移到应用程序服务器（在 JBoss 5 上），现在通过 JNDI 连接器调用数据库，使用DataSource：
Properties ppt = new Properties();
ppt.put("java.naming.factory.initial", "org.jnp.interfaces.NamingContextFactory");
ppt.put("java.naming.factory.url.pkgs", "org.jboss.naming:org.jnp.interfaces");
ppt.put("java.naming.provider.url", "jdbc/sybase");
InitialContext ctx = new InitialContext(ppt);
DataSource ds = (DataSource) ctx.lookup(AConfig.getInstance().getDatasourceJndiName());
Connection conn = ds.getConnection();
(The DataSource is configured using the basic settings, from the JBoss example)
（数据源是使用基本设置配置的，来自JBoss 示例）
However, in this setting, several procedures are failing, with this error:
但是，在此设置中，有几个过程失败，并出现以下错误：
"Stored procedure '**' may be run only in unchained transaction mode."
“存储过程‘ **’只能在非链式事务模式下运行。”
or this kind, for other cases (with the failing command changing):
或这种，对于其他情况（失败的命令发生变化）：
TRUNCATE TABLE command not allowed within multi-statement transaction
多语句事务中不允许使用 TRUNCATE TABLE 命令
From what I found on Internet, it looks like something in the JBoss or the connector is opening a transaction itself, causing these errors. As such, the diverse solutions I could find for these particular problems are too localized, and it seems like a bigger issue.
根据我在 Internet 上的发现，JBoss 中的某些内容或连接器本身正在打开事务，从而导致这些错误。因此，我可以为这些特定问题找到的各种解决方案过于本地化，这似乎是一个更大的问题。
Is there a way to prevent this behaviour (assuming that this is the actual problem)?
有没有办法防止这种行为（假设这是实际问题）？
My knowledge in this particular field is quite thin, this is new to me. As such, there are probably important details missing to this description. Please indicate me how I can improve this question, what details I can add, if necessary.
我在这个特定领域的知识很薄，这对我来说是新的。因此，此描述中可能缺少重要的细节。请告诉我如何改进这个问题，如有必要，我可以添加哪些细节。
采纳答案by PerformanceDBA
That is not correct.
那是不正确的。
Certainly look at the bigger picture. But there is an even bigger picture still.
当然要看大局。但还有更大的图景。
Application server or not, is not the problem. Settings in JBoss vs the previous app (Java) server is the problem. Your coders have correctly ensured that their stored procs execute true transactions, and they are protected from subversion by outside entities (anycalling stored proc or app server). If they have done that, then those sprocs will run from anyapp server.
应用服务器与否，不是问题。JBoss 与以前的应用程序 (Java) 服务器中的设置是问题所在。您的编码人员已正确确保他们的存储过程执行真正的事务，并且他们受到保护，免受外部实体（任何调用存储过程或应用程序服务器）的破坏。如果他们这样做了，那么这些 sproc 将在任何应用服务器上运行。
"Refactoring" is for the MS world, it is not required at all in the Sybase or Relational world. If you change the sprocs to remove the tight transaction control, the business will suffer: loss of data integrity; loss of referential integrity; lost updates; duplicate transactions; etc. If you are going to subvert the sprocs, or remove transaction control (as opposed to "refactor"), be warned that the consequence is enormous.
“重构”适用于 MS 世界，在 Sybase 或 Relational 世界中根本不需要。如果您更改 sprocs 以消除严格的事务控制，业务将遭受：数据完整性丢失；失去参照完整性；丢失更新；重复交易；等等。如果你打算破坏 sprocs，或者删除事务控制（而不是“重构”），请注意后果是巨大的。
Clearly, JBoss is defaulting to either AUTOCOMMIT or SET CHAINED ON (because many people do not write true transactions, and these are the defaults for MS SQL ), and your previous Java (app) server did not do that.
显然，JBoss 默认为 AUTOCOMMIT 或 SET CHAINED ON（因为很多人没有编写真正的事务，而这些是 MS SQL 的默认值），而您以前的 Java（应用程序）服务器没有这样做。
Second, ODBC is very slow compared with a direct connection, so if you have not felt it yet, be aware that you will, very soon. Datasources aren't "implemented", they are merely configured (takes a few minutes). They use ODBC or JDBC. It is a FAT layer between, and places a small buffer between, the program and the database, and of course gives up all the control that you had and enjoyed before, when you had the native connection. I have seen it as much as twelve timesslower.
其次，与直接连接相比，ODBC 速度非常慢，所以如果您还没有感觉到它，请注意您很快就会感觉到。数据源不是“实现”的，它们只是被配置（需要几分钟）。他们使用 ODBC 或 JDBC。它是程序和数据库之间的 FAT 层，并在程序和数据库之间放置了一个小缓冲区，当然，当您拥有本机连接时，它放弃了您之前拥有和享受的所有控制权。我已经看到它慢了十二倍。
Third, didn't anyone check JBoss out (a) before choosing it, for Sybase native connectivity (as opposed to generic, MS oriented), (b) during implementation and (c) during testing ?
第三，没有人在选择 JBoss 之前检查过 (a)，对于 Sybase 本机连接（相对于通用的，面向 MS 的），(b) 在实施过程中和 (c) 在测试过程中吗？
If your connections are the problem, certainly, then just deal with the connection problem, and implement connection pooling (Java and Sybase have libraries for that), rather than reducing the quality and performance of your app, as well as the Consistency (that's the C in ACID) of the database.
如果您的连接是问题，当然，那么只需处理连接问题，并实现连接池（Java 和 Sybase 有用于此的库），而不是降低应用程序的质量和性能，以及一致性（这就是C 在 ACID 中）的数据库。
EAServer (Sybase) and WebShpere (IBM) have no such problems; they perform connection pooling; and they use native connection to ASE (no ODBC or JDBC required).
EAServer（Sybase）和WebShpere（IBM）没有这样的问题；他们执行连接池；并且它们使用到 ASE 的本机连接（不需要 ODBC 或 JDBC）。
回答by Miguel Ortega
Apparently in Sybase stored procedures are created to run either in chained or unchained mode.
显然，在 Sybase 中，存储过程被创建为以链式或非链式模式运行。
If you are getting this error it means your SP was created as Unchained. This Java line conn.setAutoCommit(false);is translated to "set chained on".
如果您收到此错误，则表示您的 SP 已创建为 Unchained。此 Java 行conn.setAutoCommit(false);被翻译为“set chained on”。
You can run this Sybase SP to list the transaction mode of all your SP's in your DB:
您可以运行此 Sybase SP 以列出数据库中所有 SP 的事务模式：
sp_procxmode
So you need to invoke your SP on the unchained mode and explicitly use, create transaction, commit transaction and rollback transaction.
所以你需要在 unchained 模式下调用你的 SP 并显式地使用、创建事务、提交事务和回滚事务。
For instance:
例如：
insert into publishers
values ("9906", null, null, null)
begin transaction
delete from publishers where pub_id = "9906"
rollback transaction
回答by Martijn Verburg
This might help: http://forum.springsource.org/showthread.php?t=49398, 'Holly' appears to have solved the problem
这可能会有所帮助：http: //forum.springsource.org/showthread.php?t=49398，'Holly' 似乎已经解决了这个问题
