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

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Fazool
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!
Reply | Threaded
Open this post in threaded view
|

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

Ludovic Orban-2
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 <[hidden email]>

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



Reply | Threaded
Open this post in threaded view
|

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

Ludovic Orban-2
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 <[hidden email]>
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 <[hidden email]>

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




Reply | Threaded
Open this post in threaded view
|

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

Fazool
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
>>
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

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

Ludovic Orban-2
Isolation level is the responsibility of the database, not the transaction manager. If you believe Postgres behaves incorrectly, you should ask them for support, not us.

I nevertheless searched a bit on the problem and found this page: http://www.postgresql.org/docs/8.4/static/transaction-iso.html which seems to describe limitations of Postgres's serializable isolation level implementation. But again, you should discuss that issue on the Postgres forums and eventually come back here if those guys believe the problem happens because of the TM.


2011/10/20 Fazool <[hidden email]>

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 <[hidden email]>
>
>> 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 <[hidden email]>
>>
>>>
>>> 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
>>>
>>>
>>>
>>
>
>

--
View this message in context: http://old.nabble.com/%22Multiple-BTM-with-multiple-jdbc-datastores%22-%2B-%22jdbc-prepareStatements%22-tp32685902p32692733.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