Hello guys.
I'm having deadlocks when getting the next sequence number for a table. Bellow is the configurations I'm using:
1- Toplink 10.1.2
2- JTA transaction with xternal connection pool
3- Oracle database
4- I'm using toplink table sequence strategy
5- The session.xml
<?xml version="1.0" encoding="UTF-8"?>
<toplink-sessions version="10g Release 3 (10.1.3.0.0)" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<session xsi:type="server-session">
<name>PersistenceProject</name>
<server-platform xsi:type="oc4j-1013-platform"/>
<event-listener-classes/>
<logging xsi:type="toplink-log">
<log-level>all</log-level>
</logging>
<primary-project xsi:type="xml">META-INF/PersistenceProject.xml</primary-project>
<login xsi:type="database-login">
<platform-class>oracle.toplink.platform.database.oracle.Oracle10Platform</platform-class>
<password>xxx</password>
<external-connection-pooling>true</external-connection-pooling>
<external-transaction-controller>true</external-transaction-controller>
<sequencing>
<default-sequence xsi:type="table-sequence">
<name>Custom</name>
<table>SEQUENCIA</table>
<name-field>SEQ_NOME</name-field>
<counter-field>SEQ_CONTADOR</counter-field>
</default-sequence>
</sequencing>
<datasource>jdbc/toplink_testesDS</datasource>
<bind-all-parameters>true</bind-all-parameters>
<cache-all-statements>true</cache-all-statements>
<batch-writing>true</batch-writing>
</login>
<connection-pools>
<read-connection-pool>
<name>ReadConnectionPool</name>
<exclusive>false</exclusive>
</read-connection-pool>
<write-connection-pool>
<name>default</name>
</write-connection-pool>
<sequence-connection-pool>
<name>SequenceConnectionPool</name>
<max-connections>2</max-connections>
<min-connections>2</min-connections>
<login xsi:type="database-login">
<platform-class>oracle.toplink.platform.database.oracle.Oracle10Platform</platform-class>
<password>xxx</password>
<sequencing>
<default-sequence xsi:type="table-sequence">
<name>Default</name>
</default-sequence>
</sequencing>
<datasource>jdbc/toplink_testesDS</datasource>
</login>
</sequence-connection-pool>
</connection-pools>
<connection-policy/>
</session>
</toplink-sessions>
I realized that Toplink is obtaining the sequence number in the same transaction of the business rule I'm executing. I've read on the manual that using a table sequence can cause deadlocks in the sequence table and that is really happening. I tryed to raise the pre-alocation size but the problem still happens when we have high number of transactions.
I tryed to do what the manual recomends: using a sequence connection pool configured at session.xml. I configured the sequence pool on many ways and I have always a toplink error:
1- Using a native connection pool setting the "driver manager" as the workbench "Database Driver option ", "oracle.jdbc.driver.OracleDriver" as the driver class, "jdbc:oracle:thin:@XXX.xxx.xxx.XXX:1521:PORTALDS" as the connection URL, the username and passws set.
I've got the TOPLINK-7104 error explaining "the sequence login can't use an external transaction control".
2- Using a external connection pool "J2EE Datasource" as the workbench "Database Driver" option.
I've got the TOPLINK-7104 error explaining "the sequence login can't use an external transaction control".
The manual is clear that you have to use a sequence connection pool when using table sequence strategy and JTA (page 999), but is does not explain how to configure it within the application server and in the Jdeveloper.
How to configure correctly? Or does this strategy a better choice than oracle native sequence ?
regards
Eros
Message was edited by:
user607167