> Forgot to mention essential information:
>
> The EntityManager uses the same DataSource as the connection which
> updates the table...
Sure, but DataSource is only a factory for JDBC connection instances --
not more. It can be implemented in a lot of ways (e. g. it can pool, or
create new instances all the time, etc.) -- you have neither control
about nor any guarantee. If it is implemented in a way that you get a
new JDBC Connection instance, you'll get a new TX. So how to guarantee
that EntityManager is using the same JDBC Connection instance? Since you
cannot register a non-XA JDBC connection to use a preexisting TX (AFAIK
only XADataSource has the needed methods to do that), you will end up
with using the same DataSource (factory) but different TXs. If you need
the same TX then I do not see another way than using XA (which, in turn,
means using 2PC). Or is there a way to tell an EntityManager to use a
preexisting JDBC Connection instance (have not checked the API, actually)?
Besides that I think it should work to clear the JPA cache -- the O/R
mapper is forced to reload from the DB then (what certainly is a major
performance drawback and might not work depending on the actual TX
isolation level).
Have Fun
Markus
>
> regards,
>
> adam
> Adam Bien schrieb:
>> Markus,
>>
>> I use both in the same TX. So using the same connection is the only
>> way to keep the data consistent. Otherwise you would really have to
>> use XA.
>>
>> On WLS 10 it worked without XA - the configuration was very
>> similar... However the connection is only my suspicion. Aggresive
>> caching could be the cause as well...
>>
>> regards,
>>
>> adam
>> Markus KARG schrieb:
>>> Adam,
>>>
>>> what makes you so certain that the EntityManager and the DataSource
>>> are using technically the same JDBC Connection instance (unless they
>>> will do, they will end up with two different connections provided
>>> over the same physical link)? I mean, unless you take explicit care
>>> for that, the EntityManager will use a different JDBC Connection
>>> provided by the same DataSource.
>>>
>>> Have Fun
>>> Markus
>>>
>>> Adam Bien schrieb:
>>>> Markus,
>>>>
>>>> you are right, but actually I do not want to have 2PC. I would
>>>> expect, that the EntityManager as well as the DataSource would use
>>>> exactly the *same* connection.
>>>> Both are accessing the same resource - so 2PC / XA are actually not
>>>> needed. The application server should not initiate a
>>>> two-phase-commit protocol in this case.
>>>>
>>>> thank you for the link and help!,
>>>>
>>>> adam
>>>>
>>>> Markus KARG schrieb:
>>>>> Adam,
>>>>>
>>>>> I guess that the problem is that you declared the connection
>>>>> factory to use DataSource instead of XADataSource. AFAIK only
>>>>> XADataSource can participate in distributed TX, while DataSource
>>>>> always will use a local (different) exception. At least this is
>>>>> what I have understood from the JDBC spec what the main difference
>>>>> between DataSource and XADataSource is. See
>>>>> http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/xadistra.htm#i1066469.
>>>>> Can you try the same use case with "|OracleXADataSource| " instead
>>>>> of "OracleDataSource" and "XADataSource" instead of "DataSource"?
>>>>> Maybe that will help. :-)
>>>>>
>>>>> Have Fun
>>>>> Markus
>>>>>
>>>>> Adam Bien schrieb:
>>>>>> Forgot screenshot / configuration,
>>>>>>
>>>>>> adam
>>>>>>
>>>>>> Adam Bien schrieb:
>>>>>>> Hi All,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> I ran into following problem: Direct changes in the database
>>>>>>> (SQL updates) performed in the same Bean-method, and so
>>>>>>> transaction, are not visible for the EntityManager (subsequent
>>>>>>> find).
>>>>>>>
>>>>>>> Even invoking EntityManager#clear does not solve the problem.
>>>>>>> The problem is strange, I suspect that the DB-connection from
>>>>>>> the datasource does not participate in the TX. This would
>>>>>>> explain such behaviour.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Server: Glassfishv2u2
>>>>>>>
>>>>>>> Database: OracleXE
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> The Bean looks like:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> @Stateless
>>>>>>>
>>>>>>> @Remote(VehicleManager.class)
>>>>>>>
>>>>>>> @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
>>>>>>>
>>>>>>> public class VehicleManagerBean implements VehicleManager {
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> @PersistenceContext
>>>>>>>
>>>>>>> private EntityManager entityManager;
>>>>>>>
>>>>>>> @Resource(mappedName = "jdbc/db1")
>>>>>>>
>>>>>>> * private DataSource dataSource;*
>>>>>>>
>>>>>>> @EJB
>>>>>>>
>>>>>>> private DataService dataService;
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> public Vehicle findChangeDBClearFind(int id, String newColor) {
>>>>>>>
>>>>>>> Vehicle vehicle = this.find(id);
>>>>>>>
>>>>>>> try {
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> //changes the color of the vehicle directly in the DB
>>>>>>>
>>>>>>> * this.changeVehiclesColor(id, newColor);*
>>>>>>>
>>>>>>> * * } catch (Exception e) {
>>>>>>>
>>>>>>> throw new EJBException("Problem executing statement"
>>>>>>> + e, e);
>>>>>>>
>>>>>>> }
>>>>>>>
>>>>>>> this.entityManager.clear();
>>>>>>>
>>>>>>> return this.find(id);
>>>>>>>
>>>>>>> }
>>>>>>>
>>>>>>> private void changeVehiclesColor(int id, String color) throws
>>>>>>> Exception {
>>>>>>>
>>>>>>> Connection connection = this.dataSource.getConnection();
>>>>>>>
>>>>>>> Statement statement = connection.createStatement();
>>>>>>>
>>>>>>> String sqlStatement = "update vehicle set color=\'" +
>>>>>>> color + "\' where id=" + id;
>>>>>>>
>>>>>>> System.out.println(sqlStatement);
>>>>>>>
>>>>>>> int updatedRecords = statement.executeUpdate(sqlStatement);
>>>>>>>
>>>>>>> if (updatedRecords != 1) {
>>>>>>>
>>>>>>> throw new IllegalStateException("Vehicle with id: " +
>>>>>>> id + " cannot be updated!");
>>>>>>>
>>>>>>> }
>>>>>>>
>>>>>>> connection.close();
>>>>>>>
>>>>>>> statement.close();
>>>>>>>
>>>>>>> }
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> I deployed the same code to WLS 10 and it worked - the changes
>>>>>>> were visible. I suppose it is probably a configuration problem.
>>>>>>> I tried with different DataSource configurations - with no
>>>>>>> effect. I attached my current configuration as well (screenshot).
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Hopefully I overlooked something. If not - I will file it as a bug.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Thank you in advance,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> regards,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> adam bien
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
http://www.xing.com/go/invita/58469