如何在多线程环境中生成PreparedStatements？
我有一个多线程代码的工作版本，但是我对我的PreparedStatement-wrapperclass 是非线程安全的感到不满意。因此，我决定在 ThreadLocal 中生成PreparedStatements 以使包装器线程安全。
SQLException 立即开始向我招手。底层的 Oracle 错误是检测到 ORA-00060 死锁，根据互联网的说法，它应该只发生在写入场景中。我的陈述都是只读的。它出现在一些我既没有听说过也没有有意识地访问过的不起眼的 ACL 包中。
我花了一些时间和精力准备并测试了一个假设，即生成 Connection 对象并从 DataSource 对象中准备该对象的语句不应发生在同时，因为数据库的访问控制可能不是“线程安全的”（尽管 DataSource 肯定是）。有人可以证实或否认这一发现吗？
如果确实如此，是否有最佳实践如何避免在多线程应用程序中同时生成PreparedStatements？
编辑：正如所询问的异常文本：
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "XXX.PKG_ACL", line 129
ORA-06512: at "XXX.PKG_ACL", line 459
ORA-06512: at "XXX.PKG_UTILS", line 1933
ORA-06512: at line 6
I've had a working version of multi-threaded code, however I was unsatisfied with my PreparedStatement-wrapperclass being non-threadsafe. So I decided to generate the PreparedStatements in a ThreadLocal to make the wrapper threadsafe.
Immediately the SQLExceptions startet to hail down on me. The underlying Oracle-error is an ORA-00060 deadlock detected which should - according to the internet - only occur in write scenarios. My statements are all read-only. It occurs in some obscure ACL-package which I've neither heard of nor accessed consciously.
I've with some time and effort prepared and tested a hypothesis which is, generating a Connection object and preparing a statement from that Object from a DataSource object should not occur at the very same time because the access control of the database might not be "threadsafe" (althoug DataSource most certainly is). Can someone confirm or deny that finding?
If this is indeed the case, is there a best-practise how to avoid that PreparedStatements are generated at the very same time in a multithreaded application?
EDIT: As asked the text of the exception:
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "XXX.PKG_ACL", line 129
ORA-06512: at "XXX.PKG_ACL", line 459
ORA-06512: at "XXX.PKG_UTILS", line 1933
ORA-06512: at line 6
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我还没有听到任何类似的消息，如果它确实存在，那可能纯粹是一个实现细节。您可以发布您的堆栈跟踪（当然是通过剥离个人信息）吗？
另外，您为什么要尝试使
PreparedStatement线程安全而不是依赖准备好的语句池/缓存？或者更具体地说，什么样的分析促使您实现ThreadLocalPreparedStatement？I haven't heard anything along those lines, and if it does exist, it might purely be an implementation detail. Can you post your stack trace (by stripping personal info of course)?
Also, any reason why you are trying to make
PreparedStatementthread safe rather than relying on the prepared-statement pool/cache? Or more specifically, what kind of analysis drives you to implement aThreadLocalPreparedStatement?一条语句属于一个连接，连接不能真正并发使用。线程通常应该首先“拥有”一个连接，然后再在其上执行语句。所以就按照这个成语
你无法避免[1]和[4]，它们是必要的；反正如果集中的话也不会太贵。
您想要缓存[2]，这也没有必要，驱动程序可能已经进行了缓存。
A statement belongs to a connection, and a connection cannot really be used concurrently. A thread typically should "own" a connection first, before executing statements on it. So just follow the idiom
You can't avoid [1] and [4], they are necessary; not too expensive anyway if pooled.
You want to cache [2], that's not necessary either, the driver probably already does the caching.