The root cause for throwing this error is because our code has performed a lookup on the other db schema.
The method I executed that result in the above error is doing the following task.
1) Invoke the SLSB to create an entity (BMP). (Transaction attribute of SLSB is "Required") --> The entity is stored at db SCHEMA 1
2) Invoke the same SLSB to retrieve some entities. (Transaction attribute of SLSB is "Required") --> The entities is stored at db SCHEMA 2.. The retrieval is the cause of the error.
If I swap the entity to DB SCHEMA 1, i didn't see such an error. I am wondering why by swapping to schema 1, the problem will be resolved?
The following show the comparison before and after swap to schema 1
# Before swap the table to schema 1
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:1973]] getManagedConnection trackByTx=true tx=TransactionImple < ac, BasicAction: -3f572aa4:7a5:47df66fd:a78 status: ActionStatus.RUNNING >
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.JBossManagedConnectionPool] [WorkerThread#0[127.0.0.1:1973]] supplying ManagedConnection from pool: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1c66fa2[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@cb20ac handles=0 lastUse=1205823524048 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@19d9413 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@162cab9 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1ce28b8 txSync=null]
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.JBossManagedConnectionPool] [WorkerThread#0[127.0.0.1:1973]] Got connection from pool tracked by transaction org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1c66fa2[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@cb20ac handles=0 lastUse=1205823524048 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@19d9413 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@162cab9 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1ce28b8 txSync=null] tx=TransactionImple < ac, BasicAction: -3f572aa4:7a5:47df66fd:a78 status: ActionStatus.RUNNING > [InUse/Available/Max]: [1/19/20]
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.JBossManagedConnectionPool] [WorkerThread#0[127.0.0.1:1973]] Using connection from pool tracked by transaction org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1c66fa2[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@cb20ac handles=0 lastUse=1205823524048 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@19d9413 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@162cab9 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1ce28b8 txSync=null] tx=TransactionImple < ac, BasicAction: -3f572aa4:7a5:47df66fd:a78 status: ActionStatus.RUNNING > [InUse/Available/Max]: [1/19/20]
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:1973]] Pre-enlist: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1c66fa2[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@cb20ac handles=0 lastUse=1205823524048 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@19d9413 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@162cab9 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1ce28b8 txSync=null] threadTx=TransactionImple < ac, BasicAction: -3f572aa4:7a5:47df66fd:a78 status: ActionStatus.RUNNING >
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:1973]] Get synchronizer org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1c66fa2[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@cb20ac handles=0 lastUse=1205823524048 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@19d9413 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@162cab9 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1ce28b8 txSync=null] threadTx=TransactionImple < ac, BasicAction: -3f572aa4:7a5:47df66fd:a78 status: ActionStatus.RUNNING >
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:1973]] Enlisting resource org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1c66fa2[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@cb20ac handles=0 lastUse=1205823524048 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@19d9413 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@162cab9 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1ce28b8 txSync=TxSync30490554{tx=TransactionImple < ac, BasicAction: -3f572aa4:7a5:47df66fd:a78 status: ActionStatus.RUNNING > wasTrackByTx=true enlisted=false}]
[18:03:08-14:58:44,501] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:1973]] start, xid: < 131075, 28, 26, 1--3f572aa4:7a5:47df66fd:a78-3f572aa4:7a5:47df66fd:b85
# After swap to schema 1
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:2035]] getManagedConnection trackByTx=true tx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING >
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.JBossManagedConnectionPool] [WorkerThread#0[127.0.0.1:2035]] Previous connection tracked by transaction org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@5ba615[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@f4b724 handles=0 lastUse=1205824120828 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@1b5d036 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@23a321 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1aad6e1 txSync=TxSync30362139{tx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING > wasTrackByTx=true enlisted=true}] tx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING > [InUse/Available/Max]: [1/19/20]
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:2035]] Pre-enlist: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@5ba615[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@f4b724 handles=0 lastUse=1205824120828 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@1b5d036 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@23a321 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1aad6e1 txSync=TxSync30362139{tx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING > wasTrackByTx=true enlisted=true}] threadTx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING >
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:2035]] Get synchronizer org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@5ba615[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@f4b724 handles=0 lastUse=1205824120828 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@1b5d036 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@23a321 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1aad6e1 txSync=TxSync30362139{tx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING > wasTrackByTx=true enlisted=true}] threadTx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING >
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:2035]] Check enlisted org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@5ba615[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@f4b724 handles=0 lastUse=1205824120828 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@1b5d036 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@23a321 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1aad6e1 txSync=TxSync30362139{tx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING > wasTrackByTx=true enlisted=true}] threadTx=TransactionImple < ac, BasicAction: -3f572aa4:7d4:47df68c9:a7c status: ActionStatus.RUNNING >
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.CachedConnectionManager] [WorkerThread#0[127.0.0.1:2035]] registering connection from org.jboss.resource.connectionmanager.TxConnectionManager@3179f3, connection : org.jboss.resource.adapter.jdbc.WrappedConnection@142dd82, key: com.gridnode.gtas.server.gridnode.facade.ejb.GridNodeManagerBean@f3806c
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.TxConnectionManager] [WorkerThread#0[127.0.0.1:2035]] connectionClosed called mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@f4b724
[18:03:08-15:08:46,766] [TRACE:org.jboss.resource.connectionmanager.CachedConnectionManager] [WorkerThread#0[127.0.0.1:2035]] unregistering connection from org.jboss.resource.connectionmanager.TxConnectionManager@3179f3, object: org.jboss.resource.adapter.jdbc.WrappedConnection@142dd82, key: com.gridnode.gtas.server.gridnode.facade.ejb.GridNodeManagerBean@f3806c
The other approach i have tried is to change the transaction attribute of the SLSB to "RequiresNew", it also resolved the issue.
Could I know what is the approach we use if we deal with two DB schema in the same transaction?