« Return to Thread: "Multiple BTM with multiple jdbc datastores" + "jdbc prepareStatements"

Re: "Multiple BTM with multiple jdbc datastores" + "jdbc prepareStatements"

by Fazool :: Rate this Message:

Thanks for the reply Ludovic. Kindly see below.

Ludovic Orban-2 wrote:

Nothing in JTA or BTM prevents you from using the serializable isolation level. Maybe your database has such limitation though.

I am using Postgres. Please note that I am using two datastores (2 postgres servers) and two BTMs. I ran the following experiment:

I have two Postgres servers, PG1 and PG2. In each server, I have a table MY_TABLE with a single row containing one integer column, A. Lets denote PG1(A) to be the value of A in the single row of MY_TABLE on database server PG1.

I initialize them to the following:
PG1(A) = 1
PG2(A) = 2

Now, I start two separate java processes P1 and P2, using separate BTMs but both use PG1 and PG2 as data source. I set serialization level to 'serializable'. I run the processes concurrently and simulate the following (by using sleep):

P1 P2
===========================
btm.begin()
Read PG1(A)
btm.begin()
Update PG1(A) = 3
Update PG2(A) = 4
btm.commit()
Read PG2(A)
btm.commit()

On P1, the value read for PG1(A) is '1', while for PG2(A) is '4'. This is wrong, as in serializable isolation, P1 should have read value '2' for PG2(A). The behaviour I get is read commited isolation level. Can you kindly comment?

Thanks for your time.

Ludovic Orban-2 wrote:

One last thing: you can definitely run multiple transaction managers against
a single DB but you have to make sure every single BTM instance has a
different serverId.

2011/10/20 Ludovic Orban <lorban@bitronix.be>

> Hi,
>
> Nothing in JTA or BTM prevents you from using the serializable isolation
> level. Maybe your database has such limitation though.
>
> If you want to use prepared statements, just make sure you always call
> Connection.prepareStatement() with the exact same SQL queries and enable the
> prepared statement cache and the BTM connection pool will do the rest for
> you.
>
> Just keep in mind that not all databases benefit from preparing statements.
> Depending on the DB / JDBC driver implementations you may not get any
> performance benefit.
>
> Ludovic
>
>
>
> 2011/10/19 Fazool <fazoolmein@gmail.com>
>
>>
>> Hi,
>>
>> I am using BTM for prototyping a shared nothing database; where a
>> transaction can touch multiple databases so I need two-phase commit. I
>> will
>> appreciate if you can answer the following:
>>
>> 1) As per documentation, I can use multiple BTM with a single datastore. I
>> want to use multiple BTMs (running on different machines) for multiple
>> jdbc
>> datastores, where a transaction can update rows on different datastores.
>> According to my experiments, we cannot have 'Seriablizable' isolation
>> level
>> in this context. Is this correct?
>>
>> 2) I am using prepareStatements in jdbc. As I see, I have to 'prepare' a
>> prepareStatement everytime I want to run a query. For example:
>>
>> btm.begin()
>> PrepareStatement p = con.prepareStatement("...");
>> p.setInt(1,1);
>> ...
>> p.executeUpdate();
>> btm.commit();
>>
>> Creating a statement everytime I want to run a transaction has performance
>> penalties. I am unable to do something like:
>>
>> PrepareStatement p = con.prepareStatement("...");
>>
>> btm.begin()
>> p.setInt(1,1);
>> p.executeUpdate();
>> btm.commit();
>>
>> btm.begin()
>> p.setInt(1,2);
>> p.executeUpdate();
>> btm.commit();
>>
>> i.e. use the same prepare statement multiple times. Is setting a high
>> value
>> of PreparedStatementCacheSize going to improve efficiency of
>> "con.prepareStatement("...");" within a btm.begin()/end()? I ran some
>> basic
>> experiments, and PreparedStatementCacheSize didnt help much. Can you
>> kindly
>> comment on how to make this efficient?
>>
>> Thank you very much!
>>
>> --
>> View this message in context:
>> http://old.nabble.com/%22Multiple-BTM-with-multiple-jdbc-datastores%22-%2B-%22jdbc-prepareStatements%22-tp32685902p32685902.html
>> Sent from the Bitronix Transaction Manager mailing list archive at
>> Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>> http://xircles.codehaus.org/manage_email
>>
>>
>>
>

« Return to Thread: "Multiple BTM with multiple jdbc datastores" + "jdbc prepareStatements"